Page 1 of 2

Inserting low-values into a varchar column

PostPosted: Sun Sep 02, 2012 5:19 pm
by paulmac1968
I'm having an issue inserting data into a varchar column when there are low-values in the data. In this case, the low-values are valid because it represents a value of zeroes in a comp-3 field. Basically, what is happening is that data after and including the byte with the hex 00 don't get inserted.

Anyone ever have an issue with this?

Thanks.

Paul Mac

Re: Inserting low-values into a varchar column

PostPosted: Sun Sep 02, 2012 11:01 pm
by dick scherrer
Hello,

LOW-VALUES (x'000000') is NOT valid for a comp-3 field. If this is being done in a COBOL program, move ZEROS to the host variable.

Re: Inserting low-values into a varchar column

PostPosted: Mon Sep 03, 2012 2:18 am
by BillyBoyo
How did you get low-values into the comp-3 field?

If this is "working" elsewhere, can you show your field definition and the value of compiler option NUMPROC that you are using.

"low-values" is binary zeros, so zero in a binary (COMP, COMP-4, COMP-5) fields. It is not zero in a comp-3 field,

Can you explain exactly what you mean by this:

Basically, what is happening is that data after and including the byte with the hex 00 don't get inserted.


If it helps, show the data you have, with HEX ON, please.

Re: Inserting low-values into a varchar column

PostPosted: Mon Sep 03, 2012 2:39 pm
by GuyC
is the table defined as EBCDIC or as UNICODE ?
Anyway, column should be defined as varbinary or as varchar for bit data

Re: Inserting low-values into a varchar column

PostPosted: Mon Sep 03, 2012 5:27 pm
by paulmac1968
BillyBoyo,

The field is defined as PIC 9V999 COMP-3. So when you have a value of 1.00 for example, it'll appear as x'01000F'. The important piece here would be that second byte which is x'00'. That's a low-value.

I'm using a stored procedure to do the actual insert. The cobol program that does the call to it has NUMPROC(MIG).

Finally, what I mean by the "Basically... " statement is this. If you look at the string that I'm trying to put into the varchar column, it's 7500 bytes long. When I examine the stirng in storage before I attempt to commit, all 7500 bytes are there. After the insert I look at the database. Only the first 238 bytes are there. The byte before the low-value I describe above is the last piece of data on the table.

Here's what the data actually looks like in hex

FFFFFFFF000
2013091310F

As I stated earlier, from the second to the last byte you see on, it doesn't make it to the database.

Thx.

Re: Inserting low-values into a varchar column

PostPosted: Mon Sep 03, 2012 5:32 pm
by paulmac1968
GuyC,

The column is defined as LONG VARCHAR.

Thx.

Paul

Re: Inserting low-values into a varchar column

PostPosted: Mon Sep 03, 2012 6:31 pm
by GuyC
if it's a C or java stored procedure my guess would be that the hostvariable is defined as a (null-terminated) string.
It still should be defined as LONG VARCHAR FOR BIT DATA.

Re: Inserting low-values into a varchar column

PostPosted: Tue Sep 04, 2012 5:05 am
by BillyBoyo
paulmac1968 wrote:BillyBoyo,

The field is defined as PIC 9V999 COMP-3. So when you have a value of 1.00 for example, it'll appear as x'01000F'. The important piece here would be that second byte which is x'00'. That's a low-value.

I'm using a stored procedure to do the actual insert. The cobol program that does the call to it has NUMPROC(MIG).

Finally, what I mean by the "Basically... " statement is this. If you look at the string that I'm trying to put into the varchar column, it's 7500 bytes long. When I examine the stirng in storage before I attempt to commit, all 7500 bytes are there. After the insert I look at the database. Only the first 238 bytes are there. The byte before the low-value I describe above is the last piece of data on the table.

Here's what the data actually looks like in hex

FFFFFFFF000
2013091310F

As I stated earlier, from the second to the last byte you see on, it doesn't make it to the database.

Thx.


Can you show your Cobol definition of the structure to go into the VARCH. Maybe the first 5 sub-fields, and the five sub-fields with the around the comp-3.

The NUMPROC(MIG) is interesting, as it is intended for "migration" from pre-Enterprise to Enterprise Cobol. The presumption being that once the migration is fully "bedded-in" then PFD or NOPFD can be selected.

At the moment, not relevant to your question, as you don't have low-values in the whole comp-3 field, you happen to have one byte of a comp-3 that has X'00' in it.

Re: Inserting low-values into a varchar column

PostPosted: Wed Sep 05, 2012 1:30 am
by paulmac1968
GuyC,

I did what you suggested. Changed to a varchar for bit data. However, I'm now getting a -311. In this case, it doesn't make sense since I'm defining a length within the length of the column itself.

Here's the column definition

type_name column_size
LONG VARCHAR FOR BIT DATA 23000

In the program, my host variables are defined as such...
01 VARIN-PAYLOAD-VARCHAR.
49 VARIN-PAYLOAD-VARCHAR-LEN PIC S9(4) USAGE COMP.
49 VARIN-PAYLOAD-VARCHAR-DATA PIC X(23000).


When the insert executes, here are my values...
49 VARIN-PAYLOAD-VARCHAR-LEN > +9999
49 VARIN-PAYLOAD-VARCHAR-DATA > 20A0033809651130913140913C2013 {lots more data after this}

When I call my stored procedure, I use VARIN-PAYLOAD-VARCHAR to populate the column.

So, this seems inconsistent with what the definition of a -311 is.

Any hints??

Thanks.

Paul M

Re: Inserting low-values into a varchar column

PostPosted: Wed Sep 05, 2012 2:06 am
by BillyBoyo
Another compiler option, then. What do you have for TRUNC.

Without TRUNC(BIN) your COMP PIC S9(4) can hold a maximum value of +9999 and a minimum of -9999.

If you move greater than 9999 to it, you will get truncation. Exactly how it truncates can depend on what you are MOVEing to it. If you don't have TRUNC(BIN) that'll be at least part of your problem. If so, show what you are MOVEing to the field and we can show what value you'd get after truncation. Could easily explain the situation you started out with.

If you don't have BIN, the best resolution is to make the field COMP-5, which will allow +/-32k-ish, the maximum values that the storage can hold, rather than the PICTURE defines.

If you have BIN, it is something else :-)