Node-RED Postgres @open-kappa/node-red-contrib-mydb

[79cj7jeep]

@open-kappa/node-red-contrib-mydb

Judging by the number of people who download this, I'm certain I'm just missing something basic in my attempt to use it.

I have a simple postgres database and table set up.

I have 5 columns.

AccessToken: (TEXT)
SecondsPassed: (SMALLINT)
RoomOccupied: (BOOLEAN)
RoomLightsOn: (BOOLEAN)
RoomAVSystemActive: (BOOLEAN)

When I try a simple insert command where I: manually assign the values, it works without issue:

INSERT INTO "MYDATABASE"."test" ("SecondsPassed", "RoomOccupied")
VALUES (100, "TRUE");

The record shows up in the DB, and no errors, etc.

Now, I have a msg object that contains a payload:

msg.payload : Object
object
AccessToken: "1234ABCD5678EFGH"
SecondsPassed: 21
RoomOccupied: "FALSE"
RoomLightsOn: "FALSE"
RoomAVSystemActive: "TRUE"

When I try to use the data from the msg.payload object, I get errors.

The query looks like this:

INSERT INTO "MYDATABASE"."test" ("SecondsPassed", "RoomOccupied")
VALUES ({{msg.payload.SecondsPassed}}, {{msg.payload.RoomOccupied}});

The error states:

msg : error
"error: syntax error at or near ",""

Which is not exceptionally useful, but it does seem to relate to the comma that is separating the two values contained in the {{}}.

I have tried substituing a static value for each of the msg.payload objects above, ie:

INSERT INTO "MYDATABASE"."test" ("SecondsPassed", "RoomOccupied")
VALUES (100, {{msg.payload.RoomOccupied}});

and the error message changes to:

msg : error
"error: syntax error at or near ")""

or the other value:

INSERT INTO "MYDATABASE"."test" ("SecondsPassed", "RoomOccupied")
VALUES ({{msg.payload.SecondsPassed}}, TRUE);

and the error reverts back to:

msg : error
"error: syntax error at or near ",""

I'm at a loss. For my purposes, it seems this particular DB connector would work very well for the simple context I'm looking for, but for the life of me, I cannot get it to work unless I use static values.

Am I missing something with the {{}} syntax or something? I've tried single {} and triple {{{}}}, and while the error message may change, it never produces a positive result.

Would really appreciate any help.

[dceejay]

Have you added a debug node to the output of the function or template you are using to create that, in order to check exactly what query you are passing into the node ?
I've not used postgres in a while. How does it require the booleans to be converted/submitted exactly ?

[79cj7jeep]

I do have a debug node on the output of the mydb node that is sending the query to postgres, but it just produces the error messages I posted such as:

msg : error
"error: syntax error at or near ",""

As far as the formatting goes, I'm not sure specifically what formatting issue there may be, but when I enter a query with static values, as opposed to trying to retrieve the data from the msg object, the postgres query works properly.

I appreciate the ideas, but still at a loss. Ideally, I'd like a more verbose error response so I can drill down what exactly the query being sent looks like, but I do not see any way of doing this with the node-red-contrib-mydb node.

[dceejay]

No We need to see the debug from the input side of the db node

[79cj7jeep]

Sorry, I misunderstood.

Here is the debug node for the msg.payload being used by the db node:

6/9/2020, 8:17:27 AM
msg.payload : Object

{"AccessToken":"ABCD1234EFGH5678",
"SecondsPassed":301,
"RoomOccupied":"FALSE",
"RoomLightsOn":"FALSE",
"RoomAVSystemActive":"FALSE"}

[Colin]

@79cj7jeep

VALUES (100, "TRUE");

I note that you have quotes round TRUE

@79cj7jeep

VALUES ({{msg.payload.SecondsPassed}}, {{msg.payload.RoomOccupied}});

here you have not got quotes, that will expand to
VALUES( 21,FALSE);

[FStefanni]

Hi,

even if we use this node in production at work, I have re-checked just to be sure, and I confirm that it works fine. It is indeed an issue about how you write the query, so in general I suggest to write and test it in plain SQL, e.g. by using pgAdmin.

The problem seems exactly what Colin has written: the missing quotes. For example, I have created this message:

