Rexx - Databases

Rexx has the abipty to work with a variety of databases which are psted below.

HSQLDB

Oracle

SQL Server

MySQL

MongoDB

All the information for Rexx databases can be found once you cpck on the following pnk − https://rexxsql.sourceforge.net/

In our example, we are going to use MySQL DB as a sample. So the first step is to ensure to download the required drivers from the Rexx SQL site so that Rexx programs can work with SQL accordingly. So follow the subsequent steps to ensure that Rexx programs can work with MySQL databases.

Step 1 − Go to the following drivers download page from the Rexx site − https://sourceforge.net/projects/rexxsql/files/rexxsql/2.6/

Step 2 − Download the MYSQL drivers - rxsql26B3_my_w32_ooRexx

Step 3 − Unzip the contents to the local machine.

Step 4 − Add the path of the unzipped folder to the path variable on your machine.

For all the subsequent examples, make sure of the following pointers are in place −

You have created a database TESTDB.

You have created a table EMPLOYEE in TESTDB.

This table has fields FIRST_NAME, LAST_NAME, AGE, SEX and INCOME.

User ID "testuser" and password "test123" are set to access TESTDB.

Ensure you have downloaded the mysql jar file and added the file to your classpath.

You have gone through MySQL tutorial

Database Connection

To estabpsh a database connection, you first need to the Rexxsql DLL and then use the SQLConnect function to estabpsh a connection to the database. The syntax and example of how this can be achieved is given below.

Syntax

SQLConnect(cname,username,password,dbname) 

Parameters

cname − This is the name to give to the connection.

username − The user name to connect to the database.

password − The password to connect to the database.

dbname − The database schema to connect to.

Return Value

A value equal to 0 will mean that the database connection is successful.

Example

/* Main program */ 
Call RxFuncAdd  SQLLoadFuncs ,  rexxsql ,  SQLLoadFuncs  
Call SQLLoadFuncs 
say SQLConnect(c1,  testuser  ,  test123 , testdb )

The output of the above program would be as shown below.

0

Creating a Database Table

The next step after connecting to the database is to create the tables in our database. The following example shows how to create a table in the database using Rexx. All of the commands in Rexx SQL are executed by using the SQLCommand function.

Syntax

SQLConnect(sname,statement)

Parameters

sname − This is the name to give to the statement to execute.

statement − This is the statement which needs to be executed against the database.

Return Value

A value equal to 0 will mean that the command was successful.

Example

/* Main program */ 
Call RxFuncAdd  SQLLoadFuncs ,  rexxsql ,  SQLLoadFuncs  
Call SQLLoadFuncs 

if SQLConnect(c1, testuser , test123 , testdb ) == 0 then say  Connect Succedded  
if SQLCommand(u1,"use testdb") == 0 then say  Changed database to testdb  
   sqlstr =  create table employee (first_name char(20) not null, last_name 
   char(20),age int, sex   
   char(1), income float)  

if SQLCommand(c2,sqlstr) == 0 then say  Employee table created 

The output of the above program would be as shown below.

Connect Succedded 
Changed database to testdb 
Employee table created 

Operations on a Database Table

The following types of operations are most commonly performed on a database table.

Performing Transaction

Transactions are a mechanism that ensures data consistency. Transactions have the following four properties −

Atomicity − Either a transaction completes or nothing happens at all.

Consistency − A transaction must start in a consistent state and leave the system in a consistent state.

Isolation − Intermediate results of a transaction are not visible outside the current transaction.

Durabipty − Once a transaction was committed, the effects are persistent, even after a system failure.

Here is a simple example of how to implement transactions.

Example

/* Main program */ 
Call RxFuncAdd  SQLLoadFuncs ,  rexxsql ,  SQLLoadFuncs  
Call SQLLoadFuncs 

if SQLConnect(c1, testuser , test123 , testdb ) == 0 then say  Connect Succedded  
if SQLCommand(u1,"use testdb") == 0 then say  Changed database to testdb  
   sqlstr = "DELETE FROM EMPLOYEE WHERE AGE > 20" 

if SQLCommand(c2,sqlstr) == 0 then 
if sqlcommit() == 0 then say committed

The output of the above program would be as shown below.

Connect Succedded 
Changed database to testdb 
COMMITTED

Commit Operation

The commit operation is what tells the database to proceed ahead with the operation and finapze all changes to the database. In our above example, this is achieved by the following command.

Sqlcommit() 

Rollback Operation

If you are not satisfied with one or more of the changes and you want to revert back those changes completely, then use rollback method. In our above example, this is achieved by the following command.

SqlRollback()