-1

When calling a stored procedure using either ExecuteResultSet or ExecuteReader

using (DB2Connection conn = new DB2Connection(connstr))
{
    conn.Open();
    DB2Command cmd = conn.CreateCommand();
    cmd.Transaction = conn.BeginTransaction();

    DB2Parameter db2param = new DB2Parameter("@ENTERPRISE_ID_PR091", DB2Type.Char, 15);
    db2param.Direction = ParameterDirection.InputOutput;
    db2param.Value = enterpriseID.ToCharArray();
    cmd.Parameters.Add(db2param);

    //... many parameters

    cmd.CommandType = CommandType.StoredProcedure; 
    cmd.CommandText = "XXISCHMA.XXIPW09D"; 

    DB2ResultSet dr = cmd.ExecuteResultSet(DB2ResultSetOptions.Scrollable);
}

In Web API the Exceptionis thrown:

SQL0035N The file "C:\Users\documents\visual studio 2013\Projects\App\Web\msg\en_US\db2nmp.xml" cannot be opened

In other Applications an Exception is thrown:

ERROR [22023] [IBM][DB2] SQL0310N SQL statement contains too many host variables.

I don't think the exception texts are correct...

Is this by design?

In COBOL the SQL code of -310 is returned, which is "DECIMAL HOST VARIABLE OR PARAMETER number CONTAINS NON-DECIMAL DATA".

mackjazzy
  • 48
  • 1
  • 8
  • I think the error message is correct: there are no parameter markers in `cmd.CommandText = "XXISCHMA.XXIPW09D"`, so your use of `cmd.Parameters.Add(db2param)` is meaningless. – mustaccio Jul 28 '15 at 13:13
  • It works when the sp finds a row. As I understand, parameter markers are for cmandtype text i.e. "CALL spname(?,?,?)" – mackjazzy Jul 28 '15 at 13:23
  • OK, I can catch an exception in the Web API Controller. So the question why ExecuteResultSet throws an exception in the first place. – mackjazzy Jul 28 '15 at 19:31
  • SQL0035N is about a missing error message file -- [see if this helps](http://stackoverflow.com/questions/14135302/ibm-db2-driver-deployment-installation) – mustaccio Jul 28 '15 at 20:14
  • thanks for the info on SQL0035N - now I get the exception ERROR [22023] [IBM][DB2] SQL0310N SQL statement contains too many host variables. @mustaccio – mackjazzy Jul 29 '15 at 13:48
  • See my first comment – mustaccio Jul 29 '15 at 13:50

1 Answers1

0

Turns out the error -310 returned to the COBOL test program was the one to look at.

So we changed the DECIMAL TO NUMBER in the COBOL stored procedure and now we get back parameters instead of an exception.

Still don't know why this was a problem only when the select does not find any records. I only look at the c# side of the world. Oy veh!

mackjazzy
  • 48
  • 1
  • 8