[SQL] placeholders work for SQLite but not for mySQL

[Kay C Lan]

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 <beugelaar at 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.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
> _______________________________________________
> 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