Using variables in mysql functions

Got a LiveCode personal license? Are you a beginner, hobbyist or educator that's new to LiveCode? This forum is the place to go for help getting started. Welcome!

Moderators: Klaus, FourthWorld, heatherlaine, kevinmiller

istech
Posts: 130
Joined: Thu Sep 19, 2013 10:08 am

Re: Using variables in mysql functions

Post by istech » Mon Jun 15, 2015 2:48 pm

I still get some issues with adding NOW() or TIMESTAMPS to date and time fields in mysql and LC. If its part of a variable it does not update the field but a literal works fine. See below for example.

(DOES NOT WORK)
put "NOW()" into thetimedate
put "1" into tBID
put "Update b SET date_time_field=(:1) WHERE bookings_id=(:2)" into tSQL
revExecuteSQL gConnectionID, tSQL, "thetimedate", "tBID"
answer the result

(WORKS)
put "1" into tBID
put "Update b SET date_time_field=NOW() WHERE bookings_id=(:1)" into tSQL
revExecuteSQL gConnectionID, tSQL, "tBID"
answer the result

I wonder if someone can explain this.

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: Using variables in mysql functions

Post by phaworth » Tue Jun 16, 2015 12:19 am

What error do you get? Looks like you can only use literal values in the parameters, not function calls.
Pete

tellboy
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 50
Joined: Wed Mar 06, 2013 1:31 pm

Re: Using variables in mysql functions

Post by tellboy » Tue Jun 16, 2015 9:47 am

I find using "merge" to create the sql is easier to follow when using variables and the '

Like so:

put merge("select sum(amount) from '[[tAccntName]]' where amount <> 0 and date >= '[[tStartDate]]' and date <= '[[tEndDate]]' and category = '[[tCategory]]'") into tSQL


All the best

Terry

istech
Posts: 130
Joined: Thu Sep 19, 2013 10:08 am

Re: Using variables in mysql functions

Post by istech » Tue Jun 16, 2015 6:33 pm

phaworth wrote:What error do you get? Looks like you can only use literal values in the parameters, not function calls.
Pete
No errors. For the literal the result is 1 and for the var the result is 0.
tellboy wrote:I find using "merge" to create the sql is easier to follow when using variables and the '

Like so:

put merge("select sum(amount) from '[[tAccntName]]' where amount <> 0 and date >= '[[tStartDate]]' and date <= '[[tEndDate]]' and category = '[[tCategory]]'") into tSQL


All the best

Terry
I will give it a shot and see how it goes. It would be nice to see if LC has a answer as well.

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: Using variables in mysql functions

Post by phaworth » Tue Jun 16, 2015 7:47 pm

Interesting. The zero indicates that no rows were updated but you're using the same tDBID value in both cases and you normally only get a zero if the UPDATE couldn't find any qualifying rows to update. It still feels like the variables can't contain function calls but I would have expected some sort of error message in the result if that were the case since you would essentially be trying to put an invalid value - NOW() - into a timestamp field.

I think you're right that you'll have to ask LC about this. Will be interested to hear the response.

Pete

Post Reply

Return to “Getting Started with LiveCode - Complete Beginners”