return {
    "payload": {
        "value1": "Hi!"
    }
};

The template inside mydb node is:

INSERT INTO "testTable" ("value1", "value2")
VALUES ('{{msg.payload.value1}}', 'TRUE');

where my testTable has two columns of type text,
and it works fine.

Please also note: in your insert you write:

INSERT INTO "MYDATABASE"."test"

But "MYDATABASE" should be the schema name, written with single quotes, and "test" the table name. In my example, I have used the default schema (namely "public"), so I can avoid to specify it.

I hope this helps.

[79cj7jeep]

Appreciate the advice on this, and I have double checked this. In fact, if I statically use:

INSERT INTO "MYDATABASE"."test" ("SecondsPassed", "RoomOccupied")
VALUES ( 123, TRUE);

or If I add quotes around the "TRUE":

INSERT INTO "MYDATABASE"."test" ("SecondsPassed", "RoomOccupied")
VALUES ({{msg.payload.SecondsPassed}}, "TRUE");

or if I used single quotes around 'TRUE':

INSERT INTO "MYDATABASE"."test" ("SecondsPassed", "RoomOccupied")
VALUES ({{msg.payload.SecondsPassed}}, 'TRUE');

This all works and returns no error.

Now If I use no quotes around the second value:

INSERT INTO "MYDATABASE"."test" ("SecondsPassed", "RoomOccupied")
VALUES ({{msg.payload.SecondsPassed}}, {{msg.payload.RoomOccupied}});

or if I use double quotes around the the second value:

INSERT INTO "MYDATABASE"."test" ("SecondsPassed", "RoomOccupied")
VALUES ({{msg.payload.SecondsPassed}}, "{{msg.payload.RoomOccupied}}");

or if I use single quotes around the second value:

INSERT INTO "MYDATABASE"."test" ("SecondsPassed", "RoomOccupied")
VALUES ({{msg.payload.SecondsPassed}}, '{{msg.payload.RoomOccupied}}');

debug shows this error message:

7/1/2020, 11:14:36 AM[node: DB Insert](http://x.x.x.x:1880/#)
msg : error
"error: syntax error at or near ","

Please note, the DB types are SMALLINT (SecondsPassed) and BOOLEAN(RoomOccupied). It seems the boolean is the issue for some reason.. I cannot get the boolean value to pass, quoted with single or double quotes, or without quotes.

Again, if I send:

INSERT INTO "MYDATABASE"."test" ("SecondsPassed", "RoomOccupied")
VALUES ({{msg.payload.SecondsPassed}}, TRUE);

It works just fine.

If I send:

INSERT INTO "MYDATABASE"."test" ("SecondsPassed", "RoomOccupied")
VALUES ({{msg.payload.SecondsPassed}}, "TRUE");

it works just fine.

If I send:

INSERT INTO "MYDATABASE"."test" ("SecondsPassed", "RoomOccupied")
VALUES ({{msg.payload.SecondsPassed}}, 'TRUE');

it works just fine.

It's only if I try and resolve the boolean value by means of {{msg.payload.RoomOccupied"}} that it is an issue.

If I debug the msg object for msg.payload.RoomOccupied, I get: "TRUE" as the value.

[79cj7jeep]

I think I found the cause of the issue. In the sample solution shown earlier the key and value pairs in the object are shown as quoted value.

In the JSON object I am receiving from a HTTP POST object, the keys are not quote deliniated.

They appear as:

@79cj7jeep

msg.payload : Object
object
AccessToken: "1234ABCD5678EFGH"
SecondsPassed: 21
RoomOccupied: "FALSE"
RoomLightsOn: "FALSE"
RoomAVSystemActive: "TRUE"

So, when the {{msg.payload.SecondsPassed}} is resolved, it is not working.

Following the previous advice, if I create an object and put the data as such:

return {
"payload": {
"SecondsPassed": 200,
"RoomOccupied": "TRUE"
}
};

This works just fine with the previos DB insert code I was using.

Any suggestions on how to deal with this JSON issue? I tried the json node and converted to Javascript object, but same issue persists there as wel;l.

Thanks for all the time and help.

[zenofmud]

What node are you using the '{{msg.payload.SecondsPassed}}' in?