Page 1 of 1
AdoServer Find method for SQL files
Posted: Mon Oct 25, 2021 6:59 pm
by jjgregg
Anyone with sample code to show how to make the find method work on an SQL AdoServer? What is the best methodology for locating records much like the good old seek method used with dBase files? I can´t get the find method to work with SQL tables in a VO 2,8sp4 appiication, it doesn't even register a VO error, the program just exits and I'm unable to know what happened or what is causing it to fail.
Appreciate any help.
John
AdoServer Find method for SQL files
Posted: Mon Oct 25, 2021 9:07 pm
by Jamal
.
AdoServer Find method for SQL files
Posted: Mon Oct 25, 2021 9:35 pm
by robert
John,
The AdoServer:Find() method is different from the DbServer:Seek().
When you select data from a SQL backend you usually filter the results by adding a WHERE clause. This is the preferred way to bring only the rows that you need to the client workstation
Code: Select all
oServer := AdoServer{"Select * from customers where CompanyName = 'SomeName'",...}
The Find() method does not go to the server, but seeks in the data that has been fetched from the server before (this data is stored in an AdoRecordSet)
The criteria that you can use for the Find method are limited:
https://docs.microsoft.com/en-us/sql/ad ... rver-ver15
And if you do not need the bind the data to a form / browser your even better of by using an AdoCommand:
Code: Select all
oCmd := AdoCommand{}
oCmd:ActiveConnection := oConn
oCmd:CommandText := "Select * from customers where CompanyName = 'SomeName'"
oRecordSet := oCmd:Execute()
? oRecordSet:RecordCount
if oRecordSet:RecordCount > 0
? oRecordSet:FieldGet(1)
endif
AdoServer Find method for SQL files
Posted: Mon Oct 25, 2021 11:47 pm
by jjgregg
Thanks Robert, I agree, I´ve been using AdoCommands for most everything but for a moment I thought it would be great to have something like the old dbServer:Seek() command.
In the example above,, I guess one should always scan all records returned by a recordset to locate the one record we are looking for much like the dbServer:Seek() method which returns the first record.
Thanks for your help.
John
AdoServer Find method for SQL files
Posted: Tue Oct 26, 2021 3:57 am
by jjgregg
Thanks for your help Jamal. I am using SQL tables so I have to use AdoServer instead of AdoDBServer, the Seek method is different between these 2:
FOR ADODBSERVER
METHOD Seek(uSearchExpr, lSoftSeek, lLast) CLASS AdoDbServer
LOCAL lOk AS LOGIC
lOk := SUPER:Seek(uSearchExpr, lSoftSeek, lLast)
SELF:Notify(NOTIFYFILECHANGE)
RETURN lOk
FOR ADOSERVER
Prototype
METHOD Seek ( aKeys, lOption ) CLASS AdoServer
Argument(s)
aKeys An array of values to compare against each corresponding column in the index
lSeek (Optional) A SeekEnum value that specifies the type of comparison to be made Default is adSeekFirstEq
the server will send a NOTIFYINTENTTOMOVE before the operation and a NOTIFYRECORDCHANGE after the operation.
--------------------------------
Thanks for your suggestions.
Best regards,
John