SQLite - Last Row Insert ID

Creating desktop or client-server database solutions?

Moderators: Klaus, FourthWorld, heatherlaine, robinmiller, kevinmiller

Post Reply
simon.schvartzman
Posts: 278
Joined: Tue Jul 29, 2014 12:52 am
Location: Brazil

SQLite - Last Row Insert ID

Post by simon.schvartzman » Thu Jun 16, 2016 10:57 pm

Hi team, I know this has been discussed before but after spending two hours browsing the internet/forum without success please help me with a simple / complete example about how to handle the last inserted row on an SQLite database.

What I want to achieve is:

- An SQLite DB where the first column holds an unique ID
- When I insert a new record, the new ID should be the previous one + 1

And the simplest way to do it would be....

Many thanks in advance!

D4vidrim
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 58
Joined: Fri May 31, 2013 9:32 am

Re: SQLite - Last Row Insert ID

Post by D4vidrim » Fri Jun 17, 2016 8:05 am

Hi,
I'm not enough familiar with SQLite, but you should look for sequences.
I've found something like this:

create table Categories (
pk_categoryid integer primary key autoincrement,
category_name text
);

A sequence does exactly what you need.

Mikey
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 675
Joined: Fri Jun 27, 2008 9:00 pm

Re: SQLite - Last Row Insert ID

Post by Mikey » Fri Jun 17, 2016 2:11 pm

You should be reading up on SQLite. This is not a LC issue. The SQLite documentation is very easy to read and clear, and includes flowcharts on how to construct statements. For this case, look up, ROWID, and "primary key". In sqlite the first integer, indexed, autoincrement, not-nullable in a table is an alias for ROWID. In other words, you don't have to have a primary key, if you don't want one.

AxWald
Posts: 351
Joined: Thu Mar 06, 2014 2:57 pm

Re: SQLite - Last Row Insert ID

Post by AxWald » Sat Jun 18, 2016 4:04 pm

Hi,

when working with a database engine it's priceless to have a good database manager software.
Should you use a Mozilla based browser I suggest SQLite Manager. If not, get one ;-)

A quick look there showed me that the definition is (from a working database):

Code: Select all

"ID" INTEGER PRIMARY KEY  NOT NULL
And it also has a nice link to the SQLite documentation ;-)

Have fun!
Livecode programming until the cat hits the fan ...

simon.schvartzman
Posts: 278
Joined: Tue Jul 29, 2014 12:52 am
Location: Brazil

Re: SQLite - Last Row Insert ID

Post by simon.schvartzman » Sun Jun 19, 2016 5:28 pm

Many thanks to all of you for your help.

I ended up using an alternative solution. As my SQLite DB is linked to a data grid every time I need to get the information I use the "dgNumberOfRecords" parameter which gives me what I was looking for.

Probably is not the best or most elegant solution but it works...

Best!
Simon
________________________________________
To ";" or not to ";" that is the question

rinzwind
Posts: 131
Joined: Tue May 01, 2012 10:44 am

Re: SQLite - Last Row Insert ID

Post by rinzwind » Tue Jul 05, 2016 7:58 pm

Every table in sqlite already has an unique rowid by design. https://www.sqlite.org/autoinc.html

MaxV
Posts: 1574
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: SQLite - Last Row Insert ID

Post by MaxV » Wed Jul 06, 2016 10:49 am

rinzwind wrote:Every table in sqlite already has an unique rowid by design. https://www.sqlite.org/autoinc.html
ATTENTION, that rowid changes without notice, it's used by sqlite, never trust on it.
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

Mikey
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 675
Joined: Fri Jun 27, 2008 9:00 pm

Re: SQLite - Last Row Insert ID

Post by Mikey » Wed Jul 06, 2016 1:19 pm

Really? Cite?

Mikey
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 675
Joined: Fri Jun 27, 2008 9:00 pm

Re: SQLite - Last Row Insert ID

Post by Mikey » Wed Jul 06, 2016 2:17 pm

I'm glad this topic came up. After doing more research on this, it seems that the only time that ROWID will change is if VACUUM is called manually. However, if you use VACUUM, you can mitigate that behavior by assigning a row that is INTEGER PRIMARY KEY. In that case, VACUUM will not reassign rowid's.

Post Reply

Return to “Databases”