|
Problem IC71175
|
Status: Closed |
STORED PROCEDURE PARAMETERS CORRUPTED DURING PREPARE USING EXECUTEREADER METHOD OF ODBC INTERFACE AND C# PROGRAM |
| product: |
DB2 FOR LUW / DB2FORLUW / 980 - 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.8 FP4 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.8 Fix Pack 4 for AIX and Linux
DB2 Version 9.8 Fix Pack 5 for AIX and Linux
|
| Solution |
Problem was first fixed in Version 9.8 Fix Pack 4 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 14.09.2010
08.08.2011
08.08.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.8.FP4
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.8.0.4
|
 |
