[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

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.
0
Stef Merlijn
Asked:
Stef Merlijn
1 Solution
 
Shanmuga SundaramCommented:
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
 
Stef MerlijnDeveloperAuthor Commented:
Thank you very much. This helped a lot.
0
 
Geert GruwezOracle 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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now