Calling IBM iSeries RPG Programs with SQL Stored Procedures and C#Calling IBM iSeries RPG programs with SQL stored procedures and getting results back. A more scalable and better performance than CWBX API.
This tutorial will look at calling IBM iSeries RPG programs with SQL-stored procedures. This method is the best in terms of flexibility, scalability and performance.
When I first started working with ASP.Net and an iSeries back end, I used the IBM CWBX API Calls to communicate with the existing business logic. However, I soon discovered that performance would be an issue and decided to try a different solution. Calling RPG programs with SQL-stored procedures adds a security layer to the back end. Since the front end cannot specify or change what the program is called, the only commands that can be executed are those defined in the stored procedure.
The RPG programs on the iSeries do not need to be changed as long as they support in and out parameters, the same as those using iSeries CWBX API calls. The only changes that need to be made to the iSeries are the addition of stored procedures into the library.
Creating a SQL stored procedure on an iSeries (for those who don't already know) is as simple as running the following command within the SQL environment.
CREATE PROCEDURE LIBRARY/PROCNAME(INOUT parameter1 CHAR (2500))
LANGUAGE RPGLE NOT DETERMINISTIC CONTAINS SQL EXTERNAL NAME
RPGOBJ/RPGPRG PARAMETER STYLE GENERAL Where LIBRARY/PROCNAME is the location and name of the stored procedure and RPGOBJ/RPGPRG is the library and name of the program. In this example, one parameter is defined with a length of 2500 characters. You can prompt the command for further details and help, as I am unfamiliar with the iSeries SQL parameters.
On the .Net side, we need to use the IBM DB2 Provider and OleDB. This method works like calling a Microsoft SQL Server stored procedure, except for a few different objects.
Let's have a look at a quick example:
string result = string.Empty;
string conStr = "DataSource=127.0.0.1;DefaultCollection=LIBRARY;UserID=USERNAME;Password=PASSWORD; DataCompression=True;Pooling=true;"; // Change to suit your system
// Create a connection
iDB2Connection conn = null;
try
{
conn = new iDB2Connection(conStr);
conn.Open();
// Create the command
iDB2Command dbCmd = new iDB2Command();
dbCmd.CommandText = "PROCNAME"; // Name of the stored procedure
dbCmd.CommandType = CommandType.StoredProcedure;
dbCmd.Connection = conn;
// Add a parameter
iDB2Parameter dbParam_ID = new iDB2Parameter();
dbParam_ID.ParameterName = "@parameter1";
dbParam_ID.Value = "Example";
dbParam_ID.DbType = DbType.String;
dbParam_ID.Direction = ParameterDirection.InputOutput;
dbCmd.Parameters.Add(dbParam_ID);
// Call the procedure
dbCmd.ExecuteScalar();
// Get the response
result = dbParam_ID.Value.ToString().Trim();
}
catch
{
// Do your error handling here
}
finally
{
// Close the connection
if (conn != null)
conn.Close();
}
Console.WriteLine(result); Note: Do not forget to trim the result from the stored procedure. If the procedure defines a length of 2500 characters, you will get back 2500 characters even if the data is only one character long.
The performance of this method is much greater than that of the CWBX API calls. Because it uses native SQL calls, it is far more scalable. The only thing I do with my code is have a managed connection pool, as it takes a few seconds to create a new iDB2 connection object.
RGP Code
Here's a small example of an RPG program that shows how to read and write according to the parameters. This program will convert an incoming parameter string from lower to upper case.
*===================================================================*
d* Constants *
*===================================================================*
d c#Lower c Const('abcdefghijklmnopqrstuvwxyz')
d c#Upper c Const('ABCDEFGHIJKLMNOPRRSTUVWXYZ')
*===================================================================*
* Program Variables *
*===================================================================*
d InParms s 2500a
*===================================================================*
* Entry parms *
*===================================================================*
* Entry parameters
c *Entry Plist
c Parm InParms
*===================================================================
* Mainline *
*===================================================================
* Convert the incoming parameter from Lower to Upper case
c Eval InParms = %XLate(c#Lower:c#Upper:InParms)
* End the program (set on the Last Record indicator and Return to the calling program)
c Eval *INLR = *On
c Return = 
