Accessing _rowid_

[sandy771]

I am querying an sqlite database with the following schema

CREATE TABLE Members (id INTEGER NOT NULL PRIMARY KEY, permanent INTEGER)

My query (via TUniQuery) is "select _rowid_ from members"

The query returns 22 rows

However a subsequent

UniQuery1->FieldByName("_rowid_")->AsString

gives an exception UniQuery1:Field '_rowid_' not found

The same query but on a table without a defined primary key, fails in the same way

The same command in a sqlite shell returns the rowid's

Any idea why this may be?


Thanks

[stevel]

rowid is a reserved (special) word ie. column name is SQLite, and even if the table does not have this column defined, you can still select it via SQL.

So the following should work:

Code: Select all

UniQuery1->FieldByName("rowid")->AsString

[AlexP]

Hello,

If you have an INTEGER PRIMARY KEY filed in the table and you are using the rowid field in the query - SQLite will return the keyfield values: http://www.sqlite.org/autoinc.html . If there is no such field, SQLite returns the "rowid" field name for columns in the query that have names ROWID, _ROWID_ or OID.

[sandy771]

Thanks Alex - I was just replying to your email.

There seems to be a slight discrepenacy betwen SQlite and UniDac then (not a problem now I am aware of it)

Using a query of the form "select _rowid_ from testtable"

The correct number of rows are returned but i get an error _rowid_ not found. However rowid (no underscores seems to work)

[AlexP]

As I wrote you earlier, ROWID, _ROWID_ and OID are synonyms in a query, but SQLite returns ROWID for all these fields. Therefore, you can access this fields by ROWID name only, not depending on which names you use in the query.