asked on

How to approach MS SQL Server database from Delphi

Hi,

For my application I have migrated from MS Access to SQL Server. I'm currently figuring out what would be a good way to retrieve and store data from and to the database from within Delphi.
I use a ADOConnection to cennect to MS SQL Server 2005.
To retrieve data I currently use StoredProcedures.

Suppose I have a table "CUSTOMERS" that contains 100.000 records. Of course I don't want to retrieve all records from the database at once, but only a small part of it.
I know I can use "Select TOP 200", but how can I get to records 201 and up?

What is the best approach to accomplish this?
Should I use TADOQuery or TADOStoredProc to access the data?
How to deal with record locking?

Please feel free to ask any questions to clearify mine.

Shanmuga Sundaram D

You can use both tadoquery and tadostoredproc objects. This depends on what you want to do. Most tend to use tadostoredproc because we write stored procs for just about anything to add an extra layer of abstraction between the front end and the database engine. The reason is that it is easier to maintain that way for us. Also if you use a TAdostoredproc and then make a minor change to the stored proc, there is need to redistribute the app . With a TAdoQuery object, you should make changes to the delphi code, recompile the app and then tell everyone that there are changes made in the app and to update the exe with the latest version.Delphi's ADO objects have the Locate method which does the same thing. See the documentation for TCustomADODataSet.Locate in your help. Also please refer

http://books.google.co.in/books?id=HWo0H6GYuqYC&pg=PA389&lpg=PA389&dq=TADOQuery+or+TADOStoredProc&source=web&ots=bcxQ4ne5y_&sig=-w4umSaR-KK2OZNbFjacDFQnZxY&hl=en&sa=X&oi=book_result&resnum=6&ct=result#PPA391,M1

for more details

Stef Merlijn

ASKER

Thanks for the info.
The part that needs clarification for me is how to handle a large number of records.

ASKER CERTIFIED SOLUTION

Johnjces

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Stef Merlijn

ASKER

Thank you very much. This helped a lot.

Geert G

my 2 cents:

there is still the ability of assigning a RANK() to a record
and working according to this RANK()

SELECT * FROM (
select RANK() OVER (ORDER BY EMPLOYEEID) __RANKING, E.*
from EMPLOYEE E) A
WHERE __RANKING >= :START AND __RANKING < :START + :DELTA

Parameters:
START = 1
DELTA = 100

you need to set columns starting with '__' invisible