Skip to content

DBAzine.com

Sections
Personal tools
You are here: Home » Of Interest » Articles of Interest » Quick Database Scripting - Part 2: Portable Database Programming with Rexx
Seeking new owner for this high-traffic DBAzine.com site.
Tap into the potential of this DBA community to expand your business! Interested? Contact us today.
Who Are You?
I am a:
Mainframe True Believer
Distributed Fast-tracker

[ Results | Polls ]
Votes : 3558
 

Quick Database Scripting - Part 2: Portable Database Programming with Rexx

by Howard Fosdick

Part 1  |  Part 2

In my previous article, we discussed how scripting offers a quick way to program common database tasks. Scripting is quicker than coding in traditional languages like Java, C++, or COBOL, yet it supplies the programmability and flexibility reporting that query tools often lack.

There are quite a variety of scripting languages. Perl and Rexx top our recommendations for database scripting in IT organizations. Both run on any platform and connect to all commercial and open-source database management systems. Both are standardized, widely used, and available in free, and open-source distributions.

We chose Rexx for our scripting examples because it is very easy to learn, yet quite powerful. Our goal is for you be able to script common database tasks by the end of this article. Rexx fits nicely with this goal because it is simple to read, learn, and understand.

Getting Set Up

In the previous article, we asked you to download and install the Regina Rexx interpreter and the Rexx/SQL database interface. Both are open-source products. If you haven’t downloaded and installed these products, please do so now. The hyperlink for each product points to the relevant download Web site. Download the *.exe files if you’re using Windows, or the *.tar.gz or *.rpm files if you’re running Linux. Then decompress and install the two products in just the same way you would install any other Windows or Linux product. Installation is easy, but if you need them, both products download with install instructions.

The Rexx/SQL interface is database-independent. It is easy to redirect Rexx database scripts from one database product to another with minimal changes. Later in this article, we’ll discuss how to re-target the example scripts to other databases. To start, we’ll use the open-source MySQL database in the scripting examples. Download and install MySQL now. Decompress and install the product just like any other Windows or Linux product. It comes with complete install instructions.

Rexx/SQL supplies different interfaces or drivers to connect to different database products. You can use either the native MySQL driver or the generic ODBC driver to connect Rexx/SQL scripts to MySQL databases. The former offers best performance while the latter is more portable and generic. We ran our example scripts using the native MySQL driver.

A First Example Script

Now it is time for the first example script. The script performs several steps; with the exception of step 3, every database script performs these same steps:

      1. Load the Rexx/SQL function library for use
      2. Connect to the database
      3. Retrieve and display environmental information about the database
      4. Disconnect from the database

The output of the script looks like the following. It reports the version and release of the Rexx/SQL interface, as well as the MySQL database name and database version:

The Rexx/SQL Version is: rexxsql 2.4 02 Jan 2000 WIN32 MySQL
The database Name is:  mySQL
The database Version is:  4.0.18-max-debug

Here is the entire script:

/****************************************************************/
/* DATABASE INFO: */
/* */
/* Connects to MySQL and displays database information. */
/****************************************************************/

/* load all SQL functions, make them accessible to this script */

call RxFuncAdd 'SQLLoadFuncs','rexxsql', 'SQLLoadFuncs'
call SQLLoadFuncs


/* connect to the MySQL database with default user & password */

call SQLConnect ,,,'mysql'


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

This script shows that Rexx comments are enclosed between the starting marker /* and the ending marker */. This script starts with a comment box describing what it does. It also contains one-line comments throughout that describe its actions.

The first two executable lines in the script invoke or call the Rexx/SQL functions RxFuncAdd and SQLLoadFuncs. The former loads the single function SQLLoadFuncs into memory, while the latter executes SQLLoadFuncs. This loads the rest of the Rexx/SQL library into memory. After these two statements are completed, all Rexx/SQL commands or functions are available to the script.

In the RxFuncAdd call, the first and last parameters indicate the name of the function to load from Rexx/SQL. The middle parameter, rexxsql, indicates the name of the external Rexx/SQL library. Under Windows, the full file name for this library is rexxsql.dll, while under Linux, it is librexxsql.so. (If this sounds rather detailed, don’t worry. These two statements are always coded the same way at the start of every Rexx database script, and can even be placed into a subroutine. There’s really no need to explore them further.)

