[SQL] placeholders work for SQLite but not for mySQL

[Erik Beugelaar]

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.

Cheers,
Erik


 Kay C Lan <lan.kc.macmail at gmail.com> wrote:

What is the trick to passing an empty variable to a mySQL database
using placeholders?

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
tSqLiteStatement
 -- 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:
http://lists.runrev.com/mailman/listinfo/use-livecode