Help with a Select Query Function

General
1

Hi, I need help formatting a query from a node-red function node to a sqlite database. I have already designed and verified my database and the query I am trying to use works properly within sqlite. The problem is I do not know how to properly format the query to use it within the node-red function node.

Here is the query as I use it in sqlite:
Select Contacts.Email FROM Contacts WHERE Contacts.Status = "PMI_Notify";

Here is my attempt at using it with in node-red:
msg.topic = "Select Contacts.Email FROM Contacts WHERE Contacts.Status = "PMI_Notify";"

I get an error as : "SyntaxError: Unexpected identifier"
I know, from my research it is something to do with the fact that "PMI_Notify" has quotes around it but I can not figure out how to properly format the query.
Can anyone help?

Thanks
Clan

2

Double quotes. Enclose the sql statement in double quotes and the field values in single quotes.

1 Like
3

Or the other way around :grinning:

1 Like
4

I just tried that as:
msg.topic = "Select Contacts.Email FROM Contacts = 'PMI_Notify' ;"
However, I now get an error as:
"Error: SQLITE_ERROR: near "=": syntax error"
(Thanks for the idea though...:slight_smile:

5

You have lost the WHERE.

2 Likes
6

Some databases don't like single quotes - I can't remember whether SQLite is sensitive to that but it may be better to use single quotes on the outer string and keep the double quotes for the SQL itself.

1 Like
7

Thank Colin. Yes, I did forget the 'WHERE' clause and afterwards it works perfectly. Thanks to everyone who tried to help. I don't know what I would do if not for people like you.

8

I believe that if PMI_Notify is a column name then it should be in double quotes, if it is a string literal then it should be in single quotes, but if double quotes are used and there is no column PMI_Notify then it will be interpreted as a literal. I think that this is being deprecated however and later sqlite versions will generate a warning if double quotes are used and there is no matching column name.

9

OK, I will watch for that. In my case it was a literal string referencing a column in my database. Take care

10

Not sure what you mean by that. If PMI_Notify is a column name, so you want to test for the Status and PMI_Notify columns containing the same value then it should be in double quotes (so the outer quotes must be single). If it is a string and you are testing the Status column to see if it contains that string (I suspect that is what you are doing) then it should be in single quotes.

11

I thought the "proper" way was to use backticks for identifiers and single quotes for strings (i read contradictions about it whether or not is double/single)

msg.topic = "Select `Email` FROM Contacts WHERE `Status` = 'PMI_Notify';"

But most databases have fallback compatbility built in.

1 Like
12

Sorry, PMI_Notify is the literal string stored in the 'Status' text column of the database. This is for email contacts that need to be notified of an upcoming PMI. Other IT errors, for instance are sent to other contacts that control programming errors and they have a literal string 'IT' in the status column. Sorry for the confusion.

13

I think the SQL Standard is to use double quotes for identifiers, and sqlite adheres better to the standard than most, but sqlite also allows backticks for identifiers for compatibility with mysql and even [ ] for compatibility with Microsoft dbs.

1 Like
14

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.