A Rexx interface to SQL databases

Version 2.5

10 October 2006

6. Using Rexx/SQL

SQL statements fall into two broad categories DDL and DML. DDL is Data Definition Language. These are statements like CREATE TABLE, DROP INDEX. DML statements are Data Manipulation Language statements of which there are two forms; queries (SELECT statements) and data modification statements (INSERT, UPDATE and DELETE statements).

To execute any SQL statement the program must first connect to a database server.

Each statement must be executed in a work area or context area.

For DDL statements, the underlying steps are:

allocate a work area
parse (prepare) the statement
execute the statement
release any resources

For DML data modification statements, the underlying steps are:

allocate a work area
parse (prepare) the statement
bind any required values to the placemarkers (if any)
execute the statement
release any resourcesb

For DML query statements, the underlying steps are:

allocate a work area
parse (prepare) the statement
bind any required values to the placemarkers (if any)
execute the statement
fetch each row until end of selection (or done)
release any resources

Since there is a reasonable overhead in allocating work areas and in parsing statements these should be minimised. The Rexx/SQL interface provides the means of doing this. The SQLPREPARE function allocates a work area to a statement and parses the statement. Work areas are deallocated from a statement when the SQLDISPOSE call is issued. While a statement is allocated to a work area it remains prepared (that is parsed and optimised). Because statement names are global, preparing a different statement with the same name as an existing statement disposes the existing one. After a statement has been prepared with SQLPREPARE , it is bound to a work area and remains bound until the statement is disposed of with SQLDISPOSE . The statement can be executed many times by the following means:

Queries - repeatedly opening and closing the cursor using the functions; SQLOPEN, SQLFETCH and SQLCLOSE. Typically, multiple calls are made to SQLFETCH to retrieve all rows selected in the cursor. SQLCLOSE is optional.
Data modification statements - repeatedly calling SQLEXECUTE. Each call may supply new bind values. The statement is not reparsed each time.

The following table shows the order in which the database functions are to be called for the different types of SQL statements.

DML		DDL
SELECT	INSERT,DELETE etc.	CREATE,DROP etc.	DESCRIBE
SQLPREPARE	SQLPREPARE	SQLPREPARE	SQLPREPARE
SQLOPEN	SQLEXECUTE	SQLEXECUTE	SQLDESCRIBE
SQLFETCH (in loop)	SQLDISPOSE	SQLDISPOSE	SQLDISPOSE
SQLCLOSE
SQLDISPOSE

Guidelines for Efficient Rexx/SQL

Rexx/SQL provides a couple of different mechanisms with which to execute SQL statements. This section provides some guidelines on what Rexx/SQL functions should be used when. By default use SQLCOMMAND unless otherwise specified below. It is more efficient to call this one Rexx/SQL function than to call the equivalent individual functions described above.

DDL

Always use SQLCOMMAND.

DML query statements

There following are reasons why you might need to consider using the individual Rexx/SQL functions rather than SQLCOMMAND:

When you need to execute the same query multiple times with different values of columns in the WHERE clause. See Other DML Statements below for more details.
When the number of rows expected to be returned is very large. SQLCOMMAND fetches every row from the query into stems for each column. If you are returning a large number of rows this can take quite a long time and use quite a lot of memory for the column contents. Calling SQLFETCH for each row, or fetching a small number of rows, say 100, in each call to SQLFETCH will reduce memory usage. It won't however reduce the time it takes; it will increase it if you eventually return every row.
When you don't require the contents of every row in the query. In this case you may have a query that returns many rows, but you are only interested in the first row. Rather than have SQLCOMMAND fetch every row, you can simply call SQLFETCH once to get the contents of the first row of data.

Other DML statements

The most appropriate usage of the individual Rexx/SQL functions is when you need to call the same DML statement repeatedly, but with different values. If you are doing multiple inserts to the one table, it is more efficient to insert those rows with a prepared statement that includes parameter markers, and call SQLEXEC multiple times with the different values for each inserted row. Of course, the database must support placemarkers in SQL statements.

Dynamic Library Implementations

The Rexx external functions in the dynamic library need to be loaded by a call to RxFuncAdd() followed by a call to SQLLOADFUNCS, or its database specific equivalent. eg.

              Call RXFuncAdd 'SQLLoadFuncs','rexxsql','SQLLoadFuncs'
              Call SqlLoadFuncs

To load all Rexx/SQL external functions using the Oracle specific dynamic library:

              Call RXFuncAdd 'ORALoadFuncs','rexxora','ORALoadFuncs'
              Call ORALoadFuncs

NB. The case of the function name specified in the third parameter of the RXFuncAdd() function MUST be exactly as indicated in the above examples. When using Rexx/SQL on Unix platforms, the second parameter must also be specified in lower case to match the name of the shared library.

Before exiting from a Rexx/SQL program, call SQLDROPFUNCS. This call does not deregister the external functions, rather it frees up all resources used by the current program.

Return to Table of Contents
Last updated 10 October 2006