SQLite "Error: SQLITE_ERROR: no such column:"

General
1

Hello,

I just try a hunderet times but I can't fix it..

I created a new table: CREATE TABLE 'erfassungsbeleg' ('id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 'personal' INTEGER, 'name' CHAR,'kostentraeger' INTEGER,'stunden' INTEGER,'taetigkeit' INTEGER,'epoch' INTEGER, 'date' INTEGER)

This worked. But if i want to insert sth to the database there is always a problem with the column "name" because this is a word/name (if i set numbers in the string it worked but with words in the string it doesn t).

[{"id":"30814450.0881bc","type":"inject","z":"f8fb4f16.cdbf78","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":320,"y":200,"wires":[["8355bd56.24fd38"]]},{"id":"8355bd56.24fd38","type":"function","z":"f8fb4f16.cdbf78","name":"SQL","func":"// Leerer String wird als Variable sql angelegt\nvar sql = \"\";\n// Das Datum wird deklariert\nvar d = new Date();\nvar epoch = d.getTime();\nvar year    = d.getFullYear();\nvar monthX   = d.getMonth()+1; \nvar dayX     = d.getDate();\nif(monthX.toString().length == 1) \n{\nvar monthX = '0'+monthX;\n}\nif(dayX.toString().length == 1) \n{\nvar dayX = '0'+dayX;\n}   \nvar today = year+monthX+dayX;\n// Leeres Objekt wird erstellt\nvar outputs = [];\n\nvar nam    = \"text\";\nvar id      = \"2571\";\nvar kosten  = \"23\";\nvar stunden = \"8\";\nvar index   = \"40\";\n\n\n\n\n\nsql =   \"INSERT INTO erfassungsbeleg (personal,name,kostentraeger,stunden,taetigkeit,epoch,date)\" +\n        \"VALUES (\"+id+','+nam+\",\"+kosten+\",\"+stunden+\",\"+index+\",\"+epoch+\",\"+today+\")\";\n        outputs.push({topic:sql});\n\n\nreturn [ outputs ];","outputs":1,"noerr":0,"x":570,"y":400,"wires":[["2c99cd56.4625ba"]]},{"id":"2c99cd56.4625ba","type":"sqlite","z":"f8fb4f16.cdbf78","mydb":"bff91f4.bf534e","sqlquery":"msg.topic","sql":"","name":"SQLite","x":790,"y":400,"wires":[[]]},{"id":"e0e361f4.136f48","type":"inject","z":"f8fb4f16.cdbf78","name":"","topic":"CREATE TABLE 'erfassungsbeleg' ('id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 'personal' TEXT, 'name' CHAR,'kostentraeger' INTEGER,'stunden' INTEGER,'taetigkeit' INTEGER,'epoch' INTEGER, 'date' INTEGER)","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":480,"y":620,"wires":[["2c99cd56.4625ba"]]},{"id":"bff91f4.bf534e","type":"sqlitedb","z":"","db":"C:\\Users\\name\\SQLite\\sqlite","mode":"RW"}]

Maybe I m too stupid..

2

Hello YT2310

You use “nam” instead of “name”, when You specify the colums in the insert statement in the function. The colums in the create and the insert statement must of couse be equal. Your create statement in the code and in the text is also not equal on nam/name.

Best regards
Brian

3

Hi Brian5600,
that's not the problem. This is an error that has arisen since I have tried to find out if it is the name "name" (thought it might be because it is an instance or something in SQL ..). My problem is that although my "Name" tab is set as TEXT, I can read a string of numbers into the database, but not a string of letters.

4

Show us what a debug node showing the message going to the SQL node shows and the full text of the error that you see. Set the debug node to show complete message.

5

This is what is going in to the sql node:
{"topic":"INSERT INTO erfassungsbeleg (personal,name,kostentraeger,stunden,taetigkeit,epoch,date)VALUES (2571,Max,23,8,40,1564036958112,20190725)","_msgid":"60ebd092.0d281"}

and this is the error which is shown:

"Error: SQLITE_ERROR: no such column: Max"

screenshot

grafik

6

What happens if you put Max in quotes?

7

the error still emerges exactly the same

8

You used single ' quotes correct ?
Show us the input.

INSERT INTO erfassungsbeleg (personal,name,kostentraeger,stunden,taetigkeit,epoch,date) " +
        "VALUES ("+id+",'"+name+"',"+kosten+","+stunden+","+index+","+epoch+","+today+")"
9 
var sql = "";

var d = new Date();
var epoch = d.getTime();
var year    = d.getFullYear();
var monthX   = d.getMonth()+1; 
var dayX     = d.getDate();
if(monthX.toString().length == 1) 
{
var monthX = '0'+monthX;
}
if(dayX.toString().length == 1) 
{
var dayX = '0'+dayX;
}   
var today = year+monthX+dayX;

var outputs = [];

var namen    = "Max";
var id      = 2571;
var kosten  = 23;
var stunden = 8;
var index   = 40;





sql =   "INSERT INTO erfassungsbeleg (personal,name,kostentraeger,stunden,taetigkeit,epoch,date)" +
        "VALUES ("+id+","+namen+","+kosten+","+stunden+","+index+","+epoch+","+today+")";
        outputs.push({topic:sql});


return [ outputs ];
10

You need to fix the sql part with single quotes.

11

Show us what the debug now shows, it needs to have quotes round Max. The reason is that without the quotes SQL assumes you want to insert the value of the column called Max into the record, whereas you want to insert a string "Max".

12

now it shows this:

grafik

my problem was exatly what you just both discribed

14

there is no error any more :slightly_smiling_face:
this fixed my problem!
Thank you guys!

15

Just to clarify, it is the error that both @bakman2 and myself both described.

16

yes, see where my solution is set..