Autoincrement value from SQLite?

Other
#1

First time using either SQLite or Corona’s database functionality…

In MySQL, we can get the next autoincrement value of a table by reading from MySQL’s schema table, like this:

SELECT `AUTO_INCREMENT` FROM INFORMATION\_SCHEMA.WHERE TABLE\_SCHEMA='dbName' AND TABLE\_NAME='tblName';

As far as I can tell from the documentation, the alternate in SQLite is to read the value of a ‘seq’ field from the sqlite_sequence table, so I’m assuming this:

SELECT `seq` FROM `sqlite_sequence` WHERE name='tblName';

Not entirely sure whether this is correct or not, but moving on - I’m only selecting one record here, and some documentation says to do that using db:first_row but other documentation says that this isn’t possible with SQLite3, or perhaps just with Corona’s implementation, so to do something like this instead:

local foo = db:nrows( [[SELECT `seq` FROM `sqlite_sequence` WHERE name='tblName';]] ) local bah = foo[1] print(bah.seq)

But this is just erroring out for me on the foo[1] part, because foo is a function and not a table.

Similarly, rewriting it to this results in nothing being printed:

for foobah in db:nrows( [[SELECT `seq` FROM `sqlite_sequence` WHERE name='tblName';]] ) do print(foobah.seq) end

So my conclusion is that perhaps foo is a function rather than a table if the recordset is empty? In which case, is my initial SQL incorrect? Does sqlite_sequence not actually exist as a table? And SQL errors aren’t just output to the simulator console?

Or am I still just trying to read the result wrongly?

Further up in the same code file, I have this…

local db = sqlite3.open( system.pathForFile( "local.db", system.DocumentsDirectory ) ) db:exec( [[CREATE TABLE IF NOT EXISTS tblName ( fldID INTEGER PRIMARY KEY autoincrement, fldName, fldName, fldName );]] )

(fldName is of course a bunch of actual field names really - just simplifying for the sake of this post)

I can see the resulting local.db file within the sandbox so the library itself is definitely working.

#2

Finally had chance to jump back on to this one, and cracked it.

Turns out SQLite simply doesn’t create an entry in the sqlite_sequence database until after the first record insertion, contrary to MySQL which would create an entry when the table is created.

So in case this is useful to anybody else, this is what I’ve ended up doing:

local nextID = 1 for result in db:nrows( [[SELECT `seq` FROM `sqlite_sequence` WHERE name='tblTableName']] ) do nextID = result.seq + 1 end

Now nextID is the next available autoinc value, which is just 1 if no records yet exist in tblTableName. Handy in showing the assumed record ID prior to its creation. Note ‘assumed’ because if something else triggers an insert prior to inserting the record on display, the value actually used when the new record is inserted will be different.

#3

Finally had chance to jump back on to this one, and cracked it.

Turns out SQLite simply doesn’t create an entry in the sqlite_sequence database until after the first record insertion, contrary to MySQL which would create an entry when the table is created.

So in case this is useful to anybody else, this is what I’ve ended up doing:

local nextID = 1 for result in db:nrows( [[SELECT `seq` FROM `sqlite_sequence` WHERE name='tblTableName']] ) do nextID = result.seq + 1 end

Now nextID is the next available autoinc value, which is just 1 if no records yet exist in tblTableName. Handy in showing the assumed record ID prior to its creation. Note ‘assumed’ because if something else triggers an insert prior to inserting the record on display, the value actually used when the new record is inserted will be different.