Inserting string with special characters into MySQL field

[dave.kilroy]

Thanks for the 'update' Adrien! I think for day-to-day code I may stick with separate "INSERT" and "UPDATE" statements - but will make some experiments in the future

Kind regards

Dave

[chipsm]

I don't know if your problem was solved.
I had problems opening a table that had spaces in its name. I solved the problem by using the tick mark, instead of quotes, to surround the string. The tick mark is located on your keyboard just left of the #1 key.
see my posting below.
http://forums.livecode.com/viewtopic.ph ... 02#p127428
I hope this helps.

[quailcreek]

I’m at a loss. I thought I had this taken care of but I just realized it’s still not working. What combination of single quotes and double quotes in my UPDATE statement will allow pDescription to contain single quotes and or double quotes and still save to the SQLite Db? Thank you.

Code: Select all

These were posted by Klause
-- Put (double) quotes around a given string:
function dQuotes tString
   return QUOTE & tString & QUOTE
end dQuotes
--------------------------------
-- Put (single) quotes around a given string:
function sQuotes tString
   return "'" &tString& "'"
end sQuotes
--------------------------------

put mobileControlGet(“Description”,"text") into pDescription

put dQuotes (pDescription) into pDescription -- this allows single quote to be save to the Db
put sQuotes (pDescription) into pDescription -- this allows double quote to be save to the Db

put "UPDATE MyData SET Description = "&pDescription&" WHERE TragetID = '"&pTarget_ID&"'" into tSQLStatement
revExecuteSQL  the uDatabaseID of this stack ,tSQLStatement

if the result is an integer then
  answer information “Update successfully."
else
  answer error "Sorry, there was an error updating."
end if

[Adrien]

Use prepared statements!

Code: Select all

-- before that...
put quote & "blablabla" & quote into pDescription -- or single quotes "'"... use your functions
--
put pDescription into SQLArray[1] -- or put quote & pDescription & quote instead of statement above.. you already have your two functions
put pTarget_ID into SQLArray[2]
put "UPDATE MyData SET Description = :1 WHERE TargetID = :2" into tSQLStatement
revExecuteSQL the uDatabaseID of this stack, tSQLStatement, "SQLArray"

(also, "TragetID" or "TargetID"? )

I think it should work. Let me know, can't test at the moment.

Cheers,

[quailcreek]

Hi Adrien,
The result is a 0. It looks like the array is not being populated. When I answer the tSQLStatement the values for Description is 1 and the value for TargetID is 2. Shouldn't they show the value of pDescription and pTarget_ID? Unless I'm misinterpreting the data.

[Adrien]

Should have been:
revExecuteSQL the uDatabaseID of this stack, tSQLStatement, "SQLArray"

with quotes on the "SQLArray"

I edited my previous post, hope this is it...


Cheers,

[quailcreek]

Perfecto!! No double quotes or single quotes needed on pDescription. Thanks again, Adrien.

One more question. For my error check I'm using - if the result is "1" then - for a positive completion of the statement. Is "1" always the indication of a positive completion?

In case anyone else is looking to do this, here's the INSERT script.

Code: Select all

put pName into SQLArray[1]
put pDescription into SQLArray[2]

put "INSERT into MyData(Name ,Description) VALUES (:1,:2)” into tSQLStatement
revExecuteSQL the uDatabaseID of this stack, tSQLStatement, "SQLArray"

[phaworth]

Generically, a numeric value in the result indicates the number of rows affected by the SQL statement so it won't always be 1. AN UPDATE with a WHERE statement for example, might affect several rows. Same for a DELETE with WHERE. I guess in this case, you know for a fact that only 1 row should be inserted so any other value would indicate a failure of some sort, even a different numeric value. An SQL error, as opposed to an unexpected number of affected rows, would be indicated by the result beginning with "revdberr"
Pete

[Adrien]

Strait from the dictionary:

The revExecuteSQL command places a return value into the result, to indicate the outcome of the query.

For successful queries, the revExecuteSQL command returns the number of rows affected for INSERT, UPDATE and DELETE statements. For all other statements, 0 is returned.

For unsuccessful queries, an error string is returned, describing the problem.

So you can have a successful but useless UPDATE, that will return 0, but no error happened. You have to check "if the result is a number then, else.." (or is an integer..).

For the other mostly used function, revDataFromQuery, the dictionary says:

If the query is not successful, the revDataFromQuery function returns an error message beginning with the string "revdberr,". You can test for success by checking whether the first item of the returned value is "revdberr".

Cheers,

[quailcreek]

Thanks again, Pete and Adrien,
After checking I see that 0 is an integer. So if I check for the result being an integer then 0 would give a true value even though. Looks like I need to re-think my error checks.