SELECT FROM Db

[Jan Schenkel]

--- Liam Lambert <liamlambert at mac.com> wrote:
> Hello everyone
> what am I doing wrong with this script
> I have used similar code that worked but this does
> not
> 
>   global gConID
>    local tBookingID
>    set itemDel to tab
>    put fld "id"  into tName
> 
>    put "SELECT  (Surname) FROM Booking where
> FirstName = 'tName'"  
> into tFname
>    put revdb_querylist(,,gConID,tFname) into tName
>    put tName into fld "surName"
> 
> Thanks
> Liam
> 

Hi Liam,

The SQL query has no direct way of knowing that tName
is a variable in Revolution - that's probably why you
get an error saying something is wrong in your query.

There are two solutions:

1) Either construct the query as a whole:
##
  global gConID
  put fld "id" into tName
  put "SELECT Surname FROM Booking" && \
      "WHERE FirstName = '" & tName & "'" \
      into tQuery
  put revdb_querylist(,,gConID,tQuery) into tSurName
  put tSurName into fld "surName"
##

2) Or use variable binding:
##
  global gConID
  put fld "id" into tName
  put "SELECT Surname FROM Booking" && \
      "WHERE FirstName = :1" \
      into tQuery
  put revdb_querylist(,,gConID,tQuery,"tName") \
      into tSurName
  put tSurName into fld "surName"
##

Personally, I prefer method 1, while using the 'merge'
function to make things easier to read.
##
  global gConID
  put fld "id" into tName
  put merge("SELECT Surname FROM Booking" && \
      "WHERE FirstName = '[[tName]]'") \
      into tQuery
  put revdb_querylist(,,gConID,tQuery) into tSurName
  put tSurName into fld "surName"
##
But for BLOB data, the data binding is a blessing.

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