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

Using variables in mysql functions

Post by istech » Wed May 20, 2015 7:42 am

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

Mark
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 5138
Joined: Thu Feb 23, 2006 9:24 pm
Contact:

Re: Using variables in mysql functions

Post by Mark » Wed May 20, 2015 11:16 am

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

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

Re: Using variables in mysql functions

Post by istech » Wed May 20, 2015 3:35 pm

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

dunbarx
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 6237
Joined: Wed May 06, 2009 2:28 pm
Location: New York, NY

Re: Using variables in mysql functions

Post by dunbarx » Wed May 20, 2015 3:41 pm

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.

Code: Select all

put "123" into tDate
answer tDate && "tDate"
See what I mean? Fix the line

Code: Select all

get revDataFromQuery(, , theID, tSQL, "date")
Craig Newman

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

Re: Using variables in mysql functions

Post by istech » Wed May 20, 2015 4:02 pm

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

dunbarx
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 6237
Joined: Wed May 06, 2009 2:28 pm
Location: New York, NY

Re: Using variables in mysql functions

Post by dunbarx » Wed May 20, 2015 4:08 pm

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

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

Re: Using variables in mysql functions

Post by istech » Wed May 20, 2015 4:17 pm

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

dunbarx
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 6237
Joined: Wed May 06, 2009 2:28 pm
Location: New York, NY

Re: Using variables in mysql functions

Post by dunbarx » Wed May 20, 2015 4:49 pm

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

Mark
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 5138
Joined: Thu Feb 23, 2006 9:24 pm
Contact:

Re: Using variables in mysql functions

Post by Mark » Wed May 20, 2015 5:37 pm

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

dave.kilroy
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 858
Joined: Wed Jun 24, 2009 1:17 pm
Location: Plymouth, UK
Contact:

Re: Using variables in mysql functions

Post by dave.kilroy » Wed May 20, 2015 6:30 pm

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
"...this is not the code you are looking for..."

SparkOut
Posts: 2028
Joined: Sun Sep 23, 2007 4:58 pm

Re: Using variables in mysql functions

Post by SparkOut » Wed May 20, 2015 8:16 pm

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.

Mark
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 5138
Joined: Thu Feb 23, 2006 9:24 pm
Contact:

Re: Using variables in mysql functions

Post by Mark » Wed May 20, 2015 10:09 pm

Hi,

You need to write

Code: Select all

 put "2013-04-04" into myDate
instead of

Code: Select all

put "'"&"2013-04-04"&"'" into myDate
I.e. leave out the single quotes. A complete script could be:

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

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

Re: Using variables in mysql functions

Post by istech » Thu May 21, 2015 7:28 am

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

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.

Mark
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 5138
Joined: Thu Feb 23, 2006 9:24 pm
Contact:

Re: Using variables in mysql functions

Post by Mark » Thu May 21, 2015 7:40 am

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

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

Re: Using variables in mysql functions

Post by istech » Fri May 22, 2015 8:20 am

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

Post Reply

Return to “Getting Started with LiveCode - Complete Beginners”