Passing Value from a Set Node into a MySQL query

Questions
#1

Hi!
At the end of a rather long workflow, I’m looking to delete a specific row from a table in a MySQL db. This is the simplified end:

image
image813×185 6.79 KB

I need to pass a value from Set1 Node, to specify which is the row is to be deleted.
The statement I eventually came up with that did not cause a MySQL error and was expecting to work is this:

DELETE FROM pending WHERE txid = '{{$node["Set1"].json["txid"]}}'

However, it returns "affectedRows": 0,
Meaning, it does not do anything.

For the sake of validating the data and overall flow, I tested with passing the actual value (and then re-added the row) :

DELETE FROM pending WHERE txid = "A6E9F87537FB9DCC3F926B6795D96E17B4186E660F894C1F142392D3E0BC91"

That works and deletes the row. Could someone please set me straight and tell me what’s wrong with the MySQL statement?

Here is a look at the Set1 Node:

image
image1086×614 30.8 KB

#2

Hi @diskin

I have no way to check at the moment and havent used mysql in a while.
But I see you are using single quotes instead of double quotes you use with the manual query.
Could that be it?

#3

Thanks for the suggestion. But unfortunately, that’s not it. When I used double quotes instead of single around the dynamic/node query, it threw a MySQL error.

#4

Hi @diskin, I am sorry to hear you’re running into trouble here.

I’ll set up a test database and give this a go. Can you share your workflow and confirm the exact version of MySQL you’re running as well as the type of your txid column? I tried this using a varchar column and it deleted just fine:

image
image1287×381 16.5 KB

image
image827×359 14 KB

Edit: one though I just had, is there a chance your query is just a text and not an expression (meaning it would not be evaluated)?

#5

@MutedJam
Ah…your Edit nailed it. It had been entered as text not as an expression as you said.
Thank you very much!

1 Like
#6

Glad to hear, thanks a lot for confirming!