• Welcome to OS2World OLD-STATIC-BACKUP Forum.
 

News:

This is an old OS2World backup forum for reference only. IT IS READ ONLY!!!

If you need help with OS/2 - eComStation visit http://www.os2world.com/forum

Main Menu

Retrieve information with rexxsql

Started by jep, 2008.04.02, 12:54:55

Previous topic - Next topic

jep

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

RobertM

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.


|
|
Kirk's 5 Year Mission Continues at:
Star Trek New Voyages
|
|


jep


Good to know because I've read it several times and never dared to try that one!  :)

//Jan-Erik