Once the Rexx/SQL library is loaded, the script needs to connect to the database:

call SQLConnect ,,,'mysql'

This SQLConnect statement connects to a MySQL database named mysql. (MySQL creates the mysql database by default when it is installed.) The SQLConnect statement is about the only statement that is coded differently, depending on which database management system to which the script connects. Here is its template:

SQLConnect([connection name], [username], [password], [database], [host])

In this case, we only encoded the database name and took defaults for the other parameters.  DB2 UDB also minimally requires the database name parameter, while for Oracle database connections, all parameters are optional. Generic ODBC database connections require the username, password, and database name. Here is an example that connects to an Oracle database as the userid scott with the password tiger:

call SQLConnect ‘MYCON’,’scott’,’tiger’         /*  Scott lives!  */

Once the database connection is established, the script displays the Rexx/SQL version. The Rexx say instruction displays a literal string message on the screen, followed by the results of the Rexx/SQL SQLVariable call:

say 'The Rexx/SQL Version is:' SQLVariable('VERSION')

Then, the script calls the SQLGetInfo function twice, with the DBMSNAME and DBMSVERSION parameters to retrieve information about the database. Both calls retrieve an array of information into the Rexx array variable desc. The value desc.1 refers to the first element retrieved and placed into the array. Rexx say instructions display the information to the user’s screen:

call SQLGetinfo ,'DBMSNAME','desc.' 
say 'The database Name is: ' desc.1

call SQLGetinfo ,'DBMSVERSION','desc.'
say 'The database Version is: ' desc.1

Finally, the script disconnects from the database and drops the Rexx/SQL function library from memory:

call SQLDisconnect
call SQLDropFuncs 'UNLOAD'

Adding Error Checking

While this first example script is a great start, DBAzine readers will recognize a big defect in this script: It does not check the return codes of database operations. Checking database return codes is essential to robust production programming. We can upgrade the script by changing the encoding of the Rexx/SQL commands into embedded functions.

For example, recode this call statement:

call SQLLoadFuncs

This equivalent Rexx statement does exactly the same thing, but adds error checking:

if SQLLoadFuncs() <> 0 then 
   say 'sqlloadfuncs failed, rc: ' rc

That the Rexx/SQL function SQLLoadFuncs is embedded within the if statement is indicated by the presence of the parentheses that immediately follow it: SQLLoadFuncs(). The embedded function plunks its return code right back into the Rexx statement so that the interpreter picks it up. Thus, as in many programming languages, Rexx gives you the option either to call a function, or encode it within larger statements as a “nested function.”

You should code any parameters that the function requires inside the parentheses that follow the function name. Code empty parentheses, as in the previous example, if there are no parameters to send to the Rexx/SQL function.

The above example checks the return code from the SQLLoadFuncs function and displays an error message via the Rexx say instruction if the Rexx/SQL function fails. All Rexx/SQL functions return 0 if successful or a non-zero value otherwise. The say instruction displays the literal message inside the single quotation marks (our error message), plus the value of the special Rexx variable named rc (rc always contains the return code of the last function called).

With this background, we can enhance the original script to manage errors encountered by the database interface. Here is the improved script:

/****************************************************************/
/* DATABASE INFO: */
/* */
/* Connects to MySQL and displays database information. */
/****************************************************************/
signal on syntax /* capture SQL syntax errors */


/* load all SQL functions, make them accessible to this script */

if RxFuncAdd('SQLLoadFuncs','rexxsql', 'SQLLoadFuncs') <> 0 then
say 'rxfuncadd failed, rc: ' rc

if SQLLoadFuncs() <> 0 then
say 'sqlloadfuncs failed, rc: ' rc


/* connect to the MySQL database, use default user/password */

if SQLConnect(,,,'mysql') <> 0 then call sqlerr 'On connect'


/* get and display some database information */

say 'The Rexx/SQL Version is:' SQLVariable('VERSION')

if SQLGetinfo(,'DBMSNAME','desc.') <> 0
then call sqlerr 'Error getting db name'
else say 'The database Name is: ' desc.1

if SQLGetinfo(,'DBMSVERSION','desc.') <> 0
then call sqlerr 'Error getting db version'
else say 'The database Version is: ' desc.1


