[SOLVED] sqlite - using last_insert_rowid() with multiple inserts

sqlite - using last_insert_rowid() with multiple inserts

I'm trying to insert record a record to my 'quote_link' table using last_insert_rowid() to get the id from the newly created record in the 'quote' table. Then I'm using this as the value to insert into into ql_quote_id column in the 'quote_link' table.

This works

tx.executeSql( 'INSERT INTO quotelink(ql_quote_id,ql_cat_id,ql_kind_id) Values (last_insert_rowid(),?,?)', [cat_id,kind_id],

however, i am doing a number of such insets and only the first one gets the correct value for ql_quote_id (for the second insert the value of last_insert_rowid() gets the id from first insert)

I'm thinking I need to do a select sub query but I haven't a clue what the syntax would

Solution

The SQLResultSet of the result set callback allows you to get that ID:

tx.executeSql('INSERT INTO quote(...) VALUES(...)',
              [...],
              function(tx, result) {
    var quote_id = result.insertId;
    tx.executeSql('INSERT INTO quotelink(...) VALUES(?,?,?)',
                  [quote_id, cat_id, kind_id]);
});