Rexx - Databases

Advertisements

Previous Page
Next Page  

Rexx has the ability to work with a variety of databases which are listed below.

  • HSQLDB
  • Oracle
  • SQL Server
  • MySQL
  • MongoDB

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

Databases

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 establish a database connection, you first need to the Rexxsql DLL and then use the SQLConnect function to establish 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.

Sr.No. Operation & Description
1 Insert Operation

It is required when you want to create your records into a database table.
2 Read Operation

A READ Operation on any database means to fetch some useful information from the database.
3 Update Operation

The UPDATE Operation on any database means to update one or more records, which are already available in the database.
4 Delete Operation

The DELETE operation is required when you want to delete some records from your database.
5 Closing a Connection

The following command can be used to close a connection to the database.

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.

  • Durability − 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 finalize 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()
Advertisements