QMF VARCHAR Length Variable

[Member 310024]

My question is about querying a DB2 data-base using QMF.
The DB2 record contains a VARCHAR(186) field.
(The 1st 2 bytes = Length Variable, the remaining 184 contain the variable length text).
I can get the Length Variable & Text using SQL embedded COBOL, but when I use a SELECT * in QMF, I only get the text variable returned in my result.
I want the Length Variable too.
How do I tell QMF that I want it returned also?
Thanks in advance.

 

[MarcLodge]

Hi Terminate,

I'm not sure that you can instruct QMF to return the actual varchar length. I would select it implicitly using LENGTH and possibly RTRIM too. eg.

SELECT LENGTH(RTRIM(VARCHAR_COL)), VARCHAR_COL

I have a feeling that you need the RTRIM otherwise you can run into problems if the column is blank because the LENGTH function returns the full length of the column. I'm not 100% certain of this, it's just a recollection I have from a while back.

Hope this helps.

Marc

 

[MarcLodge]

Terminate,
I've just had a play and have got back some odd results. One table I did it on gave back a 0 length when the column was blank, but another gave back the full length of the column. Another gave a difference of 2 between the standard LENGTH and the RTRIM and on investigation I discovered that the field had two spaces at the end!

I'd have a try out with your columns and see what fits your needs best.

Marc

 

[Member 310024]

Marc

I think the RTRIM construct calculates the position
of the last non-space character.
The sql works perfectly on my data-base, as far as
doing this calculation is concerned.
But ... is the length variable guaranteed to be containing
this value.
For example, if a programmer puts a value in the text field
and also puts a number larger than the text length, into
the length field, and then inserts the record into the DB,
does DB2 keep the length value the programmer specified,
OR recalculate the length variable based on the length
of the text?

What I want to do, is check that programmers are not just
putting say, the maximum size of the VARCHAR field, into
the length variable, and defeating the whole purpose of
using the VARCHAR concept.

 

[MarcLodge]

Terminate,
I don't think the LENGTH function will give you the last char of the data, and I think that this is where I've always used RTRIM.

Thinking about it, it's quite possible that an application wishes to include blanks in it's VARCHAR column, as this may be a quite valid condition. It's reasonable therefore for the application to define the VARCHAR LENGTH as longer than the last non space char of the column.

It looks as if it will depend on your business application to determine exactly what you require, but in most circustances, that might well include the RTRIM function.

Hope it all works out well.

Marc