Re: [node-red] How to insert a parameter in the SQL query?

[Colin Law]

On 28 April 2018 at 11:34, Alessandro Saletti <salets...@live.it> wrote:

I'm creating a hangout bot using nodered.I have a SQL database on Azure and for each user I have an id saved in msg.conversationId and I would like to pass this id to a query. I create a function "sql query" with this query:

msg.payload = {"action" : "Q","query" : "SELECT a = ISNULL(Nome, null) FROM Users WHERE id_conversation=msg.conversationId;"};




Possibly

msg.payload = {action: "Q", query: `SELECT a = ISNULL(Nome, null) FROM Users WHERE id_conversation=${msg.conversationId};`};

 

Note they are backticks round the select string, which allows the use of the ${} string interpolation syntax.  Feed it into a debug to make sure it looks right.  You don't need the quotes round action and query.

Colin

But, of course I am wrong

This is the node red flow:

Does anyone has any suggestions? Thanks so much!

--
http://nodered.org
 
Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+unsubscribe@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.
To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/657c7d86-9315-4b4f-bf4c-e8b82a2fb952%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Colin Law]

Why have you posted the same question again?

Colin

On 28 April 2018 at 17:30, Alessandro Saletti <salets...@live.it> wrote:

Il giorno sabato 28 aprile 2018 12:34:44 UTC+2, Alessandro Saletti ha scritto:

I'm creating a hangout bot using nodered.I have a SQL database on Azure and for each user I have an id saved in msg.conversationId and I would like to pass this id to a query. I create a function "sql query" with this query:

msg.payload = {"action" : "Q","query" : "SELECT a = ISNULL(Nome, null) FROM Users WHERE id_conversation=msg.conversationId;"};




But, of course I am wrong

This is the node red flow:

Does anyone has any suggestions? Thanks so much!

--
http://nodered.org
 
Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+unsubscribe@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/7a7280d9-58b8-436b-a01e-a6ea9dd8540b%40googlegroups.com.

[Colin Law]

Please reply to the list, not to myself directly.  I have copied this to the list for reference.

See below

On 28 April 2018 at 18:09, Alessandro Saletti <salets...@live.it> wrote:

Sorry man, my fault

However thank you! But now i have this error message

"Error: {"message":"Invalid column name 'Ugw6y1hyFf0RThLEHCZ4AaABAagB_f4H'.","stack":"RequestError: Invalid column name 'Ugw6y1hyFf0RThLEHCZ4AaABAagB_f4H'.\n at RequestError (/home/vcap/app/.node-red/node_modules/tedious/lib/errors.js:34:12)\n at Parser.<anonymous> (/home/vcap/app/.node-red/node_modules/tedious/lib/connection.js:207:36)\n at emitOne (events.js:96:13)\n at Parser.emit (events.js:188:7)\n at Parser.<anonymous> (/home/vcap/app/.node-red/node_modules/tedious/lib/token/token-stream-parser.js:42:15)\n at emitOne (events.js:96:13)\n at Parser.emit (events.js:188:7)\n at addChunk (/home/vcap/app/.node-red/node_modules/readable-stream/lib/_stream_readable.js:291:12)\n at readableAddChunk (/home/vcap/app/.node-red/node_modules/readable-stream/lib/_stream_readable.js:278:11)\n at Parser.Readable.push (/home/vcap/app/.node-red/node_modules/readable-stream/lib/_stream_readable.js:245:10)\n at Parser.Transform.push (/home/vcap/app/.node-red/node_modul..."

Ugw6y1hyFf0RThLEHCZ4AaABAagB_f4H is the value of msg.conversationId

That is an SQL issue not a node-red issue.  Since the id is a string rather than a number I guess you need quotes round it in the query, so

 

WHERE id_conversation='${msg.conversationId}';

Those are single quotes not backticks of course.

Colin

 

Il giorno sabato 28 aprile 2018 18:38:18 UTC+2, Colin Law ha scritto:

Why have you posted the same question again?

Colin

On 28 April 2018 at 17:30, Alessandro Saletti <salets...@live.it> wrote:

Il giorno sabato 28 aprile 2018 12:34:44 UTC+2, Alessandro Saletti ha scritto:

I'm creating a hangout bot using nodered.I have a SQL database on Azure and for each user I have an id saved in msg.conversationId and I would like to pass this id to a query. I create a function "sql query" with this query:

msg.payload = {"action" : "Q","query" : "SELECT a = ISNULL(Nome, null) FROM Users WHERE id_conversation=msg.conversationId;"};




But, of course I am wrong

This is the node red flow:

Does anyone has any suggestions? Thanks so much!

--
http://nodered.org
 
Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.

To unsubscribe from this group and stop receiving emails from it, send an email to node-red+u...@googlegroups.com.

To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.