Using variables in mysql functions
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller
Using variables in mysql functions
Hey all,
I hope you can help in this problem. I want to use a mysql function like the one below and it works fine when I use a date. But as soon as I try to use a var in the function it fails. I have tried a lot of place holders but none have worked so far. Any help on this matter is greatly appreciated.
global theID
on mouseUp
local date
put "'"&"2013-04-04"&"'" into date
answer date with "okay"
put "SELECT TIMESTAMPDIFF(MONTH, (:1), '2014-04-04')" into tSQL
get revDataFromQuery(, , theID, tSQL, "date")
answer the result with "okay"
end mouseUp
I hope you can help in this problem. I want to use a mysql function like the one below and it works fine when I use a date. But as soon as I try to use a var in the function it fails. I have tried a lot of place holders but none have worked so far. Any help on this matter is greatly appreciated.
global theID
on mouseUp
local date
put "'"&"2013-04-04"&"'" into date
answer date with "okay"
put "SELECT TIMESTAMPDIFF(MONTH, (:1), '2014-04-04')" into tSQL
get revDataFromQuery(, , theID, tSQL, "date")
answer the result with "okay"
end mouseUp
Re: Using variables in mysql functions
Hi,
"date" is a reserved word. Don't you get a compilation or execution error? Try myDate instead.
What does the result contains?
An observation like "it fails" is not informative. You need to describe how it fails, what kind of errors you get, and what it should do instead of failing.
Mark
"date" is a reserved word. Don't you get a compilation or execution error? Try myDate instead.
What does the result contains?
An observation like "it fails" is not informative. You need to describe how it fails, what kind of errors you get, and what it should do instead of failing.
Mark
The biggest LiveCode group on Facebook: https://www.facebook.com/groups/livecode.developers
The book "Programming LiveCode for the Real Beginner"! Get it here! http://tinyurl.com/book-livecode
The book "Programming LiveCode for the Real Beginner"! Get it here! http://tinyurl.com/book-livecode
Re: Using variables in mysql functions
Hi Mark,
Sorry for being a little short. The query should return the number of months between the two dates. If I use two dates in the MySQL query it works fine. However if I introduce a date in a var the result is empty.
There seems to be a problem reading the var when called in a function. Maybe my format for the placeholder is incorrect or not supported in Livecode.
The "date" var was just for this example and still comes back empty whatever var name I use. I hope you can help.
Thanks
Sorry for being a little short. The query should return the number of months between the two dates. If I use two dates in the MySQL query it works fine. However if I introduce a date in a var the result is empty.
There seems to be a problem reading the var when called in a function. Maybe my format for the placeholder is incorrect or not supported in Livecode.
The "date" var was just for this example and still comes back empty whatever var name I use. I hope you can help.
Thanks
-
- VIP Livecode Opensource Backer
- Posts: 9660
- Joined: Wed May 06, 2009 2:28 pm
- Location: New York, NY
Re: Using variables in mysql functions
Hi.
One thing you must remember is that literals are not variables. I understand your use of "date" as an explanatory example, but what you cannot do is express that value with quotes.
See what I mean? Fix the line
Craig Newman
One thing you must remember is that literals are not variables. I understand your use of "date" as an explanatory example, but what you cannot do is express that value with quotes.
Code: Select all
put "123" into tDate
answer tDate && "tDate"
Code: Select all
get revDataFromQuery(, , theID, tSQL, "date")
Re: Using variables in mysql functions
Thanks Craig
I have tried this below and the same result.
The "date" var was just a example and would never use date as a var. I have tried the below and the same issue. It returns empty.
global theID
on mouseUp
local t
put "'"&"2013-04-04"&"'" into t
answer t with "okay"
put "SELECT TIMESTAMPDIFF(MONTH, (:1), '2014-04-04')" into tSQL
get revDataFromQuery(, , theID, tSQL, "t")
answer the result with "okay"
end mouseUp
I have tried this below and the same result.
The "date" var was just a example and would never use date as a var. I have tried the below and the same issue. It returns empty.
global theID
on mouseUp
local t
put "'"&"2013-04-04"&"'" into t
answer t with "okay"
put "SELECT TIMESTAMPDIFF(MONTH, (:1), '2014-04-04')" into tSQL
get revDataFromQuery(, , theID, tSQL, "t")
answer the result with "okay"
end mouseUp
-
- VIP Livecode Opensource Backer
- Posts: 9660
- Joined: Wed May 06, 2009 2:28 pm
- Location: New York, NY
Re: Using variables in mysql functions
Hi.
You did it again. The "t" in that line is surrounded by quotes. it is no longer a variable in that case, but rather the literal "t", that is literally, numToChar(116).
Sorry to be so literal.
Run my earlier example. Lose those quotes.
Craig
You did it again. The "t" in that line is surrounded by quotes. it is no longer a variable in that case, but rather the literal "t", that is literally, numToChar(116).
Sorry to be so literal.
Run my earlier example. Lose those quotes.
Craig
Re: Using variables in mysql functions
Very strange Sorry Craig I have always followed the documentation and the example below is from the documentation.
local tImageData
put the text of image "MyImage" into tImageData
get revDataFromQuery(, , myID, "SELECT size FROM images WHERE imagedata = :1", "*btImageData")
or
get revDataFromQuery(, , myID,"SELECT x,y FROM test WHERE x = :1 AND y = :2", "valueX", "valueY")
They always use quotes when ref var in a call in SQL and I always used quotes with the calls.. Why different now? Is the docs wrong?
In any case I am getting a error with the place holder now. (:1).
local tImageData
put the text of image "MyImage" into tImageData
get revDataFromQuery(, , myID, "SELECT size FROM images WHERE imagedata = :1", "*btImageData")
or
get revDataFromQuery(, , myID,"SELECT x,y FROM test WHERE x = :1 AND y = :2", "valueX", "valueY")
They always use quotes when ref var in a call in SQL and I always used quotes with the calls.. Why different now? Is the docs wrong?
In any case I am getting a error with the place holder now. (:1).
-
- VIP Livecode Opensource Backer
- Posts: 9660
- Joined: Wed May 06, 2009 2:28 pm
- Location: New York, NY
Re: Using variables in mysql functions
Well, maybe I am wrong. I have never made an SQL function call, and perhaps the parsers here or there treat parameters differently than straight LiveCode.
Perhaps someone who knows what they are talking about will chime in?
Craig
Perhaps someone who knows what they are talking about will chime in?
Craig
Re: Using variables in mysql functions
Hi,
I use function calls in MySQL all the time and I also use variables in the revDataFromQuery function. You're correct to put quotes around the variable names. I'll have a closer look now...
Mark
I use function calls in MySQL all the time and I also use variables in the revDataFromQuery function. You're correct to put quotes around the variable names. I'll have a closer look now...
Mark
The biggest LiveCode group on Facebook: https://www.facebook.com/groups/livecode.developers
The book "Programming LiveCode for the Real Beginner"! Get it here! http://tinyurl.com/book-livecode
The book "Programming LiveCode for the Real Beginner"! Get it here! http://tinyurl.com/book-livecode
-
- VIP Livecode Opensource Backer
- Posts: 858
- Joined: Wed Jun 24, 2009 1:17 pm
- Location: Plymouth, UK
- Contact:
Re: Using variables in mysql functions
Hi all - yep I also use revDataFromQuery a lot and the 'placeholder' variable(s) or array always have to be in quote marks (as opposed to how the rest of LiveCode works). I've never thought to find out exactly why, assuming it was something to do with how they were used to swap out values (doing their placeholder thing) within SQL...
Dave
Dave
"...this is not the code you are looking for..."
Re: Using variables in mysql functions
It is because the variable name is being passed as a parameter so that the function can then know what to look up for population of the query, rather than passing the contents of the variable. It's hard to describe, I guess it is a bit like a construction using value() or another abstraction step, like do ... so yes, it's about like you said Dave.
Re: Using variables in mysql functions
Hi,
You need to write
instead of
I.e. leave out the single quotes. A complete script could be:
Kind regards,
Mark
You need to write
Code: Select all
put "2013-04-04" into myDate
Code: Select all
put "'"&"2013-04-04"&"'" into myDate
Code: Select all
on mouseUp
put revOpenDatabase("mysql","localhost","database","user","password") into myDatabaseID
if myDatabaseID is an integer then
put ""&"2013-04-04"&"" into myDate
put "SELECT TIMESTAMPDIFF(MONTH, (:1), '2014-04-04')" into mySql
get revDataFromQuery(, , myDatabaseID , mySql, "myDate")
put it & cr & the result // show results in message box
revCloseDatabase myDatabaseID
else
beep
answer myDatabaseID
end if
end mouseUp
Mark
The biggest LiveCode group on Facebook: https://www.facebook.com/groups/livecode.developers
The book "Programming LiveCode for the Real Beginner"! Get it here! http://tinyurl.com/book-livecode
The book "Programming LiveCode for the Real Beginner"! Get it here! http://tinyurl.com/book-livecode
Re: Using variables in mysql functions
Hey Mark,
They say "The punch that you don't see is the one that knocks you out" in this case it was the single quotes
Thanks fellow, works fine now. Still have no idea why I needed to remove the single quotes. As every example I have seen in mysql requires them.
In any case thanks again Mark for your help in solving this and all who tried to help.
They say "The punch that you don't see is the one that knocks you out" in this case it was the single quotes
Thanks fellow, works fine now. Still have no idea why I needed to remove the single quotes. As every example I have seen in mysql requires them.
In any case thanks again Mark for your help in solving this and all who tried to help.
Re: Using variables in mysql functions
Hi,
When you use place holders and a variable list, LiveCode escapes the values in the variables in a way comparable to SQLi. This should protect against code injection. If you include single quotes, the quotes are considered part of the value and are escaped too. LiveCode then adds another set of single quotes.
Kind regards,
Mark
When you use place holders and a variable list, LiveCode escapes the values in the variables in a way comparable to SQLi. This should protect against code injection. If you include single quotes, the quotes are considered part of the value and are escaped too. LiveCode then adds another set of single quotes.
Kind regards,
Mark
The biggest LiveCode group on Facebook: https://www.facebook.com/groups/livecode.developers
The book "Programming LiveCode for the Real Beginner"! Get it here! http://tinyurl.com/book-livecode
The book "Programming LiveCode for the Real Beginner"! Get it here! http://tinyurl.com/book-livecode
Re: Using variables in mysql functions
So in essence LC will add the single quotes automatically if there is a var list with a placeholder? Great to understand the inner workings of LC a little better.
Thanks Mark
Thanks Mark