Subject: Getting the DATA TYPE from syscolumns: An article with sample code attached
From: [email-masked] (Tim Schaefer)
Date: 6 Nov 1995 05:10:36 GMT
Newsgroups: comp.databases.informix

################################################################################
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: [email-masked] (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 <[email-masked]>, [email-masked] 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                  [email-masked]
CARS Information Systems
4000 Executive Park Drive
Cincinnati, OH  (USA) 45241

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

From: [email-masked] (Tim Schaefer)
Subject: Getting the DATA TYPE from syscolumns: Code Sample #1:gettype.c
Date: 6 Nov 1995 05:11:49 GMT

/*****************************************************************************
*

      PROGRAM: gettype.c ( gettype )
       AUTHOR: Tim Schaefer, The Computer Business Company, Inc.
       LATEST: Tue Oct 31 17:47:54 EST 1995
HOST LANGUAGE: Standard C
  DESCRIPTION: Returns the character-string representation of an INFORMIX data
               type.  For a given column-type spcified in the syscolumns table
               and a given column-length, will convert these integer values
               into a meaningful string useful for use in program development.
        USAGE: gettype column-type column-length
      RETURNS: character-string without carriage-return or line-feed.
     EXAMPLES: A typical application of this program would be to build record
               structures for 4GL programs.  See the get_dts.sh shell-script
               for an example.

******************************************************************************
/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <ctype.h>

char  large_fld[12];
char  small_fld[12];
short large_size;
short small_size;
char  dt_size[36];
char  intv_size[36];
char  hex_string[5] = "" ;
short null_size;
int   min_length;
int   max_length;
char  fld_nm[12];
short fld_size;
char  ct_str[51];
char  not_null[10];
short msize;
short nsize;
short large_lngth;
short small_lngth;

main(argc, argv)
int argc;
char **argv;
{
int   type_num = 0;
int length_num = 0;

  type_num = atol( argv[1] ) ;
length_num = atol( argv[2] ) ;
	
convert_type (type_num, length_num);

fprintf(stdout, "%s\n", ct_str );

}
convert_type (coltype_num, col_length)
int  coltype_num;
int  col_length ;
{
int cnv_coltype = 0;
int cnv_collen  = 0;

  strcpy( not_null , "" ) ;

  if ( coltype_num >= 256 )
     {
      cnv_coltype = coltype_num - 256 ;
      coltype_num = cnv_coltype;
      strcpy( not_null , " NOT NULL " );
     }

  switch( coltype_num )
	{
       	case 0:
             	sprintf( ct_str, "CHAR (%d)", col_length );
		break;
       	case 1:
            	strcpy( ct_str , "SMALLINT" );
		break;
       	case 2:
            	strcpy( ct_str , "INTEGER" );
		break;
       	case 3:
            	strcpy( ct_str , "FLOAT" );
		break;
       	case 4:
            	strcpy( ct_str , "SMALLFLOAT" );
		break;
       	case 5:
            	msize = col_length / 256;
            	nsize = col_length % 256;
		if ( nsize == 255 || nsize <= 0 )
                   {
		   sprintf(ct_str , "DECIMAL (%d)", msize );
		   }
		else
		   {
		   sprintf(ct_str , "DECIMAL (%d,%d)", msize, nsize );
		   }
		break;
       	case 6:
            	strcpy( ct_str , "SERIAL" );
		break;
       	case 7:
            	strcpy( ct_str , "DATE" );
		break;
       	case 8:
            	msize = col_length / 256 ;
            	nsize = col_length % 256 ;

            	sprintf( ct_str , "MONEY (%d", msize );
            	if ( nsize != 2 )
               	{
               	strcat( ct_str, ", " );
               	strcat( ct_str, nsize );
               	}
            	strcat( ct_str , ")" );
		break;

       	case 9:
            	strcpy( ct_str , "UNDEFINED" );
		break;

       	case 10:
            	cnvrt_dt (col_length) ;
            	sprintf( ct_str , "DATETIME %s", dt_size ) ;
		break;

       	case 11:
            	strcpy ( ct_str , "BYTE" );
		break;

       	case 12:
            	strcpy ( ct_str , "TEXT" );
		break;

       	case 13:
		min_length = 0;
		max_length = 0;
  		if ( col_length >= 256 )
     		   {
      		   cnv_collen = col_length - 256 ;
      		   col_length = cnv_collen;
     		   }
            	cnvrt_varch (col_length) ;
		sprintf( ct_str , ""  );
            	if ( min_length > 0 )
                   {
            	   sprintf( ct_str , "VARCHAR(%d,%d)", max_length, min_length
);
                   }
                else
               	   {
            	   sprintf( ct_str , "VARCHAR(%d)", max_length );
               	   }
		break;

       	case 14:
            	cnvrt_intvl(col_length) ;
            	sprintf( ct_str , "INTERVAL %s", intv_size );
		break;

       default:
            	sprintf( ct_str , "UNDEFINED: %d", coltype_num );
    }

    strcat( ct_str , not_null );

}

cnvrt_dt (clngth)
long clngth;
{
  sprintf(hex_string, ""  );
  sprintf(hex_string, "%4x", clngth );

  qual_fld ('l', hex_string[2]) ;
   strcpy( large_fld, fld_nm );

  qual_fld ('s', hex_string[3]) ;
  strcpy( small_fld, fld_nm );

  strcpy( dt_size , large_fld );
  strcat( dt_size , " TO "    );
  strcat( dt_size , small_fld );
}

cnvrt_intvl (clngth)
long  clngth ;
{
  long  fld_lngth ;
  short  i;

  sprintf(hex_string, ""  );
  sprintf(hex_string, "%3x", clngth );

  qual_fld ('l', hex_string[1]) ;
   strcpy( large_fld, fld_nm );

  qual_fld ('s', hex_string[2]) ;
   strcpy( small_fld, fld_nm );

  fld_lngth = intvl_lngth(clngth);

  if ( fld_lngth > 0 )
     {
       strcpy ( large_fld , " (" );
       strcat ( large_fld , fld_lngth );
       strcat ( large_fld , ")" );
     }
   strcpy( intv_size , large_fld );
   strcat( intv_size , " TO "    );
   strcat( intv_size , small_fld );
}

cnvrt_varch (clngth)
long  clngth ;
{
  int str0;
  int str1;
  int str2;
  int str3;

  sprintf(hex_string, ""  );
  sprintf(hex_string, "%4x", clngth );

  str0 = dec_digit(hex_string[0]) ;
  str1 = dec_digit(hex_string[1]) ;
  str2 = dec_digit(hex_string[2]) ;
  str3 = dec_digit(hex_string[3]) ;

  if ( str0 > 0 )
     {
     min_length = str0 + str1 ;
     }
  else
     {
     min_length = 1 ;
     }

  max_length = clngth;
}

qual_fld (ftype, fvalue)
char ftype;
char fvalue;
{
    switch( fvalue )
	{
         case  '0':
              	strcpy ( fld_nm , "YEAR" );
              	fld_size = 4 ;
		break;

         case  '2':
              	strcpy ( fld_nm , "MONTH" );
              	fld_size = 2 ;
		break;

         case  '4':
              	strcpy ( fld_nm , "DAY" );
              	fld_size = 2 ;
		break;

         case  '6':
              	strcpy ( fld_nm , "HOUR" );
              	fld_size = 2 ;
		break;

         case  '8':
              	strcpy ( fld_nm , "MINUTE" );
              	fld_size = 2 ;
		break;

         case  'A':
         case  'a':
              	strcpy ( fld_nm , "SECOND" );
              	fld_size = 2 ;
		break;

         case  'B':
         case  'b':
              	strcpy ( fld_nm , "FRACTION (1)" );
              	fld_size = 1 ;
		break;

         case  'C':
         case  'c':
              	fld_size = 2 ;
              	if ( ftype == 'l' )
                 	{
                 	strcpy ( fld_nm , "FRACTION" );
                 	}
              	else
                 	{
                 	strcpy ( fld_nm , "FRACTION (2)" );
                 	}
		break;

         case  'D':
         case  'd':
              	strcpy ( fld_nm , "FRACTION"    );
              	fld_size = 3 ;
		break;

         case  'E':
         case  'e':
              	strcpy ( fld_nm , "FRACTION (4)" );
              	fld_size = 4 ;
		break;

         case  'F':
         case  'f':
              	strcpy ( fld_nm , "FRACTION (5)" );
              	fld_size = 5 ;
		break;

         default:
              	sprintf ( fld_nm , "uh oh:%s", fvalue );
              	fld_size = 0 ;
		break;
    }
}

intvl_lngth (clngth)
long  clngth ;
{
  long   dec_lngth      ;
  long   num_flds       ;
  long   default_lngth  ;
  long   ret_lngth      ;

  int dec_digit_1 ;
  int dec_digit_2 ;

  sprintf(hex_string, ""  );
  sprintf(hex_string, "%3x", clngth );

     dec_lngth   = atoi( hex_string[0] ) ;
     dec_digit_1 = atoi( hex_string[1] ) ;
     dec_digit_2 = atoi( hex_string[2] ) ;

     num_flds = dec_digit_2 - dec_digit_1  ;

    switch( num_flds )
	{
         case 0 :
              if ( dec_lngth == large_lngth )
                 {
                 ret_lngth = 0;
                 }
              else
                 {
                 ret_lngth = dec_lngth;
                 }
		break;

         case 1 :
              	ret_lngth = 0 ;
		break;

         case -1:
              	ret_lngth = 0 ;
		break;

         default:
              default_lngth =  (large_lngth + small_lngth) +  (num_flds - 2);
              if ( default_lngth == dec_lngth )
		 {
                 ret_lngth = 0 ;
		 }
              else
		 {
                 ret_lngth = large_lngth +  ( dec_lngth - default_lngth );
		 }
		break;
    	}

 return ( ret_lngth );

}

atolong( char s[] )
{

int i, n;

n = 0;

for ( i= 0; s[i] >= '0' && s[i] <= '9'; ++i )
    n = 10 * n + (s[i] - '0');
return n;

}

dec_digit( char_2_dig )
int char_2_dig ;
{
   int ret_int;

	switch( char_2_dig )
		{

		case 48:
			ret_int = 0;
			break;

		case 49:
			ret_int = 1;
			break;

		case 50:
			ret_int = 2;
			break;

		case 51:
			ret_int = 3;
			break;

		case 52:
			ret_int = 4;
			break;

		case 53:
			ret_int = 5;
			break;

		case 54:
			ret_int = 6;
			break;

		case 55:
			ret_int = 7;
			break;

		case 56:
			ret_int = 8;
			break;

		case 57:
			ret_int = 9;
			break;

		case 65:
		case 32:
			ret_int = 0;
			break;

		case 66:
		case 97:
			ret_int = 10;
			break;

		case 67:
		case 98:
			ret_int = 11;
			break;

		case 68:
		case 99:
			ret_int = 12;
			break;

		case 69:
		case 100:
			ret_int = 13;
			break;

		case 70:
		case 101:
			ret_int = 14;
			break;

		case 102:
			ret_int = 15;
			break;

		default:
			ret_int = char_2_dig ;
			break;
		}

	return ret_int;
}

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

From: [email-masked] (Tim Schaefer)
Subject: Getting the DATA TYPE from syscolumns: Code Sample #2:get_dts.sh
Date: 6 Nov 1995 05:12:46 GMT

#!/bin/sh
##############################################################################
#
# PROGRAM: get_dts.sh: get data types shell for a table using the gettype
#          program.
#  AUTHOR: Tim Schaefer, The Computer Business Company, Inc.
#  LATEST: Tue Oct 31 08:04:33 EST 1995
#   USAGE: get_dts.sh database table
##############################################################################
#
DAT_FILE1=temp1.tmp
DAT_FILE2=temp2.tmp
DAT_FILE3=temp3.tmp
DAT_FILE4=temp4.tmp
DAT_FILE5=temp5.tmp
DAT_FILE6=temp6.tmp
database=$1
table=$2
if [ $# -lt 2 ]; then
   echo "Usage: $0  database  table"
   exit
fi
isql -s ${database} > ${DAT_FILE1} 2>/dev/null <<+

select tabname, tabid
from systables
where tabname = "${table}" ;
+
sed -e '/tabname/d' -e '/^$/d' ${DAT_FILE1} > ${DAT_FILE2}
awk ' { print $2  } '  ${DAT_FILE2} > ${DAT_FILE1}

tabid=`cat ${DAT_FILE1}`

isql -s ${database} > ${DAT_FILE3} 2>/dev/null <<+
select syscolumns.tabid, colno, colname, coltype, collength, tabname
from syscolumns, systables
where systables.tabid = $tabid and syscolumns.tabid = $tabid
order by syscolumns.colno;
+
sed -e '/tabname/d' -e '/^$/d' ${DAT_FILE3} > ${DAT_FILE4}
awk ' { printf("%s|%s|%s|%s|%s|%s|\n",$1,$2,$3,$4,$5,$6) } ' ${DAT_FILE4} >
${DAT_FILE5}
awk -F"|" ' BEGIN { run_str="" }
{
{ printf("%s|%-s|", $6,$3 ) }
run_str=" gettype "$4" "$5
{ system( run_str ) }
}
' ${DAT_FILE5} > ${DAT_FILE6}
awk ' { printf("%s|\n",$0 ) } ' ${DAT_FILE6}
rm ${DAT_FILE1} ${DAT_FILE2} ${DAT_FILE3} ${DAT_FILE4} ${DAT_FILE5}
${DAT_FILE6}

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

From: [email-masked] (Tim Schaefer)
Subject: Getting the DATA TYPE from syscolumns: Code Sample #3:db_tab1.ec
Date: 6 Nov 1995 05:13:55 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.

------------------------------------------------------------------------------
-
*/

#include <stdio.h>
#include <string.h>
#include <ctype.h>
#include <decimal.h>
#include <setjmp.h>
#include <signal.h>
#include <errno.h>

EXEC SQL include sqltypes;

#define LCASE(c) (isalpha(c) ? (isupper(c) ? tolower(c) : c) : c)

jmp_buf sjbuf;

#define DB 1
#define CN 2
int db_or_cn;		/* to determine whether to close or disconnect */

EXEC SQL BEGIN DECLARE SECTION;
char dbenv[81];
char server[19];
EXEC SQL END DECLARE SECTION;

char msgbuf[81];
char  large_fld[12];
char  small_fld[12];
short large_size;
short small_size;
char  dt_size[36];
char  intv_size[36];
char  hex_string[5] = "" ;
short null_size;
int   min_length;
int   max_length;
char  fld_nm[12];
short fld_size;
char  ct_str[51];
char  not_null[10];
short msize;
short nsize;
short large_lngth;
short small_lngth;

main( argc, argv, env )
int argc;
char **argv;
char **env;
{
    int from = 0;
    char *p;
    int onintr();
    long err_chk(), dspcols();

    /*
     * sqlstart establishes an implicit default connection to the
     * server specified by INFORMIXSERVER; sqlstart() should only be
     * called when there are no other connections.
     */

    sqlstart();			
    db_or_cn = DB;

    signal(SIGINT, onintr);
    from = setjmp(sjbuf);

    strcpy ( dbenv,  argv[1] ) ;

    p = strchr(dbenv,'@');

	if (p == NULL)
	    {

	    /*
	     * if the database command does not contain a server
	     * name, then open the database on the implicit default
	     * connection established by sqlstart() or on current connection.
	     */

	    EXEC SQL database :dbenv;
	    db_or_cn = DB;
	    if (err_chk("OPEN") < 0)
		exit( -1 ) ;
	    }

	else
	    {

	    /*
	     * if the database command does contain a server
	     * name, then open the database on the server specified.
	     */

	    EXEC SQL connect to :dbenv;
	    db_or_cn = CN;
	    if (err_chk("Connect to") < 0)
		exit ( -1 );

	    if (dbenv[0] == '@')
		{
		/*
		 * if ONLY the server name has been provided,
		 * we still need to ask for a database name.
		 */
		printf("\n\n\tEnter the database name: ");
		while (getans(dbenv, sizeof(dbenv)) < 0)
		    {
		    printf("\n\tIllegal name\t");
		    exit ( -1 );
		    }
		EXEC SQL database :dbenv;
		db_or_cn = CN;	/* so it will be disconnected */
		if (err_chk("OPEN") < 0)
		    exit ( -1 );
		}
	    }

    dsptbls();
    EXEC SQL disconnect all;
    exit(1);
}

/*
    The onintr() function catches SIGINT and terminates the database engine.
    It then allows the user to select another database.
*/

onintr()
{
    char ans;

    ans = ' ';
    printf("\n ***INTERRUPT *** \n");
    signal(SIGINT, onintr);
    sqlbreak();
    if (db_or_cn == DB)
	EXEC SQL close database;
    else
	EXEC SQL disconnect current;
    while(ans != 'y' && ans != 'n')
        {
	printf("\n\n\t*** Select another database? (y/n)");
	getans(&ans,1);
        }
    if(ans == 'y')
        longjmp(sjbuf,0);
    exit(1);
}

/*
    The dsptbls() function selects the tabname and tabid columns from the
    systables table and displays them. It then calls dspcols() to display
    the columns.
*/

dsptbls()
{
    EXEC SQL BEGIN DECLARE SECTION;
    char tabnm[19];
    long tabid;
    EXEC SQL END DECLARE SECTION;

    EXEC SQL declare systabs cursor for
             select tabname, tabid from systables
             order by tabname;
    EXEC SQL open systabs;
    if(err_chk("OPEN") < 0)
	return(0);
    while(1)
        {
	EXEC SQL fetch systabs into :tabnm, :tabid;
	if((err_chk("fetch")) != 0)
	    break;
	printf("\n\n\nTable Name: %s   Tabid: %d\n", tabnm, tabid);
	if(dspcols(tabid) < 0)
	    break;
	}
	printf("\n");
}

/*
    The dspcols() function accesses the syscols table to display the name,
    data type and length of each column in the table specified by tabid.
*/

long dspcols(tabid)
EXEC SQL BEGIN DECLARE SECTION;
int tabid;
EXEC SQL END DECLARE SECTION;
{
    EXEC SQL BEGIN DECLARE SECTION;
    char colname[19];
    short coltype, collength, colno;
    EXEC SQL END DECLARE SECTION;
    long ret;
    char *rtypname();

    EXEC SQL declare syscols cursor for
              select colname, coltype, collength, colno
                from syscolumns
               where tabid = :tabid
               order by colno
    ;

    EXEC SQL open syscols;

    if(err_chk("OPEN") < 0)
        exit(1);

    printf("\n\t%3s\t%-20s %-10s\n","No", "Column", "Type" );

    while(1)
        {
	EXEC SQL fetch syscols into :colname, :coltype, :collength, :colno;
	if((ret = err_chk("fetch")) != 0)
	    return(ret);
        convert_type (coltype, collength);
	
	printf("\n\t%3d\t%-20s %s", colno, colname, ct_str);
        }
/*
printf("\n\t%3d\t%-20s %-10s\t\t%d", colno, colname, rtypname(coltype),
collength);
*/
}

getans(ans, len)
char *ans;
int len;
{
    char buf[512], c;
    int n = 0;

    while((c = getchar()) != '\n')
        buf[n++] = LCASE(c);
    buf[n] = '\0';
    if(n > 1 && n >= len)
        return(-1);
    if(len <= 1)
        *ans = buf[0];
    else
	strncpy(ans, buf, len);
    return 1;
}

/*
   The err_chk() function checks the SQLSTATE status variable to see
   if an error has occurred following an SQL statement. If an error has
   occurred, err_chk executes the GET DIAGNOSTICS statement and prints
   the detail for each exception that is returned.
*/

long err_chk(s)
char *s;
{
EXEC SQL BEGIN DECLARE SECTION;
int exception_count;
char overflow[2];
int exception_num=1;
char class[255];
char subclass[255];
char message[255];
int messlen;
int rowcount;
char sqlstate[6];
int i;
EXEC SQL END DECLARE SECTION;

   if (!strncmp(SQLSTATE, "00", 2) || !strncmp(SQLSTATE,"02",2))
      {
      return(SQLCODE);
      }
   else
      {
         /*
         printf("---------------------------------");
         printf("-------------------------\n");
         printf("%s:\t SQLSTATE: %s\n",s,SQLSTATE);
         printf("SQLCODE: %d\n", SQLCODE);
         */

         EXEC SQL get diagnostics :exception_count = NUMBER,
                  :overflow = MORE;

         /*
         printf("NUMBER: %d\n", exception_count);
         printf("MORE : %s\n", overflow);
         */

         for (i = 1; i <= exception_count; i++)
             {

             EXEC SQL get diagnostics  exception :i :sqlstate =
                      RETURNED_SQLSTATE,
                      :class = CLASS_ORIGIN, :subclass = SUBCLASS_ORIGIN,
                      :message = MESSAGE_TEXT, :messlen = MESSAGE_LENGTH;

             /*
             printf("SQLSTATE: %s\n",sqlstate);
             printf("CLASS: %s\n",class);
             printf("SUBCLASS: %s\n",subclass);
             printf("TEXT: %s\n",message);
             printf("MESSLEN: %d\n",messlen);
             */
             }

         /*
          * Return the SQLCODE
         */

       return(SQLCODE);
       }
}

convert_type (coltype_num, col_length)
int  coltype_num;
int  col_length ;
{
int cnv_coltype = 0;
int cnv_collen  = 0;

  strcpy( not_null , "" ) ;

  if ( coltype_num >= 256 )
     {
      cnv_coltype = coltype_num - 256 ;
      coltype_num = cnv_coltype;
      strcpy( not_null , " NOT NULL " );
     }

  switch( coltype_num )
	{
       	case 0:
             	sprintf( ct_str, "CHAR (%d)", col_length );
		break;
       	case 1:
            	strcpy( ct_str , "SMALLINT" );
		break;
       	case 2:
            	strcpy( ct_str , "INTEGER" );
		break;
       	case 3:
            	strcpy( ct_str , "FLOAT" );
		break;
       	case 4:
            	strcpy( ct_str , "SMALLFLOAT" );
		break;
       	case 5:
            	msize = col_length / 256;
            	nsize = col_length % 256;
		if ( nsize == 255 || nsize <= 0 )
                   {
		   sprintf(ct_str , "DECIMAL (%d)", msize );
		   }
		else
		   {
		   sprintf(ct_str , "DECIMAL (%d,%d)", msize, nsize );
		   }
		break;
       	case 6:
            	strcpy( ct_str , "SERIAL" );
		break;
       	case 7:
            	strcpy( ct_str , "DATE" );
		break;
       	case 8:
            	msize = col_length / 256 ;
            	nsize = col_length % 256 ;
            	sprintf( ct_str , "MONEY (%d", msize );
            	if ( nsize != 2 )
               	{
               	strcat( ct_str, ", " );
               	strcat( ct_str, nsize );
               	}
            	strcat( ct_str , ")" );
		break;

       	case 9:
            	strcpy( ct_str , "UNDEFINED" );
		break;

       	case 10:
            	cnvrt_dt (col_length) ;
            	sprintf( ct_str , "DATETIME %s", dt_size ) ;
		break;

       	case 11:
            	strcpy ( ct_str , "BYTE" );
		break;

       	case 12:
            	strcpy ( ct_str , "TEXT" );
		break;

       	case 13:
		min_length = 0;
		max_length = 0;
  		if ( col_length >= 256 )
     		   {
      		   cnv_collen = col_length - 256 ;
      		   col_length = cnv_collen;
     		   }
            	cnvrt_varch (col_length) ;
		sprintf( ct_str , ""  );
            	if ( min_length > 0 )
                   {
            	   sprintf( ct_str , "VARCHAR(%d,%d)", max_length, min_length
);
                   }
                else
               	   {
            	   sprintf( ct_str , "VARCHAR(%d)", max_length );
               	   }
		break;

       	case 14:
            	cnvrt_intvl(col_length) ;
            	sprintf( ct_str , "INTERVAL %s", intv_size );
		break;

       default:
            	sprintf( ct_str , "UNDEFINED: %d", coltype_num );
    }

    strcat( ct_str , not_null );

}

cnvrt_dt (clngth)
long clngth;
{
  sprintf(hex_string, ""  );
  sprintf(hex_string, "%4x", clngth );

  qual_fld ('l', hex_string[2]) ;
   strcpy( large_fld, fld_nm );

  qual_fld ('s', hex_string[3]) ;
  strcpy( small_fld, fld_nm );

  strcpy( dt_size , large_fld );
  strcat( dt_size , " TO "    );
  strcat( dt_size , small_fld );
}

cnvrt_intvl (clngth)
long  clngth ;
{
  long  fld_lngth ;
  short  i;

  sprintf(hex_string, ""  );
  sprintf(hex_string, "%3x", clngth );

  qual_fld ('l', hex_string[1]) ;
   strcpy( large_fld, fld_nm );

  qual_fld ('s', hex_string[2]) ;
   strcpy( small_fld, fld_nm );

  fld_lngth = intvl_lngth(clngth);

  if ( fld_lngth > 0 )
     {
       strcpy ( large_fld , " (" );
       strcat ( large_fld , fld_lngth );
       strcat ( large_fld , ")" );
     }
   strcpy( intv_size , large_fld );
   strcat( intv_size , " TO "    );
   strcat( intv_size , small_fld );
}

cnvrt_varch (clngth)
long  clngth ;
{
  int str0;
  int str1;
  int str2;
  int str3;

  sprintf(hex_string, ""  );
  sprintf(hex_string, "%4x", clngth );

  str0 = dec_digit(hex_string[0]) ;
  str1 = dec_digit(hex_string[1]) ;
  str2 = dec_digit(hex_string[2]) ;
  str3 = dec_digit(hex_string[3]) ;

  if ( str0 > 0 )
     {
     min_length = str0 + str1 ;
     }
  else
     {
     min_length = 1 ;
     }

  max_length = clngth;
}

qual_fld (ftype, fvalue)
char ftype;
char fvalue;
{
    switch( fvalue )
	{
         case  '0':
              	strcpy ( fld_nm , "YEAR" );
              	fld_size = 4 ;
		break;

         case  '2':
              	strcpy ( fld_nm , "MONTH" );
              	fld_size = 2 ;
		break;

         case  '4':
              	strcpy ( fld_nm , "DAY" );
              	fld_size = 2 ;
		break;

         case  '6':
              	strcpy ( fld_nm , "HOUR" );
              	fld_size = 2 ;
		break;

         case  '8':
              	strcpy ( fld_nm , "MINUTE" );
              	fld_size = 2 ;
		break;

         case  'A':
         case  'a':
              	strcpy ( fld_nm , "SECOND" );
              	fld_size = 2 ;
		break;

         case  'B':
         case  'b':
              	strcpy ( fld_nm , "FRACTION (1)" );
              	fld_size = 1 ;
		break;

         case  'C':
         case  'c':
              	fld_size = 2 ;
              	if ( ftype == 'l' )
                 	{
                 	strcpy ( fld_nm , "FRACTION" );
                 	}
              	else
                 	{
                 	strcpy ( fld_nm , "FRACTION (2)" );
                 	}
		break;

         case  'D':
         case  'd':
              	strcpy ( fld_nm , "FRACTION"    );
              	fld_size = 3 ;
		break;

         case  'E':
         case  'e':
              	strcpy ( fld_nm , "FRACTION (4)" );
              	fld_size = 4 ;
		break;

         case  'F':
         case  'f':
              	strcpy ( fld_nm , "FRACTION (5)" );
              	fld_size = 5 ;
		break;

         default:
              	sprintf ( fld_nm , "uh oh:%s", fvalue );
              	fld_size = 0 ;
		break;
    }
}

intvl_lngth (clngth)
long  clngth ;
{
  long   dec_lngth      ;
  long   num_flds       ;
  long   default_lngth  ;
  long   ret_lngth      ;

  int dec_digit_1 ;
  int dec_digit_2 ;

  sprintf(hex_string, ""  );
  sprintf(hex_string, "%3x", clngth );

     dec_lngth   = atoi( hex_string[0] ) ;
     dec_digit_1 = atoi( hex_string[1] ) ;
     dec_digit_2 = atoi( hex_string[2] ) ;

     num_flds = dec_digit_2 - dec_digit_1  ;

    switch( num_flds )
	{
         case 0 :
              if ( dec_lngth == large_lngth )
                 {
                 ret_lngth = 0;
                 }
              else
                 {
                 ret_lngth = dec_lngth;
                 }
		break;

         case 1 :
              	ret_lngth = 0 ;
		break;

         case -1:
              	ret_lngth = 0 ;
		break;

         default:
              default_lngth =  (large_lngth + small_lngth) +  (num_flds - 2);
              if ( default_lngth == dec_lngth )
		 {
                 ret_lngth = 0 ;
		 }
              else
		 {
                 ret_lngth = large_lngth +  ( dec_lngth - default_lngth );
		 }
		break;
    	}

 return ( ret_lngth );

}

atolong( char s[] )
{

int i, n;

n = 0;

for ( i= 0; s[i] >= '0' && s[i] <= '9'; ++i )
    n = 10 * n + (s[i] - '0');
return n;

}

dec_digit( char_2_dig )
int char_2_dig ;
{
   int ret_int;

	switch( char_2_dig )
		{

		case 48:
			ret_int = 0;
			break;

		case 49:
			ret_int = 1;
			break;

		case 50:
			ret_int = 2;
			break;

		case 51:
			ret_int = 3;
			break;

		case 52:
			ret_int = 4;
			break;

		case 53:
			ret_int = 5;
			break;

		case 54:
			ret_int = 6;
			break;

		case 55:
			ret_int = 7;
			break;

		case 56:
			ret_int = 8;
			break;

		case 57:
			ret_int = 9;
			break;

		case 65:
		case 32:
			ret_int = 0;
			break;

		case 66:
		case 97:
			ret_int = 10;
			break;

		case 67:
		case 98:
			ret_int = 11;
			break;

		case 68:
		case 99:
			ret_int = 12;
			break;

		case 69:
		case 100:
			ret_int = 13;
			break;

		case 70:
		case 101:
			ret_int = 14;
			break;

		case 102:
			ret_int = 15;
			break;

		default:
			ret_int = char_2_dig ;
			break;
		}

	return ret_int;
}

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

From: [email-masked] (Tim Schaefer)
Subject: Getting the DATA TYPE from syscolumns: Code Sample #4:db_tab2.ec
Date: 6 Nov 1995 05:15:33 GMT

/*
------------------------------------------------------------------------------
-
  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.

------------------------------------------------------------------------------
-
*/

#include <stdio.h>
#include <string.h>
#include <ctype.h>
#include <decimal.h>
#include <setjmp.h>
#include <signal.h>
#include <errno.h>

EXEC SQL include sqltypes;

#define LCASE(c) (isalpha(c) ? (isupper(c) ? tolower(c) : c) : c)

jmp_buf sjbuf;

#define DB 1
#define CN 2
int db_or_cn;		/* to determine whether to close or disconnect */

EXEC SQL BEGIN DECLARE SECTION;
char dbenv[81];
char server[19];
char tab_name[19];
char col_name[19];
EXEC SQL END DECLARE SECTION;

char msgbuf[81];

main( argc, argv, env )
int argc;
char **argv;
char **env;
{
    int from = 0;
    char *p;
    int onintr();
    long err_chk(), dspcols();

    /*
     * sqlstart establishes an implicit default connection to the
     * server specified by INFORMIXSERVER; sqlstart() should only be
     * called when there are no other connections.
     */

    sqlstart();			
    db_or_cn = DB;

    signal(SIGINT, onintr);
    from = setjmp(sjbuf);

    strcpy ( dbenv,  argv[1] ) ;
    strcpy ( tab_name,  argv[2] ) ;
    strcpy ( col_name,  argv[3] ) ;

    p = strchr(dbenv,'@');

	if (p == NULL)
	    {

	    EXEC SQL database :dbenv;
	    db_or_cn = DB;
	    if (err_chk("OPEN") < 0)
		exit( -1 ) ;
	    }
	else
	    {
	    EXEC SQL connect to :dbenv;
	    db_or_cn = CN;
	    if (err_chk("Connect to") < 0)
		exit ( -1 );

	    if (dbenv[0] == '@')
		{
		printf("\n\n\tEnter the database name: ");
		while (getans(dbenv, sizeof(dbenv)) < 0)
		    {
		    printf("\n\tIllegal name\t");
		    exit ( -1 );
		    }
		EXEC SQL database :dbenv;
		db_or_cn = CN;	/* so it will be disconnected */
		if (err_chk("OPEN") < 0)
		    exit ( -1 );
		}
	    }

    dsptbls();
    EXEC SQL disconnect all;
    exit(1);
}

onintr()
{
    char ans;

    ans = ' ';
    printf("\n ***INTERRUPT *** \n");
    signal(SIGINT, onintr);
    sqlbreak();
    if (db_or_cn == DB)
	EXEC SQL close database;
    else
	EXEC SQL disconnect current;
    while(ans != 'y' && ans != 'n')
        {
	printf("\n\n\t*** Select another database? (y/n)");
	getans(&ans,1);
        }
    if(ans == 'y')
        longjmp(sjbuf,0);
    exit(1);
}

dsptbls()
{
    EXEC SQL BEGIN DECLARE SECTION;
    char tabnm[19];
    long tabid;
    EXEC SQL END DECLARE SECTION;

    EXEC SQL declare systabs cursor for
             select tabname, tabid from systables
             where tabname = :tab_name
             order by tabname;

    EXEC SQL open systabs;

    if(err_chk("OPEN") < 0)
	return(0);

    while(1)
        {
	EXEC SQL fetch systabs into :tabnm, :tabid;
	if((err_chk("fetch")) != 0)
	    break;
	/* printf("\n\n\nTable Name: %s   Tabid: %d\n", tabnm, tabid); */
	if(dspcols(tabid) < 0)
	    break;
	}
	printf("\n");
}

/*
    The dspcols() function accesses the syscols table to display the name,
    data type and length of each column in the table specified by tabid.
*/

long dspcols(tabid)
EXEC SQL BEGIN DECLARE SECTION;
int tabid;
EXEC SQL END DECLARE SECTION;
{
    EXEC SQL BEGIN DECLARE SECTION;
    char colname[19];
    short coltype, collength, colno;
    EXEC SQL END DECLARE SECTION;
    long ret;
    int  numchars;
    char null_ind[9];

    char *rtypname();

    strcpy( null_ind, "" );

    EXEC SQL declare syscols cursor for
              select colname, coltype, collength, colno
                from syscolumns
               where tabid = :tabid
                 and colname = :col_name
               order by colno
    ;

    EXEC SQL open syscols;

    if(err_chk("OPEN") < 0)
        exit(1);

    /* printf("\n\t%3s\t%-20s %-10s\t\t%-10s\n","No", "Column", "Type",
"Length" ); */

    while(1)
        {
	EXEC SQL fetch syscols into :colname, :coltype, :collength, :colno;
	if((ret = err_chk("fetch")) != 0)
          {
	    return(ret);
          }

        numchars = rtypwidth( coltype, collength  );
	if ( coltype >= 256 )
           {
		strcpy( null_ind, "not null" );
           }
        else
           {
		strcpy( null_ind, "" );
           }

/* 	printf("%3d %-20s %-10s  %d  %d", colno, colname, rtypname(coltype),
numchars, collength); */

printf("%-20s %-10s  %d %s\n", colname, rtypname(coltype), numchars,null_ind
);

        }
}

getans(ans, len)
char *ans;
int len;
{
    char buf[512], c;
    int n = 0;

    while((c = getchar()) != '\n')
        buf[n++] = LCASE(c);
    buf[n] = '\0';
    if(n > 1 && n >= len)
        return(-1);
    if(len <= 1)
        *ans = buf[0];
    else
	strncpy(ans, buf, len);
    return 1;
}

/*
   The err_chk() function checks the SQLSTATE status variable to see
   if an error has occurred following an SQL statement. If an error has
   occurred, err_chk executes the GET DIAGNOSTICS statement and prints
   the detail for each exception that is returned.
*/

long err_chk(s)
char *s;
{
EXEC SQL BEGIN DECLARE SECTION;
int exception_count;
char overflow[2];
int exception_num=1;
char class[255];
char subclass[255];
char message[255];
int messlen;
int rowcount;
char sqlstate[6];
int i;
EXEC SQL END DECLARE SECTION;

   if (!strncmp(SQLSTATE, "00", 2) || !strncmp(SQLSTATE,"02",2))
      {
      return(SQLCODE);
      }
   else
      {
         /*
         printf("---------------------------------");
         printf("-------------------------\n");
         printf("%s:\t SQLSTATE: %s\n",s,SQLSTATE);
         printf("SQLCODE: %d\n", SQLCODE);
         */

         EXEC SQL get diagnostics :exception_count = NUMBER,
                  :overflow = MORE;

         /*
         printf("NUMBER: %d\n", exception_count);
         printf("MORE : %s\n", overflow);
         */

         for (i = 1; i <= exception_count; i++)
             {

             EXEC SQL get diagnostics  exception :i :sqlstate =
                      RETURNED_SQLSTATE,
                      :class = CLASS_ORIGIN, :subclass = SUBCLASS_ORIGIN,
                      :message = MESSAGE_TEXT, :messlen = MESSAGE_LENGTH;

             /*
             printf("SQLSTATE: %s\n",sqlstate);
             printf("CLASS: %s\n",class);
             printf("SUBCLASS: %s\n",subclass);
             printf("TEXT: %s\n",message);
             printf("MESSLEN: %d\n",messlen);
             */
             }

         /*
          * Return the SQLCODE
         */

       return(SQLCODE);
       }
}

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

From: [email-masked] (Tim Schaefer)
Subject: Getting the DATA TYPE from syscolumns: Code Sample #5:db_tab3.ec
Date: 6 Nov 1995 05:16:14 GMT

/*
------------------------------------------------------------------------------
-
  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.
------------------------------------------------------------------------------
-
*/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <ctype.h>
#include <decimal.h>
#include <setjmp.h>
#include <signal.h>
#include <errno.h>

EXEC SQL include sqltypes;

#define LCASE(c) (isalpha(c) ? (isupper(c) ? tolower(c) : c) : c)

jmp_buf sjbuf;

#define DB 1
#define CN 2
int db_or_cn;		/* to determine whether to close or disconnect */

EXEC SQL BEGIN DECLARE SECTION;
char dbenv[81];
char server[19];
char tab_name[19];
char col_name[19];
EXEC SQL END DECLARE SECTION;

char msgbuf[81];

char  large_fld[12];
char  small_fld[12];
short large_size;
short small_size;
char  dt_size[36];
char  intv_size[36];
char  hex_string[5] = "" ;
short null_size;
int   min_length;
int   max_length;
char  fld_nm[12];
short fld_size;
char  ct_str[51];
char  not_null[10];
short msize;
short nsize;
short large_lngth;
short small_lngth;

main( argc, argv, env )
int argc;
char **argv;
char **env;
{
    int from = 0;
    char *p;
    int onintr();
    long err_chk(), dspcols();

    /*
     * sqlstart establishes an implicit default connection to the
     * server specified by INFORMIXSERVER; sqlstart() should only be
     * called when there are no other connections.
     */

    sqlstart();			
    db_or_cn = DB;

    signal(SIGINT, onintr);
    from = setjmp(sjbuf);

    strcpy ( dbenv,  argv[1] ) ;
    strcpy ( tab_name,  argv[2] ) ;
    strcpy ( col_name,  argv[3] ) ;

    p = strchr(dbenv,'@');

	if (p == NULL)
	    {

	    EXEC SQL database :dbenv;
	    db_or_cn = DB;
	    if (err_chk("OPEN") < 0)
		exit( -1 ) ;
	    }
	else
	    {
	    EXEC SQL connect to :dbenv;
	    db_or_cn = CN;
	    if (err_chk("Connect to") < 0)
		exit ( -1 );

	    if (dbenv[0] == '@')
		{
		printf("\n\n\tEnter the database name: ");
		while (getans(dbenv, sizeof(dbenv)) < 0)
		    {
		    printf("\n\tIllegal name\t");
		    exit ( -1 );
		    }
		EXEC SQL database :dbenv;
		db_or_cn = CN;	/* so it will be disconnected */
		if (err_chk("OPEN") < 0)
		    exit ( -1 );
		}
	    }

    dsptbls();
    EXEC SQL disconnect all;
    exit(1);
}

onintr()
{
    char ans;

    ans = ' ';
    printf("\n ***INTERRUPT *** \n");
    signal(SIGINT, onintr);
    sqlbreak();
    if (db_or_cn == DB)
	EXEC SQL close database;
    else
	EXEC SQL disconnect current;
    while(ans != 'y' && ans != 'n')
        {
	printf("\n\n\t*** Select another database? (y/n)");
	getans(&ans,1);
        }
    if(ans == 'y')
        longjmp(sjbuf,0);
    exit(1);
}

dsptbls()
{
    EXEC SQL BEGIN DECLARE SECTION;
    char tabnm[19];
    long tabid;
    EXEC SQL END DECLARE SECTION;

    EXEC SQL declare systabs cursor for
             select tabname, tabid from systables
             where tabname = :tab_name
             order by tabname;

    EXEC SQL open systabs;

    if(err_chk("OPEN") < 0)
	return(0);

    while(1)
        {
	EXEC SQL fetch systabs into :tabnm, :tabid;
	if((err_chk("fetch")) != 0)
	    break;
	/* printf("\n\n\nTable Name: %s   Tabid: %d\n", tabnm, tabid); */
	if(dspcols(tabid) < 0)
	    break;
	}
	printf("\n");
}

/*
    The dspcols() function accesses the syscols table to display the name,
    data type and length of each column in the table specified by tabid.
*/

long dspcols(tabid)
EXEC SQL BEGIN DECLARE SECTION;
int tabid;
EXEC SQL END DECLARE SECTION;
{
    EXEC SQL BEGIN DECLARE SECTION;
    char colname[19];
    short coltype, collength, colno;
    EXEC SQL END DECLARE SECTION;
    long ret;
    int  numchars;
    char null_ind[9];

    char *rtypname();

    strcpy( null_ind, "" );

    EXEC SQL declare syscols cursor for
              select colname, coltype, collength, colno
                from syscolumns
               where tabid = :tabid
                 and colname = :col_name
               order by colno
    ;

    EXEC SQL open syscols;

    if(err_chk("OPEN") < 0)
        exit(1);

    /* printf("\n\t%3s\t%-20s %-10s\t\t%-10s\n","No", "Column", "Type",
"Length" ); */

    while(1)
        {
	EXEC SQL fetch syscols into :colname, :coltype, :collength, :colno;
	if((ret = err_chk("fetch")) != 0)
          {
	    return(ret);
          }

/*
        numchars = rtypwidth( coltype, collength  );
	if ( coltype >= 256 )
           {
		strcpy( null_ind, "not null" );
           }
        else
           {
		strcpy( null_ind, "" );
           }
printf("%-20s %-10s  %d %s\n", colname, rtypname(coltype), numchars,null_ind
);
*/

        convert_type (coltype, collength);
	
        printf("%-20s %s", colname, ct_str );

        }
}

getans(ans, len)
char *ans;
int len;
{
    char buf[512], c;
    int n = 0;

    while((c = getchar()) != '\n')
        buf[n++] = LCASE(c);
    buf[n] = '\0';
    if(n > 1 && n >= len)
        return(-1);
    if(len <= 1)
        *ans = buf[0];
    else
	strncpy(ans, buf, len);
    return 1;
}

/*
   The err_chk() function checks the SQLSTATE status variable to see
   if an error has occurred following an SQL statement. If an error has
   occurred, err_chk executes the GET DIAGNOSTICS statement and prints
   the detail for each exception that is returned.
*/

long err_chk(s)
char *s;
{
EXEC SQL BEGIN DECLARE SECTION;
int exception_count;
char overflow[2];
int exception_num=1;
char class[255];
char subclass[255];
char message[255];
int messlen;
int rowcount;
char sqlstate[6];
int i;
EXEC SQL END DECLARE SECTION;

   if (!strncmp(SQLSTATE, "00", 2) || !strncmp(SQLSTATE,"02",2))
      {
      return(SQLCODE);
      }
   else
      {
         /*
         printf("---------------------------------");
         printf("-------------------------\n");
         printf("%s:\t SQLSTATE: %s\n",s,SQLSTATE);
         printf("SQLCODE: %d\n", SQLCODE);
         */

         EXEC SQL get diagnostics :exception_count = NUMBER,
                  :overflow = MORE;

         /*
         printf("NUMBER: %d\n", exception_count);
         printf("MORE : %s\n", overflow);
         */

         for (i = 1; i <= exception_count; i++)
             {

             EXEC SQL get diagnostics  exception :i :sqlstate =
                      RETURNED_SQLSTATE,
                      :class = CLASS_ORIGIN, :subclass = SUBCLASS_ORIGIN,
                      :message = MESSAGE_TEXT, :messlen = MESSAGE_LENGTH;

             /*
             printf("SQLSTATE: %s\n",sqlstate);
             printf("CLASS: %s\n",class);
             printf("SUBCLASS: %s\n",subclass);
             printf("TEXT: %s\n",message);
             printf("MESSLEN: %d\n",messlen);
             */
             }

         /*
          * Return the SQLCODE
         */

       return(SQLCODE);
       }
}

convert_type (coltype_num, col_length)
int  coltype_num;
int  col_length ;
{
int cnv_coltype = 0;
int cnv_collen  = 0;

  strcpy( not_null , "" ) ;

  if ( coltype_num >= 256 )
     {
      cnv_coltype = coltype_num - 256 ;
      coltype_num = cnv_coltype;
      strcpy( not_null , " NOT NULL " );
     }

  switch( coltype_num )
	{
       	case 0:
             	sprintf( ct_str, "CHAR (%d)", col_length );
		break;
       	case 1:
            	strcpy( ct_str , "SMALLINT" );
		break;
       	case 2:
            	strcpy( ct_str , "INTEGER" );
		break;
       	case 3:
            	strcpy( ct_str , "FLOAT" );
		break;
       	case 4:
            	strcpy( ct_str , "SMALLFLOAT" );
		break;
       	case 5:
            	msize = col_length / 256;
            	nsize = col_length % 256;
		if ( nsize == 255 || nsize <= 0 )
                   {
		   sprintf(ct_str , "DECIMAL (%d)", msize );
		   }
		else
		   {
		   sprintf(ct_str , "DECIMAL (%d,%d)", msize, nsize );
		   }
		break;
       	case 6:
            	strcpy( ct_str , "SERIAL" );
		break;
       	case 7:
            	strcpy( ct_str , "DATE" );
		break;
       	case 8:
            	msize = col_length / 256 ;
            	nsize = col_length % 256 ;
            	sprintf( ct_str , "MONEY (%d", msize );
            	if ( nsize != 2 )
               	{
               	strcat( ct_str, ", " );
               	strcat( ct_str, nsize );
               	}
            	strcat( ct_str , ")" );
		break;

       	case 9:
            	strcpy( ct_str , "UNDEFINED" );
		break;

       	case 10:
            	cnvrt_dt (col_length) ;
            	sprintf( ct_str , "DATETIME %s", dt_size ) ;
		break;

       	case 11:
            	strcpy ( ct_str , "BYTE" );
		break;

       	case 12:
            	strcpy ( ct_str , "TEXT" );
		break;

       	case 13:
		min_length = 0;
		max_length = 0;
  		if ( col_length >= 256 )
     		   {
      		   cnv_collen = col_length - 256 ;
      		   col_length = cnv_collen;
     		   }
            	cnvrt_varch (col_length) ;
		sprintf( ct_str , ""  );
            	if ( min_length > 0 )
                   {
            	   sprintf( ct_str , "VARCHAR(%d,%d)", max_length, min_length
);
                   }
                else
               	   {
            	   sprintf( ct_str , "VARCHAR(%d)", max_length );
               	   }
		break;

       	case 14:
            	cnvrt_intvl(col_length) ;
            	sprintf( ct_str , "INTERVAL %s", intv_size );
		break;

       default:
            	sprintf( ct_str , "UNDEFINED: %d", coltype_num );
    }

    strcat( ct_str , not_null );

}

cnvrt_dt (clngth)
long clngth;
{
  sprintf(hex_string, ""  );
  sprintf(hex_string, "%4x", clngth );

  qual_fld ('l', hex_string[2]) ;
   strcpy( large_fld, fld_nm );

  qual_fld ('s', hex_string[3]) ;
  strcpy( small_fld, fld_nm );

  strcpy( dt_size , large_fld );
  strcat( dt_size , " TO "    );
  strcat( dt_size , small_fld );
}

cnvrt_intvl (clngth)
long  clngth ;
{
  long  fld_lngth ;
  short  i;

  sprintf(hex_string, ""  );
  sprintf(hex_string, "%3x", clngth );

  qual_fld ('l', hex_string[1]) ;
   strcpy( large_fld, fld_nm );

  qual_fld ('s', hex_string[2]) ;
   strcpy( small_fld, fld_nm );

  fld_lngth = intvl_lngth(clngth);

  if ( fld_lngth > 0 )
     {
       strcpy ( large_fld , " (" );
       strcat ( large_fld , fld_lngth );
       strcat ( large_fld , ")" );
     }
   strcpy( intv_size , large_fld );
   strcat( intv_size , " TO "    );
   strcat( intv_size , small_fld );
}

cnvrt_varch (clngth)
long  clngth ;
{
  int str0;
  int str1;
  int str2;
  int str3;

  sprintf(hex_string, ""  );
  sprintf(hex_string, "%4x", clngth );

  str0 = dec_digit(hex_string[0]) ;
  str1 = dec_digit(hex_string[1]) ;
  str2 = dec_digit(hex_string[2]) ;
  str3 = dec_digit(hex_string[3]) ;

  if ( str0 > 0 )
     {
     min_length = str0 + str1 ;
     }
  else
     {
     min_length = 1 ;
     }

  max_length = clngth;
}

qual_fld (ftype, fvalue)
char ftype;
char fvalue;
{
    switch( fvalue )
	{
         case  '0':
              	strcpy ( fld_nm , "YEAR" );
              	fld_size = 4 ;
		break;

         case  '2':
              	strcpy ( fld_nm , "MONTH" );
              	fld_size = 2 ;
		break;

         case  '4':
              	strcpy ( fld_nm , "DAY" );
              	fld_size = 2 ;
		break;

         case  '6':
              	strcpy ( fld_nm , "HOUR" );
              	fld_size = 2 ;
		break;

         case  '8':
              	strcpy ( fld_nm , "MINUTE" );
              	fld_size = 2 ;
		break;

         case  'A':
         case  'a':
              	strcpy ( fld_nm , "SECOND" );
              	fld_size = 2 ;
		break;

         case  'B':
         case  'b':
              	strcpy ( fld_nm , "FRACTION (1)" );
              	fld_size = 1 ;
		break;

         case  'C':
         case  'c':
              	fld_size = 2 ;
              	if ( ftype == 'l' )
                 	{
                 	strcpy ( fld_nm , "FRACTION" );
                 	}
              	else
                 	{
                 	strcpy ( fld_nm , "FRACTION (2)" );
                 	}
		break;

         case  'D':
         case  'd':
              	strcpy ( fld_nm , "FRACTION"    );
              	fld_size = 3 ;
		break;

         case  'E':
         case  'e':
              	strcpy ( fld_nm , "FRACTION (4)" );
              	fld_size = 4 ;
		break;

         case  'F':
         case  'f':
              	strcpy ( fld_nm , "FRACTION (5)" );
              	fld_size = 5 ;
		break;

         default:
              	sprintf ( fld_nm , "uh oh:%s", fvalue );
              	fld_size = 0 ;
		break;
    }
}

intvl_lngth (clngth)
long  clngth ;
{
  long   dec_lngth      ;
  long   num_flds       ;
  long   default_lngth  ;
  long   ret_lngth      ;

  int dec_digit_1 ;
  int dec_digit_2 ;

  sprintf(hex_string, ""  );
  sprintf(hex_string, "%3x", clngth );

     dec_lngth   = atoi( hex_string[0] ) ;
     dec_digit_1 = atoi( hex_string[1] ) ;
     dec_digit_2 = atoi( hex_string[2] ) ;

     num_flds = dec_digit_2 - dec_digit_1  ;

    switch( num_flds )
	{
         case 0 :
              if ( dec_lngth == large_lngth )
                 {
                 ret_lngth = 0;
                 }
              else
                 {
                 ret_lngth = dec_lngth;
                 }
		break;

         case 1 :
              	ret_lngth = 0 ;
		break;

         case -1:
              	ret_lngth = 0 ;
		break;

         default:
              default_lngth =  (large_lngth + small_lngth) +  (num_flds - 2);
              if ( default_lngth == dec_lngth )
		 {
                 ret_lngth = 0 ;
		 }
              else
		 {
                 ret_lngth = large_lngth +  ( dec_lngth - default_lngth );
		 }
		break;
    	}

 return ( ret_lngth );

}

atolong( char s[] )
{

int i, n;

n = 0;

for ( i= 0; s[i] >= '0' && s[i] <= '9'; ++i )
    n = 10 * n + (s[i] - '0');
return n;

}

dec_digit( char_2_dig )
int char_2_dig ;
{
   int ret_int;

	switch( char_2_dig )
		{

		case 48:
			ret_int = 0;
			break;

		case 49:
			ret_int = 1;
			break;

		case 50:
			ret_int = 2;
			break;

		case 51:
			ret_int = 3;
			break;

		case 52:
			ret_int = 4;
			break;

		case 53:
			ret_int = 5;
			break;

		case 54:
			ret_int = 6;
			break;

		case 55:
			ret_int = 7;
			break;

		case 56:
			ret_int = 8;
			break;

		case 57:
			ret_int = 9;
			break;

		case 65:
		case 32:
			ret_int = 0;
			break;

		case 66:
		case 97:
			ret_int = 10;
			break;

		case 67:
		case 98:
			ret_int = 11;
			break;

		case 68:
		case 99:
			ret_int = 12;
			break;

		case 69:
		case 100:
			ret_int = 13;
			break;

		case 70:
		case 101:
			ret_int = 14;
			break;

		case 102:
			ret_int = 15;
			break;

		default:
			ret_int = char_2_dig ;
			break;
		}

	return ret_int;
}

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