Re: Schema Generator, Data Dictionary Questions

Thanks to all who responded to my post. Since I posted I spoke with technical support at Informix, and they faxed me "Tech Info Article #921, Interpreting values in coltype/collength in systables syscolumns, from the Fall 1990 Technotes". The document gives information above and beyond what I could find in the Informix Guide to SQL Reference, so I'll pass some of it on. [The first part describes the integer values that correspond to the various data types (coltype), and discusses how restricting null values are represented (basically add 256 to coltype for "not null")] [picking up with the document...] collength For the types char, smallint, integer, float, smallfloat, serial, and date, collength represents the actual storage length in bytes. All other types have representations that do not specify the actual number of bytes that the column utilizes. Calculations are necessary to translate this representation into the actual length of the column. Decimal and Money Types Both decimal and money are represented in the same way. Since collength is a smallint, it is a two-byte number. The length is stored in the leftmost byte, while the precision is stored in the rightmost byte. The length can be found by taking the value of syscolumns and dividing by 256 using integer division. The precision is the value of syscolumns mod 256. An exception to this rule is the floating decimal. The precision for a decimal where the decimal point can float will show as 255. [Examples supporting this description are given] If you need the actual byte length of decimal data stored in the table refer to the article "Using C-ISAM and SQL on the Same Database Files" published in the Winter 1989 issue of Tech Notes. [Discussion of varchar and blob types skipped] datetime and interval types For datetime and 2 types, collength is an encrypted length definition made up of 4-bit groups. If it is necessary to encode the encrypted definition, refer to the datetime.h header file supplied with both the INFORMIX-4GL and INFORMIX-ESQL/C products. [end of document] As the document, and an email response to my original post, points out, scale and precision have restricted ranges, as they are both 8 bit quantities. I'm sure somewhere in the Informix Manuals they describe the range of acceptable values for scale, precision and the like, I just missed it. Now that I know the answer, it's obvious; perhaps this post can enlighten others who would make the same mistake. Some of you might wonder if the purpose of the schema generator is to build sql schemas, why represent the syscolumn information in a numeric form at all, just store the plain English form, since that's what is generated anyway. The intention of storing a value and going through the translation step back to English is two-fold. First, it saves storage. Second, it allows verification after the database has been created (compare the dictionary's syscolumn value against the syscolumns table's). Thanks for all the help, both suggestions and code. Bill Gale bgale@portal.hq.videocart.com