Since I have several posts on interfacing between .NET and the AS/400, and because this type of information is scarce on the internet, I often receive emails from people asking how to do certain things. As I believe I have stated in every AS/400 related article I have posted, I actually know very little about the AS/400. I just try to share the little bits that I do know. Let this paragraph serve as my ongoing disclaimer concerning my AS/400 ignorance.
Recently, I received an email from James Wright asking about calling Stored Procedures on the AS/400. He was attempting to use the cwbx.dll to call them, but was having trouble. I informed him that I am not sure cwbx.dll can do that. I am not saying that it cannot, just that I don't know that it can since I have never done it. I recommended that instead he use either the ADO.NET DB2 native provider or one of the DB2 OleDb providers and try to call the DB2 stored procedure as a stored procedure because I have done that in the past and it worked. However, because it was so long ago that I did this, and because I was only doing some experimentation at the time, I don't have any code doing this that I could share.
Fortunately, James resolved how to use IBM's DB2 .NET managed provider (IBM.Data.DB2.iSeries.dll) to call his stored procedure after some trouble. Since it was problematic, he wanted to share the fruits of his labor so that others may benefit. Please read the comments in the code below because he was kind enough to include what caused his issues in them.
You will need to add a reference for "IBM DB2 UDB for iSeries .NET Provider" to your project. The dll for this provider is named IBM.Data.DB2.iSeries.dll and is in my Program Files\IBM\Client Access directory. Also, you will need to add a using directive like this:
using IBM.Data.DB2.iSeries;
First, here is an example of his code:
class AS400_DataBase_Test
{
///
/// Replace SERVERNAME, USERNAME, PASSWORD, LIBRARYNAME, PROCEDURENAME, PARAMNAME1, PARAMNAME2 and FIELDNAME1
/// with valid values.
///
/// AS400 Structure:
/// ***********************************************************************************************
/// Example(AS400 Programmers words):
/// TESTONPR is a Stored procedure
/// which calls a CL TESTONBCL
/// which calls TESTSQLONB which is a SQLRPGLE PROGRAM
///
/// To put the example to this code, PROCEDURENAME would be TESTONPR.
/// ***********************************************************************************************
///
/// The key to making this work is ensuring that the Stored Procedure on the AS400 side is declared
/// using the Library name as well as the Procedure name.
/// Example:
/// CREATE PROCEDURE MYTEST is wrong
/// CREATE PROCEDURE LIBRARYNAME/MYTEST is correct
/// The Externale Name of the procedure must also contain the library name.
/// Example:
/// EXTERNAL NAME MYTEST is wrong
/// EXTERNAL NAME LIBRARYNAME/MYTEST is correct
///
/// Other settings that are known to work on the Stored Procedure on the AS400 side:
/// RESULT SETS 1
/// RESULT SETS 4000
/// Not DETERMINISTIC
/// MODIFIES SQL DATA
/// PARAMETER STYLE GENERAL
///
/// Other Notes:
/// Not defining the "Direction" (Input, Output, InputOutput, etc.) of each Parameter
/// caused this example to not work. Also I had to be very specific about my lengths and
/// justifications (right vs. left). These may be AS400 specifications that I just wasn't
/// aware of.
///
/// A concatonated string with a specific fields information for each record returned.
public string RunTest()
{
iDB2Connection conn = new iDB2Connection();
iDB2Command sqlselect = new iDB2Command();
iDB2DataReader dr;
StringBuilder sb = new StringBuilder();
char space = ' ';
try
{
conn.ConnectionString = "DataSource=SERVERNAME;Connection Timeout=30;UserID=USERNAME;Password=PASSWORD";
sqlselect.CommandText = "CALL LIBRARYNAME.PROCEDURENAME(?,?)";
sqlselect.CommandType = CommandType.Text;
sqlselect.Connection = conn;
conn.Open();
sqlselect.Parameters.Add("PARAMNAME1", iDB2DbType.iDB2Char, 3);
sqlselect.Parameters["PARAMNAME1"].Direction = ParameterDirection.Input;
sqlselect.Parameters["PARAMNAME1"].Value = "T".PadRight(3, space);
sqlselect.Parameters.Add("PARAMNAME2", iDB2DbType.iDB2Char, 5);
sqlselect.Parameters["PARAMNAME2"].Direction = ParameterDirection.Input;
sqlselect.Parameters["PARAMNAME2"].Value = "11".PadRight(5, space);
dr = sqlselect.ExecuteReader();
while (dr.Read())
{
string fn = dr["FIELDNAME1"].ToString();
sb.Append("Field # 1 is: " + fn + System.Environment.NewLine);
}
}
catch (Exception ex)
{
// Log Somewhere
}
finally
{
conn.Close();
}
return sb.ToString();
}
}
James also mentioned this in his email to me, which I will post here for additional information:
"The biggest piece of information I just discovered is that it seems that .Net can't keep track of which Library (database) it's supposed to be in on the AS400 so the AS400 MUST declare the Stored Procedure with the Library Name. I never found a single site that explained that, I just bumped into it basically because one of my AS400 programmers did it and the other didn't."
The source module can be downloaded from the Free Downloads section available here.
If you have any questions or comments, James can be reached at jwright at watlow dot com.