Saving array to mysql - letters and numbers

[RogierQ]

Hi!

After about 4 hours googling and trying i hope someone here knows the answer

i have a array that i want to save to a database.
the array is below
[2,3,"b",5,6,7]

when it is saved in the table the " " are removed from the b
tried, escape() jsonparse and so on
but when i want to convert i back to an array i have errors, or all characters with " "

what is the correct way to do this?
or is it just not possible?

Thanks
Rogier

[Steve-Mcl]

The correct way is really to put each element into a separate field.

Is the array always 6 elements? And is the 3rd item always a char/string?

Or do you really want to save the array as a string? In which case you could convert it to JSON and store it as a string.

The solution changes depending on what you want.

[Colin]

What sort of database?
Usually the quotes shown round a string do not actually exist, they are just used when displaying it to show that the thing between is a string and not a number.

Show us the query you are using to write the data (feed it into a debug node), show us what you get out when you query the database (again in a debug node) and tell us what field types you have configured in the database (if the database is one where you create a table with field types.

[RogierQ]

Database is Mariadb

write to

INSERT INTO `spel`( `data`) VALUES ('1,2,3,b,b,7')

query

[{"id":78,"data":"1,2,3,b,b,7","ts":"2021-03-09T21:57:18.000Z"}]

field of data is text field

[RogierQ]

Also tried

"\"b\""

That gives me

1,"b",4,5,6,7

[Steve-Mcl]

@RogierQ

[2,3,"b",5,6,7]

If you really want to save the array as a string (then restore it later on) then convert the array to JSON before inserting...

Insert SQL generated...

Select data looks like this (after conversion back from JSON to JS)...

demo flow...

[{"id":"e96e40f1.fb1b5","type":"debug","z":"47169a4b.e85434","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"topic","targetType":"msg","statusVal":"payload","statusType":"auto","x":892,"y":656,"wires":[]},{"id":"90417dd.11e7f8","type":"debug","z":"47169a4b.e85434","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":1158,"y":608,"wires":[]},{"id":"2663d18c.ed894e","type":"inject","z":"47169a4b.e85434","name":"insert [2,3,\"b\",5,6,7]","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[2,3,\"b\",5,6,7]","payloadType":"json","x":378,"y":608,"wires":[["235f6f6.56b4a9"]]},{"id":"c150d1dc.acaf9","type":"inject","z":"47169a4b.e85434","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":348,"y":864,"wires":[["646d2107.5838d"]]},{"id":"646d2107.5838d","type":"template","z":"47169a4b.e85434","name":"create table jsondata","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"CREATE TABLE `test`.`jsondata` ( \n    `id` INT NOT NULL AUTO_INCREMENT , \n    `data` VARCHAR(32000)  NOT NULL , \n    `ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , \n    PRIMARY KEY (`id`)\n) ENGINE = MyISAM;\n\n","output":"str","x":532,"y":864,"wires":[["94718358.f209c"]]},{"id":"5c5f83f3.67e3cc","type":"debug","z":"47169a4b.e85434","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"true","targetType":"full","statusVal":"payload","statusType":"auto","x":1138,"y":864,"wires":[]},{"id":"94718358.f209c","type":"mysql","z":"47169a4b.e85434","mydb":"4b1566d2.1f4b38","name":"","x":882,"y":864,"wires":[["5c5f83f3.67e3cc"]]},{"id":"baa7eee5.586d","type":"mysql","z":"47169a4b.e85434","mydb":"4b1566d2.1f4b38","name":"","x":882,"y":608,"wires":[["90417dd.11e7f8"]]},{"id":"535e0efb.bd89f","type":"inject","z":"47169a4b.e85434","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"Word0\":1,\"Word2\":2,\"Word4\":3,\"Word6\":4,\"Word8\":5}","payloadType":"json","x":338,"y":736,"wires":[["89a81ab7.032f78"]]},{"id":"81b016d1.115428","type":"mysql","z":"47169a4b.e85434","mydb":"4b1566d2.1f4b38","name":"","x":770,"y":736,"wires":[["85a9b30f.536e2"]]},{"id":"89a81ab7.032f78","type":"template","z":"47169a4b.e85434","name":"SELECT top 100 * from jsondata","field":"topic","fieldType":"msg","format":"sql","syntax":"mustache","template":"SELECT * from jsondata limit 100;","output":"str","x":562,"y":736,"wires":[["81b016d1.115428"]]},{"id":"7f1d70ee.8bd3d","type":"debug","z":"47169a4b.e85434","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":1158,"y":736,"wires":[]},{"id":"235f6f6.56b4a9","type":"json","z":"47169a4b.e85434","name":"","property":"payload","action":"","pretty":false,"x":562,"y":608,"wires":[["3c68006.976b7"]]},{"id":"3c68006.976b7","type":"template","z":"47169a4b.e85434","name":"build SQL","field":"topic","fieldType":"msg","format":"sql","syntax":"mustache","template":"insert into jsondata (data) values ('{{{payload}}}');","output":"str","x":716,"y":608,"wires":[["baa7eee5.586d","e96e40f1.fb1b5"]]},{"id":"13d8515.bf545af","type":"inject","z":"47169a4b.e85434","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"Word0\":1,\"Word2\":2,\"Word4\":3,\"Word6\":4,\"Word8\":5}","payloadType":"json","x":338,"y":800,"wires":[["5b6bab3d.a2e8e4"]]},{"id":"5b6bab3d.a2e8e4","type":"template","z":"47169a4b.e85434","name":"DELETE from jsondata","field":"topic","fieldType":"msg","format":"sql","syntax":"mustache","template":"DELETE from jsondata;","output":"str","x":532,"y":800,"wires":[["bc70acd9.15ba9"]]},{"id":"bc70acd9.15ba9","type":"mysql","z":"47169a4b.e85434","mydb":"4b1566d2.1f4b38","name":"","x":882,"y":800,"wires":[["6c7dafa1.7408a"]]},{"id":"6c7dafa1.7408a","type":"debug","z":"47169a4b.e85434","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":1158,"y":800,"wires":[]},{"id":"85a9b30f.536e2","type":"function","z":"47169a4b.e85434","name":"convert json to js obj","func":"var rows = msg.payload.map((e) => (\n    {\n        \"id\":e.id,\n        \"data\": JSON.parse(e.data),\n        \"ts\":e.ts\n    }\n    ));\nmsg.payload = rows;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":964,"y":736,"wires":[["7f1d70ee.8bd3d"]]},{"id":"91a73c87.150a3","type":"template","z":"47169a4b.e85434","name":"drop table jsondata","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"DROP TABLE `test`.`jsondata`; ","output":"str","x":522,"y":912,"wires":[["94718358.f209c"]]},{"id":"a25f1553.f00078","type":"inject","z":"47169a4b.e85434","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":348,"y":912,"wires":[["91a73c87.150a3"]]},{"id":"4b1566d2.1f4b38","type":"MySQLdatabase","name":"","host":"192.168.1.38","port":"3306","db":"test","tz":"","charset":"UTF8"}]

[RogierQ]

Wow how nice that you made an example for me.
Really happy with that!
Thanks a lot!!

[RogierQ]

Finally found the magic solution...

In the template node it must be with 3 { instead of 2{ .... took me hours be thanks to the example of @Steve-Mcl i found it!