How to Check whether There is Existing Data before Allowing Users to Insert the Data to the Database

[rishanaziz]

Hi,

Database type: MySQL

There is no data available on the database and I should allow the user to enter the data to the database.

I'm trying to do if else statement with this empty msg.payload (e.g. if (msg.payload===null)), but I'm getting SQL Syntax Error. My SELECT and INSERT statements in the function are working fine individually, but once I come up with complete function, the error coming up.

The following is the function code:

var msgSelect = {};
var msgInsert = {};

msgSelect.topic = "SELECT name FROM test WHERE name='" + msg.payload.name + "'";

if (msgSelect.payload===null)
{
	msgInsert.topic="INSERT INTO test (name,lastname,code) VALUES ('"+ msg.payload.name +"','"+ msg.payload.lastname +"','"+ msg.payload.code +"')";
	msg.payload = "Data inserted successfully!";
}
else
{
	msg.payload = "Data insertion failed!";
}
return msg;

Thanks for helping.

[Colin]

An empty array is not the same as null. Test the length of the array instead.

[rishanaziz]

Give me an example to test the length of the array.

[knolleary]

@rishanaziz See https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/length for how to get the length of an array.

[rishanaziz]

I got the following output when I SELECT statement. I would like to compare the count in if else statement. How to do it? If the count is 0, then only I would like to allow the users to insert the data. I also would like to know whether we are able to have two SQL statements in a function node. If it's possible, I'm able to SELECT and INSERT statement in the same function node.

[knolleary]

This page in the docs tells you about the tools available in the Debug sidebar to find out how to access any part of a message.

In this instance, msg.payload is an Array with a single element. That element is an object with a count property. Putting that all together gives: msg.payload[0].count

So you could use a Switch node to branch your flow by testing that property for 0 or otherwise:

