VOICE Home Page: http://www.os2voice.org

July 2004 Newsletter Index < Previous Page | Next Page > Feature Index editor@os2voice.org

In close collaboration: MySQL and OS/2 - Part 6: Accessing MySQL from REXX

Today we use REXX to access our test database.

It is necessary to download the REXX driver, RexxSQL, found on the Internet at <http://rexxsql.sourceforge.net/> in the Downloads area. Download rxsql24RC1_my_os2.zip and save it in its own directory. After unzipping it you should have the following files:

    Copying
    History
    Install
    Readme
    rexxmy.dll
    rexxmy.exe
    rexxsql.dll
    rexxsql.exe

Copy the files rexxmy.dll and rexxsql.dll into a directory listed in the config.sys LIBPATH, e.g. into \os2\dll\ or d:\usr\dll\.

Create a directory for the REXX program. I use p:\Rexx\mysql\. In this new directory create an empty file named ListLand.cmd and open it with a text editor.

As you learned from Thomas Klein's article series "Dr. Dialog: or How I Learned to Love REXX," you know that every REXX program must begin with a comment line. We enter:

/* Program: ListLand.cmd    */
/* Author: Wolfgang Draxler */
/* Date: 05.05.2004         */

Now we bind the driver rexxsql.dll. Do this with the commands RxFuncAdd and SQLLoadFuncs.

Call RxFuncAdd "SQLLoadFuncs", "rexxsql", "SQLLoadFuncs"
Call SQLLoadFuncs

More detailed information about these commands is found in the April 2004 issue "In close collaboration: MySQL and OS/2: Part 3."

The next step creates a connection to the MySQL database. This is done with the command SQLConnect().

The syntax of the command is:

SQLCONNECT([connectionname], [username], [password], [database], [host])

If SQLConnect() returns a negative result, the connection was not successful. This happens, for example, if the database is not running, or the Username or Password are incorrect.

There are five parameters specified:

connectionname

The name given to the connection between the REXX program and the database. Other REXX-SQL commands require this name to work properly since it is possible for a single REXX program to connect to multiple databases. This happens in programs that manipulate data from several databases (that may even be on different hosts).

username

This defines the user's account on the MySQL database.

password

The User-Password which authenticates the user to the MySQL database.

database

The database to be accessed.

host

The computer on which the database is running and which must be accessed by the program. This can be an IP address or the hostname of the computer. "localhost" specifies the computer where the REXX program is running.

For our case the call should be as follows (replace <PW> with your own root password):

if SQLConnect("Conn", "root", "<PW>", "address", "localhost") < 0 then
  do
    say "The connection did not succeed"
    exit
  end

After a successful connection and when an error occurs, we write the REXX program so that it correctly logs off the database and terminates. The log-off command is:

SQLDisconnect([connectionname])

As we see we need the connection name that was defined in SQLConnect().

Now we send a query to the database. This is done with the command:

