_File and _Field Tables

[KMoody]

Progress: 10.2b SP7
OpenEdge Architect: 3.4.2.R342_v20090122-9I96EiWElHi8lheoJKJIvhM3JfVsYbRrgVIWL
OpenEdge OS: Windows 7 Professional 2009 SP1

I need to use the Can-* fields in _File and _Field to specify database rights for lists of users. However, the Can-* fields are Character x(63) by default. I need more than 63 characters to store my lists of users, so I've unfrozen the _File and _Field tables and changed the Can-* field type to Character x(150).

Is this change potentially dangerous? If so, how can I store longer lists in these fields?

 

[TomBascom]

All data in Progress databases is variable length.

Progress databases do not enforce length restrictions based on the display format.

This gives SQL-minded people fits. Sorry about that. Us Progress people think that it is a feature and cannot imagine a world that is constrained by silly limits.

x(63) just means that, by default, the *display* of that field will be 63 characters wide.

You can override that default with an explicit FORMAT phrase any time.

You can assign a field of any width (up to the maximum width of 30,000 character) regardless of the fields dictionary FORMAT.

In other words... there was no need to unfreeze anything or change the format in the dictionary. Storing extra long data in those fields will not hurt anything.

 

[KMoody]

Aha! Thanks! That's good to hear. Makes my life easier.

 

[RealHeavyDude]

Furthermore I would not recommend you to make any changes to the database meta-schema. Such changes might bite you somewhere where it really hurts when you least expect it.

They might work now but maybe when you upgrade to the next release something will silently break.

Heavy Regards, RealHeavyDude.

 

[GregTomkins]

This gives SQL-minded people fits. Sorry about that. Us Progress people think that it is a feature and cannot imagine a world that is constrained by silly limits.

A thousand times yes!! It's beyond me how SQL developers can argue that this 'silly limit' is a good thing, but argue they do. I'd say, this is one of the (relatively few) ways in which Progress is unequivocally superior. Of course, nothing is stopping you from using VARCHAR. Well, nothing except support for it is occasionally inconsistent across SQL's.

 

[KMoody]

I guess a size limit would reduce fragmentation and force users to enter information correctly in some cases, but I doubt that advantage outweighs flexibility.

 

[GregTomkins]

I think the argument is that it's more efficient to retrieve a field by, say, reading bytes 13-22 (where 13-22 can be stored in advance) than it is to scan through a block of memory, counting field delimiters, which is how I assume Progress does it, at least at some level (I'm sure TomB understands this better).

But, if you really want to go there, how about a separate data type for 1 bit numbers vs. 2 bit numbers vs. 3 bit numbers, etc?

 

[TomBascom]

The IO op is vastly more expensive than the string positioning stuff. So while there might be a teeny, tiny advantage it is unlikely that anyone would ever notice it.

 

[GregTomkins]

It's much more likely they'd notice that your name is saved as 'BASCO' because some developer wants $5,000 to correct his misassumption that a last name cannot be longer than 5 characters