|
Problem IC69143
|
Status: Closed |
STORED PROCEDURE PARAMETERS CORRUPTED DURING PREPARE USING EXECUTEREADER METHOD OF ODBC INTERFACE AND C# PROGRAM |
| product: |
DB2 FOR LUW / DB2FORLUW / 910 - DB2 |
| Problem description: |
Problem Abstract:
The parameters of a stored procedure call get corrupted during
the prepare phase when using the ODBC Interface to execute the
SQL with the ExecuteReader method.
Client: Windows 2003 64 Bit, DB2 V9.1 FP6a 64 Bit
Server: remote zOS based DB2 8.1.5 database stored procedure
How easy is problem to reproduce? Easy
Problem Desc:
The ExecuteReader() method will produce unexpected results
depending on how the stored procedure uses the corrupted stored
procedure parameters.
Here is a c# program that uses ODBC interface that can reproduce
this defect:
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Odbc;
using System.Data.OleDb;
namespace testDB
{
class Program
{
static void Main(string[] args)
{
string cmdstr = "";
if (args.Length > 0)
{
cmdstr = args[0];
}
TestODBCDB2(cmdstr);
}
static void TestODBCDB2(String cmdstr)
{
// create and open a new connection
try
{
// open the connection
Console.WriteLine("Openning connection....");
String connStr =
"DSN=SAMPLE;UID=UserID;PWD=password;DATABASE=SAMPLE;WSID=serv
erName";
OdbcConnection Conn = new
OdbcConnection(connStr);
Conn.Open();
OdbcCommand command = new OdbcCommand("call
TEST_STORED_PROC ('Param1',0);");
command.Connection = Conn;
OdbcDataReader reader =
command.ExecuteReader(System.Data.CommandBehavior.SequentialAcce
ss);
Console.WriteLine("printing result");
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
Console.Write(reader.GetValue(i));
Console.Write("\t");
}
Console.WriteLine("");
}
command.Dispose();
Console.WriteLine("done");
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
Console.WriteLine(e.StackTrace);
}
}
}
} |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* Problem Abstract: *
* *
* The parameters of a stored procedure call get corrupted *
* during the prepare phase when using the ODBC Interface *
* to execute the SQL with the ExecuteReader method. *
* *
* Client: Windows 2003 64 Bit, DB2 V9.1 FP6a 64 Bit *
* Server: remote zOS based DB2 8.1.5 database stored procedure *
* *
* How easy is problem to reproduce? Easy *
* *
* Problem Desc: *
* The ExecuteReader() method will produce unexpected results *
* depending on how the stored procedure uses the corrupted *
* stored procedure parameters. *
* *
* Here is a c# program that uses ODBC interface that can *
* reproduce this defect: *
* <BEGIN C# program> *
* using System; *
* using System.Collections.Generic; *
* using System.Text; *
* using System.Data.Odbc; *
* using System.Data.OleDb; *
* *
* namespace testDB *
* { *
* class Program *
* { *
* static void Main(string[] args) *
* { *
* string cmdstr = ""; *
* if (args.Length > 0) *
* { *
* cmdstr = args[0]; *
* } *
* TestODBCDB2(cmdstr); *
* } *
* *
* static void TestODBCDB2(String cmdstr) *
* { *
* // create and open a new connection *
* try *
* { *
* // open the connection *
* Console.WriteLine("Openning *
* connection...."); *
* String connStr = *
* "DSN=SAMPLE;UID=UserID;PWD=password;DATABASE=SAMPLE;WSID=serve
* OdbcConnection Conn = new *
* OdbcConnection(connStr); *
* Conn.Open(); *
* OdbcCommand command = new OdbcCommand("call *
* TEST_STORED_PROC ('Param1',0);"); *
* command.Connection = Conn; *
* OdbcDataReader reader = *
* *
* command.ExecuteReader(System.Data.CommandBehavior.SequentialAc
* Console.WriteLine("printing result"); *
* while (reader.Read()) *
* { *
* for (int i = 0; i < *
* reader.FieldCount;i++) *
* { *
* Console.Write(reader.GetValue(i)); *
* Console.Write("\t"); *
* } *
* Console.WriteLine(""); *
* } *
* command.Dispose(); *
* Console.WriteLine("done"); *
* } *
* catch (Exception e) *
* { *
* Console.WriteLine(e.ToString()); *
* Console.WriteLine(e.StackTrace); *
* } *
* } *
* } *
* } *
* <END C# program> *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 V9.1 FP10 or use the circumvention given *
**************************************************************** |
| Local Fix: |
Do not use the Executereader method. Prepare and execute the
SQL statement that calls a stored procedure separately. |
| available fix packs: |
DB2 Version 9.1 Fix Pack 10 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12 for Linux, UNIX and Windows
|
| Solution |
Problem was first fixed in Version 9.1 Fix Pack 10 |
| Workaround |
not known / see Local fix |
| BUG-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC69553 IC69554 IC71175
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 10.06.2010
29.06.2011
29.06.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.1.FP10
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.1.0.10
|
 |
