SQLSTATE=39501 Stored Procedure/db2 error



IBM's flagship relational database management system

SQLSTATE=39501 Stored Procedure/db2 error

Postby KellyD » Thu May 05, 2011 6:45 am

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
KellyD
 
Posts: 2
Joined: Thu May 05, 2011 6:23 am
Has thanked: 0 time
Been thanked: 0 time

Re: SQLSTATE=39501 Stored Procedure/db2 error

 

Re: SQLSTATE=39501 Stored Procedure/db2 error

Postby KellyD » Thu May 05, 2011 8:42 pm

ok, I have a solution. the Linkage section of the stored procedure needs to be a fullword, so I had to change the 05 levels to 01 levels. Once I did that, it worked. Hope this helps anyone else struggling.
Kelly
KellyD
 
Posts: 2
Joined: Thu May 05, 2011 6:23 am
Has thanked: 0 time
Been thanked: 0 time

Re: SQLSTATE=39501 Stored Procedure/db2 error

Postby BillyBoyo » Sun May 08, 2011 4:34 am

KellyD wrote:ok, I have a solution. the Linkage section of the stored procedure needs to be a fullword, so I had to change the 05 levels to 01 levels. Once I did that, it worked. Hope this helps anyone else struggling.
Kelly


I'm not quite clear on what you mean by "the Linkage section of the stored procedure needs to be a fullword"? You have defined PARM-SQLCODE as a fullword already. Do you mean it has to be on a fullword-boundary?

If you made all the 05's into 01's, how did you get "addressability" for them? Can you show your working code, please?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post