Page 2 of 2

Re: Using variables in mysql functions

Posted: Mon Jun 15, 2015 2:48 pm
by istech
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.

Re: Using variables in mysql functions

Posted: Tue Jun 16, 2015 12:19 am
by phaworth
What error do you get? Looks like you can only use literal values in the parameters, not function calls.
Pete

Re: Using variables in mysql functions

Posted: Tue Jun 16, 2015 9:47 am
by tellboy
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

Re: Using variables in mysql functions

Posted: Tue Jun 16, 2015 6:33 pm
by istech
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.

Re: Using variables in mysql functions

Posted: Tue Jun 16, 2015 7:47 pm
by phaworth
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