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: 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 #include #include #include 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: tschaefe@gate.net (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: tschaefe@gate.net (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 #include #include #include #include #include #include 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: tschaefe@gate.net (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 #include #include #include #include #include #include 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: tschaefe@gate.net (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 #include #include #include #include #include #include #include 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; } ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++