SQLite - Last Row Insert ID
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
-
- Posts: 641
- Joined: Tue Jul 29, 2014 12:52 am
- Location: Brazil
SQLite - Last Row Insert ID
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!
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!
Re: SQLite - Last Row Insert ID
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.
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.
Re: SQLite - Last Row Insert ID
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.
Re: SQLite - Last Row Insert ID
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):
And it also has a nice link to the SQLite documentation ;-)
Have fun!
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
Have fun!
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!
-
- Posts: 641
- Joined: Tue Jul 29, 2014 12:52 am
- Location: Brazil
Re: SQLite - Last Row Insert ID
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!
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
________________________________________
To ";" or not to ";" that is the question
Re: SQLite - Last Row Insert ID
Every table in sqlite already has an unique rowid by design. https://www.sqlite.org/autoinc.html
Re: SQLite - Last Row Insert ID
ATTENTION, that rowid changes without notice, it's used by sqlite, never trust on it.rinzwind wrote:Every table in sqlite already has an unique rowid by design. https://www.sqlite.org/autoinc.html
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w
Re: SQLite - Last Row Insert ID
Really? Cite?
Re: SQLite - Last Row Insert ID
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.