I have a static cursor used in Delphi by TAdsQuery component displayed in a TDBGrid:
select * from a where id not in (select id from b where key = :Key) sort by name.
1. When I want to update this cursor with updates made to the underlying tables by the same or other users, I have to close and re-open the query. If value of parameter :Key has changed I also have to do an unprepare between close and open. Is this correct?
2. When I update the contents of the static cursor I want to remind the record selected in the grid before the update and reposition to that record after the update. How can this be done? Bookmark doesn't work, because the internal record number seems to be a temporary one for the records in the static cursor. If a record is deleted or inserted in the meanwhile all subsequent records have a records number -1 rep. +1.
Using a live cursor doesn't work because I'm referencing a second table.
For using a FindKey or FindNearest I didn't find a way to define it in IDE to use it.
Any hints appreciated.
"rowid" is a string field. So I suggest to store the value of the rowid field in a variable, and later on use TAdsQuery.Locate('rowid', my_variable, []) to reposition the grid. Usage DisableControls/EnableControls is recommended, ideally the user don't see any scrolling.
And how do I read the rowid? Query.FieldByName('rowid').asString fails with "column rowid not found".
Test your SQL in ARC (advantage data architect). You shall see the 18-char long rowid field values.
Btw. using ARC you also can see all fields of all tables you use, if there is a primary key and of what type it is. ARC is the swiss army knife for all ADS operations. You can do ISAM type filtering and advanced SQL queries with an SQL debugger and query plan analyzer.
Ok I checked rowid in ARC. There I can see the column. But using Delphi component TAdsQuery this rowid is not found. TAdsQuery.FindFiled('rowid') is nil. I checked in debug mode that TAdsQuery.SQL.Text contains the rowid in select statement.
Is there a special way to read it?
Do you use persistent fields, created at designtime? Then you have to create a new persistent field for rowid. Doubleclick on the TAdsQuery component, rightclick in the window, then "add all fields".
Yes, I use persistant fields... Sorry, that I didn't see it... Of course I have to add the field to the persistent field list ... Now it works...
RowID is a virtual column calculated from the physical position of the record ... I'd rather use a primary key field to locate the correct record (rowid might change when inserting/deleting/updating records).
If I want to do this as a service for all my tables. How can I find out the primary key field to read it? The static cursor seems not to have any properties to recognize this...
But, sequence of rowid is still the same like the recno of the base table. If I do an "order by name" (name is not a primary index) rowid is not in ascending order. If the selected record is gone after updating the static cursor, I want to repos. to the record next to the selected record.
Neither a primary key, recno nor rowid would help in this situation.
So I would try to do a locate, but locate just finds the exact string. If locate is just runing thru the table record by record I could do this by my own and implement some logic to do some of kind "locate nearest". Correct?
Or any other hint?
1. Prepare/UnPrepare has no function in Delphi ADS Components. You can see this in the source code: "Description: indicate prepared, but really do nothing" (Prepare function).
2. Use ROWID pseudocolumn of Table "a" for this.
Thanks, for your answer. So I don't have to care for Prepare/UnPrepare.
regarding 2:
I would use: "select rowid, field1, field2, ... from a where id not in (select id from b where key = :Key) sort by name". But AQuery.FindField('rowid') is nil. How can I read this rowid?
And: how can i reposition to that rowid after a Close/Open?