SQLite INSERT - How to get the New ID

[Traxgeek]

Hi,

I've gotten to the stage where I am now want to INSERT a record into my SQLite database
When I created the table in my DB (CREATE TABLE...), I set the first field/column of that Table to be :

integer PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE

like this

CREATE TABLE DBProperties (PID integer PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, ........

This works just fine. And I progressed to the point where...
I can now UPDATE a record BUT cannot for the life of me see how to get my new DB_ID value back out so of my newly created / INSERTed record...

Is there a way for me to get the DB_ID, that SQLite just created/inserted, rather than having to send my user back to a full list of DB records and expect them to fin and then select the record they've just entered ? ? ?

I note that the revExecuteSQL command simply returns the number of affected rows/records, a 0 or an ERR.... value
What I had hoped for was the newly created Record ID... I simply can't see how to get at this for a newly created record... Arrrgh...

TIA

Regards.

[Klaus]

Hi Traxgeek,

not sure, but I think you need to use another SQL command to get PID from the database.
Just supply the correct "WHERE" value.

Better you also fetch the PID together with all other data, so you can refer to it whenever needed!
Store it in a hidden field, custom property or in an array element, depending on how you return
the data from the db query.

That's how I do it


Best

Klaus

[dave_probertGA6e24]

Hi Trax,

Remember that SQLite has an automatic (hidden) auto incrementing 'rowid' field - which will contain the same value as any one that you create too.

There is an SQL command you can use with SQLite:

last_insert_rowid()
The last_insert_rowid() function returns the ROWID of the last row insert from the database connection which invoked the function. The last_insert_rowid() SQL function is a wrapper around the sqlite3_last_insert_rowid() C/C++ interface function.

Taken from the http://www.sqlite.org site language information.

That should do the trick.

Cheers,
Dave

[sturgis]

You can use "limit" to cheat. I forget how things work, but a) if your db is set up to always use a new number even if an older number is available, you could use
"select * from tablename order by pid desc limit 1"

If the table can recycle unused numbes (and even if its set up the first way, once it hits a certain value, forget what it is, I think it will start recycling values then also.. I think)

Either way, you can use the rowid instead in this situation.
"select * from USERS order by ROWID DESC limit 1" -- will return the whole row
"select PID from users order by ROWID dDESC limit 1" -- will just return the PID

Of course if the database is being continually actively updated this method might not work.

Oops, ignore this and go with daves answer. Does basically the same thing with MUCH less typing. (much much)

And I have a method to do it all at once.

You can do something like this..

revdatafromquery(,,2,"BEGIN; insert into users(userID,name,email,emailList) VALUES(null,'mike','mike@','false'); select last_insert_rowid();COMMIT")

Uses revdatafromquery so you actually get data back, does a begin transation, does the insert, selects the last_insert_rowid(); and commits it. Leave off the trailing ; after commit, revdatafromquery adds it. The rowid is returned as a result and can be then used in further queries/updates/deletes WHERE ROWID=theId

[Klaus]

Hi Trax,
Remember that SQLite has an automatic (hidden) auto incrementing 'rowid' field - which will contain the same value as any one that you create too.
There is an SQL command you can use with SQLite:

last_insert_rowid()
The last_insert_rowid() function returns the ROWID of the last row insert from the database connection which invoked the function. The last_insert_rowid() SQL function is a wrapper around the sqlite3_last_insert_rowid() C/C++ interface function.

Taken from the http://www.sqlite.org site language information.
That should do the trick.
Cheers,
Dave

Cool, thanks for the hint and link, Dave!