Page 1 of 1
sql insert with special char
Posted: Tue Jul 22, 2014 3:36 pm
by 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
Re: sql insert with special char
Posted: Tue Jul 22, 2014 4:27 pm
by 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.
Re: sql insert with special char
Posted: Tue Jul 22, 2014 5:13 pm
by 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
Re: sql insert with special char
Posted: Thu Jul 24, 2014 7:05 am
by Simon
Hi dantomlin,
I think ' is the escape character
as in '/
or I''m (check it, there a two of them).
Simon