Page 2 of 2
Re: Inserting string with special characters into MySQL fiel
Posted: Sat May 23, 2015 10:41 am
by 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
Re: Inserting string with special characters into MySQL fiel
Posted: Tue Jun 16, 2015 10:49 pm
by 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.
Re: Inserting string with special characters into MySQL fiel
Posted: Sat Aug 15, 2015 7:32 pm
by 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
Re: Inserting string with special characters into MySQL fiel
Posted: Sun Aug 16, 2015 2:39 am
by 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,
Re: Inserting string with special characters into MySQL fiel
Posted: Sun Aug 16, 2015 5:49 am
by 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.
Re: Inserting string with special characters into MySQL fiel
Posted: Sun Aug 16, 2015 2:24 pm
by 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,
Re: Inserting string with special characters into MySQL fiel
Posted: Sun Aug 16, 2015 4:25 pm
by 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"
Re: Inserting string with special characters into MySQL fiel
Posted: Sun Aug 16, 2015 8:16 pm
by 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
Re: Inserting string with special characters into MySQL fiel
Posted: Sun Aug 16, 2015 8:27 pm
by 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,
Re: Inserting string with special characters into MySQL fiel
Posted: Sun Aug 16, 2015 8:52 pm
by 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.