Retrieve CLOB Parameter

[d***@trustmark.com]

This is a C#.NET application, which calls a stored procedure via DB2 Connect 8.1 version. I use ODBC.NET driver.

The stored procedure returns three parameters. The first two are CHAR type and the third one is CLOB. When I execute the query, I get the first two values but the CLOB value comes as DBNULL. No error is thrown.

After reviewing, the CLOB data is being populated on the mainframe, but when we attempt to access the output Clob parameter , no data is present. Has anyone seen this before?

Any information anyone can provide would be greatly appreciated.

[f***@yahoo.com]

First, how and where did u execute that stored procedure?

As of the it writing clob into mainframe (are you working on the same mainframe?), make sure to check the procedure you reviewed may have call another one to write the clob to the mainframe. If the testing data case does write clob data out, you need to use the same data case to execute that procedure. make sure the receiving variable being able to hold the clob data in terms of type and location if any.

Your description is unclear in terms of describing the exact situation you were encountering.

[d***@trustmark.com]

Thanks for your response.
I develop C# application. The data is in Mainframe(Z/OS). The mainframe developers wrote a stroed procedure which returns a CLOB output and two CHAR outputs. My requirements is I need to read the output data and manipulate it in my application.

On the mainframe side they tested the procedure and made sure it returns the CLOB. When I call the same procedure through C# application I get the CHAR outputs successfully but the CLOB comes as DBNULL.

As I use the ODBC.Net provider the equivalent data type for CLOB is SQL_LONGVARCHAR which is defined as OdbcType.Text.

This is part of my c# code.

..
odcomm.CommandText = "{CALL DB2.READCLOB(?,?,?,?,?,?,?,?,?,?)}"
...
odcomm.Parameters.Add("@CP-OUT-SQLCODE", OdbcType.VarChar, 10).Direction = Data.ParameterDirection.Output
odcomm.Parameters.Add("@CP-OUT-COUNT", OdbcType.VarChar, 5).Direction = Data.ParameterDirection.Output
odcomm.Parameters.Add("@CP-OUT-DATA", OdbcType.Text, 152000).Direction = Data.ParameterDirection.Output
odcon.Open()
odcomm.ExecuteNonQuery()
odcon.Close()
Label1.Text = odcomm.Parameters("@CP-OUT-SQLCODE").Value.ToString()
Label2.Text = odcomm.Parameters("@CP-OUT-COUNT").Value.ToString()
TextBox1.Text = odcomm.Parameters("@CP-OUT-DATA").Value.ToString()

[f***@yahoo.com]

As I said that you need to use the same test data as the mainframe guy used and you better to try it on command line to dry run that stored procedure to verify that it does return the CLOB properly.

When you run your code, you need to use that same testing data. But I never code in C#, I am a J2EE guy.

On the other hand, I believe that you may directly use the SQL select statement to retrive that CLOB column and test it. I don't know how you use C# to hold the retrieved binary bytes data.