/* disconnnect from the database and drop the SQL functions */

if SQLDisconnect() <> 0 then call sqlerr 'On disconnect'

if SQLDropFuncs('UNLOAD') <> 0 then
say 'sqldropfuncs failed, rc: ' rc

exit 0


/* capture any SQL error and write out SQLCA error messages */

sqlerr: procedure expose sqlca.
parse arg msg
say 'Program failed, message is: ' msg
say sqlca.interrm /* write SQLCA messages */
say 'SQL error is:' sqlca.sqlerrm /* write SQLCA messages */
call SQLDropFuncs 'UNLOAD'
exit 99

syntax: procedure /* capture any syntax errors */
say 'Syntax error on line: ' sigl /* identify syntax error*/
return

The main difference in this improved script is that we’ve replaced all the database calls with embedded functions in if statements that check for database errors.

We’ve made a couple of other improvements to this script as well. The script starts with a Rexx signal instruction. This enables an exception routine that catches syntax errors, as per its keywords on syntax. When the Rexx interpreter encounters a syntax error, it automatically diverts the flow of control in this program to the subroutine at the bottom of the script, named syntax. The line near the bottom of the program that says syntax: procedure indicates the start of the syntax error routine. As you can see, this routine displays a message and the value of the special Rexx variable named sigl, which indicates which statement had the syntax error. The return statement then returns control back to the main routine in the script.

The other subroutine in this script is named sqlerr. This statement identifies the start of that routine and makes the SQL Communications Area, or SQLCA, available to that outine through the expose sqlca. keywords:

sqlerr: procedure expose sqlca.

In this subroutine, the parse arg msg statement reads the string value passed into the routine and places it into the variable named msg. The subroutine then displays this message, along with various parts of the SQLCA, as indicated by the variables sqlca.interrm and sqlca.sqlerrm. The exit 99 instruction at the end of the subroutine unconditionally ends the entire script and passes the return code of 99 up to the operating system.

Here’s an example of how error messages look when this script encounters a database error. In this example, we had mistakenly coded mysqlxxxx as the database name:

Program failed, message is:  On connect
REXX/SQL-1: Database Error
SQL error is: Unknown database 'mysqlxxxx'

Note that we also added the exit 0 instruction at the end of the main routine in this example program. This ends the script with a return code of 0 and prevents execution from continuing into the subroutines sqlerr and syntax coded at the bottom of the program.

Retrieving Data

Now we know how to perform the basic database operations necessary for every script: connecting to the database, disconnecting from the database, and handling database errors. Let’s look at a sample data retrieval program.

This example retrieves one or more rows for the Phone Directory application. This application system has a single table, phonedir, that contains just two columns. The first column contains last names, while the other column contains phone numbers.
This simple script shows one way to retrieve and display all rows in the table:

/****************************************************************/
/* PHONE DIRECTORY LIST: */
/* */
/* Displays the phone directory's contents. */
/****************************************************************/
signal on syntax /* capture SQL syntax errors */
call sql_initialize /* load all Rexx/SQL functions*/

if SQLConnect(,,,'mysql') <> 0 then call sqlerr 'On connect'
if SQLCommand(u1,"use test") <> 0 then call sqlerr 'On use'

sqlstr = 'select * from phonedir order by lname'
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 'Name:' s1.lname.j 'Phone:' s1.phone.j
end

call sql_pgm_end /* disconnect, drop functions */
exit 0

The script starts by enabling the syntax error routine. The second line in the script (call sql_initialize), invokes a subroutine that contains the RxFuncAdd and SQLLoadFuncs statements. This code is exactly the same as shown in the first two sample scripts, so we’ve omitted the code of the sql_initialize routine from the previous listing for clarity of illustration.

Next, the script connects to MySQL. The script tells MySQL it wants to use the database named test by issuing the MySQL use test command. The script uses the Rexx/SQL SQLCommand function to do this. SQLCommand allows scripts to issue any SQL DML, DDL, or DCL command in a single call.

Now the program creates the select statement required to retrieve the data from the phonedir table. It assigns this statement to the Rexx variable named sqlstr. The script uses the SQLCommand function to issue the select statement in a single call, while checking the database return code:

if SQLCommand(s1,sqlstr) <> 0 then call sqlerr 'On select'

