| Latest versions | fixlist |
| 11.1.0.7 |
 |
| 10.5.0.9 |
|
| 10.1.0.6 |
|
| 9.8.0.5 |
|
| 9.7.0.11 |
|
| 9.5.0.10 |
|
| 9.1.0.12 |
|
| Have problems? - contact us. | |
| Register for free |
|
| Contact form |
|
DB2 - Problem description
| Problem IC69553 | Status: Closed |
STORED PROCEDURE PARAMETERS CORRUPTED DURING PREPARE USING EXECUTEREADER METHOD OF ODBC INTERFACE AND C# PROGRAM | |
| product: | |
DB2 FOR LUW / DB2FORLUW / 950 - 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: *
* *
* 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.SequentialAc
* 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); *
* *
* } *
* *
* } *
* *
* } *
* *
* } *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 V9.5 Fix Pack 7 or follow the suggested *
* circumvention *
**************************************************************** | |
| 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.5 Fix Pack 7 for Linux, UNIX, and Windows | |
| Solution | |
Problem was first fixed in Version 9.5 Fix Pack 7 | |
| Workaround | |
not known / see Local fix | |
| Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 29.06.2010 19.10.2010 19.10.2010 |
| Problem solved at the following versions (IBM BugInfos) | |
9.5.FP7 | |
| Problem solved according to the fixlist(s) of the following version(s) | |
| 9.1.0.7 |
|
| 9.5.0.7 |
|