SQLite function to return rowid on INSERT

[KeineAhnung]

Hi,

I would like to write a function within my QML project that inserts data into a SQLite table and returns the rowid of the inserted line on success.
This is what i got so far:


function saveData(name, mode, adjust) {
var db = getDatabase();
var res = "";
db.transaction(function(tx) {
var rs = tx.executeSql('INSERT INTO profiles ' +
'VALUES (?,?,?);', [name, mode, adjust]);
if (rs.rowsAffected > 0) {
res = tx.executeSql('SELECT last_insert_rowid();');
} else {
res = "Error";
}
});
console.log(res)
return res;
}

But I do net get an integer back, I get an object. How is this done right and is there a better way to do it than what I tried?

Thanks.

[ChrisW67]

The executeSql() call returns an SQLResultSet object that already contains the insertId if one was generated. No need for a separate query.
http://www.w3.org/TR/2009/WD-webdatabase-20091029/#database-query-results

BTW: Does the profile table have an INTEGER PRIMARY KEY column that can provide the auto-generated id?

[KeineAhnung]

Thanks for the answer, I guessed that there is a better way to do it but I am still struggling to find out how to access this insertId property.

At the moment I do not have a primary key defined. My understanding was that a SQLite table always has as ID column rowid unless I prevent it. Is it not possible to access rowid with insertId?


function initialize() {
var db = getDatabase();

db.transaction(
function(tx) {
tx.executeSql('CREATE TABLE IF NOT EXISTS profiles ('+
'name TEXT UNIQUE,' +
'mode INTEGER,' +
'adjust INTEGER)');
});
}

[ChrisW67]

Yes, you access that internal id by declaring a column of type "integer primary key" , effectively an alias for the internal row id, and then it is returned as the last insert id. Without that alias the internal row id is purely internal and hidden.

The last insert id should be available as rs.insertId at line 7 in your original code.

[KeineAhnung]

Thanks for the help. I've been playing around a little the rowid is returned with rs.insertId even if I did not declare a primary key.