A bad return code calls the sqlerr subroutine. The code for this routine is not shown since its duplicates that which was shown in earlier program examples.

The heart of this program is its retrieval loop. The loop executes as long as there is a row in the result set to process, as indicated by the SQLCA variable named rowcount:

do j = 1 to sqlca.rowcount 
    say 'Name:'  s1.lname.j  'Phone:'  s1.phone.j
end

The syntax on the say instruction shows how to refer to the two columns in each row. s1 is the statement name, as coded and assigned when the select statement was executed:

if SQLCommand(s1,sqlstr) <> 0 then call sqlerr 'On select'

lname and phone are the names of the two columns. The subscript j allows for processing all the rows in the result set through the Rexx do loop.

This handy Rexx/SQL row notation processes multiple-row result sets without the need for cursor processing. Of course, since Rexx/SQL supports database standards, it offers traditional cursor processing, if you prefer it.

If we were to re-code this script using cursor processing, it would look like the following:

sqlstr = 'select * from phonedir order by lname'
if SQLPrepare(s1,sqlstr) <> 0 then call sqlerr 'On prepare'

if SQLOpen(s1) <> 0 then call sqlerr 'On open'

/* this loop displays all rows from the SELECT statement */

do while SQLFetch(s1) > 0
say 'Name:' s1.lname 'Phone:' s1.phone
end

if SQLClose(s1) <> 0 then call sqlerr 'On close'
if SQLDispose(s1) <> 0 then call sqlerr 'On dispose'

As always, one prepares the cursor, then opens it for processing. The Rexx/SQL SQLPrepare and SQLOpen functions perform these steps. Then, fetch rows for processing, one at a time, by the SQLFetch call. Finally, close the cursor and dispose of its resources.

Whether you use Rexx/SQL’s shorthand format for processing multi-row results sets, or traditional cursor processing, the script should issue the SQLDisconnect and SQLDrop functions when it concludes. These functions terminate the database connection and drop the Rexx/SQL library from memory.  In the example, we collected these final statements into a new subroutine we named sql_pgm_end:

call sql_pgm_end                  /* disconnect, drop functions */
exit 0

You can see that common database service routines like syntax, sqlerr, sql_intiialize, and sql_pgm_end are very handy. They centralize common processing outside of the main program driver and simplify its code. They also ensure standardization across scripts. We recommend common database routines for these reasons.

Creating the Phonedir Table and Loading Data

Now that you’ve seen how to retrieve data from a table, let’s look at how to initially create and load the table.

The SQL create table statement is part of the data definition language, or DDL. So far, we’ve only issued the select statement to retrieve data; this statement is part of SQL’s data manipulation language or DML.

The following example script shows that you can issue any DDL (or DCL) statement in a single call via the SQLCommand statement. This script first issues a SQL drop table statement to ensure that the phonedir table does not already exist. Since the program does not care whether or not this statement succeeds, it does not check for any error on the drop table statement. Then, the script issues the create table statement via a single SQLCommand call. The script employs the common database service routines introduced previously.

Here is the script:

/****************************************************************/
/* PHONE DIRECTORY: */
/* */
/* Creates the phone directory and loads data into it. */
/****************************************************************/
signal on syntax /* capture SQL syntax errors */
call sql_initialize /* load all Rexx/SQL functions*/

if SQLConnect(,,,'mysql') <> 0 then call sqlerr 'On connect'
if SQLCommand(u1,"use test") <> 0 then call sqlerr 'On use'


/* drop the table if it exists, and create the table a-new */

rc = SQLCommand(d1,"drop table phonedir") /* dont care about rc */

sqlstr = 'create table phonedir (lname char(10), phone char(8))'
if SQLCommand(c1,sqlstr) <> 0 then call sqlerr 'On create'

say "Enter last name and phone number ==> "
pull lname phone .


/* this loop collects data from user, inserts it as new rows */

do while (lname <> 'EXIT')
sqlstr = "insert into phonedir values('" || lname || "'",
",'" || phone "')"
if SQLCommand(i1,sqlstr) <> 0 then call sqlerr 'On insert'
say "Enter last name and phone number ==> "
pull lname phone .
end

call sql_pgm_end /* disconnect, drop functions */
exit 0

