How to get last inserted row id from sqlite in phonegap android

Question

I am creating an app in which i am inserting a data in the table. After inserting the data in the table i want to get the id of that row so i can do further operation depending on it. I tried to use last_insert_rowid() function of sqlite but found no luck. can any one tell which is the best way to get last inserted row id. Any idea is appreciated. Thanks in advance.

Intellection

last_insert_rowid() should have worked. Did your insert go through? – Gang

hope you are using the correct query SELECT last_insert_rowid(). Do not include the table name – Gang

i may use it in wrong way can you give me an example of it and how to pass the result to the variable. – Intellection

Answer 1

You can get the id of the last insert on a table like this -

tx.executeSql("INSERT INTO profile('name','label','list_order','category') values(?,?,?,?)", 
    [currentProfile.name, currentProfile.label, currentProfile.list_order, currentProfile.category], 
    function(tx, results){
        var lastInsertId = results.insertId; // this is the id of the insert just performed
    }, 
    failCB
)

The results.insertId in WebSQL is similar to mysql_insert_id() in MySQL -

mysql_query("INSERT INTO mytable (product) values ('kossu')");
printf("Last inserted record has id %d\n", mysql_insert_id());

Lunna

Exactly what i was looking for. Do you maybe know how i could get the row data i just inserted from this result set as well. – Extrude

@Extrude In the example above you can reference the currentProfile object. I imagine in your case you might have this inside of a loop. In that case you could wrap the tx.executeSql inside of a closure so that it keeps scope on the currentProfile object. – Lunna

sadly not. in my case it is not in a for-loop. but the data is retrieved from all the textbox obects. the problem is there are about 20. and was hoping there is an easy way to get a resource object that has all the properties set already from the result set. because I pass the object that is created in the success event and pass that on to the viewing page. – Extrude

Answer 2

Do you have a column in your table named rowid, oid, or _rowid_? If you do, that will cause an issue with last_insert_rowid() working as intended. See here.

A workaround: If you have an ID column that is auto-incremented, you could use the following SQL to get the last entry.

SELECT * FROM yourTable ORDER BY yourIdColumn DESC LIMIT 1

It's hacky, but it would work.

Septuagesima

This will not work if you also delete records from your db. The new records take ids of the deleted records – Ranzini