revExecuteSQL failure

[montymay]

I am trying to put data in the fields of a LC card into a sqlite database, but what looks like a correct script does not work. The data does not appear in the database after I run the script.

Code: Select all

GLOBAL gDatabaseID
on mouseup
     repeat with j=1 to 4
          put fld j into MyArray[j]
     end repeat
put "INSERT INTO MyTable VALUES ('"&MyArray[1]&"','"&MyArray[2]&"','"&MyArray[3]&"','"&MyArray[4]&"')" into tSQL
revExecuteSQL gDatabaseID, tSQL,"MyArray"
end mouseUp

I am connected to the database. When I check the variable tSQL I see that it is accurately populated with the data. Does anyone see what is wrong with the script or what could have gone wrong in the IDE? Thanks for any suggestion.

Monty May

[AxWald]

Hi,

you may want to post the resulting tSQL string for better inspection, and, most important, "the result" after the call to revExecuteSQL.

Have fun!

[montymay]

Thanks for your quick reply. I had seen the result function explained in the DIctionary but had never used it. I added a line putting it into a variable and ran the actual script on my card which has 16 fields rather than four. Here's the new script below and the results from the variable watcher above.

Code: Select all

GLOBAL gDB_DatabaseID
on mouseup
     repeat with j=1 to 16
          put fld j into MyArray[j]
          end repeat

put "INSERT INTO superctDetails VALUES ('"&MyArray[1]&"','"&MyArray[2]&"','"&MyArray[3]&"','"&MyArray[4]&"','"&MyArray[5]&"','"&MyArray[6]&"','"&MyArray[7]&"','"&MyArray[8]&"','"&MyArray[9]&"','"&MyArray[10]&"','"&MyArray[11]&"','"&MyArray[12]&"','"&MyArray[13]&"','"&MyArray[14]&"','"&MyArray[15]&"','"&MyArray[16]&"')" into tSQL
     revExecuteSQL gDB_DatabaseID, tSQL,"MyArray"
     put the result into theResult
end mouseUp

I don't know what the syntax error near the "s" is or where it is. I might add that this card and others came from a stack where I successfully exported most of the cards, but this card and about a hundred others could not be exported, so I'm thinking there's something in those cards that is causing the failure.

Monty May

[bangkok]

One for your 16 fields probably contains a "forbidden" character.

Like for instance :
-single quote
-double quote
-tabulation

I would say a single quote. Because it's used as a column delimiter in SQL.

****************
http://www.sqlite.org/lang_expr.html
A string constant is formed by enclosing the string in single quotes ('). A single quote within the string can be encoded by putting two single quotes in a row - as in Pascal. C-style escapes using the backslash character are not supported because they are not standard SQL.
*****************


In order to use a single quote in a string that you want to insert in a SQL query, you need to use '' (2 single quote) or \' (with MySQL).

So in your script add :

Code: Select all

repeat with j=1 to 16
put fld j into MyArray[j]
replace "'" with "''" in MyArray[j]
end repeat

With MySQL, it should be :

Code: Select all

replace "'" with "\'" in MyArray[j]

Last point, to be able to properly debug your SQL query, you need to "see" it, in a whole.

So add :

Code: Select all

put tSQL
revExecuteSQL gDatabaseID, tSQL

[SparkOut]

You are mixing the "literal" and "placeholder" syntax...

From the dictionary under revExecuteSQL

If you specify an arrayName rather than a list of ordinary variables, the revExecuteSQL command substitutes the corresponding element of the array for each of the placeholders in the query:

revExecuteSQL myID,"insert into mytable values(:1,:2,:1)","myArray"

Try

Code: Select all

GLOBAL gDB_DatabaseID
on mouseup
     repeat with j=1 to 16
          put fld j into MyArray[j]
     end repeat

     put "INSERT INTO superctDetails VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16)" into tSQL
     revExecuteSQL gDB_DatabaseID, tSQL,"MyArray"
     put the result into theResult
end mouseUp

given a (numerially indexed) array, revExecuteSQL will substitute the placeholder index with the array index. And it will do the escaping for you.

[montymay]

Thank you Bangkok and Sparkout.

The apostrophes in the text was the problem, or most of it, and the escape technique worked. About a hundred records were inserted.

Thanks for the comment on the mixture of literal and placeholder syntax. No need to try it now but your suggestion will be used in future revExecuteSQL commands. I wish there was a good online exposition on placeholders and literals for beginners.

Monty May