PLEASE help - UI Form Data to SQLite db

General
1

HI, All you NR superstars!! I really need urgent help on a seems to be something simple that I of some reason just can't get to work after 3 days of trying, and a LOT of Reading !!!

I attempting to pass data from a UI Form from the dashboard into an SQLite dB.

Hardware: Raspberry PI 4 / Node-Red v 1.0.6

So far I have successfully

  • created Dashboard with the relevant form to add a " new user" called "users"
  • I have installed SQLite and have created the DB with the relevant table for the user as "users" ( Confirmed)

The Issue - I have tried to pass the [ Form ] data to a [ function ] node and link it to the [ SQLite ] node as per the instructions founded here on these forums.

Unfortunately, regardless of the millions of attemps in all different ways, forms, and methods, I just don't seem to get any values to the Sqlite node from the function node.

Can some PLEASE be so kind and inform me what I am doing wrong, I'm sure its something simple, I just don't seem to identify the issue.

here is my Function node code that's playing dead :

var users="user_name:Johnny,User_surname:Bravo,user_pin:3333,user_acc_level:1,user_acc_code:1";
//extract elements from data
var user_name=payload.user_name;
var user_surname=payload.User_surname;
var user_pin=payload.user_pin;
var user_acc_level=payload.user_acc_level;
var user_acc_code=payload.user_acc_code;


//create SQL command
msg="(user_name,User_surname,user_pin,user_acc_level,user_acc_code) values("+",\'"+user_name +"\',\'"+User_surname +"\'," + user_pin + "," + user_acc_level +"," + user_acc_code +")";
var topic="INSERT INTO USERS" +msg;
var msg1={}; //create new msg object
//The SQlite node expects the command in the topic field
msg1.topic=topic;
return msg1;

and this is the "debug" values ( complete msg object) from the incoming form data to the function node

ty7/3/2020, 4:33:49 PMnode: 8d87e409.564458
users : msg : Object
object
payload: object
user_name: "Jac"
User_surname: "McDonald"
user_pin: 9874
user_acc_level: 5
user_acc_code: 1
topic: "users"
socketid: "TbsdLj_kQzyESfbBAAAH"
_msgid: "d16e70ab.d3361"pe or paste code here

Your fast responses will be greatly appreciated to get this thing to work asap. thanks in advance !!

2

In a function node, you need to access payload via the msg object

E.g...

var user_pin=msg.payload.user_pin;

Ps, use a different variable name than msg for building your SQL

E.g...

//create SQL command
var sql="(user_name,User_surname,user_pin,user_acc_level,user_acc_code) values("+",\'"+user_name +"\',\'"+User_surname +"\'," + user_pin + "," + user_acc_level +"," + user_acc_code +")";
var topic="INSERT INTO USERS" +sql;

msg.topic=topic;

return msg;
3

HI Steve , thanks for your quick response

let me digest your replay quick, please dont go away...ahahh

4

So , if i understood you correctly , if i swap out the "var msg" before the sql command , then it should work ?

5

no worries - here is another tip...

use template strings to improve readability...

var mystr = `this is a template string and this is the value of a variable - ${myVariable}`

so your SQL would be...

msg.topic = `INSERT INTO USERS (
  user_name, User_surname, user_pin, user_acc_level, user_acc_code
) values (
   '${user_name}', '${User_surname}', '${user_pin}', ${user_acc_level}, ${user_acc_code} 
);`
return msg;
6

no

try this...

//get values from msg...
var user_name= msg.payload.user_name;
var user_surname= msg.payload.User_surname;
var user_pin= msg.payload.user_pin;
var user_acc_level= msg.payload.user_acc_level;
var user_acc_code= msg.payload.user_acc_code;


//create SQL command
msg.topic = `INSERT INTO USERS (
  user_name, User_surname, user_pin, user_acc_level, user_acc_code
) values (
   '${user_name}', '${user_surname}', '${user_pin}', ${user_acc_level}, ${user_acc_code} 
);`
return msg;
7

hey Steve, I have tried the "SQL Command with "var sql" as instructed and , still no response from the function node to the SQL node ..

var sql="(user_name,User_surname,user_pin,user_acc_level,user_acc_code) values("+",\'"+user_name +"\',\'"+User_surname +"\'," + user_pin + "," + user_acc_level +"," + user_acc_code +")";
var topic="INSERT INTO USERS" +sql;

msg.topic=topic;

return msg;
8

try this...

//get values from msg...
var user_name= msg.payload.user_name;
var user_surname= msg.payload.User_surname;
var user_pin= msg.payload.user_pin;
var user_acc_level= msg.payload.user_acc_level;
var user_acc_code= msg.payload.user_acc_code;

node.warn([user_name, user_surname, user_pin, user_acc_level, user_acc_code])

//create SQL command
msg.topic = `INSERT INTO USERS (
  user_name, User_surname, user_pin, user_acc_level, user_acc_code
) values (
   '${user_name}', '${user_surname}', '${user_pin}', ${user_acc_level}, ${user_acc_code} 
);`

node.warn(msg.topic);

return msg;

show me what appears in your debug panel.

9

Hey Steve, Your the BOSS, Thanks so much !! it worked. my inexperience in SQL is embarrassing, but thankfully there are the superstars like STEVE-Mcl !! thank you again and again !!

here is the results from the debug

7/3/2020, 5:47:42 PMnode: e6eb54ae.445558
INSERT INTO USERS ( user_name, User_surname, user_pin, user_acc_level, user_acc_code ) values ( 'Jac', 'McDonald', '9874', 5, 1 ); : msg : Object
object
payload: array[0]
topic: string
INSERT INTO USERS (
  user_name, User_surname, user_pin, user_acc_level, user_acc_code
) values (
   'Jac', 'McDonald', '9874', 5, 1 
);
socketid: "TbsdLj_kQzyESfbBAAAH"
_msgid: "a3e3080c.269d28"

and confirmed in the DB table :

SELECT * FROM USERS : msg.payload : array[2]
array[2]
0: object
user_name: "Pieter"
user_surname: "Hover"
User_pin: 5054
user_acc_level: 5
user_acc_code: 1
1: object
user_name: "Jac"
user_surname: "McDonald"
User_pin: 9874
user_acc_level: 5
user_acc_code: 1
10

No worries, glad to help.

PS, dont forget to mark the thread as complete :+1:

EDIT - you beat me to it :slight_smile:

11

will do , have a awesome day / night ...ps i might to puch on your bt soon again ( tonight)