Field LENGTH vs Field Format

[sherzog1027]

Recently, I made some updates to a few fields by making their format larger, for example from x8 to x20. However, in other Progress DBs that we have on a local machine, it did take the update from the .df but when querying it, it still only returned 8 characters. I was able to find out that it was related to the LENGTH field. Why does the LENGTH of the field not change with the format? Is this a known issue of any kind? We are trying to figure out why this matters and what we can do to make sure this does not happen again. If anyone has any input or idea about this, I would appreciate it. Thanks.

 

[jongpau]

Why does the LENGTH of the field not change with the format?

Hi,

Length of a field in Progress/OpenEdge means the length of the actual contents, so LENGTH("ABC") will return 3, regardless of the format. However, unlike in other databases, the format of a field really only relates to how it is displayed and updated and does not restrict the length of the stored data. For instance, in a version 9 database you can have a character field format "x(20)", but you can store up to 32k of data in the field (as long as you do not exceed the maximum total record size, which I believe is also 32k - I am sure someone will correct me if I am wrong there).

The same goes with numbers by the way. You can have an integer formatted ">>9" but that doe not mean that the number you can store in that field has to be between 0 and 999. In fact, you can store any number between the minimum (negative) and maximum integer values (you will however get a "value cannot be displayed" message when you try to display the such a value without giving it an appropriate format).

Paul

 

[sherzog1027]

Paul,
Thanks for the response. What you said in there I already know about. When I created the .df file to update the local DB, it does the following:

UPDATE FIELD "ccf_fmt" OF "ccr_form"
FORMAT "x(20)"

However, what I am referring to, it should look like this:

UPDATE FIELD "ccf_fmt" OF "ccr_form"
FORMAT "x(20)"
LENGTH 20

This is the LENGTH I am referring to. I had a value in that field that was 15 chars long, but it would only return 8 (which is what LENGTH originally was). We are trying to understand why it does this to begin with (length and format) and also, why the DB does not output the LENGTH to be the same as the format when doing a .df and if there is a way to make it does so.

 

[Casper]

Hi,

Like JongPau already said the format is the display format. If you changed the database so that the field you are talking about has a display format of X(20) then the display statement will display the field with format X(20).
It will do that also if you enter a whole sentence in the field. You can overrule default format display by providing your own format.

e.g. :display ccr_form.ccf_fmt format 'X(200)'.

The display format has nothing to do with the ammount of data contained in the field.

Length and format are two completely different things.
Length is the actual length of the data in that particular field in that particular row of the table. (Progress function)
Format is the default format which is used to display the data in that field. (Database Property)

That's one of the reasons that you have to run dbtool if you want to use SQL on a Progress database. Unlike most other databases fields in a Progress database doesn't have a fixed length.

Casper

 

[sherzog1027]

I understand what the difference is and how to override it in a display statement, but in practice, length is apparently something I need to set. No matter how long my field actually was, it would only think the length was 8.

I wanted PackingSlip.rpt returned, but every call to it would return PackingS, which are the first 8 characters. I was able to update the LENGTH field in the table manually, but if LENGTH is truly the length of the current value, why did it not change until I told it to use 20 as the LENGTH?

I have not heard of dbtool before, what is that?

 

[Casper]

Ok, we are probably mixing some definitions up here....

With length I mean the Progress function Length and not a field name. Length is NOT something you set. Length is a progress function which returns the length of the data in a field.

From online help Length:

Returns the number of characters, bytes, or columns in a string, or returns the number of bytes in an expression of type RAW or a BLOB field, as an INTEGER value.

So if the actual content of a field is 100 characters and the dislay format = 'X(8)' then length would return: 100.

message length(fieldname) view-as alert-box.

I was able to update the LENGTH field in the table manually

If you say you where able to set LENGTH for a particular table then I really wonder what you did, since length function is read-only and only applies to a (read this as 1) field and not to a whole set of records which is what a table is.

but if LENGTH is truly the length of the current value, why did it not change until I told it to use 20 as the LENGTH?

I already answered that one.

So now back to your problem:

you say:

I wanted PackingSlip.rpt returned, but every call to it would return PackingS

How do you return this?
Show me some code, then I can tell you what the problem is.
If you use put then you have to say: put unformatted to get the data without the display format.....

And while we at it: Progress version would be nice too

Regards,

Casper.

 

[sherzog1027]

OK, I agree, I think we are mixing up some terms. I am not referring to the length function within Progress. If you do a dump of a table definition, it will include something like the following:

[FONT=&quot]ADD FIELD "ccf_cust" OF "ccr_form" AS character
DESCRIPTION "Customer#"
FORMAT "x(8)"
INITIAL ?
LENGTH 8
ORDER 5010
CASE-SENSITIVE[/FONT]

The highlighted section is what I am referring to. This is a property of a field within a table. This property does not change when you update the format of the field. This was causing my problem.

As far as our code goes, it is not progress code that gives us the issue. We use Visual J++ for a project (I know, I know, I am trying to get rid of it...) that does an SQL call SELECT ccf_fmt FROM ccr_mstr... This select returns only the first 8 characters (as defined by the LENGTH property of the field). I had to update this property to 20 for it to return the full value into my project. This is where I am struggling to understand this length property and why SQL cares about it and Progress does not.

 

[Casper]

Ah, that explains a lot, thought already I was missing something here.

from the Knowledge base:

The LENGTH attribute is a format used for DataServer support. It is ignored for Progress database data.

And after reading this in the KB, I can understand you confusion about this....
Unless the database you are using isn't a Progress database.

I'll see if I can reproduce this....

Regards,

Casper

 

[Casper]

Depending on what type of access you use for SQL you get different results.
In the SQL-world fields are all of fixed length.

If you use Progress OID then the result is always in display format. So if any field contains more data then can be dislplayed with display format the data doesn't show up with SQL.

If you use sql92 then the sql-width property defines the fixed length of the field. If a field contains "more" data then this width property then an error is raised.
The sql-width is default twice the display format. Because in a progress database it is more then likely that data can exceed this value a tool is made by Progress, which is called dbtool. This tools adjust the sql-width to the maximum value a given field in a table has.

I just tried to reproduce this LENGTH issue, but in 10.1B I can't reproduce it. I took the sports2000 database and altered the field customer.name from the customer table to have length 2.
But select statement returns entire name.....

What version of Progress are you on and what type of connection do you use to retrieve data with SQL?

Regards,

Casper