DATABASE FUNCTIONS

[Frank D. Engel, Jr.]

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 
directly.

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!
else
   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 
anymore:

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
else
   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 
returned):

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 www.doteasy.com