SQLite3 - Insert data into a table using variables

Discuss about plugins that don't have a dedicated forum
Post Reply
Yvan_Amiga
Posts: 10
Joined: Mon Mar 14, 2022 5:05 pm

SQLite3 - Insert data into a table using variables

Post by Yvan_Amiga »

I want to insert data in a SQLite Database. The test code below with static values works

Code: Select all

TaskManagerDB:exec([[INSERT INTO TBL_Pendenz(PEN_Titel, PEN_Kommentar, PEN_Datum, PEN_Datum_yyyymmdd, PEN_Zeit, WAL_Kategorie, WAL_Prioritaet, PEN_Erledigt) 
		     VALUES('Titel2', 'Kommentar2', '22.08.2022', 20220822, '14:40', 5, 3, 0);]])

But the values should be dynamic, so I assigned them to variables and now I try to issue the same INSERT statements with variables but I did not suceed. The last thing I tried was but build the SQL statement with a string the following way.

Code: Select all

;p_xxx are variables with the values I want to insert

;First I build the SQL INSERT statement

Local insertSQL$ = "INSERT INTO TBL_Pendenz(PEN_Titel, PEN_Kommentar, PEN_Datum, PEN_Datum_yyyymmdd, PEN_Zeit, WAL_Kategorie, WAL_Prioritaet, PEN_Erledigt) "
insertSQL$ = insertSQL$ .. "VALUES('" .. p_title$ .. "', '" .. p_comment$ ..  "', '" .. p_duedate$ ..  "', '" .. p_duedate_yyyymmdd$ ..  "', '" .. p_duetime$ ..  "', " 
insertSQL$ = insertSQL$ .. p_category ..  ", " .. p_priority .. ", 0);"

;Then I try to execute the statement


TaskManagerDB:exec(insertSLQ$)
But it's doesn't work. There is no error message, but the data is not entered into the Database.

Any hints?
User avatar
airsoftsoftwair
Posts: 5433
Joined: Fri Feb 12, 2010 2:33 pm
Location: Germany
Contact:

Re: SQLite3 - Insert data into a table using variables

Post by airsoftsoftwair »

Yvan_Amiga wrote: Sat Aug 27, 2022 5:15 pm But it's doesn't work. There is no error message, but the data is not entered into the Database.
Must be something wrong with the way you construct that second string. Let's just compare them...

Code: Select all

Local a$ = [[INSERT INTO TBL_Pendenz(PEN_Titel, PEN_Kommentar, PEN_Datum, PEN_Datum_yyyymmdd, PEN_Zeit, WAL_Kategorie, WAL_Prioritaet, PEN_Erledigt) VALUES('Titel2', 'Kommentar2', '22.08.2022', 20220822, '14:40', 5, 3, 0);]]

p_title$ = "Titel2"
p_comment$ = "Kommentar2"
p_duedate$ = "22.08.2022"
p_duedate_yyyymmdd$ = "20220822"
p_duetime$ = "14:40"
p_category = 5
p_priority = 3

Local insertSQL$ = "INSERT INTO TBL_Pendenz(PEN_Titel, PEN_Kommentar, PEN_Datum, PEN_Datum_yyyymmdd, PEN_Zeit, WAL_Kategorie, WAL_Prioritaet, PEN_Erledigt) "
insertSQL$ = insertSQL$ .. "VALUES('" .. p_title$ .. "', '" .. p_comment$ ..  "', '" .. p_duedate$ ..  "', '" .. p_duedate_yyyymmdd$ ..  "', '" .. p_duetime$ ..  "', " 
insertSQL$ = insertSQL$ .. p_category ..  ", " .. p_priority .. ", 0);"

DebugPrint(a$)
DebugPrint(insertSQL$)
Generates this output:

Code: Select all

INSERT INTO TBL_Pendenz(PEN_Titel, PEN_Kommentar, PEN_Datum, PEN_Datum_yyyymmdd, PEN_Zeit, WAL_Kategorie, WAL_Prioritaet, PEN_Erledigt) VALUES('Titel2', 'Kommentar2', '22.08.2022', 20220822, '14:40', 5, 3, 0);
INSERT INTO TBL_Pendenz(PEN_Titel, PEN_Kommentar, PEN_Datum, PEN_Datum_yyyymmdd, PEN_Zeit, WAL_Kategorie, WAL_Prioritaet, PEN_Erledigt) VALUES('Titel2', 'Kommentar2', '22.08.2022', '20220822', '14:40', 5, 3, 0);
What do we see? The string containing variables has quotes around the PEN_Datum_yyyymmdd entry. So these should probably be removed in order to make it work, e.g. something like this:

Code: Select all

