Skip to Content

Update of static cursor and repos to last record

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Feb 01, 2017 at 12:38 PM

    "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.

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 01, 2017 at 12:53 PM

    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).

    Add comment
    10|10000 characters needed characters exceeded

    • 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?

  • Jan 31, 2017 at 12:57 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 31, 2017 at 04:45 PM

    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?

    Add comment
    10|10000 characters needed characters exceeded