|
Problem IC77233
|
Status: Closed |
DB2COMMANDBUILDER.DERIVEPARAMETERS() FAILS TO FIND PARAMETERS FOR STORED PROCEDURES IN IDS ANSI MODE DATABASES |
| product: |
DB2 CONNECT / DB2CONNCT / 970 - DB2 |
| Problem description: |
If you execute the following example code against an IDS Ansi
mode database, the DB2CommandBuilder.DeriveParameters() call
does not identify any parameters for stored procedures.
reproducible test:
static void IDS_DeriveParameter_From_SP()
{
string connStr =
"database=ansimodedb;server=system:port;uid=myuid;pwd=mypwd;";
String szProcName = "MYPROC2";
try
{
using (DB2Connection tstConn = new
DB2Connection(connStr))
{
tstConn.Open();
DB2Command tstCmd = tstConn.CreateCommand();
tstCmd.CommandText = "drop procedure " +
szProcName + " (INT)";
try
{
tstCmd.ExecuteNonQuery();
}
catch
{
}
tstCmd.CommandText = "create procedure " +
szProcName + " (parm1 INT) INSERT INTO NMP_INTEGER VALUES
(parm1); end procedure";
tstCmd.ExecuteNonQuery();
tstCmd.CommandText = szProcName;
tstCmd.CommandType = CommandType.StoredProcedure;
DB2CommandBuilder.DeriveParameters(tstCmd);
IDataParameterCollection pcol =
tstCmd.Parameters;
IDbDataParameter parm = null;
for (Int32 i = 0; i < pcol.Count; i++)
{
parm = (IDbDataParameter)pcol[i];
Console.WriteLine("Param[{0}]", i);
Console.WriteLine("Name={0}",
parm.ParameterName);
Console.WriteLine("Direction={0}",
parm.Direction);
Console.WriteLine("Type={0}", parm.DbType);
}
}
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
} |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* DB2 .NET Provider on Microsoft Windows *
****************************************************************
* PROBLEM DESCRIPTION: *
* If you execute the following example code against an IDS *
* Ansi mode database, the DB2CommandBuilder.DeriveParameters() *
* call does not identify any parameters for stored procedures. *
* *
* reproducible test: *
* *
* static void IDS_DeriveParameter_From_SP() *
* { *
* string connStr = *
* "database=ansimodedb;server=system:port;uid=myuid;pwd=mypwd; *
* "; *
* String szProcName = "MYPROC2"; *
* *
* try *
* { *
* using (DB2Connection tstConn = new *
* DB2Connection(connStr)) *
* { *
* tstConn.Open(); *
* DB2Command tstCmd = tstConn.CreateCommand(); *
* tstCmd.CommandText = "drop procedure " + *
* szProcName + " (INT)"; *
* try *
* { *
* tstCmd.ExecuteNonQuery(); *
* } *
* catch *
* { *
* } *
* tstCmd.CommandText = "create procedure " + *
* szProcName + " (parm1 INT) INSERT INTO NMP_INTEGER VALUES *
* (parm1); end procedure"; *
* tstCmd.ExecuteNonQuery(); *
* *
* tstCmd.CommandText = szProcName; *
* tstCmd.CommandType = *
* CommandType.StoredProcedure; *
* *
* DB2CommandBuilder.DeriveParameters(tstCmd); *
* IDataParameterCollection pcol = *
* tstCmd.Parameters; *
* IDbDataParameter parm = null; *
* for (Int32 i = 0; i < pcol.Count; i++) *
* { *
* parm = (IDbDataParameter)pcol[i]; *
* Console.WriteLine("Param[{0}]", i); *
* Console.WriteLine("Name={0}", *
* parm.ParameterName); *
* Console.WriteLine("Direction={0}", *
* parm.Direction); *
* Console.WriteLine("Type={0}", *
* parm.DbType); *
* } *
* } *
* } *
* catch (Exception e) *
* { *
* Console.WriteLine(e.ToString()); *
* } *
* } *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 version 9.70.FP5 *
**************************************************************** |
| Local Fix: |
The problem is caused by the DB2 .NET Provider improperly
providing the fully qualified name of the stored procedure
resulting in the procedure not being found. As a work around to
the problem, provide the fully qualified stored procedure name
in the DB2Command.CommandText property. |
| available fix packs: |
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
Problem was first fixed in Version 9.7 Fix Pack 5. At a
minimum, this fix should be applied on the client. |
| Workaround |
As a work around to the problem, provide the fully qualified
stored procedure name in the DB2Command.CommandText property. |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 29.06.2011
08.12.2011
08.12.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP5,
9.70.FP5
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.5
|
 |
