Getting the DATA TYPE from syscolumns: Code Sample #5:db_tab3.ec
/*
------------------------------------------------------------------------------
-
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] = '\\