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