Accessing databases that support ODBC from Rexx by using Rexx/SQL

Contents

Introduction

You can use Easysoft ODBC drivers with Rexx/SQL to access databases such as Microsoft SQL Server, Microsoft Access, Oracle, InterBase, and Sybase ASE from Rexx. For example, access SQL Server using its Windows ODBC driver from a Rexx script by using Rexx/SQL with the Easysoft ODBC-ODBC Bridge.

Use Rexx/SQL with our Oracle, InterBase, and Sybase drivers to access those databases.

To access other databases for which an ODBC driver is available, use the ODBC-ODBC Bridge.

Rexx Interpreter

Before you can use Rexx/SQL you will need a Rexx interpreter.

You need to make a note of which Rexx interpreter you install and where it stores header and library files. We installed Regina in /usr/local/regina so the paths we need later for Rexx/SQL configuration are:

Building Rexx/SQL with the unixODBC Driver Manager

You need a Rexx interpreter to run the Rexx/SQL test code and your Rexx programs with Rexx/SQL.

We tried Rexx/SQL 2.4 and 2.4beta.

Use ./configure --help in Rexx/SQL to find out which Rexx interpreters are supported.

Ensure you install a Rexx interpreter before attempting to build Rexx/SQL.

These instructions assume you are building from a source distribution of Rexx/SQL.

  1. Install the Easysoft ODBC driver first. This is essential as Rexx/SQL needs an ODBC Driver Manager or an ODBC driver to compile and link with. Make a note of the Easysoft installation path as you need it when building Rexx/SQL.

    Easysoft ODBC drivers come with the unixODBC Driver Manager and we strongly suggest you install that as part of the Easysoft ODBC driver installation. You can build Rexx/SQL directly against the Easysoft ODBC driver, but it provides more flexibility if you build against unixODBC.

  2. Download Rexx/SQL and unpack the distribution.
  3. Read the INSTALL files in the Rexx/SQL and Easysoft ODBC driver distributions. A FAQ is distributed with ODBC-ODBC Bridge if you require further assistance. You can also refer to the product documentation for your Easysoft ODBC driver.
  4. Unpack Rexx/SQL and run ./configure --help to display the configure options. We need these configure options:
    • --with-rexx=regina
    • --with-rexxincdir=/usr/local/regina/include
    • --with-rexxlibdir=/usr/local/regina/lib

    Edit these paths for your Rexx interpreter.

    Depending on your interpreter, you may need to add extra configure options.

  5. Next, you need to define the configure options that tell Rexx/SQL which database driver you're going to use and where to find it. We're building Rexx/SQL with the unixODBC Driver Manager as:
    • This means Rexx/SQL will work with any ODBC driver installed under unixODBC.
    • Easysoft ODBC drivers include the unixODBC Driver Manager and this is the most flexible way to use ODBC drivers with Rexx/SQL.

    The unixODBC Driver Manager distributed with Easysoft ODBC drivers is installed into /usr/local/easysoft/unixODBC, by default. If you already have unixODBC installed, you need to locate it (normally it's installed in /usr/local).

    You need the following configure options to tell Rexx/SQL where to find unixODBC:

    --with-db=unixodbc
    --with-dbincdir=/usr/local/easysoft/unixODBC/include
    --with-dblibdir=/usr/local/easysoft/unixODBC/lib

    Alter the paths according to your setup.

  6. Putting all the configure options together, we get: 
    ./configure --with-rexx=regina
    --with-rexxincdir=/usr/local/regina/include 
    --with-rexxlibdir=/usr/local/regina/lib
    --with-db=unixodbc
    --with-dbincdir=/usr/local/easysoft/unixODBC/include
    --with-dblibdir=/usr/local/easysoft/unixODBC/lib
  7. Once Rexx/SQL is configured, type make all to build it.

    If you get CHAR redefinition errors like these:

    gcc -c -O2   -I. -I. -I./odbc -I./common -I./general -DUNIXODBC
    -DHAVE_CONFIG_H -I/usr/local/regina/include
    -I/usr/local/easysoft/unixODBC/include -I./cli-odbc -o loader.o 
    ./common/loader.c
    In file included from common/rxdef.h:38,
                      from common/rxpack.h:126,
                      from ./common/loader.c:23:
    /usr/local/regina/include/rexxsaa.h:71: redefinition of `CHAR'
    /usr/local/easysoft/unixODBC/include/sqltypes.h:72: 
           `CHAR' previously declared here
    /usr/local/regina/include/rexxsaa.h:77: redefinition of `VOID'
    /usr/local/easysoft/unixODBC/include/sqltypes.h:92: 
        `VOID' previously declared here

    You need to comment out the typedefs for CHAR and VOID in rexxsaa.h. The cause of the error is a conflict between unixODBC and Regina, which both defin e a typedef of CHAR and VOID. In the case above, commenting out lines 71 and 88 in rexxsaa.h and compiling with:

    /*typedef char CHAR ;*/ and /*typedef void VOID ; */

    should do. Don't forget to uncomment these lines once you have built Rexx/SQL.

  8. Consult the "Testing the Installation" section of the Rexx/SQL INSTALL file.

    To run the tests in this section, you must define an Easysoft ODBC data source local to the machine where the Easysoft ODBC driver and Rexx/SQL are installed. (If you're using the Easysoft ODBC-ODBC Bridge, the ODBC driver is the ODBC-ODBC Bridge client.)

    To do this, you must create an odbc.ini file containing the data source (refer to the DSN_definition.txt file in the ODBC-ODBC Bridge distribution or your Easysoft ODBC driver documentation). If you're using the ODBC-ODBC Bridge and the remote machine is a Windows platform, you also need to create an ODBC data source for the database you want to connect to. Use the Windows ODBC Data Source Administrator to do this.

    For example, assume you have Microsoft SQL Server running on a remote Windows machine (called winbox) where you have installed the ODBC-ODBC Bridge server. You use myname and mypassword to log into the Windows machine. You have set up a SQL Server data source on winbox called mydata, which requires database authentication (db_user and db_password). You want to access SQL Server data on winbox from your Rexx program on the other machine where you have just installed the ODBC-ODBC Bridge client and Rexx/SQL. Your odbc.ini file would have this DSN:

    [rexx_sql_test]
ServerPort = winbox:8888
LogonUser = myname
LogonAuth = mypassword
TargetDSN = mydata

    Once you have defined the local DSN, you can try Rexx/SQL:

    ./rexxsql samples/simple.cmd user=db_user pass=db_passwd data=rexx_sql_test

    The output should be similar to:

    Rexx/SQL Version: rexxsql 2.4 3 Jun 2003 UNIX ODBC
Database Name:    Microsoft_SQL_Server
Database Version: 08.00.0760
Disconnect succeeded!
  9. Make sure you run make install to install Rexx/SQL.
  10. Rexx/SQL contains other test code such as samples/tester.cmd. To run this, define and export the environment variables REXXSQL_USERNAME (database user), REXXSQL_PASSWORD (database password), and REXXSQL_DATABASE (data source) then: 
    rexxsql samples/tester.cmd setup

    The setup argument creates the tables. Then you can run the same command again but without the setup argument to exercise Rexx/SQL and the Easysoft ODBC driver more thoroughly.

Rexx/SQL examples

simple.cmd

/* Load all SQL functions, making them accessible to this script  */
call RxFuncAdd 'SQLLoadFuncs','rexxsql', 'SQLLoadFuncs'
call SQLLoadFuncs

/* Connect to the Easysoft ODBC driver database with the named user & password   */
call SQLConnect ,'db_user','db_pass','test'

/* Retrieve and display some database name and version    */
say 'The Rexx/SQL Version is:' SQLVariable('VERSION')
call SQLGetinfo ,'DBMSNAME','desc.'
say 'The database Name is: ' desc.1
call SQLGetinfo ,'DBMSVERSION','desc.'
say 'The database Version is: ' desc.1

/* Disconnnect from the database and drop the SQL functions     */
call SQLDisconnect
call SQLDropFuncs 'UNLOAD'

Errors

You can improve the above script by adding error checking:

/* Capture any SQL errors and write out error messages     */ 
sqlerr: procedure expose sqlca.
   parse arg msg
   say 'Error, message: ' msg
   say sqlca.interrm  /* write SQLCA messages */
   say 'SQL error is:' sqlca.sqlerrm    /* write SQLCA messages */
   call SQLDropFuncs 'UNLOAD'
   exit 99

Change the database calls to check for an error status. For example, in simple.cmd, change the SQLConnect call to:

if call (SQLConnect ,'db_user','db_pass','test') <> 0 then call sqlerr 'On connect'

and put the wrong database user name or password in. You might then get an error similar to:

Error, message:  On connect
REXX/SQL-1: Database Error
SQL error is: [unixODBC][][Microsoft][ODBC SQL Server Driver][SQL Server]
Login failed for user 'db_user'

Refer to ODBC diagnostics & error status codes for further information.

Retrieving data

You can retrieve data with:

sqlstr = 'select c1,c2 from mytable'
if SQLCommand(s1,sqlstr) <> 0 then call sqlerr 'On select'

/* This loop displays all rows from the SELECT statement  */
do j = 1 to sqlca.rowcount 
    say 'Column1:'  s1.c1.j  'Column2:'  s1.c2.j 
end

where c1 and c2 are the names of the result set columns and j refers to the row you want.

Resources