SQLite insert woes

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
exheusden
Posts: 170
Joined: Fri Oct 09, 2009 5:03 pm
Location: Belgium
Contact:

SQLite insert woes

Post by exheusden » Fri May 05, 2017 2:59 pm

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).

Image

What am I doing wrong?

Klaus
Posts: 13793
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: SQLite insert woes

Post by Klaus » Fri May 05, 2017 8:01 pm

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 8)

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

exheusden
Posts: 170
Joined: Fri Oct 09, 2009 5:03 pm
Location: Belgium
Contact:

Re: SQLite insert woes

Post by exheusden » Fri May 05, 2017 10:23 pm

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."

Klaus
Posts: 13793
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: SQLite insert woes

Post by Klaus » Fri May 05, 2017 11:21 pm

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

exheusden
Posts: 170
Joined: Fri Oct 09, 2009 5:03 pm
Location: Belgium
Contact:

Re: SQLite insert woes

Post by exheusden » Sat May 06, 2017 9:44 am

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?

Klaus
Posts: 13793
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: SQLite insert woes

Post by Klaus » Sat May 06, 2017 9:59 am

Sorry again, I should not try post answers at late night. :oops:

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.

SparkOut
Posts: 2834
Joined: Sun Sep 23, 2007 4:58 pm

Re: SQLite insert woes

Post by SparkOut » Sat May 06, 2017 12:35 pm

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.

exheusden
Posts: 170
Joined: Fri Oct 09, 2009 5:03 pm
Location: Belgium
Contact:

Re: SQLite insert woes

Post by exheusden » Sun May 07, 2017 10:08 am

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.

SparkOut
Posts: 2834
Joined: Sun Sep 23, 2007 4:58 pm

Re: SQLite insert woes

Post by SparkOut » Sun May 07, 2017 10:17 am

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)

exheusden
Posts: 170
Joined: Fri Oct 09, 2009 5:03 pm
Location: Belgium
Contact:

Re: SQLite insert woes

Post by exheusden » Sun May 07, 2017 11:30 am

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.

Klaus
Posts: 13793
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: SQLite insert woes

Post by Klaus » Sun May 07, 2017 1:36 pm

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

exheusden
Posts: 170
Joined: Fri Oct 09, 2009 5:03 pm
Location: Belgium
Contact:

Re: SQLite insert woes

Post by exheusden » Sun May 07, 2017 4:18 pm

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…?

MaxV
Posts: 1579
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: SQLite insert woes

Post by MaxV » Thu May 11, 2017 12:38 pm

use urlencode e urlDecode functions to avoid any trouble :D
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

quailcreek
Posts: 746
Joined: Sun Feb 04, 2007 11:01 pm
Location: McKenna, WA

Re: SQLite insert woes

Post by quailcreek » Thu Jun 22, 2017 8:29 am

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"
Tom
MacBook Pro OS Mojave 10.14

exheusden
Posts: 170
Joined: Fri Oct 09, 2009 5:03 pm
Location: Belgium
Contact:

Re: SQLite insert woes

Post by exheusden » Thu Jun 22, 2017 8:35 am

Elegant. Thank you.

Post Reply

Return to “Databases”