Re: [SQL] placeholders work for SQLite but not for mySQL

[stephen barncard]

On Tue, Aug 9, 2016 at 8:40 AM, Dr. Hawkins  wrote:

> Now that you mention that . . . is it possible that past runnings have
> created db entries of "NULL" rather than NULL?
>

I've had that happen.

Stephen Barncard - Sebastopol Ca. USA -
mixstream.org
___
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

Re: [SQL] placeholders work for SQLite but not for mySQL

[Dr. Hawkins]

On Mon, Aug 8, 2016 at 7:00 PM, Kay C Lan  wrote:

> As I said I'll get around
> to completely rebuilding the db
>


Now that you mention that . . . is it possible that past runnings have
created db entries of "NULL" rather than NULL?

-- 
Dr. Richard E. Hawkins, Esq.
(702) 508-8462
___
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

Re: [SQL] placeholders work for SQLite but not for mySQL

[Kay C Lan]

On Mon, Aug 8, 2016 at 11:46 PM, Peter Haworth  wrote:
>
> It doesn't explain the difference between using a parameter vs an embedded
> value, you should get the same result either way. I think you should submit
> a bug report.
>
Yes I would if other's were seeing the same thing so I first need to
rule out something strange with my set-up. As I said I'll get around
to completely rebuilding the db and I'll even copy and paste the
entire script into a text editor and back into the stack and see if
that makes any difference (I've used that to solve other problems). If
that doesn't fix it I'll then try and build a small demo stack + db
and see if I can reproduce it.

Thanks for you input.

___
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

Re: [SQL] placeholders work for SQLite but not for mySQL

[Peter Haworth]

SQLite is very forgiving about what data type it accepts for a column. It
will pretty much accept anything no matter what the defined data type is.
mySQL on the other hand rejects anything that doesn't match the declared
data type. That would explain the difference between the two systems.

It doesn't explain the difference between using a parameter vs an embedded
value, you should get the same result either way. I think you should submit
a bug report.

On Mon, Aug 8, 2016, 5:42 AM Ludovic THEBAULT 
wrote:

>
> > Le 8 août 2016 à 13:36, Kay C Lan  a écrit :
> > Unfortunately:
> >
> > put null into pValue
> >
> > worked fine for SQLite, but I still got the exact same mySQL ERROR =
> > Incorrect integer value: '' for column 'postcode' at row 1
> >
>
> After the revexecuteSQL do a :
> if pValue = empty then ## (or null)
> replace ":2" with "NULL" in tSQL
> end if
>
> ## where :2 is the placeholder which can be empty.
> ___
> 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

Re: [SQL] placeholders work for SQLite but not for mySQL

[Ludovic THEBAULT]

> Le 8 août 2016 à 13:36, Kay C Lan  a écrit :
> Unfortunately:
> 
> put null into pValue
> 
> worked fine for SQLite, but I still got the exact same mySQL ERROR =
> Incorrect integer value: '' for column 'postcode' at row 1
> 

After the revexecuteSQL do a :
if pValue = empty then ## (or null) 
replace ":2" with "NULL" in tSQL
end if  

## where :2 is the placeholder which can be empty.
___
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

Re: [SQL] placeholders work for SQLite but not for mySQL

[Kay C Lan]

On Sun, Aug 7, 2016 at 1:05 PM, Ludovic THEBAULT
 wrote:
>
>
> If my variable is empty, i set it to null.
> (the mysql column accept null)
>
Hi Ludovic,

sorry for the slow reply but for some reason your email ended up in my
SPAM folder.

Thanks for the suggestion, but after this stupid attempt

put "null" into pValue  --rookie mistake

I eventually read the Dictionary to discover that 'null' is wisely an
LC predefined constant. Unfortunately:

put null into pValue

worked fine for SQLite, but I still got the exact same mySQL ERROR =
Incorrect integer value: '' for column 'postcode' at row 1

And again, if I don't use a placeholder and write it out longhand it works fine:

UPDATE players SET postcode = null, updated = '20160805155320' WHERE
playerid = '123'

Result = 1 --Row updated

There must be something screwy about my set-up because I can't believe
that this is the only mySQL/LC combo I have of using a placeholder to
pass empty to an integer column in mySQL. For the same DB it happily
accepts empty via a placeholder to varchar fields. I'll probably
resort to my usual backstop - completely rebuild from scratch.

___
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

Re: [SQL] placeholders work for SQLite but not for mySQL

[Ludovic THEBAULT]

> Le 6 août 2016 à 11:07, Kay C Lan  a écrit :
> 
> 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.
> 

If my variable is empty, i set it to null.
(the mysql column accept null)


___
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

Re: [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  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  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

Re: [SQL] placeholders work for SQLite but not for mySQL

[Erik Beugelaar]

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  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

[SQL] placeholders work for SQLite but not for mySQL

[Kay C Lan]

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