”;
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/
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()
”;