All, I have researched the above error and please correct me if I misunderstand. The error
ERROR [39501] [IBM][DB2] SQL0450N Routine "SMA301SP" (specific name "2") has generated a result value, SQLSTATE value, message text, or scratchpad which is too long. SQLSTATE=39501.
indicates to me basically that my webservice calling this stored procedure has mismatched datatypes. I believe the error to be in parameter 2 the sql code, but a lack of understanding keeps me from resolving my issue.
I would sincerely appreciate any efforts to help me resolve this conflict and increase my understanding on this error exception.
I'm creating a message to send:
foreach (var item in priceListItems)
{
priceUploadString.Append(String.Format("{0}\t", item.PriceType));
priceUploadString.Append(String.Format("{0}\t", item.PriceLocation));
priceUploadString.Append(String.Format("{0}\t", item.PriceDate));
priceUploadString.Append(String.Format("{0}\t", item.ProductCode));
priceUploadString.Append(String.Format("{0}\t", Math.Round((decimal)item.Price)));
priceUploadString.Append(String.Format("{0}\r\n", item.UserId));
}
priceUploadString.Append(String.Format("\0"));
return priceUploadString.ToString();
then I connect and execute:
DbCommand dbCommand = db.GetStoredProcCommand(String.Format("{0}.SMA301SP", _db2Schema));
dbCommand.CommandType = CommandType.StoredProcedure;
db.AddInParameter(dbCommand, "@DATA_STRING", DbType.String, priceUploadString);
db.AddOutParameter(dbCommand, "@PARM_SQLCODE", DbType.Int32, 9);
db.AddOutParameter(dbCommand, "@PARM_MESSAGE", DbType.String, 80);
db.ExecuteNonQuery(dbCommand);
My stored procedure is:
LINKAGE SECTION.
*
01 PARM-DATA.
05 DATA-STRING.
10 DATA-STRING-LEN PIC S9(4) USAGE COMP.
10 DATA-STRING-TEXT PIC X(3000).
05 PARM-SQLCODE PIC S9(09) USAGE COMP.
05 PARM-MESSAGE PIC X(80).
*
01 NULL-PTR USAGE IS POINTER. --> used to connect to DPL request
PROCEDURE DIVISION USING
PARM-DATA.
my store proc source for db2 is:
CREATE PROCEDURE schemaname.SMA301SP
(DATA_STRING VARCHAR(3000) IN,
PARM_SQLCODE INTEGER OUT,
PARM_MESSAGE CHAR(80) OUT)
again, any help is appreciated.
Kelly