database query

[Jan Schenkel]

--- Robert Mann <robmann at gp-racing.com> wrote:
> Is there a way to pass a global variable to a mysql
> query?
> 
> here is what I would like to do 
> display a query in a list (which I can do this)
> select from the list (store it in a variable)
> then pass that selection to a new query
> 
> any thoughts on how to do this?
> 
> Robert Mann
> 

Hi Robert,

To achieve this, you can either construct the query
yourself, filling in the data from the global, or you
can use the variable binding parameters.

In the code below, I assume that you're already
connected to the database and have saved the
connection ID into a global variable named
gConnectionID

Approach 1:
##
on mouseUp
  global gConnectionID
  -- first we declare the globals
  global gCustName, gCustCity
  -- then we construct the query
  put "SELECT * FROM Customers WHERE" && \
      "Name = '" & gCustName & "' AND" && \
      "City = '" & gCustCity & "'" \
      into tSQLQuery
  -- query the database
  put revQueryDatabase(gConnectionID, tSQLQuery) \
      into tCursorID
  if tCursorID is not a number then
    answer error tCursorID
    exit mouseUp
  end if
  -- now do your thing with the result set
  -- ...
  -- finally clean up
  revCloseCursor tCursorID
end mouseUp
##

Approach 2:
##
on mouseUp
  global gConnectionID
  -- first we declare the globals
  global gCustName, gCustCity
  -- then we construct the query
  put "SELECT * FROM Customers WHERE" && \
      "Name = :1 AND City = :2" \
      into tSQLQuery
  -- query the database
  put revQueryDatabase( \
      gConnectionID, \
      tSQLQuery, \
      "gCustName", \
      "gCustCity",) \
      into tCursorID
  if tCursorID is not a number then
    answer error tCursorID
    exit mouseUp
  end if
  -- now do your thing with the result set
  -- ...
  -- finally clean up
  revCloseCursor tCursorID
end mouseUp
##

In approach 1, you can use a function like 'merge' to
make the script a little more readable for long
queries involving a lot of variables.

Hope this helped,

Jan Schenkel.

Quartam Reports for Revolution
<http://www.quartam.com>

=====
"As we grow older, we grow both wiser and more foolish at the same time."  (La Rochefoucauld)

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com