Site navigation
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.
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:
/usr/local/regina/include (header files)/usr/local/regina/lib (libraries)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.
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.
./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/libEdit these paths for your Rexx interpreter.
Depending on your interpreter, you may need to add extra configure options.
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.
./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
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.
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!
make install to install Rexx/SQL.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.
/* 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'
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.
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.