CODEWITHSUNDEEP
sqliterowid
user2929075
user2929075

Reputation: 71

SQLite Find ROWID of most recent or next ROWID

So I have a table with data about an image. The table looks something like this...

ROWID|title|description|file_path

The file path contains the name of the image. I want to rename the image to match the ROWID.

How do I get the latest ROWID? I need to also account for rows that have been deleted as I am using this as an autoincremented primary key. Because, if a row within the table has been deleted it is possible for the table to look like this...

1|title A|description A|..\fileA.jpg
2|title B|description B|..\fileB.jpg
5|title E|description E|..\fileE.jpg
7|title G|description G|..\fileG.jpg

On top of that there could be one or more rows that have been deleted so the next ROWID could be 10 for all I know.

I also need to account for an fresh new table or a table that has had all data deleted and the next ROWID could be 1000.

In summary, I guess the real question is; Is there a way to find out what the next ROWID will be?

Upvotes: 2

Views: 1950

Answers (5)

Sergio
Sergio

Reputation: 1

As mafu: I think last_insert_rowid is what you want, usually. So: make an insert, call last_insert_rowid and add 1, make a ROLLBACK. In the 98% of case this is the last ROWID

in SWIFT

var database: OpaquePointer?
var rowID : Int
if sqlite3_open(path, &database) == SQLITE_OK {
   sqlite3_exec(database, "BEGIN TRANSACTION", nil, nil, nil)
   sqlite3_exec(database, "INSERT INTO dettaglioclienti (idcliente) VALUES ('kkkkkk')", nil, nil, nil)
   rowID = sqlite3_last_insert_rowid(database)
   sqlite3_exec(database, "ROLLBACK", nil, nil, nil)
   sqlite3_close(database)
}
let nextRowID = rowID+1

Upvotes: 0

mafu
mafu

Reputation: 32650

I think last_insert_rowid is what you want, usually.

Note that the rowid behavior is different depending on the autoincrement flag - either it will monotonically increase, or it will assume any free id. This will not usually affect any smaller use cases though.

Upvotes: 0

user2929075
user2929075

Reputation: 71

Unfortunately neither of these methods completely worked the way I needed them to, but what i did end up doing was....

insert data into table with the fields I needed the rowid for filled with 'aaa' then updated the rows with the data.

This seemed to solve my current issue. Hopefully it doesn't cause another issue down the road.

Upvotes: 0

jeremy
jeremy

Reputation: 4314

What language? Looks like the c API has the following function:

sqlite3_int64 sqlite3_last_insert_rowid(sqlite3*);

http://www.sqlite.org/c3ref/last_insert_rowid.html

You could also just do:

select MAX(rowid) from [tablename];

Upvotes: 0

SqliteDog
SqliteDog

Reputation: 86

If you have specified AUTOINCREMENT in primary key field and table is not empty this query will return latest ROWID for table MY_TABLE:

SELECT seq
FROM sqlite_sequence 
WHERE name = 'MY_TABLE'

Upvotes: 3

Related Questions