Objecting to databases

Part 3: Creating some fetching little methods

by Bill Schindler

In the last two articles ("Objecting to databases," April 2000 and May 2000), we created the core part of the Database class and you learned some Object Rexx along the way. In this article, we'll add the ability to fetch multiple-row results from a SQL select.

Note that the code in this article relies on code that was introduced in the prior two articles.

More database methods

The code listing contains more code that goes in the Database class. These three new methods extend Database so that you can use it to return all of the result rows from a SQL select.

In order to get multiple results from the database, you need to:

Prepare the SQL statement
Open the statement
Fetch each row
Close the statement

Because most SQL databases refer to opening and closing the statement as "opening and closing a cursor" we use the name "cursor" as part of our open and close methods. (The cursor tracks the position in the result, much like a cursor in a word processor tracks the current position in the text.)

If you examine the listing, you'll see that the SQL calls in each of the methods follow the items in the bulleted list above.

How do you interpret this?

On line 41, you'll see a Rexx instruction that I try not to use: INTERPRET. I try not to use INTERPRET because it's often slower than other approaches, it can hide syntax errors, and the INTERPRET instruction can result in confusing code, even to the person who wrote it!

If you look at several Rexx programs that use the INTERPRET instruction, you'll probably find that the Value function would have served as well. For example, we could have used an INTERPRET on line 15 like this:

   INTERPRET stmtstem "= ''"

Instead, the Value function is used is used to assign a default value to the stem variable:

   CALL Value stmtstem, ''

In the case of line 41, though, INTERPRET is the simplest and cleanest solution.

The problem we have is that the name of the stem variable that contains the field information is itself stored in a variable. As far as Object Rexx is concerned, our stem variable's name is just another string.

So, in this one case, we need to dynamically build a Rexx statement and have INTERPRET process it.

Getting arrays

Even without the INTERPRET instruction, the statement on line 41 may seem a little strange. The constructed statement looks like:

    a = fld.~makearray

In Classic Rexx, stem variables are arrays. Object Rexx lets you treat a stem variable like any other full class, uh, class. So, you can use DO OVER to step through all the values in the stem:

   DO n OVER fld.
    SAY fld.n
   END

When you use an object in DO OVER (like fld.) in this way, Object Rexx automatically calls the object's makearray method. The values assigned to the control variable (n in the example) are the values returned by makearray.

You might expect the makearray method to return an array with the contents of the stem variable. Instead, makearray returns an array of the tails (the "indexes") in the stem.

So, if our fld. stem contained fld.firstname and fld.lastname, makearray would return an array containing FIRSTNAME and LASTNAME.

Now that you know how it works, why didn't we just use stmtstem in the DO OVER?

The reason is that stmtstem is one level of indirection away from the actual stem variable. Since stmtstem is a simple variable, it cannot be converted to an array and you'll get a syntax error.

So we use INTERPRET to manually do what Object Rexx normally does automatically: call makearray on the stem variable.

Putting it to work

Here's an example of using the Database class as developed so far:

   /* FetchTest.cmd */

   db = .database~new("warptech")
   sel = "select * from assocperson where associd < 10"
   inf = .directory~new
   stmt = .database~statementID
   db~openCursor(sel, stmt)

   DO WHILE db~fetch(inf, stmt)
     .output~lineout(inf~firstname inf~lastname)
     DO n OVER inf
      inf[n] = ''
     END
   END

   db~closeCursor
   EXIT

   ::requires "Database.rxx"

In the first line, we create a database object. Since this example is connecting to the WarpTech database, it passes that name to Database's init method. (You can't connect to the WarpTech database, so you'll need to modify this code to make it work with one of your own databases.)

Next, the SQL select statement is created. Then a directory object is created to hold the fetch results. The final bit of housekeeping is to create a statement ID.

The openCursor method is passed the SQL select and the statement ID. The openCursor method prepares the SQL statement and opens it. (In MySQL, this basically does all the work of processing the SQL statement. The following fetches just return the results.)

The program then loops while there's any results to fetch. The fetch method gets the directory (for storing the results) and the statement ID.

For each result row, the program prints the first name and last name. The result directory is then cleared, just to be safe.

When it's done fetching and processing the results, the program closes the cursor.

The code

   1: /*===================== openCursor ======================
   2:  */
   3: ::method openCursor
   4:   EXPOSE dbId sqlca. flds.
   5:   USE ARG exp, stmt
   6:   IF stmt <> .nil THEN
   7:    DO
   8:     stmtstem = stmt || '.'
   9:    END
  10:   ELSE
  11:    DO
  12:     stmtstem = 'flds.'
  13:     stmt = 'flds'
  14:    END
  15:   CALL Value stmtstem, ''
  16:   CALL SQLPrepare stmt, exp
  17:   CALL CheckSQLError 'PREPARE', sqlca.
  18:   IF sqlca.sqlcode = 0 THEN
  19:    DO
  20:     CALL SQLOpen stmt
  21:     CALL CheckSQLError 'OPEN', sqlca.
  22:    END
  23:   RETURN sqlca.sqlcode
  24: 
  25: /*======================== fetch ========================
  26:  */
  27: ::method fetch
  28:   EXPOSE dbId sqlca. flds.
  29:   USE ARG fields, stmt
  30:   IF stmt <> .nil THEN
  31:    DO
  32:     stmtstem = stmt || '.'
  33:    END
  34:   ELSE
  35:    DO
  36:     stmtstem = 'flds.'
  37:     stmt = 'flds'
  38:    END
  39:   v = SQLFetch(stmt)
  40:   CALL CheckSQLError 'FETCH', sqlca.
  41:   INTERPRET "a =" stmtstem || "~makearray"
  42:   DO n OVER a
  43:     fields[ n ] = Value(stmtstem || n)
  44:   END
  45:   RETURN (v <> 0)
  46: 
  47: /*===================== closeCursor =====================
  48:  */
  49: ::method closeCursor
  50:   EXPOSE dbId sqlca. flds.
  51:   USE ARG stmt
  52:   IF stmt = .nil THEN
  53:     stmt = 'flds'
  54:   CALL SQLClose stmt
  55:   RETURN sqlca.sqlcode

Personal tools

Sections