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.
Stef MerlijnDeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shanmuga SundaramDirector of Software EngineeringCommented:
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
0
Stef MerlijnDeveloperAuthor Commented:
Thanks for the info.
The part that needs clarification for me is how to handle a large number of records.
0
JohnjcesCommented:
First it really helps in doing anything with large datasets to set your Database and query cursor to point at the server. This was 100,000 records are pushed to your local client.

But to do a query of the top X reccrds then the next top X records, here is one way that works well. It acts similarly to MySQL's LIMIT 10, 1000 which will give you 1000 records starting at record 10. LIMIT X, Y.

To start I want to pull the top 100 employees starting at 0 or the first record. I will put in an ADOQuery

SELECT     TOP 100 *
FROM         Employees
WHERE     (EmployeeID NOT IN
                          (SELECT     TOP 0 EmployeeID
                            FROM          Employees
                            ORDER BY EmployeeID))

When I want the next 100, I will change the 0 in my subquery to 100. So...

SELECT     TOP 100 *
FROM         Employees
WHERE     (EmployeeID NOT IN
                          (SELECT     TOP 100 EmployeeID
                            FROM          Employees
                            ORDER BY EmployeeID))

and etc.

In summary MySQL's LIMIT X, Y in MSSQL :=

SELECT     TOP Y *
FROM         Employees
WHERE     (EmployeeID NOT IN
                          (SELECT     TOP X  EmployeeID
                            FROM          Employees
                            ORDER BY EmployeeID))

Hope this helps you. I did test on my table and works in ADOQuery and works best and quicker using server side cursors.

JOhn
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Stef MerlijnDeveloperAuthor Commented:
Thank you very much. This helped a lot.
0
Geert GOracle dbaCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.