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

1

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

c#
stored-procedures
oledbcommand
sqlcode
asked on Stack Overflow Mar 29, 2019 by Jack Whipnert • edited Mar 29, 2019 by marc_s

1 Answer

1

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);
answered on Stack Overflow Mar 29, 2019 by Jack Whipnert

User contributions licensed under CC BY-SA 3.0