Objecting to databases

by Bill Schindler

A couple of years ago, I realized that most of the work I was doing in writing database access code in Rexx was low-level code. With checking for errors at every step and tracking database-specific variables, the high level logic of my code was often obscured completely by the lower-level logic necessary to access the database.

So, I decided to hide the database access code in an Object Rexx class. My primary design goals were to hide the complex inner workings of accessing the database and to surface a simple and consistent interface.

This article and the ones to follow will show you how I developed a Database class in Object Rexx.

Setting up

For this set of articles on building a Database class, I assume that you're familiar with REXX ("Classic Rexx"). I also assume that you're willing to spend some time studying the Object Rexx documentation to pick up any details that I don't have room to cover in these articles.

In order to actively follow these articles, you need to install Object Rexx. If you're using OS/2 Warp 4, you switch to Object Rexx by going to a command line and running:

    switchrx

You'll be asked if you're sure you want to switch. Some files will be copied, and you'll need to reboot for the switch to take effect.

Any Classic Rexx programs you use should still work. There are two "gotchas" that may a Class Rexx program. You won't encounter these often, but you should be aware of them. First, Object Rexx processes the entire program when it's first run, so if there's a syntax error--even in code that's never executed--you'll get a syntax error without the program ever running. Second, Object Rexx's seek option for the Stream function is slightly different from Classic Rexx.

Note that switchrx also switches the online documentation to the Object Rexx version. Take the time to browse through the online documentation.

As I discuss examples, I'll also touch on how some of the Object Rexx statements work.

Something old, something new

Using the old database access routines, producing a simple listing of POSSI members requires about 100 lines of Rexx code. About half of those 100 lines of code look something like this:

   CALL sqlExec 'DECLARE c1 CURSOR FOR s1'
   CALL CheckError 'Declare cursor'

   CALL sqlExec 'PREPARE s1 FROM :prep'

   CALL CheckError 'Prepare'

   CALL sqlExec 'OPEN c1'
   CALL CheckError 'Open cursor'

That's pretty dense stuff. In comparsion, the code to produce a member listing using the Database class fits an entire program into 18 lines.

   /* member listing */
   possi = .database~new('POSSI')
   addr = .addressView~new(possi)
   members = .membersView~new(possi)

   members~orderBy = 'LASTNAME, FIRSTNAME'
   ok = members~first
   DO WHILE ok
     SAY members~membernumber~right(4) members~expiredate ,
    members~firstname members~lastname
     ok = members~next
   END

   EXIT

   ::requires "Database.rxx"
   ::requires "AddressView.rxx"
   ::requires "MembersView.rxx"

Using the Database class allows you to concentrate on the program logic rather than fiddling with the details of accessing the database. You don't even need to understand how the database access routines work. It also makes the resulting program a lot easier to understand, too!

Requires help

Obviously, most of the inner workings are hidden in other files.

One of the benefits of using Object Rexx is that you can put common routines into a file and make them accessible to any other Object Rexx program using the ::requires directive.

The ::requires directive tells Object Rexx to look in an external file for more routines and classes.

The definition of the Database class is in database.rxx. Once it's included using ::requires, the Database class and all of its methods are used just like any of Object Rexx's built-in classes.

Code accessed through ::requires works a little differently from an external routine that you might have used in Classic Rexx. An external routine can only receive and return simple string parameters. Routines accessed via ::requires can take anything as a parameter and return any type of object.

Take a message

If you've never seen Object Rexx code before, you're probably wondering, "what's with all those tildes?"

Well, first thing, they're called "twiddles" by Object Rexx programmers.

Twiddles are a new operator used to pass messages to objects and classes. An Object Rexx message is implemented by a method--a routine that's part of a class definition. Methods are used to "do stuff" with an object.

For example, in Object Rexx strings are objects. So, you can pass a string a message. If you want to center the string "Hello" in a 60 character wide space, you could do this:

    "Hello"~center(60)

Twiddle sends the message center to the String object "Hello." (Or you can also think of it as twiddle calling the String object's center method.)

First steps

Now that you know some Object Rexx basics, let's start building the Database class.

All of what follows will go into a file named database.rxx. The file extension isn't important; you could use .cmd if you like. I deliberately pick a non-executable extension so that I can easily pick out the supporting code from the actual runnable programs.

Start the file with the usual Rexx comment.

   /* Database class definition */

The first thing we need to do is create the Database class.

   ::class Database PUBLIC

The ::class directive tells Rexx that we're defining a new class. "Database" is the name of the class. The PUBLIC option makes the class visible outside of this file. If we don't use PUBLIC, the class is private to the file and cannot be used by other programs.

Everything following the ::class directive--up to the next ::class directive--belongs to that class.

When it's created, a new Database object needs to set up some variables, get access to the database, and so on. Object Rexx calls a special method named init when a new object is created. The init method is invoked when the new message is sent to a class to create a new object. (In the earlier code sample, .database~new("POSSI") is creating a new database object.)

You use the ::method directive to create a new method.

   ::method init
     EXPOSE dbName dbId sqlca.
     USE ARG dbName
     CALL RXFuncAdd 'SQLLoadFuncs','rexxsql','SQLLoadFuncs'
     CALL SQLLoadFuncs

     dbId = 'c' || random(1, 99999)
     CALL SQLConnect dbId, 'bill', , dbName
     CALL CheckSQLError 'CONNECT' dbName, SQLCA.

When an object is no longer needed, Object Rexx calls the uninit method and then deletes the object.

The code

   1: /*=========================================================
   2:  * Object REXX interface to MySQL database
   3:  */
   4: 
   5: /*--------------------- CheckSQLError ---------------------
   6:  */
   7: ::routine CheckSQLError
   8:   USE ARG str, SQLCA.
   9:   IF SQLCA.SQLCODE <> 0 THEN
  10:    DO
  11:     log = .stream~new("sqlerror.log")
  12:     log~open('Write Append')
  13:     log~lineout('-'~copies(10) Date() Time())
  14:     log~lineout('"'str'"' '('SQLCA.SQLCODE')')
  15:     log~lineout('>>>' SQLCA.SQLERRM)
  16:     log~lineout('>>>' SQLCA.SQLTEXT)
  17:     log~lineout('')
  18:     log~close
  19:     .error~lineout('"'str'"' '('SQLCA.SQLCODE')')
  20:     .error~lineout('>>>' SQLCA.SQLERRM)
  21:     .error~lineout('>>>' SQLCA.SQLTEXT)
  22:     RAISE HALT
  23:     EXIT
  24:    END
  25: 
  26: /************************ Database ************************
  27:  */
  28: ::class Database PUBLIC
  29: 
  30: /*========================= init ==========================
  31:  */
  32: ::method init
  33:   EXPOSE dbName dbId sqlca.
  34:   USE ARG dbName
  35:   CALL RXFuncAdd 'SQLLoadFuncs','rexxsql','SQLLoadFuncs'
  36:   CALL SQLLoadFuncs
  37: 
  38:   dbId = 'c' || random(1, 99999)
  39:   CALL SQLConnect dbId, 'userid', 'password', dbName
  40:   CALL CheckSQLError 'CONNECT' dbName, SQLCA.
  41: 
  42: /*======================== uninit =========================
  43:  */
  44: ::method uninit
  45:   EXPOSE dbId sqlca.
  46:   CALL SQLDisconnect dbId
  47:   CALL CheckSQLError 'DISCONNECT', SQLCA.
  48:   CALL SQLDropFuncs