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.macm...@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@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode _______________________________________________ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
