IBM Mainframe Forum

Hello,

If the insert is using the 20A0 as the length somehow, this is too long for the column definition ?

added trunc(bin) and then comp-5. no dice. still -311. going to have to look at it fresh in the morning. thx for your help!!!

Alright... I got it to work. Looks like the problem is with the parameter I'm passing into the stored procedure I'm using. When I just embed the sql in the program, it works fine. So my next question is, what should the stp parameter look like.

To reset, the host variable that I use to pass to the stored procedure is defined like this....

01 VARIN-PAYLOAD-VARCHAR.
49 VARIN-PAYLOAD-VARCHAR-LEN PIC S9(4) USAGE COMP.
49 VARIN-PAYLOAD-VARCHAR-DATA PIC X(23000).

The 01 level is what I pass in. It's also the variable I used when I embedded.

In my STP I have the parameter defined as....

IN IN_PAYLOAD VARCHAR (23000) FOR BIT DATA,

My column definition looks like this in Catalog Mgr...

Cmd Column Name ColNo Datatyp Length Label PKNo Null Dflt Bit
----v----1----v----2----v----3----v----4----v----5----v----6----v----7--
POL_MSTR_FILE_CLOB 7 VARCHAR 23000 0 N N B

Thanks.

Paul

Just a quick note on the last post... I did get it to work simply by imbedding the sql instead of accessing a stored procedure. However, I would really like to get it to work with my STP. Any ideas as to why imbedded it works and using a stored proc it doesn't??

Thanks.

Paul

Hello,

Maybe DISPLAYing the passed length and the first 10 bytes of the data (both in Hex) when received in the SP would help?

Yeah... I thought that might be helpful but it's not a cobol stored procedure. It's just SQL.

You have in the Cobol program the two bytes indicating the length followed by the up-to-23000 bytes of data. In your stored procedure you have just a lump of 23000. I have no idea, but does "it" assume that the first two bytes of the 23000 are the length (in which case the field should be 23002, perhaps) or is the length somewhere separate?
There must be something in some manual somewhere about stored procedures with SQL and fields greater than 9999 bytes...

Might we see this particular Stored Prodedure (if it is not too long)

I still wonder if the process is trying to use the first part of the data as the length?

Gentlemen,

I did find a fix to this issue. It turned out to be pretty simple. In the CREATE PROCEDURE section after input/output parameters are declared, I added this statement...
PARAMETER VARCHAR STRUCTURE
I then had the stp dropped and recreated. Works beautiful.

Thx.

Paul

These users thanked the author paulmac1968 for the post:

BillyBoyo (Wed Sep 12, 2012 8:06 pm)

Thanks for letting us know.