Marked as: Normal
Hello,
Here's a small example on how to query a database and retrieve information.
When you've managed to install and configure the database and ODBC drivers, then you'll find this part easy. ;)
There are a few things you need to do to use a database:
Connect to the database (SQLConnect)
if it was sucessful (= 0),
create the SQL statement you want to use and send the SQL statement to the database engine ( INSERT, UPDATE, SELECT, UNOAD, READ etc. )
if it was sucessful (= 0),
close the connection /* If we don't want to use it in the near future */
loop through the values returned
/* Params: Table_name, Password */
rxGetPKey4Table: procedure
search_4_table = ARG(1) /* Name of table to search for Primary keys within */
pwd = ARG(2) /* Password for this conenction to the database */
if SQLConnect( "s1", "dba", pwd, "TESTDB" ) = 0 then
do
sqlstr = "SELECT column_name FROM sys.syscolumn KEY INNER JOIN sys.systable WHERE table_name = '"||search_4_table||"' AND pkey = 'Y'"
if SQLCOmmand( "s1", sqlstr ) = 0 then
do
call SQLDisconnect /* We close the connection as there's nothing more we want to retrieve */
say "Table "||search_4_table
do j = 1 to s1.column_name.0
say "Column name for primry key is "||s1.column_name.j
end
end
end
Return rc /* rc = automatic return value from functions to indicate if something is wrong or not */
Note 1: You can retrieve the information by using the first parameter to SQLCommand ( s1 in the example ) paired with column_name form "SELECT column_name FROM..." followed by the order number, that is s1.column_name.0 to get the number of found entries, s1.column_name.1, s1.column_name.2 etc. to get the actual data.
Note 2: You can open several connections, even to other databases at the same time, by specifying different connection placeholders ( First parameter to SQLConnect and SQLCommand ).
Actually, you can use the SELECT * FROM statement - it will return everything and build out the stem variables like this:
Stemname.Field1.1
Stemname.Field2.1
Stemname.Field3.1
Stemname.Field1.2
Stemname.Field2.2
Stemname.Field3.2
and so on....
I simplified this (doing database retrieves) by creating a function that incorporated all the calls necessary for a query into one function. It is ideal for cgi scripts, where you need to close the connections when the query is complete, but may not be as ideal for programs where you want to open a connection once and reuse it.