After creating the table, the script uses the Rexx pull instruction to read the person’s last name (lname) and phone number (phone) from the user at the keyboard. (The period following the pull instruction ignores any other data the user enters beyond the two values the script expects to read.)

The do while loop builds a SQL insert statement, inserts the row into the table, and reads the next user input. Building the insert statement requires using Rexx’s string concatenation operator (||) and also the line continuation character ( , ). When the user is done entering data into the new table, he or she enters the word “EXIT” and the do loop ends. Then, the script invokes its exit service routine sql_pgm_end and terminates.

This program shows that DDL and DCL statements can be issued as easily from within Rexx scripts as SQL DML. The SQLCommand function makes it easy to build and issue SQL statements. While you could hard code the entire SQL statement within the SQLCommand function, the two-step approach of building, then issuing, the SQL, makes debugging easier. For example, dynamically building the SQL insert statement in this program requires some fairly  tricky coding. We built the insert statement into the variable named sqlstr. To see if it the insert statement is correctly built, simply issue this statement to display it on the screen:

say  sqlstr                       /* ensure INSERT has proper syntax */

Porting and Re-targeting Scripts

We’ve emphasized that one big advantage to open-source software based on international standards is that you can easily redirect your scripts to target different databases. You need only check for these items to re-target Rexx/SQL scripts:

      1. The SQLConnect statement (its minimally-required parameters vary somewhat by DBMS)
      2. DBMS-specific SQL statements or syntax
      3. DBMS-specific SQL features

Stick to ANSI-standard SQL and your code will be database independent. We tested our coding examples under Windows with MySQL and then ran them without change under Linux.

Then we re-targeted these scripts from MySQL to DB2 UDB. The scripts ran with only two small changes:

      1. Change the name of the SQLConnect target database from mysql to DB2’s sample database.
      2. Remove the line that issues the use test statement, since this is unique to MySQL.

So, switching our scripts from Windows to Linux, and then from MySQL to DB2 UDB, was simple. With a bit of care against using DBMS-specific statements, syntax, or features, re-targeting Rexx/SQL scripts across databases becomes trivial. The result is that your skills transfer across different platforms, databases, and operating systems. And your code is portable. Open-source products like Rexx and Rexx/SQL offer compelling advantages in freeing your organization from dependence on particular vendors or products.

Next Steps

In two brief articles, we’ve demonstrated that scripting is a useful database solution. It offers a middle way, providing greater programmability than many query tools, yet supporting higher productivity than coding in traditional languages like Java, C++, or COBOL.

High-level scripting languages like Rexx can help you get the job done quickly. The tools are open source so vendor lock-in is eliminated. Best of all ... they’re free. You don’t need a big budget to get started with open-source database scripting. Check the chart below for more information, and you’re off and running!

Rexx Resources

Resource Links
Book Rexx Programmer’s Reference – Full tutorial and reference guide. Covers free Rexx, its interpreters, tools, and database scripting.
Tutorials Find free online Rexx tutorials here and here.
Forums There are several active Rexx forums, including some that address Rexx on the mainframe and Rexx under Windows.
User groups Visit the Rexx Language Association.
IBM’s Rexx Web site IBM offers plenty of good material at their Rexx Web site.
Free Rexx interpreters Regina: (Most operating systems)
BRexx: (Linux, UNIX, Windows, Mac OS, DOS)
r4: (Windows)
Reginald: (Windows)
Rexx for Palm OS: (Palm OS)
Free object-oriented Rexx interpreters Open Object Rexx: (Linux, UNIX, Windows)
roo!: (Windows)
Open sourcedatabase interface Rexx/SQL: (Supports most commercial and open source databases)
Other tools and interfaces This site and this one should get you started with the many free tools and interfaces available.

--

Howard Fosdick is an independent DBA consultant who has worked with most major scripting languages. His book Rexx Programmer’s Reference starts with an easy tutorial and then covers everything you’ll want to know about Rexx, its interfaces, the available interpreters, and database scripting.


Contributors : Howard Fosdick
Last modified 2006-01-06 10:54 AM
Transaction Management
Reduce downtime and increase repeat sales by improving end-user experience.
Free White Paper
Database Recovery
Feeling the increased demands on data protection and storage requirements?
Download Free Report!
 
 

Powered by Plone