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.
In this tutorial, we will look at calling IBM iSeries RPG programs with SQL-stored procedures. I have found this method to be 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 was going to be an issue and decided to try a different solution. Calling RPG programs with SQL-stored procedures also 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 done on the iSeries are the addition of stored procedures into a 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, there is one parameter defined with a length of 2500 characters. You can prompt the command for further details and help as I am not very familiar with the iSeries SQL parameters.
On the .Net side, we need to use the IBM DB2 Provider and OleDB. This method works in very much the same way as 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 coming back 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 1 character long.
The performance of this method is (in my opinion) much greater than that of the CWBX API calls and 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 iDB2Connection object.
RGP Code
Here's a small example RPG program which shows how to read and write 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 = 
