I have the E-mail in node working fine and can retrieve the sender's email address as msg.from (as proven with a debug node). It gets returned with quotation marks; for example: "purchasing@my_company.com"
However, my next step is to pass that email address to a SQL query (shown below). Instead of receiving back a True or False, I am getting "RequestError: Incorrect syntax near ')'."
SELECT CAST(SUM(ContactCount) AS BIT) AS ContactExists
FROM (
SELECT CAST(1 AS INT) AS ContactCount FROM dbo.tblCustomersContacts WHERE ContactEmail = {{{msg.from}}}
UNION
SELECT CAST(0 AS INT) AS ContactCount
) A
Just as a test, if I instead write the query without the mustache wrapped msg.from and instead use a valid email, it works fine. See below. The syntax error is seemingly only happening when I use the mustache formatted variables. FWIW, I tried with single and double mustache with no success.
SELECT CAST(SUM(ContactCount) AS BIT) AS ContactExists
FROM (
SELECT CAST(1 AS INT) AS ContactCount FROM dbo.tblCustomersContacts WHERE ContactEmail = 'my_email@my_company.com'
UNION
SELECT CAST(0 AS INT) AS ContactCount
As @zenofmud says, where are you doing the mustache?
Are using the better mssql-plus node and doing the query in the query editor?
Attach a debug to the output (set to show complete message) - look at the msg.query property (it will show you the finished / rendered SQL query that was executed against the db - you will clearly see where the syntax error is.
But essentially, with mustache in node-red, do not include msg. when accessing msg properties (i.e. use {{{from}}} NOT {{{msg.from}}})
EDIT...
your SQL above looks wrong due to forum formatting.
you should surround code / flow with backticks
but that did not fix anything, so I have removed them.
However, definitely putting {{{from}}} helped. I think the problem now is that the string that I am passing (which is an email address) is being rejected by SQL with the message "The multi-part identifier could not be bound." I suspect there is some way to format the email address so that SQL will accept it. See my screenshot below:
Thanks for clarifying the backticks. Feel a bit stupid for missing that one.
Re: the debug node, I changed it to complete msg object and the "Result to" in the MSSQL-Plus node to "msg.query". I still see the same text as in my second screenshot ("the multi-part identifier "--the email address in the from field--" could not be bound")
Thank you. I tried to get the debug nodes showing the info you asked about, but for some reason could not get them to display as I wished. I had to peek at the spoiler and indeed, I actually tried that in the very beginning, but I was still using {{{msg.from}}} (whereas I later changed it to {{{from}}}.
Here is the working flow showing 2 results (one from a valid email in the database and another that is not valid).
If you change the debug to "show complete message" - you get to see the final QUERY that is sent to SQL Server - it lets you understand if your mustache is rendered correctly. It also permits you to copy the query and test it in another application (to help you rule out node-red)
Glad your sorted either way.
PS, in beta right now is the ability to use parameters e.g WHERE Name = @name...
