[SQL] placeholders work for SQLite but not for mySQL

Kay C Lan lan.kc.macmail at gmail.com
Sat Aug 6 00:36:46 EDT 2016


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




More information about the Use-livecode mailing list