SQLPrepare([Statementname, SQL-Statement)

A negative result of the command means that the SQL statement could not be processed by MySQL. This happens, for example, if the SQL statement is not correct, or if the specified table does not exist.

SQLPrepare() takes the following parameters:

Statementname

Here you provide a name for the query. This is necessary so that the driver knows, when dealing with other commands, which query is involved. (This is similar to connectionname for the SQLConnect() command.)

SQL-Statement

Here you enter the desired query string. Any of the commands that we have already learned can be sent as an SQL-Statement to the database, e.g. Select, Insert, Delete, Update, Show, Create, Drop, etc.

For this first example I have chosen a simple SQL-Statement. We try it first on the SQL-Console. That ensures there is no SQL syntax error when we enter the REXX SQL command.

+-------------+-------------------------+---------+
|countrycode  | countryname             | prefix  |
+-------------+-------------------------+---------+
| A           | Austria                 | +43     |
| D           | Germany                 | +49     |
| CH          | Switzerland             | +41     |
| GB          | Great Britain           | +44     |
| USA         | United States of America| +1      |
| I           | Italy                   | +39     |
| H           | Hungary                 | +36     |
| NL          | Netherlands             | +31     |
| L           | Luxemburg               | +352    |
| B           | Belgium                 | +32     |
| E           | Spain                   | +43     |
+-------------+-------------------------+---------+
11 rows in set (0.08 sec)

As we see, the SQL-Statement worked and we can copy it to our REXX program. The complete statement is:

if SQLPrepare("prep", "Select * from land") < 0 then
  do
    say "SQL-Statement did not work"
    call SQLDisconnect("conn")
  end

To access the result it must be opened with the command:

SQLOPEN(Statementname)

As with the other functions, a negative result means an error has occurred. If this happens the program should correctly log off the database and terminate. That is, we must first close the statement that was opened with SQLOPEN(), and we must close the connection with SQLDisconnect().

The following command closes a Statement:

SQLClose(Statement)

So this part of the program looks like:

if sqlopen("prep") < 0 then
  do
    say "SQL-Statement did not work"
    call SQLClose("prep")
    call SQLDisconnect("conn")
  end

Now we display the properties of the first field (countrycode). One uses the following command:

SQLDescribe(Statementname [,Info])

The stem vaiable Info has the following structure:

In the program this reads as:

rv = SQLDescribe("prep", "desc")
say "Name-dimension:"   desc.column.name.0
say "Name-countryname:" desc.column.name.1
say "Name-Type:"        desc.column.type.1
say "Name-size:"        desc.column.size.1", " desc.column.precision.1
say "Name-Scale:"       desc.column.scale.1
say "Name-Nullable:"    desc.column.nullable.1

With the command

SQLFetch(Statementname [, number of lines])

the SQL result is read row-by-row, where each row corresponds to a data record. Since we expect a number of records, we must use a loop that calls SQLFetch() at each iteration. And after a certain point all the records will have been read, it must be possible to exit the loop. The zero (0) result of the SQLFetch() command suggests how to do this. A negative result indicates that an error has occurred.

How does one access the individual fields? It is simple when you know how!

Here is how: In the SQLPrepare() command we specified a Statementname. Not only does SQLPrepare() send the SQL statement to the database, it also generates a stem variable with the name Statementname, which in our example is prep. The stem variable has the structure of the field names of the SQL-statement, in our case prep.countrycode, prep.countryname, prep.prefix.

We can list these (stem) variables with the Rexx command SAY.

Now we add the following lines to our REXX program:

do forever rc = SQLFetch('prep')
  if rc < 0 then exit 1
  if rc = 0 then leave
  say prep.countrycode ', ' prep.countryname "," prep.prefix
end

We still have to close both the statement and the connection. As already described do this with SQLClose() and SQLDisconnect().

call SQLClose("prep")
call SQLDisconnect("conn")
say "Finished"

So the complete source code looks as follows:

/* Program: ListLand.cmd    */
/* Author: Wolfgang Draxler */
/* Date: 05.05.2004         */

Call RxFuncAdd "SQLLoadFuncs", "rexxsql", "SQLLoadFuncs"
Call SQLLoadFuncs

if SQLConnect( "Conn", "root", "<PW>", "address", "localhost") < 0 then
  do
    say "The connection did not succeed."
    exit
  end

if SQLPrepare("prep","Select * from land") < 0 then
  do
    say "SQL-Statement did not work."
    call SQLDisconnect("conn")
  end

if SQLOpen("prep") < 0 then
  do
    say "SQL-Statement did not work."
    call SQLClose("prep")
    call SQLDisconnect("conn")
  end

rv = SQLDescribe("prep", "desc")
say "Name-dimension: " desc.column.name.0
say "Name-countryname:" desc.column.name.1
say "Name-Type: " desc.column.type.1
say "Name-size: " desc.column.size.1 ", " desc.column.precision.1
say "Name-Scale: " desc.column.scale.1
say "Name-Nullable: " desc.column.nullable.1

do forever
  rc = SQLFetch('prep')
  if rc < 0 then exit 1
  if rc = 0 then leave
  say prep.countrycode ', ' prep.countryname "," prep.prefix
end

call SQLClose("prep")
call SQLDisconnect("conn")
say "Finished"

As we see, it is fairly simple to make a connection between REXX and MySQL.

Now we write a small program that makes an addition to the table LAND. For this we generate a new command file SaveLand.cmd.

First we write the header and load the REXX-MySQL drivers:

/* Program: SaveLand.cmd    */
/* Author: Wolfgang Draxler */
/* Datum: 05.05.2004        */
Call RxFuncAdd 'SQLLoadFuncs', 'rexxsql', 'SQLLoadFuncs'
Call SQLLoadFuncs

Next we ask the user for values of countrycode, countryname, and prefix. The REXX command PARSE PULL accomplishes this:

Say "country code:"
Parse Pull countrycode
Say "country name:"
Parse Pull countryname
Say "Prefix:"
Parse Pull prefix

Again make the connection to the database (replace <PW> with your root password):

if SQLConnect("Conn", "root", "<PW>", "address", "localhost") < 0 then
  do
    say "Connection did not succeed."
    exit
  end

Next create a variable "SQL" that contains the Insert command:

SQL = "Insert into country values ('"countrycode"','"countryname"','"prefix"')"
say "SQL:" SQL

This variable is now made available to SQLPrepare():

if SQLPrepare("prep", SQL) < 0 then
  do
    say "SQL-Statement did not succeed."
    call SQLDisconnect("conn")
  end

Next the connection is closed:

call SQLClose("prep")
call SQLDisconnect("conn")
say "Finished"

To test the program open an OS/2 window and change to the directory where the two REXX programs are stored (in my case f:\rexx\mysql\):

[p:\rexx\mysql]saveland
Country code:
E
Country name:
Spain
Prefix:
+43
SQL: Insert into land values ('E','Spain','+43')
Finished

To see if this worked, start the first program, listland.cmd

[p:\rexx\mysql]listland
Name-number: 3
Name-countryname: countryname
Name-Type: CHAR
Name-size: 3, 3
Name-Scale: 0
Name-Nullable: 0
A, Austria, +43
D, Germany, +49
CH, Switzerland, +41
GB, Great Britain, +44
USA, United States of America, +1
I, Italy, +39
H, Hungary, +36
NL, Netherlands, +31
L, Luxemburg, +352
B, Belgium, +32
E, Spain, +43
Finished

As we see, "Spain" is now in the table. Naturally this could also be checked with the MySQL Console.

You now know how to connect REXX and MySQL, and with this article I conclude the series. Further information about the commands are found in the documentation for the REXX driver.

References: MySQL: http://www.mysql.de MySQL for OS/2 (Yuri Dario): http://www.os2power.com/yuri/ MySQL for OS/2 (Netlabs): http://mysql.netlabs.org MySQL documentation: http://www.mysql.com/doc/en/index.html REXX driver: http://rexxsql.sourceforge.net/

Feature Index
editor@os2voice.org
< Previous Page | Newsletter Index | Next Page >
VOICE Home Page: http://www.os2voice.org