Re: Describe table from Pro*C Applications
Date: Thu, 29 Oct 1998 19:44:09 GMT
Message-ID: <3648c4f1.109569833_at_192.86.155.100>
A copy of this was sent to matthew_at_mattshouse.com (if that email address didn't require changing) On Thu, 29 Oct 1998 18:41:43 GMT, you wrote:
>Is there a way to do a 'describe <tablename>' from pro*c and populate a
>structure with the results? If not, is there a way to retrieve the same
>information using another method from pro*c?
>
>Matthew
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
One method is to query user_tab_columns or all_tab_columns. Another, perhaps more 'interesting' way is to use dynamic sql. It should result in less round trips to the server to get a table describe done.... It could look like the following. check out 'dynamic sql method 4' in the proc docs for more info on how this works....
#include <stdio.h> #include <string.h> #include <ctype.h> #define MAX_VNAME_LEN 30 #define MAX_INAME_LEN 30
EXEC SQL INCLUDE sqlca;
EXEC SQL INCLUDE sqlda;
extern SQLDA *sqlald();
extern void sqlclu();
static void sqlerror_hard()
{
EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("\nORACLE error detected:");
printf("\n% .70s \n", sqlca.sqlerrm.sqlerrmc);
EXEC SQL ROLLBACK WORK RELEASE;
exit(1);
}
static int process(char * tname)
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR sqlstmt[255];
EXEC SQL END DECLARE SECTION;
SQLDA * select_dp;
int i; int null_ok; int precision; int scale; int size = 10;
sprintf( sqlstmt.arr, "select * from %s", tname ); sqlstmt.len = strlen( sqlstmt.arr );
EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
EXEC SQL PREPARE S FROM :sqlstmt;
if ((select_dp = sqlald (size, MAX_VNAME_LEN, MAX_INAME_LEN)) == NULL )
exit(1);
select_dp->N = size;
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp; if ( !select_dp->F ) return 0;
if (select_dp->F < 0)
{
size = -select_dp->F;
sqlclu( select_dp );
if ((select_dp = sqlald (size, MAX_VNAME_LEN, MAX_INAME_LEN)) == NULL )
exit(1);
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
}
select_dp->N = select_dp->F;
for (i = 0; i < select_dp->F; i++)
{
printf ("%.*s", MAX_VNAME_LEN, select_dp->S[i]);
sqlnul (&(select_dp->T[i]), &(select_dp->T[i]), &null_ok);
switch( select_dp->T[i] )
{
case 1: printf( "%-24s(%4d)", "varchar2", select_dp->L[i] );
break;
case 2: sqlprc((unsigned long *)&(select_dp->L[i]),
&precision, &scale);
printf( "%-23s(%2d,%2d)", "number", precision, scale );
break;
case 8: printf( "%-30s", "long" );
break;
case 9: printf( "%-24s(%4d)", "varchar2", select_dp->L[i] );
break;
case 12: printf( "%-30s", "date" );
break;
case 23: printf( "%-24s(%4d)", "raw(%4d)", select_dp->L[i] );
break;
case 24: printf( "%-30s", "long raw" );
break;
case 69: printf( "%-30s", "rowid" );
break;
case 96: printf( "%-24s(%4d)", "char", select_dp->L[i] );
break;
case 105:
case 106: printf( "%-30s", "mlslabel" );
break;
case 111: printf( "%-30s", "object" );
break;
case 112: printf( "%-30s", "clob" );
break;
case 113: printf( "%-30s", "blob" );
break;
case 121:
case 122:
case 123: printf( "%-30s", "object" );
break;
default: printf( "%-30s", "not sure" );
break;
}
printf( " %s\n", null_ok?"Nullable":"" );
}
}
main( argc, argv )
int argc;
char * argv[];
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR oracleid[50] = { strlen("tkyte/tkyte"), "tkyte/tkyte" };
EXEC SQL END DECLARE SECTION;
EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
EXEC SQL CONNECT :oracleid;
printf("\nConnected to ORACLE as user: %s\n\n", oracleid.arr);
process( "EMP" );
printf( "-----------------------\n" );
process( "DEPT" );
/* Disconnect from ORACLE. */
EXEC SQL COMMIT WORK RELEASE;
exit(0);
}
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
-- http://govt.us.oracle.com/ -- downloadable utilities ---------------------------------------------------------------------------- Opinions are mine and do not necessarily reflect those of Oracle Corporation Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it.Received on Thu Oct 29 1998 - 20:44:09 CET