Receiving an SQLCode of -379 when executing a stored procedure through DB2OLEDB

Question

I'm attempting to call a stored procedure in DB2 using the Dd2OleDb ver.6 driver from C#. When calling cmd.ExecuteNonQuery, an OleDbException with a SQLCode of -379 is thrown. Full message is below.

I have a similar call to another stored procedure that works fine.

Both stored procedures work fine when using the IBM DB2 clients, however we are trying to adopt a standard of using OleDB. All the parameter values are of the correct type and in range.

OS: Windows 8.1

.Net Framework 4.6.1 Visual Studio 2017

C# client

const string storedProc = @"T99XXX.CI419UPDATE";

using (var conn = GetCisDBConnection())
using (var cmCommand = new OleDbCommand(storedProc, conn))
{
       cmCommand.CommandType = System.Data.CommandType.StoredProcedure;

       var pCustNbr = new OleDbParameter("P_CUST_NBR", customerNbr);
       var pPremNbr = new OleDbParameter("P_PREM_NBR", premiseNbr);
       var pCmsgType = new OleDbParameter("P_CMSG_TYPE", cmsgType);
       var pCmsgText = new OleDbParameter("P_CMSG_TEXT", cmsgText);
       var pStatus = new OleDbParameter("O_STATUS", OleDbType.Integer, 10, System.Data.ParameterDirection.Output, true, 0, 0, null, System.Data.DataRowVersion.Current, null);
       var pSqlCode = new OleDbParameter("O_SQLCODE", OleDbType.Integer, 10, System.Data.ParameterDirection.Output, true, 0, 0, null, System.Data.DataRowVersion.Current, null);

       cmCommand.Parameters.Add(pCustNbr);
       cmCommand.Parameters.Add(pPremNbr);
       cmCommand.Parameters.Add(pCmsgType);
       cmCommand.Parameters.Add(pCmsgText);
       cmCommand.Parameters.Add(pStatus);
       cmCommand.Parameters.Add(pSqlCode);

       conn.Open();
       cmCommand.ExecuteNonQuery(); // Returns -379

Stored procedure header:

CREATE PROCEDURE T14TOPS.CI419UPDATE (
    IN P_CUST_NBR DECIMAL(7,0),
    IN P_PREM_NBR DECIMAL(7,0),
    IN P_CMSG_TYPE DECIMAL(3,0),
    IN P_CMSG_TEXT VARCHAR(980),
    OUT O_STATUS INTEGER,
    OUT O_SQLCODE INTEGER)

The full error message is

System.Data.OleDb.OleDbException
HResult=0x80040E14
Message=An internal network library error has occurred. A network level syntax error has occurred. SQLSTATE: HY000, SQLCODE: -379
Source=System.Data

StackTrace:

at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForMultpleResults(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at CustomerCare.DataAccess.CisDataAccess.UpdateLog(Decimal customerNbr, Decimal premiseNbr, Decimal cmsgType, String cmsgText) in C:\TFS\MSServer\Source\Main\DataAccess\CisDataAccess.cs:line 103 at DataAccessTest.CisDataAccessTest.UpdateLog_Test() in C:\TFS\MSServer\Source\Main\DataAccessTest\CisDataAccessTest.cs:line 30

System.IndexOutOfRangeException
HResult=0x80131508
Message=Invalid index -1 for this OleDbParameterCollection with Count=6.
Source=System.Data

Try setting the parameter type. Looks like you have a mix of decimal and string. Make sure the parameters are in the same order as they appear in the stored procedure, since OleDb ignores the name of the parameter and relies on the index position. Something goofy with those parameters since you are getting a `Invalid index -1 for this OleDbParameterCollection` message. — LarsTech, Mar 29 '19 at 17:22

score 1 · Answer 1 · answered Mar 29 '19 at 18:01

Apparently the OleDBParameter constructor is not 100% when it comes to identifying the datatype based on the value. Using the constructor that accepts datatype and assigning the value afterwards resolved the issue.

var pCustNbr = new OleDbParameter("P_CUSTNBR", OleDbType.Decimal) { Value = customerNbr }; 
var pPremNbr = new OleDbParameter("P_PREM_NBR", OleDbType.Decimal) { Value = premiseNbr };
var pCmsgType = new OleDbParameter("P_CMSG_TYPE", OleDbType.Decimal) { Value = cmsgType };
var pCmsgText = new OleDbParameter("P_CMSG_TEXT", OleDbType.VarChar) { Value = cmsgText };
var pStatus = new OleDbParameter("O_STATUS", OleDbType.Integer, 10, System.Data.ParameterDirection.Output, true, 0, 0, null, System.Data.DataRowVersion.Current, null);
var pSqlCode = new OleDbParameter("O_SQLCODE", OleDbType.Integer, 10, System.Data.ParameterDirection.Output, true, 0, 0, null, System.Data.DataRowVersion.Current, null);

Receiving an SQLCode of -379 when executing a stored procedure through DB2OLEDB

1 Answers1