How can I access any value from previous query when using transactions?

[Ricardo1980]

I would like to create a transaction with 2 queries. The first one is a SELECT and the second one an UPDATE. I would like to use a random value (not the identifier) from the first one in the second one.

I see this in the documentation: "The function receives two arguments, the result of the last query executed, and an array containing all the previous query results. This is useful if you need values from a previous query as part of your transaction." So, I think it is possible. However, in the docs, I only see something about insertId, here: .query((r) => [ 'UPDATE myTable SET age = :age WHERE id = :id', { age: 4, id: r.insertId } ])

Can I do what I say? How can I access a random field from the previous query? Thanks a lot.

[ffxsam]

Here's an example off the top of my head, without having tested it:

await dataApi
  .transaction()
  .query('INSERT INTO table(name, age) VALUES(:name, :age) RETURNING age')
  .query(r => ['UPDATE whatever SET age = :age', { age: r.records[0].age })
  .commit()

I'm actually not 100% sure that example in the docs is correct.. I don't think r.insertId is a thing. r is the same result you'd get from a plain old dataApi.query() call, so you'd look at r.records for the records returned.

Again, I could be wrong.. this is just off the top of my head without looking at any of my code!

[Ricardo1980]

@ffxsam Thanks a lot! I am going to try and I will let you know.

[Ricardo1980]

Hello @ffxsam Quick question, how can I access a result that is not in the previous sentence, but 2 sentences earlier? Imagine: 1st: SELECT (I get a field) 2nd: INSERT INTO...ON DUPLICATE KEY UPDATE... (I use the previous field) 3rd: INSERT (using the field generated in the 1st sentence)

Can I do that using your approach? Thanks a lot.

[ffxsam]

@Ricardo1980 Wouldn't you just store it in a variable declared outside the block scope of the query callback? E.g.

let someVar;

await dataApi
  .transaction()
  .query(...)
  .query(r => {
    someVar = r.records;
  })
  .query(r => {
    // reference someVar here
  })

[wallisch]

This doesn't seem to work as described, any solutions?

[wallisch]

Figured it out, in case anyone is interested. No need to store in a local variable. You can access all previous results of the transaction via a second argument to a query function:

dataAPI.transaction()
.query("SOME_QUERY")
.query("SOME_OTHER_QUERY")
.query((last_insert, previous_inserts) => [
// Use results in query:
// last_insert == previous_inserts[1] -> SOME_OTHER_QUERY result
// previous_inserts[0] -> SOME_QUERY result
])

[ffxsam]

@wallisch Nice find!

https://github.com/jeremydaly/data-api-client/blob/master/index.js#L468

This really ought to be documented. I'll circle back and update when I have time.

[jeremydaly]

This is documented under Transaction Support:

“The function receives two arguments, the result of the last query executed, and an array containing all the previous query results. This is useful if you need values from a previous query as part of your transaction.”

We can definitely make it more explicit, though.

[ffxsam]

Ah, I missed that! Probably because my eye likes to jump right to the example code. 😉