OS2 World.Com Forum
2012.05.25, 23:26:58 *
Welcome, Guest. Please login or register.

Login with username, password and session length
News:
 
   Home   Help Search Login Register  
Pages: [1]
  Print  
Author Topic: Retrieve information with rexxsql  (Read 1707 times)
jep
Global Moderator
Sr. Member
*****
Posts: 402


View Profile
« on: 2008.04.02, 12:54:55 »

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

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

Code:
/* 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 ).
« Last Edit: 2008.04.03, 15:44:43 by jep » Logged
RobertM
Global Moderator
Hero Member
*****
Posts: 2017



View Profile WWW
« Reply #1 on: 2008.04.03, 01:40:31 »

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

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

jep
Global Moderator
Sr. Member
*****
Posts: 402


View Profile
« Reply #2 on: 2008.04.03, 15:44:08 »


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

//Jan-Erik
Logged
Pages: [1]
  Print  
 
Jump to:  

Powered by MySQL Powered by PHP Powered by SMF 1.1.14 | SMF © 2006-2011, Simple Machines LLC Valid XHTML 1.0! Valid CSS!