DATABASE FUNCTIONS
Frank D. Engel, Jr.
fde101 at fjrhome.net
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
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
More information about the use-livecode
mailing list