[{"id":"c5197dfc.82664","type":"switch","z":"c6daa392.ba6de","name":"","property":"payload[0].count","propertyType":"msg","rules":[{"t":"eq","v":"0","vt":"num"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":213,"y":1308,"wires":[[],[]]}]

@rishanaziz

I also would like to know whether we are able to have two SQL statements in a function node. If it's possible, I'm able to SELECT and INSERT statement in the same function node.

A Function node doesn't do anything with SQL. You are setting properties on a message that gets passed to a mysql node. You can only do one query at a time.

[PKGeorgiev]

I prefer to use the MySQL-ish way (I think it's called upsert => update if exists or insert when absent):

msg.topic = "INSERT INTO `registry` (`key`, `value`) VALUES ('" + msg.topic + "', '" + msg.payload + "') ON DUPLICATE KEY UPDATE `value` = '" + msg.payload + "' ";

For this to work, the key field (name in your case) need to be primary key.
This eliminates the need for two queries i.e. less nodes in the flow.

[rishanaziz]

I'm getting "TypeError: Cannot read property '0' of undefined" once I used the switch node. I'm not sure where it went wrong.

Thanks for helping.

[{"id":"b107d7d0.8bcf98","type":"function","z":"87eeee6.729851","name":"select-query","func":"//var name = {payload: (msg.payload.name)};\n//msg.payload = name;\nvar select = {topic: \"SELECT COUNT(*) AS count FROM test WHERE name='\" + msg.payload.name + \"'\"};\n\nreturn select;","outputs":1,"noerr":0,"x":670,"y":120,"wires":[["ce323c92.b8766","ea75af48.2081b"]]},{"id":"cb5c2136.c89d5","type":"function","z":"87eeee6.729851","name":"insert-query","func":"//var cntStr = {payload: (msg.payload[0])};\n//var count = {payload: (cntStr.payload.count)};\n\n//if (msg.payload[0].count === 0)\n//{\n\tinsert = {topic: \"INSERT INTO test (name,lastname,code) VALUES ('\"+ msg.payload.name +\"','\"+ msg.payload.lastname +\"','\"+ msg.payload.code +\"')\"};\n\tactionString = \"Data inserted successfully!\";\n\treturn [insert,actionString];\n/*}\nelse\n{\n\tactionString = \"Data insertion failed!\";\n\tmsg.payload = actionString;\n    return msg;\n}*/","outputs":1,"noerr":0,"x":1070,"y":180,"wires":[["a91e26c5.d2ed48","c86b585b.736d78"]]},{"id":"a91e26c5.d2ed48","type":"debug","z":"87eeee6.729851","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1290,"y":120,"wires":[]},{"id":"bd18f1d0.01967","type":"switch","z":"87eeee6.729851","name":"","property":"topic","propertyType":"msg","rules":[{"t":"eq","v":"save-button","vt":"str"}],"checkall":"true","repair":false,"outputs":1,"x":470,"y":120,"wires":[["b107d7d0.8bcf98"]]},{"id":"7e1ee6ef.c4ec08","type":"mui_button","z":"87eeee6.729851","name":"save-button","group":"862f3f87.367da","order":4,"width":0,"height":0,"passthru":false,"label":"Save","color":"","bgcolor":"","icon":"","payload":"{}","payloadType":"global","topic":"save-button","x":90,"y":220,"wires":[["4f6f5725.bf21d8"]]},{"id":"8f59d5e9.f9bf98","type":"mui_text_input","z":"87eeee6.729851","name":"name","label":"Name","group":"862f3f87.367da","order":1,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"name","x":70,"y":40,"wires":[["4f6f5725.bf21d8"]]},{"id":"17abf0bb.ba670f","type":"mui_dropdown","z":"87eeee6.729851","name":"lastname","label":"","place":"Select Last Name","group":"862f3f87.367da","order":2,"width":0,"height":0,"passthru":false,"options":[{"label":"Rishan","value":1,"type":"num"},{"label":"Login","value":2,"type":"num"}],"payload":"","topic":"lastname","x":80,"y":100,"wires":[["4f6f5725.bf21d8"]]},{"id":"a8d9a9b0.81f358","type":"mui_text_input","z":"87eeee6.729851","name":"code","label":"Code","group":"862f3f87.367da","order":3,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"code","x":70,"y":160,"wires":[["4f6f5725.bf21d8"]]},{"id":"4f6f5725.bf21d8","type":"join","z":"87eeee6.729851","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":true,"timeout":"","count":"1","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":290,"y":120,"wires":[["bd18f1d0.01967"]]},{"id":"ce323c92.b8766","type":"mysql","z":"87eeee6.729851","mydb":"272ea16.4569d5e","name":"database","x":880,"y":120,"wires":[["a91e26c5.d2ed48"]]},{"id":"ea75af48.2081b","type":"switch","z":"87eeee6.729851","name":"","property":"payload[0].count","propertyType":"msg","rules":[{"t":"eq","v":"0","vt":"num"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":870,"y":240,"wires":[["cb5c2136.c89d5"],["617d5616.2edfb8"]]},{"id":"617d5616.2edfb8","type":"function","z":"87eeee6.729851","name":"failure-msg","func":"actionString = \"Data insertion failed!\";\nmsg.payload = actionString;\nreturn msg;","outputs":1,"noerr":0,"x":1070,"y":300,"wires":[["a91e26c5.d2ed48"]]},{"id":"c86b585b.736d78","type":"mysql","z":"87eeee6.729851","mydb":"272ea16.4569d5e","name":"database","x":1280,"y":180,"wires":[[]]},{"id":"862f3f87.367da","type":"mui_group","z":"","name":"Input Test - Multiple Users","tab":"29df8a3d.2c7ba6","order":1,"disp":true,"width":"6","collapse":false},{"id":"272ea16.4569d5e","type":"MySQLdatabase","z":"","host":"192.168.3.10","port":"3306","db":"ram_test","tz":""},{"id":"29df8a3d.2c7ba6","type":"mui_tab","z":"","name":"I4Inari","icon":"dashboard","order":1}]

[rishanaziz]

I already got the answer. Thank you for your help. Really appreciate it.

[Tefita]

I have tried to modify your shared flow but I can not see the error, you can help me with the flow @rishanaziz

[zenofmud]

If you want to use the INSERT with the DUPLICATES KEY option I suggest you do a google search to see what the syntax is and the table definition requirements

[Tefita]

I have decided to continue working with the shared flow of @rishanaziz , I wish that there is no duplicate data, that is, if the name already exists in the table, the message "duplicate name, failure of data entry" is shown, otherwise a message "data entered correctly". but I can not store the data in the table even though "name" is new.
I use mysql

Regards

[zenofmud]

Well I that case, I would attach a debug node to the output of your sql node (displaying the complete msg object) Them you can do an insert of a row that doesn’t exist and an insert of a row that does exist. Comparing the debug output will give you the information you need to determine if tha insert worked or failed and you can send message based on that.

[Tefita]

Image 1 shows the msg debug of a repeated "name", where count: 1

Image 2 shows a new "name", where count: 0

For this reason I have configured the node siwtch in the following way.

I can not insert the data in the table.
Can you help me please?

[ukmoose]

a switch node can’t insert data, but in one of your tests
you have it as a number (the small 0 9) and the other you have it as a string (small a z)

[Tefita]

Thanks for the observation, but I already modified it:

But later I have configured the following in node function:
insert:

insert = {topic: "INSERT INTO pacientes (nombre,apellido,cedula,edad,sexo) VALUES ('" + msg.payload.name + "','" + msg.payload.apellido + "','" + msg.payload.cedula + "','" + msg.payload.edad + "','" + msg.payload.sexo + "')"};
actionString = "Dato ingresado satisfactoriamente!";
return [insert,actionString];

failure:

actionString = "Ya se ingresado ese nombre!";
msg.payload = actionString;
return msg;

I attach the capture of the flow

[zenofmud]

so you build an insert statement and I see you have a debug attached to the output of the function that builds it. Make sure you have that debug node set to display the complete msg object then show us the output of that debug.

You also have a debug node attached to the database node - please set that to display the complete msg object and show up that output too.

[Tefita]

I only get the msg debug from the node insert:

and I get this error.
How can I solve it please?

[ukmoose]

what function is giving you that error message?

what message are you sending to that function?

[zenofmud]

That is saying you have a error in the function node. Since you haven’t provided your flow all anybody can do at this point is say ‘You need to fix the error’