Get rowid (SQLite)

Databases
1

I need to know the rowid value of a table, that has not primary key, to store it in the RowTag
Tried diferent sintax but no luck, ideas?

Var sql As String
sql = "SELECT * from " + table + " ;" 

Var rs As RowSet
Try
  rs = db.SelectSQL(sql)
Catch error as DatabaseException
  MessageBox("DB Error loading : " + table +" " +  error.Message)
  quit
end 

If rs <> Nil Then
  Box.RemoveAllRows
  
  While not rs.AfterLastRow
    Box.AddRow 
    for i as integer = 0 to rs.ColumnCount-1
      box.ColumnTypeAt(i)=DesktopListBox.CellTypes.TextField
      box.CellTextAt(box.LastAddedRowIndex,i)= rs.ColumnAt(i)
    next
    
// getting the rowid
    box.RowTagAt(box.LastAddedRowIndex) = rs.Column(rowid) // Not works, either "rowid"
    
    rs.MoveToNextRow
  Wend
 
  rs.Close
End If
2

This is what I use:

id = db.lastRowID

3

?

You say you use that as a Primary Key replacement ?

The documentation says:
LastRowID As Int64

Returns an Int64 containing the value of the last RowID added to any table in the database.

4

Not understanding, I do not look for the last rowId, I need the rowid of each record in the recordset

5

No, the table has not a primary key (I connot add one).
I need a rerefence to each record.

6

As far as I know, all SQLite databases have a rowid column unless they are declared WITHOUT ROWID.

If you declare a column as INTEGER PRIMARY KEY then it is a synonym for rowid.

But either way, you can get it with:

rs.Column("rowid").IntegerValue

like any other column.

7

Apparently, you are correct:

image
image1314×56 26.8 KB

Returns the correct Record (ID 2).

Now, where is rowID in the current documentation ?

I found it:

https://documentation.xojo.com/api/databases/sqlitedatabase.html

Search for Primary Keys:

Primary keys

All SQLite tables have an Integer Primary Key column. If you don’t explicitly create such a column, one will be created for you with the name “rowid”. If you create your own INTEGER PRIMARY KEY column, then rowid acts as an alias to that column. This means that a query that includes rowid will instead return the column that is the primary key.

8

select rowid from mytable should work for sqlite.

9

Trying to explain better:

A database SQLite exists, I did not define it.

The DB contains several tables, some with a PRIMARY KEY, others without it.
The user may choose which table to see, then I put all records in a list. The user, then, may choose a record in the list.
If the table has PRIMARY KEY I use that one to get that record and do the job.

If the table has not PRIMARY KEY, then, when user selects a record from the listBox how can I get that record?

// Don’t works
rs.Column(“rowid”).IntegerValue
This gives a DB error (column don’t exist"

10

Either you have a table with a PRIMARY KEY, and then you use that one (you are already doing this). Or, the table does NOT have an INTEGER PRIMARY key in which case you use rowid. And the syntax I suggested does work - I use it in my app. It may be that SELECT * … does not return it and you have to explicitly ask for it. E.g., I do:

reg = db.SelectSQL ("select rowid, date_received from messages where ...")

while  (reg.AfterLastRow=False)
    rowid = reg.Column("rowid").IntegerValue
    // Other stuff 
    reg.MoveToNextRow ()
wend

And this works with no trouble. I’ll check about selecting rowid.

Can you confirm that none of the tables are declared WITHOUT ROWID ?

11

Asking with google it seems that you must explicitly ask for the rowid (as I suspected, it seems that SELECT * does not give it to you. So you need to say SELECT ROWID, * … ).

12

so my answer 3 posts above was right !

13

The sintax is


Var sql As String
sql = "SELECT rowid, * from " + table + " ;"
14

rowid here = 0…

Var sql As String
sql = "SELECT * from " + table + " WHERE rowid=" + Str(MyRowID) + ";"

You get the Record ID you set…