Using variables in mysql functions

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

[phaworth]

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

[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

[istech]

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.

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]

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