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

asales

Expert
Licensed User
Longtime User
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.
 

jsanchezc

Member
Licensed User
Longtime User
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
 
Upvote 0

sorex

Expert
Licensed User
Longtime User
right, but he uses sqlite and I don't know if something like the mssql/mysql methods exist.
 
Upvote 0

asales

Expert
Licensed User
Longtime User
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:
B4X:
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?
 
Upvote 0

asales

Expert
Licensed User
Longtime User
Select max(_id) From...

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

B4X:
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"
 
Upvote 0

sorex

Expert
Licensed User
Longtime User
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.
 
Last edited:
Upvote 0

sorex

Expert
Licensed User
Longtime User
so rowid will return the autoincrement field / primary key whatever its name is?
 
Upvote 0

asales

Expert
Licensed User
Longtime User
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:

B4X:
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.
 
Upvote 0

keirS

Well-Known Member
Licensed User
Longtime User
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.
 
Upvote 0

keirS

Well-Known Member
Licensed User
Longtime User
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.

B4X:
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.
 
Upvote 0
Top