3

I'm trying to perform an INSERT + UPDATE transaction in a database from my node.js application using node-sqlite3. However, I can't figure out how to get the last_insert_rowid when the transaction is done. My query looks something like this:

BEGIN TRANSACTION;
    INSERT INTO <table> (field1, ...) VALUES (value1, ...);
    UPDATE <table> SET prev=last_insert_rowid() WHERE <condition>;
END TRANSACTION;

This string (minus extra whitespace) is assigned to var q.

I've got my database opened at some point using this:

var db = new sqlite3.Database(dbfile)

But if I try to use:

db.run(q, function(err) {
    if (err) ...
    console.log(this.lastId)
})

not only does it print "0" with no errors, but no changes to the database occur!!! If I use exec instead of run then the query runs successfully, but I have no way of getting any data back from exec.

I have also tried something like this:

db.exec(q, function(err) {
    if (err) ...
    db.run(";", function(err) {
        if (err) ...
        console.log(this.lastId)
    })
})

But the "this" context on the "run" does not have a lastId property!

I also looked at the sqlite3-transaction package, but it doesn't seem to provide any information in the transaction callback either!

I can't run the insert as a separate run(), get the this.lastId and then use that to run the update, because this will leave the database in an invalid state between the two statements (hence the need for a transaction).

How do I get the last_insert_rowid() from this transaction?

Michael
  • 9,060
  • 14
  • 61
  • 123
  • Just wondering, but in MySQL the ability to obtain the most recently inserted ID can depend on the driver being used (e.g. Perl + `DBI` + `DBD::mysql` modules) in addition to some pre-conditions being met, such as the table ID having to be an Integer PK set to auto-increment. Have you checked if similar/different requirements are applicable in your particular case? It may also depend on which query ran last, so might need to check if it's even possible to do that in the context of an actual transaction. – code_dredd Sep 28 '16 at 21:52
  • You can make separate calls to `run` and still use a transaction. Just call `run("BEGIN TRANSACTION", ...)`, etc. and make sure you call either `COMMIT TRANSACTION` when you're done - or `ROLLBACK TRANSACTION`, if you have an error. – cartant Sep 29 '16 at 01:31
  • @cartant Thanks, I indeed did discover and try that successfully right before I had to leave yesterday, but haven't had time document it yet. – Michael Sep 29 '16 at 15:26

1 Answers1

0

In order to get the last_insert_rowid() it is necessary to break up the transaction into pieces as follows. First, run a query to begin the transaction:

db.run("BEGIN TRANSACTION")

Next, run the command to insert the row, getting the rowid in the callback:

// replace stuff in angle brackets and ellipses with proper values
db.run("INSERT INTO <table> (field1, ...) VALUES (value1, ...);", function(err,data) {
    if (err) ...
    rowid=this.lastID // rowid declared in scope visible outside callback
})

Next, run the update. Since the transactions isn't committed yet, none of these callbacks will be called yet.

db.run("UPDATE <table> SET prev=last_insert_rowid() WHERE <condition>;", function(err,data) {
    // do whatever needs doing after update is done here
})

Now commit the transaction:

db.run("END TRANSACTION;")

In this case, since the update is the last statement in the transaction, if it succeeds the entire transaction should succeed, so it should be safe to do whatever needs to be done after the transaction in that place.

Michael
  • 9,060
  • 14
  • 61
  • 123