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