sql insert with special char

[dantomlin]

How do I write a sql insert clause when the user put special characters in a text field I trying to update. These characters can be "/, "", \, #, ^"

My normal insert command returns a syntax error due to these characters...

Any suggestions would be greatly appreciated...

Thanks, Dan

[splash21]

If tDescription is a variable with your special characters, then the following will work..

Code: Select all

revExecuteSQL databaseID, "INSERT INTO MyTable (id, description) VALUES (0, :1)", "tDescription"

The :1 in the query is replaced with the tDescription variable - check out revExecuteSQL in the LiveCode dictionary.

[dantomlin]

My code creates one variable with the appropriate commas in between the fields to insert each line into the database. I'm reading an excel sheet line by line and inserting it into my database. So I'm not sure how to incorporate your suggestion unless I put each item into its own variable and then use your suggestion...

here is the code:

set itemdel to tab
put 1 into tLineNum
repeat for each line theLine in tData
put empty into tData2
put "'" & item 1 of theLine & "'" & comma after tData2
put "'" & tLineNum & "'" & comma after tData2
put "'" & item 2 of theLine & "'" & comma after tData2
put "'" & item 3 of theLine & "'" & comma after tData2
put "'" & item 4 of theLine & "'" & comma after tData2
put "'" & item 5 of theLine & "'" & comma after tData2
put "'" & item 6 of theLine & "'" & comma after tData2
put "'" & item 7 of theLine & "'" & comma after tData2
put "'" & item 8 of theLine & "'" & comma after tData2
put "'" & item 9 of theLine & "'" & comma after tData2
put "'" & item 10 of theLine & "'" & comma after tData2
put "'" & item 11 of theLine & "'" & comma after tData2
put "'" & item 12 of theLine & "'" & comma after tData2
put "'" & item 13 of theLine & "'" & comma after tData2
put "'" & item 14 of theLine & "'" & comma after tData2
put "'" & item 15 of theLine & "'" after tData2
add 1 to tLineNum
put "INSERT INTO IODetail Values (" & tData2 & ")" into tSQL
put ExecuteSQL(tSQL) into tResult
if tResult is not a number then
answer "Problem with importing the Detail IO Data"
exit InsertIODetailData
else
next repeat
end if
end repeat

[Simon]

Hi dantomlin,
I think ' is the escape character
as in '/
or I''m (check it, there a two of them).

Simon