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
sql insert with special char
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
-
- VIP Livecode Opensource Backer
- Posts: 369
- Joined: Sun Dec 19, 2010 1:10 am
- Location: UK
- Contact:
Re: sql insert with special char
If tDescription is a variable with your special characters, then the following will work..
The :1 in the query is replaced with the tDescription variable - check out revExecuteSQL in the LiveCode dictionary.
Code: Select all
revExecuteSQL databaseID, "INSERT INTO MyTable (id, description) VALUES (0, :1)", "tDescription"
LiveCode Development & Training : http://splash21.com
Re: sql insert with special char
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
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
-
- VIP Livecode Opensource Backer
- Posts: 3901
- Joined: Sat Mar 24, 2007 2:54 am
- Location: Palo Alto
Re: sql insert with special char
Hi dantomlin,
I think ' is the escape character
as in '/
or I''m (check it, there a two of them).
Simon
I think ' is the escape character
as in '/
or I''m (check it, there a two of them).
Simon
I used to be a newbie but then I learned how to spell teh correctly and now I'm a noob!