Re: mySQL Query... need some help
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: [EMAIL PROTECTED] Cognitive Applications Ltd | Phone: +44 (0)1273-821600 http://www.cogapp.com| Fax : +44 (0)1273-728866 ___ use-revolution mailing list [EMAIL PROTECTED] http://lists.runrev.com/mailman/listinfo/use-revolution
Re: mySQL Query... need some help
Also, if your field is accepting user input, be careful about
escaping the strings. Otherwise, if the user inputs text with a
quote delimiter (') in it, the query may fail in the database, or give
unexpected results. Run each text field you aren't absolutely sure of
through a function something like this:
function quoteString inText
put inText into x
replace ' with '' in x
return x
end quoteString
Now you are looking at something more like:
put revDataFromQuery(,,databaseID, SELECT * FROM mybuddies WHERE
FirstName = quoteString(field myQuery))
On Dec 17, 2004, at 5:05 AM, Ben Rubinstein wrote:
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: [EMAIL PROTECTED]
Cognitive Applications Ltd | Phone: +44 (0)1273-821600
http://www.cogapp.com| Fax : +44 (0)1273-728866
___
use-revolution mailing list
[EMAIL PROTECTED]
http://lists.runrev.com/mailman/listinfo/use-revolution
---
Frank D. Engel, Jr. [EMAIL PROTECTED]
$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep John 3:16
John 3:16 For God so loved the world, that he gave his only begotten
Son, that whosoever believeth in him should not perish, but have
everlasting life.
$
___
$0 Web Hosting with up to 120MB web space, 1000 MB Transfer
10 Personalized POP and Web E-mail Accounts, and much more.
Signup at www.doteasy.com
___
use-revolution mailing list
[EMAIL PROTECTED]
http://lists.runrev.com/mailman/listinfo/use-revolution
mySQL Query... need some help
I'm a genuine newbie and have been having a major difficulty in trying to format a mySQL query and thought that maybe someone can point me in the right direction. All I'm trying to accomplish at this point is to search for a specific record and display the info in the proper fields. Here's how I have things set up... Currently the DB is connected when the card opens DB name is: mybuddies Table name is :revprogrammers DB Fields: FirstName (Primary) LastName PhoneNumber Text Fields In Stack: ShowFirstName ShowLastName ShowPhoneNumber Query Field: myQuery I *think* this is the right general direction (not working, obviously) on mouseUp get revDataFromQuery(,,SELECT * FROM mybuddies WHERE FirstName = field 'myQuery') end mouseUp I'd jump for joy if someone could point out what I'm doing wrong or point me to a *really simple* mySQL tutorial/stack to learn from Thanks, -Doc- ___ use-revolution mailing list [EMAIL PROTECTED] http://lists.runrev.com/mailman/listinfo/use-revolution
Re: mySQL Query... need some help
I'm a genuine newbie and have been having a major difficulty in trying to format a mySQL query and thought that maybe someone can point me in the right direction. All I'm trying to accomplish at this point is to search for a specific record and display the info in the proper fields. Here's how I have things set up... snip I'd jump for joy if someone could point out what I'm doing wrong or point me to a *really simple* mySQL tutorial/stack to learn from Thanks, -Doc- At my web site, I have a MySQLtest.rev stack which I used when teaching myself all this stuff. It has buttons to make the connection, list tables, show table structures and perform queries as well as a rather crude interface for editing table structures. It might help you get going anyway. Cheers, Sarah [EMAIL PROTECTED] http://www.troz.net/Rev/ ___ use-revolution mailing list [EMAIL PROTECTED] http://lists.runrev.com/mailman/listinfo/use-revolution
