Inserting low-values into a varchar column



IBM's flagship relational database management system

Re: Inserting low-values into a varchar column

Postby dick scherrer » Wed Sep 05, 2012 2:08 am

Hello,

If the insert is using the 20A0 as the length somehow, this is too long for the column definition ?
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Inserting low-values into a varchar column

Postby paulmac1968 » Wed Sep 05, 2012 2:41 am

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!!!
paulmac1968
 
Posts: 23
Joined: Fri Aug 10, 2012 8:42 pm
Has thanked: 0 time
Been thanked: 1 time

Re: Inserting low-values into a varchar column

Postby paulmac1968 » Wed Sep 05, 2012 7:11 pm

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
paulmac1968
 
Posts: 23
Joined: Fri Aug 10, 2012 8:42 pm
Has thanked: 0 time
Been thanked: 1 time

Re: Inserting low-values into a varchar column

Postby paulmac1968 » Thu Sep 06, 2012 10:23 pm

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
paulmac1968
 
Posts: 23
Joined: Fri Aug 10, 2012 8:42 pm
Has thanked: 0 time
Been thanked: 1 time

Re: Inserting low-values into a varchar column

Postby dick scherrer » Thu Sep 06, 2012 11:34 pm

Hello,

Maybe DISPLAYing the passed length and the first 10 bytes of the data (both in Hex) when received in the SP would help?
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Inserting low-values into a varchar column

Postby paulmac1968 » Thu Sep 06, 2012 11:50 pm

Yeah... I thought that might be helpful but it's not a cobol stored procedure. It's just SQL.
paulmac1968
 
Posts: 23
Joined: Fri Aug 10, 2012 8:42 pm
Has thanked: 0 time
Been thanked: 1 time

Re: Inserting low-values into a varchar column

Postby BillyBoyo » Fri Sep 07, 2012 12:26 am

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...
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Inserting low-values into a varchar column

Postby dick scherrer » Fri Sep 07, 2012 1:32 am

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?
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Inserting low-values into a varchar column

Postby paulmac1968 » Wed Sep 12, 2012 7:57 pm

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)
paulmac1968
 
Posts: 23
Joined: Fri Aug 10, 2012 8:42 pm
Has thanked: 0 time
Been thanked: 1 time

Re: Inserting low-values into a varchar column

Postby BillyBoyo » Wed Sep 12, 2012 8:06 pm

Thanks for letting us know.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Previous

Return to DB2

 


  • Related topics
    Replies
    Views
    Last post