Frank D. Engel, Jr. fde101 at
Fri Sep 17 18:03:02 EDT 2004

I tried the Query Builder for a short time, but gave up on ever getting 
it to behave the way I needed it to.  So I just use the library calls 

The docs are reasonable for the most part, but I haven't seen too much 
in the way of a good tutorial here, so just in essence for you, to get 
you started (I use PostgreSQL, but the adjustments should be fairly 
easy here for MySQL -- probably just replace "PostgreSQL" with "MySQL" 
when opening the connection):

Before using the database, you need to create a connection.  You do 
this (maybe in the preOpenStack handler, or in a login button) with the 
revOpenDatabase function.  That function returns a "connection id", 
which you will need to retain; I like to do that in a custom property 
of my main stack:

set the database of this stack to revOpenDatabase("PostgreSQL", 
"localhost", "myDatabase", "myUsername", "myPassword")

Then, if the connection was made, the returned value will be an 
integer; otherwise, it will be an error message:

if the database of this stack is an integer then
   -- okay, we've connected!
   answer "Unable to connect to database due to an error: " & the 
database of this stack
end if

When you are finished with the database connection, possibly in a 
closeStack handler, or a "Logout" button, it is a good idea to close 
the connection; after the connection is closed, you cannot use it 

revCloseDatabase the database of this stack

In order to execute an SQL statement, assuming you don't need BLOBs, 
use the revQueryDatabase function:

put revQueryDatabase(the database of this stack, "SELECT * FROM 
myTable") into q

The return value will be a "result set ID" number, another integer, if 
the query was successful and returns a result set (SELECT), it will 
(usually) be empty if the query was successful and does not return a 
result set (INSERT, UPDATE, CREATE, ALTER, ...), and it will be an 
error message if there was an error:

if q is an integer then
   -- parse result set
   answer "There was an error when querying the database: " & q
end if

In order to work with the result set, you use the revMoveToNextRecord 
command and the revDatabaseColumnNamed function (here I use the 
revNumberOfRecords function to determine how many records were 

repeat for revNumberOfRecords(q) times
   put revDatabaseColumnNamed(q, "myColumn") into field "myField"
   revMoveToNextRecord q
end repeat

After retrieving all of the data in a result set (when q is an 
integer), it is a good idea to close the result set:

revCloseCursor q

After that, the result set is no longer usable.

There are numerous other options, but this should be enough to get you 
started at least (maybe everything you need, depending on what you are 
trying to accomplish).

Hope this helps!

On Sep 17, 2004, at 5:05 PM, Trevor DeVore wrote:
>> Doesn't anyone here work with the dynamic (sql) stuff in rev?

$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

More information about the use-livecode mailing list