Thanks Erik, yes that's what I'm doing as a workaround at the moment, I test the variable and see if it's empty, if not I use place holders and execute the original statement, if it is empty, then I've written out the longhand statement without place holders and execute that.
It's just a pain, I love using place holders, and it's just a lot of extra hassle. I'm sure there must be a trick to it I just don't know. On Sat, Aug 6, 2016 at 2:02 PM, Erik Beugelaar <beugel...@solidit.nl> wrote: > > 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 _______________________________________________ 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
