Getting the DATA TYPE from syscolumns: An article with sample code attached

################################################################################ Getting the Data Type From the Data Base by Tim Schaefer On ocassion I find it necessary to get the string representation of a data type from the data base for a column in a table. This is actually something that happens quite frequently, as I work on applications or derive understanding about columns and tables in a data base. I used to use a program called "ex30.4gl", which is one of the stock examples that comes with the Informix 4GL. This little program has most of the ONLY documentation about how to derive the data type of a column. I have recently stumbled upon some training materials that help a little, but unless you too get the training materials, this ex30.4gl program is one of the only examples I know of that actually shows you how to derive a string representation of the data type. It has some errors but is for the most part a good example. By using a modified version of ex30.4gl, I have a program called 1col.4gl that will give me the string equivalent of the data type stored in the syscolumns table, if I will pass it the data base name, the table, and the column. If there was a stores database with a table called foo and it had a column called address, then I to get a string data type on the address column I would simply: 1col.4ge stores foo address and 1col.4ge would return something like: address CHAR(20) NOT NULL if indeed this is how the address column exists in the data base. ( You can get the 1col.4gl program in my tool kit, see my home page or c.d.i archive ) For example, if the coltype in syscolumns is a 0 ( zero ), then the data type is a CHAR. Also stored is the length of the data type, which is usually literal, until the idea of a NOT NULL column comes into play. Or the data type is a VARCHAR, or DATETIME, or INTERVAL. Then things start to get interesting. ( This is where the idea of a "cute" feature in a system design can make life hell for the people who use it. ) If the data type is NOT NULL, or a DATETIME, OR VARCHAR, or INTERVAL, then al sorts of cryptic calculations need to be made, converting the stored length into HEX, then back to DEC, and on and on. It would've been far easier to just modify the damn syscolumns table and add a couple of columns to store the values instead of going through the goofy process of HEX_TO_DEC and DEC_TO_HEX. This article isn't about the merits of table design, but it does make it very difficult to understand what a columns' data type is, if you look at what is stored in syscolumns. I imagine this cute feature does indeed add overhead to the system at large, as data types are in constant need of conversion. Far better it would be to just simply store the type and length and whatever else is needed to define the data type, and then a simple lookup. A simple lookup AND A CALCULATION is the more complicated approach, but there's probably a good reason why it's done this way. :-) Recently I have run up against a feature in 7.1 where the maximum number of user threads can be exceeded. Before you quickly post your solution, I know about the ONCONFIG file. In the real world of politics, we can't always change the ONCONFIG file without going through the arduous process of having to explain why we need to. So we make a decision. Get involved in a political process, or work around the problem. Well, I've made the decision to work around the problem--the politics just ain't worth it, and I don't like the huge executable that 1col.4ge is, even after stripping it. It's still huge, and disk space is too at a premium. So, I'm motivated to write a C program that will do the same thing for me as 1col.4ge, that won't exceed the MAXUSERTHREADS and waste disk space with a huge executable. They say necessity is the mother of invention, and so it goes. I give you a new program, built on the shoulders of some unknown person at INFORMIX, who wrote the ex30.4gl program. Only this time it's in C. Standard plain vanilla C. The program is called "gettype.c". You compile it as any C program , as in cc gettype.c -o gettype and are ready to use it. It does depart from the way 1col.4gl works in that this program does NOT hit a data base. Instead, this program expects only TWO arguments, the column LENGTH, and the column TYPE. Example: gettype 0 39 CHAR (39) You might wonder how this benefits you without hitting a data base. Well, to back up a bit, I should explain WHY I was getting MAXUSERTHREADS errors. I think it was tied to the ONCONFIG file and such, but more of a problem with the On-Line engine. The program that was calling 1col.4ge was a code generator, and it was calling 1col.4ge in rapid succession to build a record structure, with columns explicitly defined instead of using the "LIKE" syntax. ( I try to avoid the LIKE syntax whenever possible, but had to switch to it for the problem with MAXUSERTHREADS. Sigh. ) The threads piled up and overloaded something ( a virtual processor??? ) enough to trash my record structure. So I set out to build a program that would bypass this problem altogether. It would have to abide by the rules, but it could also break a few too. Since ex30.4gl is my example of choice, I set out to convert it to a C program. The conversion effort is pretty straight forward, except in some cases. It was a great mystery to me why all the hexidecimal voodoo is needed, but I was able to crack the code, and here we are. In order to take advantage of this new program, you need to know the data type as it's stored in the data base, and then pass the type and length to gettype. This can be a puzzle when you may not know what the type and length are, but a simple select of the syscolumns table can find this for you. Or you can create a wrapper shell script to read a flatfile of types and lengths. This might be useful if you want to develop on a machine where the data base or INFORMIX does not exist, and need to develop record structures. You could simply unload the data from systables and syscolumns, and read the unload file for the type and length based on a column. A wrapper shell script can make it easy for you, and gettype.c can do the conversion work. The speed and disk savings are nice too. On an AIX machine gettype.c compiled to a little over 10K. This is pretty nice compared to almost a 1MB compile for the 1col.4ge program. But what if I DO want to get the data type from INFORMIX? Isn't there an ESQLC program that could do the job? Of course. and I've posted three example programs for your use. These were created from the sqls.ec program from the ESQL demo programs. There is an ESQLC library function to get the data type for a column, but it doesn't tell me if the column is a "NOT NULL" column, so I took gettype.c, and merged it into the three EC programs. Each is a different application of the gettype program, but allows you to see how it can be applied. Each of these programs are compiled: esql program.ec -o program The ESQLC programs compile to quite large executables, but smaller than the 4GL programs. This is interesting. But at least they're smaller. A sample of a wra