mySQL Query... need some help

[Ben Rubinstein]

on 17/12/04 3:58 am, docmann wrote

> on mouseUp
>   get revDataFromQuery(,,"SELECT * FROM mybuddies WHERE FirstName =
>field 'myQuery'")
> end mouseUp

I can see two issues here (I won't say that there may not be other ones!).

The first one is that you need at least two parameters to
'revDataFromQuery': database id, and SQL query.  You've got the SQL query,
but not the id.  But I'm guessing you may have just left that out of the
message?  Anyway, you need to open the database before you can call
'revDataFromQuery': see revOpenDatabase for more information.

The second problem is that the query string passed as a parameter to
'revDataFromQuery' is actually passed directly to the database, in this case
MySQL.  So MySQL gets this query:

    SELECT * FROM mybuddies WHERE FirstName = field 'myQuery'

But MySQL doesn't know anything about your Revolution fields.  So you need
to get the data out of the Revolution field before passing the query to
MySQL.  You could do something like this, just using Transcript's standard
string concatenation operator:

  put field "myQuery" into tName
  put "SELECT * FROM mybuddies WHERE FirstName = '" & tName & "'" into tSQL
  get revDataFromQuery(,, iDatabaseID, tSQL)

That way, if the field contains "Bob", then the variable tSQL will be set to
(and MySQL will get):

       SELECT * FROM mybuddies WHERE FirstName = 'Bob'

Because this is quite a common pattern in making queries from databases -
most of the query is hard wired, but part is dynamic - revDataFromQuery
includes a special feature to make this more convenient.  The string you
pass to revDataFromQuery can contain placeholders, which revDataFromQuery
fills in from variables, the names of which are passed in as additional
parameters.  In this simple case, it's hardly simpler, but it can be useful
in more complicated cases.  The following code will send exactly the same
string to MySQL:

  put field "myQuery" into tName
  put "SELECT * FROM mybuddies WHERE FirstName = ':1'" into tSQL
  get revDataFromQuery(,, iDatabaseID, tSQL, "tName")

(Note that the name of the variable holding the value to be substituted for
the placeholder is passed, rather than the value - unusual in Transcript,
and slightly confusing.)

Hope this helps,
 
  Ben Rubinstein               |  Email: benr_mc at cogapp.com
  Cognitive Applications Ltd   |  Phone: +44 (0)1273-821600
  http://www.cogapp.com        |  Fax  : +44 (0)1273-728866