Android Question [SOLVED] Read ID of record after insert in sqlite database (master/detail)

[asales]

I have 2 tables (master/detail): Client and Phone.
I have 1 layout with client profile and I can add several fields with phones for this client.
There are primary keys in both tables and in Phone table there are a PK_ID_Client.
I use Maps.Put and DBUtils.InsertMaps to save the record in table Client.

Save the Client table is OK, but how I can check the ID created of Client in SQLite database to add in field PK_ID_Client and save Phone table?

How I can save this information (client profile and phones) in two tables simultaneously?

Thanks in advance.

 

[sorex]

is it an autoincrement id?

then try

select max(your_id) from yourtable ?

 

[jsanchezc]

I have 2 tables (master/detail): Client and Phone.
I have 1 layout with client profile and I can add several fields with phones for this client.
There are primary keys in both tables and in Phone table there are a PK_ID_Client.
I use Maps.Put and DBUtils.InsertMaps to save the record in table Client.

Save the Client table is OK, but how I can check the ID created of Client in SQLite database to add in field PK_ID_Client and save Phone table?

How I can save this information (client profile and phones) in two tables simultaneously?

Thanks in advance.

To get last identity inserted use, just after insert client row execute this query:
select last_insert_rowid();

In SQL Server you can execute: select @@identity to do the same

 

[sorex]

right, but he uses sqlite and I don't know if something like the mssql/mysql methods exist.

 

[LucaMs]

select last_insert_rowid();

 

[asales]

I use SQLite.
ID is a autoincrement field in Client table and integer field (PK) in Phone table.
"Select max(id)..." don't work.

my code:

Sub AddRecord
    Dim ListOfMaps As List
    ListOfMaps.Initialize

    Dim m As Map
    m.Initialize
    m.Put("name", edName.Text)
    m.Put("address", edAddress.Text)
    ListOfMaps.Add(m)
   
    DBUtils.InsertMaps(SQL1, "Client", ListOfMaps)
End Sub

How I can get ID (autoincrement) of this record right after insert?

How I can add the new field "phone" (Phone table) after DBUtils.InsertMaps in Client table?

 

[LucaMs]

Select max(id)

Select max(_id) From...

 

[asales]

Select max(_id) From...

I try this (after DBUtils.InsertMaps in Client table):

Dim Cursor1 As Cursor
Cursor1 = SQL1.ExecQuery("SELECT Max(_id) FROM Client")
Cursor1.Position=0
Log(Cursor1.GetInt("_id"))

but I get this error: "no such column: _id"

If I use the column "id" (SELECT Max(id) ...) I get this error: "column 'id' does not exist"

 

[Mahares]

You do this:

Dim MaxRec As Int
MaxRec=SQL1.ExecQuerySingleResult("SELECT max(ROWID) FROM Client" )

or you can also do this immediately after the insert:

Dim MaxRec As Int
MaxRec=SQL1.ExecQuerySingleResult("SELECT last_insert_rowid() FROM Client" )

 

[sorex]

but I get this error: "no such column: _id"

If I use the column "id" (SELECT Max(id) ...) I get this error: "column 'id' does not exist"

we gave examples, we don't know how your id is exactly named so you have to modify that.

 

[LucaMs]

It was my error!

SQLite works in a strange manner. If you have a field declared as INTEGER PRIMARY KEY it will be a "copy" of ROWID (which is an internal field).
ROWID has some aliases and I remembered also _id (perhaps _ID).

 

[sorex]

so rowid will return the autoincrement field / primary key whatever its name is?

 

[LucaMs]

ROWID is an internal autoincrement field (you can explicitly declare a table without rowid)

 

[klaus]

ROWIDs and the INTEGER PRIMARY KEY

 

[LucaMs]

https://www.sqlite.org/autoinc.html

 

[asales]

It was my error!

SQLite works in a strange manner. If you have a field declared as INTEGER PRIMARY KEY it will be a "copy" of ROWID (which is an internal field).
ROWID has some aliases and I remembered also _id (perhaps _ID).

Thanks @LucaMs. This works:

Cursor1 = SQL1.ExecQuery("SELECT Max(_rowid_) AS id1 FROM client")
Cursor1.Position=0 
Log(Cursor1.GetInt("id1"))

Now I get the ID of Client table, right after insert, and add the fields in Phone table with foreign key.

 

[keirS]

You could use my SQLExtended library. That wraps the insert method from the API which returns the ROWID. The advantage being it is faster as the API uses the
sqlite3_last_insert_rowid function to return the ROWID so you are only running one SQL statement not two.

 

[Mahares]

Post #9 already offered the solution using the last_insert_rowid() in one SQL statement.

 

[keirS]

Post #9 already offered the solution using the last_insert_rowid() in one SQL statement.

Not the same thing. Only one SQL statement is executed.

iRowId = oSQLE.insert("atable",cInsertStatement)

You solution uses one statement to do the insert and one statement to get the RowID. The API calls the sqlite3_last_insert_rowid function directly and is not wrapped in a SELECT statement so it is faster because there is no overhead in executing a second SQL statement.