database connect question

[FireWorx]

Your right !

SO THIS iRev script WORKS OK BUT...: I need to pass the data to the query via the global pNum and variable not hard coded into the iRev file.

<?rev set the errorMode to "inline" ?>
<?rev put revOpenDatabase("mysql", "localhost", "Database_Name", "User_Name_", "password") into tConId
put "'" & "400" & "'" into pNum
put "SELECT * FROM `Table_Name` WHERE StreetNumber =" && pNum into tSQL
if tConId is a number then
put revDataFromQuery(tab, return, tConID, tSQL) into tList
put tList
revCloseDatabase tConID
end if
?>

IN OTHER WORDS what am trying to do IN THE LC STACK is

on mouseup
global pNum
put "'" & "400" & "'" into pNum
put "theQuery=urlencode(myQuery)" into myQuery
post myQuery to url "http://www.mydomain.on-rev.com/NewQuery.irev"
      put it into tMyResult
answer tMyResult
end mouse up

Then in the iRev File on the server named /NewQuery.irev"

<?rev set the errorMode to "inline" ?>
<?rev put revOpenDatabase("mysql", "localhost", "Database_Name", "User_Name_", "password") into tConId
global pNum ## <----THIS IS THE PART THAT IS NOT WORKING
put "SELECT * FROM `Table_Name` WHERE StreetNumber =" && pNum into tSQL
if tConId is a number then
put revDataFromQuery(tab, return, tConID, tSQL) into tList
put tList
revCloseDatabase tConID
end if
?>
Anyone? Help Bankok? I suspect globals need to be performed with the $_Get command but I can't wrap my head around it. I need a simple example showing the data being transferred from the LC desktop to the iRev script.
Dave
Dave

[bangkok]

You can't pass a "global" variable from a LC desktop to... a irev script.

Here is what you need to do : you're going to send a "parameter", and catch it with $_POST . Plus you've added quote in the urlencode function. It can't work.

Here are the codes :

Code: Select all

on mouseup
    put "400" into pNum
  put "theQuery="&urlencode(pNum) into myQuery
   post myQuery to url "http://www.mydomain.on-rev.com/NewQuery.irev"
         put it into tMyResult
   answer tMyResult
end mouse up

Now in your irev script.

Code: Select all

<?rev set the errorMode to "inline"

#### here you receive the parameter sent by the LC desktop app
put $_POST["theQuery="] into theQuery

put revOpenDatabase("mysql", "localhost", "Database_Name", "User_Name_", "password") into tConId

put "SELECT * FROM mytable WHERE StreetNumber ="&theQuery into tSQL

put revDataFromQuery(tab, return, tConID, tSQL) into tList

revCloseDatabase tConID

put tList

?>

Voila.

After that, once it will work, you'll need to add some security :
-sanitize the parameter you receive from the LC app.
Look at the thread here :
http://forums.runrev.com/phpBB2/viewtop ... 12&t=11855

[FireWorx]

Cool...
And can I add another parameter by inserting a comma after the first? I would like both street number and street name to be sent. Thanks and thanks on the sanitize data script. I very much appreciate your help.

I made this work but I like the way you did it better I think.

<?rev set the errorMode to "inline" ?>
<?rev put revOpenDatabase("mysql", "localhost", "dbName", "username", "password") into tConId
put $_GET["pStreetName"] into tStreetName
put $_GET["pStreetNum"] into tStreetNum
put "SELECT * FROM `AlarmInfo` WHERE StreetNumber =" && tStreetNum && "AND StreetName =" && tStreetName into tSQL
if tConId is a number then
put revDataFromQuery(tab, return, tConID, tSQL) into tList
put tList
revCloseDatabase tConID
end if
?>

Then passed two parameters after the url
mydomain.on-rev.com/acquery.irev?pStreetName='abalone'&pStreetNum='400'

[bangkok]

Cool...
And can I add another parameter by inserting a comma after the first? I would like both street number and street name to be sent.

Yes it's possible, by using "items" (tabulation or comma)

For instance

Code: Select all

put "400" into pNum
put "New street" into pStreet
put pnum&tab&pStreet into myQuery
put "theQuery="&urlencode(pNum) into myQuery

And then :

Code: Select all

set itemdelimiter to tab
put $_POST["theQuery="] into theQuery
put item 1 of theQuery into tStreetNum 
put item 2 of theQuery into tStreetName 
put "SELECT * FROM `AlarmInfo` WHERE StreetNumber ='"&tStreetNum&"' AND StreetName ='"&tStreetName&"'" into tSQL

Why I prefer to put the "single quote", hardcoded into the SQL query ?

Because with your method (the single quotes are passed with the parameters), you can't sanitize the parameters.

The rule should be (to avoid security issue) : to remove all single quote, double quote (; = --) etc.... in all the input the irev script will receive.

So instead of :

Code: Select all

"AND StreetName =" && tStreetName into tSQL

It's better if you do :

Code: Select all

"' AND StreetName ='"&tStreetName&"'" into tSQL

[FireWorx]

Hi I tried the script as you wrote it and it did not work for me. I think in the button script you placed the pNum var where you might have wanted to put the concateted var myQuery. I witched those and I think I got that part right. However the iRev script does not get any data in TheQuery. See Below and if you can help that would be great.

BUTTON SCRIPT
on mouseup
 put "400" into pNum
put "New street" into pStreet
put pnum&tab&pStreet into myQuery
put "TheQuery="&urlencode(myQuery) into TheQuery
ask "whats up?" with TheQuery ## for test purposes
## at this point TheQuery contains theQuery=400%09New+street
 post TheQuery to url "http://www.mydomain.on-rev.com/newquery.irev"
         put it into tMyResult
   answer tMyResult
end mouse up

THE iREV SCRIPT BELOW where the var TheQuery continues to be empty hence leaving the vars tStreetNum and tStreetName empty as well. ? The SQL query happens but of course the query is no good without the parameters
hmm? What do you think i"m doing wrong.

<?rev set the errorMode to "inline" ?>
<?rev put revOpenDatabase("MY CONNECT STRING) into tConId

set itemdelimiter to tab
put $_POST["TheQuery="] into TheQuery
put item 1 of TheQuery into tStreetNum
put item 2 of TheQuery into tStreetName
put "SELECT * FROM `AlarmInfo` WHERE StreetNumber ='"&tStreetNum&"' AND StreetName ='"&tStreetName&"'" into tSQL

if tConId is a number then
put revDataFromQuery(tab, return, tConID, tSQL) into tList
revCloseDatabase tConID
end if
put tList
?>

[bangkok]

Don't :

Code: Select all

put $_POST["TheQuery="]

But :

Code: Select all

put $_POST["TheQuery"]

[the "=" is wrong, my mistake]

[FireWorx]

Works Perfect ! Thanks again ! U da man!