Error during inject into database

[sarah]

Hi there!

I'm creating flow to get csv data from opengov website. the csv data contain daily vaccination and I want to put into my database, during the process I face some problem regarding the rows that I want to exclude. Here I want to exclude rows based on column state_id which contain 0(if (state_id !== 0)), but here the "Error: A query must have either text or a name. Supplying neither is unsupported." appears when inject into the database.
Any suggestion?
thanks before!

var date = msg.payload.date;
var state_id = msg.payload.stateid;
var registered = msg.payload.regist;
//var registered_per100 = msg.payload.regist_per100;
var partly_vac = msg.payload.partly_vac;
//var partly_vac_per100 = msg.payload.partly_vac_per100;
var group_U25_m = msg.payload.group_u25_m1;
var group_U25_w = msg.payload.group_u25_w1;
var group_25_34_m = msg.payload.group_25_34_m1;
var group_25_34_w = msg.payload.group_25_34_w1;
var group_35_44_m = msg.payload.group35_44_m1;
var group_35_44_w = msg.payload.group35_44_w1;
var group_45_54_m = msg.payload.group45_54_m1;
var group_45_54_w = msg.payload.group45_54_w1;
var group_55_64_m = msg.payload.group55_64_m1;
var group_55_64_w = msg.payload.group55_64_w1;
var group_65_74_m = msg.payload.group65_74_m1;
var group_65_74_w = msg.payload.group65_74_w1;
var group_75_84_m = msg.payload.group75_84_m1;
var group_75_84_w = msg.payload.group75_84_w1;
var group_A84_m = msg.payload.groupo84_m1;
var group_A84_w = msg.payload.groupo84_w1;
var Vac_Pfizer = msg.payload.pfizer1;
var Vac_Moderna = msg.payload.moderna1;
var Vac_AstraZeneca = msg.payload.astrazeneca1;
//var population = msg.payload.population;
var lander = msg.payload.state;


//var group_u25 =  group_U25_m + group_U25_w;//

var insertStatement = "";
var deleteStatement = "";
var updateMviewStatement = "";

if (state_id !== 0){


insertStatement += "INSERT INTO s1078805.covid19_vac_daily (date, state_id, registered,  partly_vac,  group_U25_m, group_U25_w, group_25_34_m, group_25_34_w, group_35_44_m, group_35_44_w, group_45_54_m, group_45_54_w, group_55_64_m, group_55_64_w, group_65_74_m, group_65_74_w, group_75_84_m, group_75_84_w, group_A84_m, group_A84_w, Vac_Pfizer, Vac_Moderna, Vac_AstraZeneca, lander) VALUES ('" + date + "'," + state_id + "," + registered + "," + partly_vac + ", " + group_U25_m + ", " + group_U25_w + ", " + group_25_34_m + ", " + group_25_34_w + ", " + group_35_44_m + ", " + group_35_44_w + ", " + group_45_54_m + ", " + group_45_54_w + ", " + group_55_64_m + ", " + group_55_64_w + ", " + group_65_74_m + ", " + group_65_74_w + ", " + group_75_84_m + ", " + group_75_84_w + ", " + group_A84_m + ", " + group_A84_w + ", " + Vac_Pfizer + ", " + Vac_Moderna + ", " + Vac_AstraZeneca + ",'" + lander + "');";


msg.payload = insertStatement;
}
return msg;

[Steve-Mcl]

Which postgres node are you using?

NOTE: I use node-red-contrib-postgrestor-next or @digitaloak/node-red-contrib-digitaloak-postgresql without issue.

[Steve-Mcl]

PS: it is quite likely state_id is zero and your insertStatement is not being set.

Add node.warn("state_id = " + state_id ); above if (state_id !== 0){ the check the debug sidebar

[sarah]

Hi, thank you for your reply!

I use node-red-contrib-re-postgres, the csv somehow already in database but the error message still comes up, I check the last data is yesterday, so it's actually working. When not injected into the DB, the error not showing up as well.
I check as you suggest, the state_id seems setted up.

[Steve-Mcl]

So I was correct. You SQL statement was not created because the state_id value is 0.

Look at your payload, it is not a SQL statement. That's why you get the error. The payload is still an object (not SQL)

I believe you need to move the return msg inside the if block so that when state_id is 0 you don't send the msg to the database.

[sarah]

Hi Steve! thank you it works now!