IIUG Software Repository Files

From: tschaefe@gate.net (Tim Schaefer)
Newsgroups: comp.databases.informix
Subject: Getting the DATA TYPE from syscolumns: An article with sample code attached
Date: 6 Nov 1995 05:10:36 GMT

################################################################################
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 wrapper shell-script, called get_dts.sh. is also posted to show
how to get a listing of columns from syscolumns, with the column types properly
converted from their integer types into meaningful strings, for use in a program.


If you want to create a "1col.4ge", simple modify this script to select based on
yet another argument, that being the column name.  This way you can use the shell
scriptin place of 1col.4ge.

I hope these programs are of benefit to you.  I look forward to your comments
and suggestions for improvement.  Maybe you'll optimize the code into something
better and share it here.  Keep in mind while not perfect, this does offer you
an alternative to using the 4GL.

Tim
################################################################################

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

From: william@carsinfo.com (William Harris)
Subject: Re: Getting the DATA TYPE from syscolumns: An article with sample code attached
Date: 7 Nov 1995 21:14:18 GMT

In article <47k5cc$2jc6@news.gate.net>, tschaefe@gate.net says...
>I hope these programs are of benefit to you.  I look forward to your comments
>and suggestions for improvement.  Maybe you'll optimize the code into
something
>better and share it here.  Keep in mind while not perfect, this does offer
you
>an alternative to using the 4GL.

It appears that this code doesn't handle synonymns; what does Informix 7.x do
with synonyms?  Informix 5.x has two cases:  where syssyntable.btabid points
to a different table in the same database, and then there's the more
complicated case where the table exists on a different database, or a
different server entirely.
--
William Harris                  william@carsinfo.com
CARS Information Systems
4000 Executive Park Drive
Cincinnati, OH  (USA) 45241

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

From: tschaefe@gate.net (Tim Schaefer)
Subject: Getting the DATA TYPE from syscolumns: Code Sample #1:gettype.c
Date: 6 Nov 1995 05:11:49 GMT


  PROGRAM: db_tab1.ec

   AUTHOR: Original author: INFORMIX Software, Inc.

  CHANGES: Tim Schaefer, The Computer Business Co., Inc.

           Modified this program to use a command-line argument of a
           data base name instead of the prompt in the original
           program, so the output can be redirected to a file.

           No error checking is provided for use of the database name
           on the command-line other than what was originally supplied.

           No warranty expressed or implied.

           This software is being made available to you as is, and Tim
           Schaefer and TCBC are not liable for any damages or loss from
           the use of this program.

           Use of this program constitutes an agreement with these terms.

   LATEST: Tue Sep 19 08:55:06 EDT 1995

    NOTES:

   No changes have been made to the majority of this program, except as
   follows:

           1.  a database must be supplied as a command line argument
           2.  tables are now ordered by table name

   All the original comments have been retained.

   From the original program:

   * sqls.ec *

   The following program reads the systables table for the selected database
   and displays the name of each table found followed by a list of the table's
   columns and their lengths.  If the program is interrupted, onintr()
   intercepts the signal, halts the engine with sqlbreak() and allows the user
   to select another database.


  PROGRAM: db_tab2.ec
   AUTHOR: Original author: INFORMIX Software, Inc.
  CHANGES: Tim Schaefer, The Computer Business Co., Inc.

           Modified this program to use a command-line argument of a
           data base name instead of the prompt in the original
           program, so the output can be redirected to a file.

           No error checking is provided for use of the database name
           on the command-line other than what was originally supplied.

           No warranty expressed or implied.

           This software is being made available to you as is, and Tim
           Schaefer and TCBC are not liable for any damages or loss from
           the use of this program.

           Use of this program constitutes an agreement with these terms.

   LATEST: Tue Sep 19 08:55:06 EDT 1995
    NOTES:

   No changes have been made to the majority of this program, except as
   follows:

           1.  a database must be supplied as a command line argument
           2.  tables are now ordered by table name

   All the original comments have been retained.

   From the original program:

   * sqls.ec *

   The following program reads the systables table for the selected database
   and displays the name of each table found followed by a list of the table's
   columns and their lengths.  If the program is interrupted, onintr()
   intercepts the signal, halts the engine with sqlbreak() and allows the user
   to select another database.



  PROGRAM: db_tab3.ec
   AUTHOR: Original author: INFORMIX Software, Inc.
  CHANGES: Tim Schaefer, The Computer Business Co., Inc.

           Modified this program to use a command-line argument of a
           data base name instead of the prompt in the original
           program, so the output can be redirected to a file.

           No error checking is provided for use of the database name
           on the command-line other than what was originally supplied.

           No warranty expressed or implied.

           This software is being made available to you as is, and Tim
           Schaefer and TCBC are not liable for any damages or loss from
           the use of this program.

           Use of this program constitutes an agreement with these terms.

   LATEST: Tue Sep 19 08:55:06 EDT 1995
    NOTES:
   No changes have been made to the majority of this program, except as
   follows:
           1.  a database must be supplied as a command line argument
           2.  tables are now ordered by table name

   All the original comments have been retained.
   From the original program:
   * sqls.ec *

   The following program reads the systables table for the selected database
   and displays the name of each table found followed by a list of the table's
   columns and their lengths.  If the program is interrupted, onintr()
   intercepts the signal, halts the engine with sqlbreak() and allows the user
   to select another database.