[SQL] placeholders work for SQLite but not for mySQL
beugelaar at solidit.nl
Sat Aug 6 02:02:49 EDT 2016
AFAIK it is possible to add your parameter in the WHERE clause.
So maybe you can test on NULL or NOT EMPTY as the first condition before the other ones.
Kay C Lan <lan.kc.macmail at gmail.com> wrote:
What is the trick to passing an empty variable to a mySQL database
I have an SQL statement that works in SQLite and mySQL except if the
value is empty, in which case it doesn't work with mySQL (works OK
with SQLite). I'm using a placeholder :1. If I remove the place holder
and substitute the variable which is empty it works - which confirms
the mySQL column is defined nullable:
DOES NOT WORK FOR MYSQL (but does for SQLite)
put "UPDATE " & pTable & " SET " & pColumn & " = :1 , updated = '" &
tTimeStamp & "' WHERE playerid = '" & pId & "'" into tSqLiteStatement
-- will look like:
-- UPDATE players SET postcode = :1, updated = '20160805155320' WHERE
playerid = '123'
revExecuteSql mySQLID, tSqLiteStatement, "pValue" --pValue = empty
ERROR = Incorrect integer value: '' for column 'postcode' at row 1
DOES WORK FOR mySQL
put "UPDATE " & pTable & " SET " & pColumn & " = '" & pValue & "' ,
updated = '" & tTimeStamp & "' WHERE playerid = '" & pId & "'" into
-- will look like this:
UPDATE players SET postcode = '', updated = '20160805155320' WHERE
playerid = '123'
revExecuteSql mySQLID, tSqLiteStatement
Result = 1 --Row updated
Any clues appreciated.
If you are wondering why the mix and match of placeholder and
non-placeholders it's because in my troubleshooting I've been removing
them until I've finally tracked this problem down
use-livecode mailing list
use-livecode at lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
More information about the Use-livecode