IBM Support

IC69554: STORED PROCEDURE PARAMETERS CORRUPTED DURING PREPARE USING EXECUTEREADER METHOD OF ODBC INTERFACE AND C# PROGRAM

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error 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);
                }
            }
        }
    }
    

Local fix

  • Do not use the Executereader method.  Prepare and execute the
    SQL statement that calls a stored procedure separately.
    

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.7 FP3 or use the circumvention given       *
    ****************************************************************
    

Problem conclusion

  • Problem was first fixed in Version 9.7 Fix Pack 3
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC69554

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2010-06-29

  • Closed date

    2010-10-21

  • Last modified date

    2010-10-21

  • APAR is sysrouted FROM one or more of the following:

    IC69143

  • APAR is sysrouted TO one or more of the following:

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC69554

Modified date: 21 October 2010