Page 1 of 1
SQLite insert woes
Posted: Fri May 05, 2017 2:59 pm
by exheusden
The following code
Code: Select all
put "INSERT into original (title, date_rec, comments) VALUES (wkTitle" & ", " & "wkRecDate" & ", " & "wkComments);" into tSQL
revExecuteSQL tDatabaseID, tSQL
results in the error: No such column: wkTitle.
I have added other information to the error display, in order to show the contents of the variables in the Insert statement and to show that the tDatabaseID is set (79).
What am I doing wrong?
Re: SQLite insert woes
Posted: Fri May 05, 2017 8:01 pm
by Klaus
Hola muchacho,
this how your SQL string will look like:
Code: Select all
INSERT into original (title, date_rec, comments) VALUES (wkTitle, wkRecDate, wkComments);
I'm sure that is not what you inteded, right?
AND you need to put single quotes around the values, otherwise strange things might happen, as you experienced here
Using my little functions, which will save a lot of typing, not oly with database stuff, and the neccessity to count all single/double quotes:
Code: Select all
function q tString
return QUOTE & tString & QUOTE
end q
function q2
return "'" & tString & "'"
end q2
<our script should look like:
Code: Select all
...
put "INSERT into original (title, date_rec, comments) VALUES (" & q2(wkTitle) & ", " & q2(wkRecDate) & ", " & q2(wkComments);" into tSQL
## If in doubt ALWAYS -> answer tSQL
## to have the "slap on the forehead" moment :-D
revExecuteSQL tDatabaseID, tSQL
...
Best
Klaus
Re: SQLite insert woes
Posted: Fri May 05, 2017 10:23 pm
by exheusden
Thank you, Klaus.
Your
Code: Select all
put "INSERT into original (title, date_rec, comments) VALUES (" & q2(wkTitle) & ", " & q2(wkRecDate) & ", " & q2(wkComments);" into tSQL
results in a LiveCode error, so I have modified it slightly, as follows:
Code: Select all
put "INSERT into original (title, date_rec, comments) VALUES (" & q2(wkTitle) & ", " & q2(wkRecDate) & ", " & q2(wkComments) & ");" into tSQL
I think that your q2 function should include an argument (as it stood, it just returned 'tString'), so I modified it as follows:
Code: Select all
function q2 tString
return "'" & tString & "'"
end q2
I think all that is now correct, no?
However, this results in the error, "near "(": syntax error."
Re: SQLite insert woes
Posted: Fri May 05, 2017 11:21 pm
by Klaus
Yes, sorry, you are right, of course, I forgot tString in q2!
Leave out the trailing ) and that should work:
Code: Select all
...
## put "INSERT into original (title, date_rec, comments) VALUES (" & q2(wkTitle) & ", " & q2(wkRecDate) & ", " & q2(wkComments) & ");" into tSQL
put "INSERT into original (title, date_rec, comments) VALUES (" & q2(wkTitle) & ", " & q2(wkRecDate) & ", " & q2(wkComments) & ";" into tSQL
...
Best
Klaus
Re: SQLite insert woes
Posted: Sat May 06, 2017 9:44 am
by exheusden
Klaus, that I do not understand. Your leaving off of the final bracket results in the following string:
INSERT into original (title, date_rec, comments) VALUES ('wkTitle', 'wkRecDate', 'wkComments';
With the final bracket, as I proposed, results in:
INSERT into original (title, date_rec, comments) VALUES ('wkTitle', 'wkRecDate', 'wkComments');
Surely the opening bracket of the VALUES list must be closed?
Re: SQLite insert woes
Posted: Sat May 06, 2017 9:59 am
by Klaus
Sorry again, I should not try post answers at late night.
Yes, the trailing ) is neccessary, but I am not sure what the error "near "(": syntax error." means?
The SQL definitively looks correct. Is that a Liveocde error?
Sorry, no idea in the moment.
Re: SQLite insert woes
Posted: Sat May 06, 2017 12:35 pm
by SparkOut
wkTitle and the rest are variables aren't they? Something is wrong with the definition of the variable content if so. Which suggests there is something fairly basically wrong with the handler that makes the sql query. I will try and look more when I am back at my computer, not on this phone.
Re: SQLite insert woes
Posted: Sun May 07, 2017 10:08 am
by exheusden
The variable content. Of course (I think)! The wkTitle (see first post) contains a single quote. Presumable this has to be escaped. I'm off to see if I can find how to do that.
Thanks for the nudge in what I hope is the right direction.
Re: SQLite insert woes
Posted: Sun May 07, 2017 10:17 am
by SparkOut
I am thinking more that the variables don't have any content at all. Where's the code that populates the variables? Are they defined in the right scope? (script local or global, if not defined in the same handler as the sql)
Re: SQLite insert woes
Posted: Sun May 07, 2017 11:30 am
by exheusden
Thanks for your concern, Sparkout. Data is put into the wk variables from fields in the current card. That the data is there can be seen in the error shown in the first post. I have now amended the script to replace single quotes in the variables by double quotes and it works a treat.
Many thanks to yourself, who put me on the trail of the single quote in the variables themselves, and to Klaus, who helped me with the syntax.
Re: SQLite insert woes
Posted: Sun May 07, 2017 1:36 pm
by Klaus
A good alternative way to avoid these inconveniences at all might be the syntax as shown in the last post of this thread:
http://forums.livecode.com/viewtopic.php?f=7&t=29180
Re: SQLite insert woes
Posted: Sun May 07, 2017 4:18 pm
by exheusden
This has got me thinking. If SQLIte has problems with the single quote character in a text string, then it might have problems with other characters that might then also need to be escaped (&,/,¿ and suchlike come to mind). Is there a list of such problematic characters and does SQLite always escape in the same manner i.e. by using the character twice? And dare I ask about accented characters…?
Re: SQLite insert woes
Posted: Thu May 11, 2017 12:38 pm
by MaxV
use
urlencode e
urlDecode functions to avoid any trouble
Re: SQLite insert woes
Posted: Thu Jun 22, 2017 8:29 am
by quailcreek
Here's a much easier way of building the statement. It eliminates the quote issue.
Code: Select all
put wkTitle into SQLArray[1]
put wkRecDate into SQLArray[2]
put wkComments into SQLArray[3]
put "INSERT into original (title, date_rec, comments) VALUES (:1,:2,:3)" into tSQL
revExecuteSQL tDatabaseID, tSQL, "SQLArray"
Re: SQLite insert woes
Posted: Thu Jun 22, 2017 8:35 am
by exheusden
Elegant. Thank you.