Local insertSQL$ = "INSERT INTO TBL_Pendenz(PEN_Titel, PEN_Kommentar, PEN_Datum, PEN_Datum_yyyymmdd, PEN_Zeit, WAL_Kategorie, WAL_Prioritaet, PEN_Erledigt) "
insertSQL$ = insertSQL$ .. "VALUES('" .. p_title$ .. "', '" .. p_comment$ ..  "', '" .. p_duedate$ ..  "', " .. p_duedate_yyyymmdd$ ..  ", '" .. p_duetime$ ..  "', " 
insertSQL$ = insertSQL$ .. p_category ..  ", " .. p_priority .. ", 0);"
With the code above the strings are identical...
Yvan_Amiga
Posts: 10
Joined: Mon Mar 14, 2022 5:05 pm

Re: SQLite3 - Insert data into a table using variables

Post by Yvan_Amiga »

Thanks for the hint. The p_duedate_yyyymmdd variable should definitely be an INTEGER and not a string. I'll try it right now.
Yvan_Amiga
Posts: 10
Joined: Mon Mar 14, 2022 5:05 pm

Re: SQLite3 - Insert data into a table using variables

Post by Yvan_Amiga »

This was an error but still it doesnt work

I tried the following

Code: Select all

;Building String
Local insertSQL$ = "INSERT INTO TBL_Pendenz(PEN_Titel, PEN_Kommentar, PEN_Datum, PEN_Datum_yyyymmdd, PEN_Zeit, WAL_Kategorie, WAL_Prioritaet, PEN_Erledigt) "
insertSQL$ = insertSQL$ .. "VALUES('" .. p_title$ .. "', '" .. p_comment$ ..  "', '" .. p_duedate$ ..  "', " .. p_duedate_yyyymmdd$ ..  ", '" .. p_duetime$ ..  "', " 
insertSQL$ = insertSQL$ .. p_category ..  ", " .. p_priority .. ", 0);"

;looking at it in console
DebugPrint(insertSQL$)
;result was
;INSERT INTO TBL_Pendenz(PEN_Titel, PEN_Kommentar, PEN_Datum, PEN_Datum_yyyymmdd, PEN_Zeit, WAL_Kategorie, WAL_Prioritaet, PEN_Erledigt) VALUES('didl', 'comme', '11.9.2022', 20220911, '13:30', 5, 1, 0);

;Try to write it to DB
TaskManagerDB:exec(insertSLQ$)

;did not work

;For testing I just tried it manually with the output I got from DebugPrint
TaskManagerDB:exec("INSERT INTO TBL_Pendenz(PEN_Titel, PEN_Kommentar, PEN_Datum, PEN_Datum_yyyymmdd, PEN_Zeit, WAL_Kategorie, WAL_Prioritaet, PEN_Erledigt) VALUES('didl', 'comme', '11.9.2022', 20220911, '13:30', 5, 1, 0);")

;and this works. So the string is okay. It works static but not dynamicly with a string variable

;below as comparison the first and the second string, both are really the same

;INSERT INTO TBL_Pendenz(PEN_Titel, PEN_Kommentar, PEN_Datum, PEN_Datum_yyyymmdd, PEN_Zeit, WAL_Kategorie, WAL_Prioritaet, PEN_Erledigt) VALUES('didl', 'comme', '11.9.2022', 20220911, '13:30', 5, 1, 0);
;INSERT INTO TBL_Pendenz(PEN_Titel, PEN_Kommentar, PEN_Datum, PEN_Datum_yyyymmdd, PEN_Zeit, WAL_Kategorie, WAL_Prioritaet, PEN_Erledigt) VALUES('didl', 'comme', '11.9.2022', 20220911, '13:30', 5, 1, 0);


Yvan_Amiga
Posts: 10
Joined: Mon Mar 14, 2022 5:05 pm

Re: SQLite3 - Insert data into a table using variables

Post by Yvan_Amiga »

I suceeded

Code: Select all

;Building String
Local insertSQL$ = "INSERT INTO TBL_Pendenz(PEN_Titel, PEN_Kommentar, PEN_Datum, PEN_Datum_yyyymmdd, PEN_Zeit, WAL_Kategorie, WAL_Prioritaet, PEN_Erledigt) "
insertSQL$ = insertSQL$ .. "VALUES('" .. p_title$ .. "', '" .. p_comment$ ..  "', '" .. p_duedate$ ..  "', " .. p_duedate_yyyymmdd$ ..  ", '" .. p_duetime$ ..  "', " 
insertSQL$ = insertSQL$ .. p_category ..  ", " .. p_priority .. ", 0);"

and then instead of TaskManagerDB:exec(insertSLQ$) do

Code: Select all

Local stmt = TaskManagerDB:prepare(insertSQL$)
stmt:dostep()
stmt:finalize()
and be happy, it's written to the SQLite DB :D
Post Reply