This file includes instructions on building Rexx/SQL with ODBC support
provided by the Easysoft ODBC-ODBC Bridge.

The Easysoft ODBC-ODBC Bridge has been proven with Rexx/SQL 2.4beta
and should work with later versions too.

Contents:

o About Rexx/SQL
o Rexx Interpreter
o Building Rexx/SQL with OOB
o Rexx/SQL Examples
  o simple.cmd
  o errors
  o Retrieving Data
o Resources

About Rexx/SQL
==============

Rexx/SQL provides Rexx programmers with a consistent, simple, yet
powerful interface to SQL databases.

Rexx/SQL's WWW home is http://rexxsql.sourceforge.net/index.html (and
father site, http://www.rexx.org).

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 interpeter 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:

for header files: /usr/local/regina/include
for libraries: /usr/local/regina/lib

Building Rexx/SQL with OOB
==========================

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 OOB 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 OOB was installed as you
   will need it when building Rexx/SQL.

   OOB comes with the unixODBC Driver Manager and we strongly suggest
   you install that as part of the OOB installation. You can build
   Rexx/SQL directly against the OOB driver but it provides more
   flexibility to build against the unixODBC Driver Manager.

2. Download Rexx/SQL from Rexx/SQL's home page
   http://sourceforge.net/projects/rexxsql (or alternatively
   ftp://ftp.easysoft.com/pub/beta/odbc-odbc-bridge/Rexx_SQL) and
   unpack the distribution.

3. Please read very carefully the INSTALL files in the Rexx/SQL and
   OOB distributions. A FAQ is distributed with OOB if you require
   further assistance.

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:

   a) this means Rexx/SQL will work with any ODBC driver installed
      under unixODBC.

   b) the OOB comes 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 OOB 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 redifinition 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 OOB ODBC data source local to the machine where the
   OOB client and Rexx/SQL are installed. 

   To do this you must create an odbc.ini file containing the
   data source (see DSN_definition.txt in the OOB
   distribution). 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
   tests. e.g

   ./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 OOB 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 OOB 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 the 

/* 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:

http://www.easysoft.com/developer/interfaces/odbc/diagnostics_error_status_codes.html 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
=========

Regina Rexx Interpreter
  http://regina-rexx.sourceforge.net/

Rexx/SQL
  http://rexxsql.sourceforge.net/

The Rexx Language Association
  http://www.rexxla.org/

IBMs Rexx Web site
  http://www-306.ibm.com/software/awdtools/rexx/

