Web Design that taps into the haromny and vision of your dreams.

Calling IBM iSeries RPG Programs with SQL Stored Procedures and C#

Calling IBM iSeries RPG programs with SQL stored procedures

Written By on in C#

634 words, estimated reading time 3 minutes.

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, both 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 an additional security layer to the back end. Since the front end cannot specify or change what 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, with the exception of 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 I 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                                                             =

Last updated on: Wednesday 21st June 2017

 

Comments
William

William

Having issues with loading a data grid with data set coming from RPG stored procedure. Do you have sample? I'm using C# and VSP2013. Thanks.

Reply to William

 

Leave a Reply

Your email address will not be published.





If you find something abusive or that does not comply with our terms or guidelines please flag it as inappropriate.

Copyright © 2001-2018 Tim Trott, all rights reserved. Web Design by Azulia Designs

This web page is licensed for your personal, private, non-commercial use only.

Disclaimer, Privacy & LegalSitemapContact Me