Accessing ODBC Databases from Rexx by using the Rexx/SQL Interface

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

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

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

Easysoft ODBC drivers have been proven with Rexx/SQL 2.4beta and should work with later versions too.

About Rexx/SQL

The Rexx/SQL home page contains download links, documentation, mailing list instructions and links to other Rexx resources.

Rexx/SQL's WWW home is http://rexxsql.sourceforge.net/index.html.

You can find the Rexx/SQL downloads at http://sourceforge.net/projects/rexxsql under the download link.

The Rexx/SQL home page contains download links, documentation, mailing list instructions and links to other Rexx resources.

Rexx Interpreter

Before you use Rexx/SQL you will need a Rexx interpreter. You can find the Regina Rexx interpreter at http://regina-rexx.sourceforge.net.

We used version 2.2 when writing this document but since then there are more recent releases.

You need to make a note of which Rexx interpreter you install and where it stores the 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 will need a Rexx interpreter to run the Rexx/SQL test code and your Rexx programs using Rexx/SQL (see previous section).

We have tried Rexx/SQL 2.4 and 2.4beta but this should also work with any later releases.

You may use ./configure --help in Rexx/SQL to see which Rexx interpreters are supported.

Please 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 installation path used when the Easysoft ODBC driver was installed as you will 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 to build against the unixODBC Driver Manager.

  2. Download Rexx/SQL from Rexx/SQL's home page and unpack the distribution.
  3. Please read very carefully the INSTALL files in the Rexx/SQL and the Easysoft ODBC driver distributions. A FAQ is distributed with OOB 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 locate the options required. Here there are some differences between Rexx/SQL versions 2.4beta and 2.4 e.g.

    the older option --with-regina is now replaced with --with-rexx=regina. We document the options for the 2.4 final release here.

    We need configure options:

    • --with-rexx=regina
    • --with-rexxincdir=/usr/local/regina/include
    • --with-rexxlibdir=/usr/local/regina/lib

    Amend these 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 which tell Rexx/SQL which database driver you are going to use and where to find it. We are 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 come with 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 by default into /usr/local/easysoft/unixODBC. If you already have unixODBC installed you need to locate it (commonly it is 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.

    NOTE: Here again the configure options in Rexx/SQL 2.4beta and 2.4 changed. You used to define the database driver with a separate option e.g. --with-easysoft-oob:

        ./configure --with-regina --with-easysoft-oob \
          --with-dbincdir=/usr/local/easysoft/oob/client/include \
          --with-dblibdir=/usr/local/easysoft/oob/client
      
  6. Putting all the configure options together we have:
      ./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 errors for redefinition of CHAR 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
      

    this is due to a conflict between unixODBC and Regina which are both defining a typedef of CHAR and VOID. You will need to comment out the typedefs for CHAR and VOID in rexxsaa.h. In the case above commenting out lines 71 and 88 in rexxsaa.h (whilst compiling) with:

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

    should do. Do not forget to uncomment them after you have built Rexx/SQL.

  8. You should then consult the "Testing the Installation" section of the Rexx/SQL INSTALL file.

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

    To do this you must create an odbc.ini file containing the data source (see the DSN_definition.txt file in the OOB distribution or your Easysoft ODBC driver documentation). If the remote machine is a Windows platform, you also need to create an ODBC data source for the database you wish to connect to, using the ODBC Administrator.

    As an example, assume you have MS SQL Server running on a remote windows machine (called ntbox) where you have installed the OOB Server. You use "myname" and "mypassword" to log into the Windows machine "ntbox". You have set up a data source on ntbox with the ODBC Administrator for MS SQL Server called "mydata" which requires database authentication "db_user" and "db_password". You want to access data in MS SQL Server on ntbox from your Rexx program on the other machine where you have just installed the OOB client and Rexx/SQL. Your odbc.ini file would look like this:

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

    Once you have defined the local data source you can try the Rexx/SQL

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

    To MS SQL Server you should then see something like this:

    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

    Some of the Rexx/SQL tests suite will not run properly until Rexx/SQL is installed.

  10. Rexx/SQL contains other test code such as samples/tester.cmd. You can run this by defining and exporting the environment variables REXXSQL_USERNAME, REXXSQL_PASSWORD and REXXSQL_DATABASE as your database user, password and data source name then running:
        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

Writing simple Rexx/SQL scripts is simple. Some examples are shown below:

simple.cmd

      /* load all SQL functions, make them accessible to this script  */
      call RxFuncAdd 'SQLLoadFuncs','rexxsql', 'SQLLoadFuncs'
      call SQLLoadFuncs
      
      /* connect to the Easysoft ODBC driver database with 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. Add:

      /* capture any SQL error 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
    

then changing the database calls to check for an error status e.g. from simple.cmd (above) change the SQLConnect call to:

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

and deliberately put the wrong database username or password in and you might get an error like:

      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'
    

Please see:

ODBC Diagnostics & Error Status Codes for a description of ODBC errors.

Retrieving Data

You can retrieve data with this extract:

      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" and the names of the columns in the result-set and "j" refers to the row you want.

Resources