Hello all. I have problem with UPDATE statement in SQLITE node ...
This script in function node follows SELECT :
var sel_song1 = flow.get("song_select1");// ID autoincrement value for identify
var sel_new1 = flow.get("song_new1");
var sel_num1 = flow.get("song_num1");
var sel_type1 = flow.get("song_type1");
var sel_title1 = flow.get("song_name1");
var sel_date1 = flow.get("song_date1");
var sel_text1 = flow.get("song_text1");
var sel_save1 = parseInt(flow.get("song_save1"));
if (sel_save1==1){// button SAVE
if (sel_new1 == false) {// switch if you need new song, or UPDATE older
msg.topic = 'UPDATE songs SET song_book_id = ' + sel_type1 + ', title = ' + sel_title1 + ', lyrics = ' + sel_text1 + ', song_number = ' + sel_num1 + ' , create_date = ' + sel_date1 + ' WHERE id = ' + sel_song1 + ';'
}
return msg;
}
at the msg output from function is SQLITE DB node with defined DB.
I have googled some examples, but without running code. What I have defined wrong ??
DB columns are defined correctly. If I SELECT from DB, this returns correct values from existing rows.
Thanks for all reactions.
msg.topic = `UPDATE songs SET song_book_id = '${sel_type1}', title = '${sel_title2}', lyrics = '${sel_text1}', song_number = '${sel_num1}' , create_date = '{sel_date1}' WHERE id = '{sel_song1}';`
This potentually maybe the source of your problem - its just a guess
You are using single quotes, to break between strings and variables - but shouldn't string values also use single quotes in SQLite (I have used them here) - string literals will solve that.
The single quotes are not used in the runtime evaluation (as it uses 'back ticks')
Therefore SQLite will "see" the single quotes in the query
msg.topic = `UPDATE songs SET song_book_id = '${sel_type1}', title = '${sel_title2}', lyrics = '${sel_text1}', song_number = '${sel_num1}' , create_date = '${sel_date1}' WHERE id = '${sel_song1}'`
EDIT
I am an MsSQL user, so may not be on the mark with the format of the query that is required, i.e does integers/decimals require being wrapped in quotes for example
msg.topic = `UPDATE songs SET song_book_id = '${sel_type1}', title = '${sel_title2}', lyrics = '${sel_text1}', song_number = ${sel_num1} , create_date = '${sel_date1}' WHERE id = ${sel_song1}`
I have tried both, also I have checked all settings, but withou positive result.
Here is a definition of table:
id - INTEGER
song_book_id - INTEGER
title - TEXT
lyrics - TEXT
song_number - TEXT
create_date - TEXT
Also I have updated it by this :
msg.topic = `UPDATE songs SET song_book_id = ${sel_type1}, title = '${sel_title1}', lyrics = '${sel_text1}', song_number = '${sel_num1}' , create_date = '${sel_date1}' WHERE id = ${sel_song1}`;
and update this :
var sel_song1 = parseInt(flow.get("song_select1"));// ID autoincrement value for identify
var sel_new1 = flow.get("song_new1");
var sel_num1 = flow.get("song_num1");
var sel_type1 = parseInt(flow.get("song_typ1"));
var sel_title1 = flow.get("song_name1");
var sel_date1 = flow.get("song_date1");
var sel_text1 = flow.get("song_text1");
var sel_save1 = parseInt(flow.get("song_save1"));
but without result. Well I am also PHP and MsSQL user and this makes me wrinkles
So, here is screens :
Debug nodes added few day back, but there is no messages on Debug node (all system messages and all check boxes of Debug node are checked). Only SQL error appears on click BUTTON SAVE as you read LOG1 in Debug25.
7. 10. 2023 22:19:34 node: debug 17
UPDATE songs SET song_book_id = 13, title = 'Right here waiting', lyrics = '<?xml version='1.0' encoding='UTF-8'?><song version="1.0"><lyrics><verse label="1" type="v"><![CDATA[1.Oceans apart, day after day
And I slowly go insane
I hear your voice on the line
But it doesn't stop the pain
If I see you next to never
How can we say forever?
Wherever you go, whatever you do
I will be right here waiting for you
Whatever it takes or how my heart breaks
I will be right here waiting for you.
2.I took for granted all the times
That I thought would last somehow
I hear the laughter, I taste the tears
But I can't get near you now
Oh, can't you see it, baby?
You've got me goin' crazy
Wherever you go, whatever you do
I will be right here waiting for you
Whatever it takes or how my heart breaks
I will be right here waiting for you.
3.I wonder how we can survive
This romance
But in the end, if I'm with you
I'll take the chance
Oh, can't you see it, baby?
You've got me goin' crazy
Wherever you go, whatever you do
I will be right here waiting for you
Whatever it takes or how my heart breaks
I will be right here waiting for you.]]></verse></lyrics></song>', song_number = '4' , create_date = '2018-06-12 17:27:27' WHERE id = 12 : msg.payload : number
1
So debug node 17 is at the output of function node.
The SQLlite node will replace any value with $whatever with the value that is in the payload array (and at the same index) - so it’s important to have them match the order of each other.
Topic = ‘Set name = $I_like_trains where id = $but_dislike_buses’
Payload = [‘marcus’, 100]
Will work for example - the order that $ appears will be the order of the element in the payload array
Marcus, thanks to show me part of way to get success. But nothing is done without hard work.
This was the correct application of UPDATE statement :
msg.topic = `UPDATE songs SET song_book_id = ${sel_type1}, title = '${sel_title1}', lyrics = '${sel_text1}', song_number = '${sel_num1}' , create_date = '${sel_date1}' WHERE id = ${sel_song1}`;
msg.payload = [sel_type1, sel_title1, sel_text1, sel_num1, sel_date1];
to succesfull output, together with DB modification. It was necessary update all rows data under lyrics column to transfer text without XML tags and all is running now. This was performed by REPLACE command directly in SQLITE.
Yeasterday late night for me was too tired to brainstorming.
