Re: ProC,SQLDA Question
Date: Fri, 05 Jun 1998 15:54:13 GMT
Message-ID: <357b143d.9029413_at_192.86.155.100>
A copy of this was sent to Robert Ogren <rmogren_at_gte.net> (if that email address didn't require changing) On Thu, 04 Jun 1998 15:31:23 -0700, you wrote:
>Hello,
>
>Is there a way to get the column names from Oracle via the SQLDA struct.
>
>Something similar to the sqlda->sqlvar_struct->sqlname in Informix.
>I ordered the ProC manual but it is not here yet. I need the col names
>so I
>can compare to the list of of names and data that will be inserted into
>the DB.
>
>Any help is appreaciated.
>
>Thanks
>
>Rob
>
Yes you can. Here is an example. It dumps any query as a comma separated stream of data on stdout.
usage: flat userid=uname/pw sqlstmt=select * from emp
For example:
$ ./flat userid=scott/tiger 'sqlstmt=select * from emp'
Connected to ORACLE as user: scott/tiger
EMPNO ,ENAME ,JOB ,MGR ,HIREDATE ,SAL ,COMM ,DEPTNO 7369,SMITH,CLERK,7903,17-DEC-80,800,(null),20 7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,800,30 7521,WARD,SALESMAN,7698,22-FEB-81,3250,600,307566,JONES,MANAGER,7839,02-APR-81,2975,100,20 7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30 ....
#include <stdio.h> #include <string.h> #include <ctype.h> #define MAX_VNAME_LEN 30 #define MAX_INAME_LEN 30 static char * USERID = NULL;
static char * SQLSTMT = NULL;
#define vstrcpy( a, b ) \
(strcpy( a.arr, b ), a.len = strlen( a.arr ), a.arr)
EXEC SQL INCLUDE sqlca;
EXEC SQL INCLUDE sqlda;
extern SQLDA *sqlald();
extern void sqlclu();
static void die( char * msg )
{
fprintf( stderr, "%s\n", msg ); exit(1);
}
static int lengths[] =
{ -1, 0, 45, 0, 0, 0, 0, 0, 2000, 0, 0,
18, 25, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 512, 2000 };
static void process_parms( argc, argv )
int argc;
char * argv[];
{
int i;
for( i = 1; i < argc; i++ )
{
if ( !strncmp( argv[i], "userid=", 7 ) ) USERID = argv[i]+7;
else
if ( !strncmp( argv[i], "sqlstmt=", 8 ) ) SQLSTMT = argv[i]+8;
else
{
printf( "usage: %s userid=xxx/xxx sqlstmt=the-query\n", argv[0] );
exit(1);
}
}
if ( USERID == NULL || SQLSTMT == NULL )
{
printf( "usage: %s userid=xxx/xxx sqlstmt=the-query\n", argv[0] );
exit(1);
}
}
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 * sqlstmt)
{
SQLDA * select_dp;
int i;
int null_ok;
int precision;
int scale;
int size = 10;
EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
EXEC SQL PREPARE S FROM :sqlstmt;
EXEC SQL DECLARE C CURSOR FOR S;
if ((select_dp = sqlald (size, MAX_VNAME_LEN, MAX_INAME_LEN)) == NULL )
die( "Cannot allocate memory for select descriptor." );
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 )
die( "Cannot allocate memory for select descriptor." );
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
}
select_dp->N = select_dp->F;
for (i = 0; i < select_dp->N; i++)
select_dp->I[i] = (short *) malloc(sizeof(short));
for (i = 0; i < select_dp->F; i++)
{
sqlnul (&(select_dp->T[i]), &(select_dp->T[i]), &null_ok);
if ( select_dp->T[i] < sizeof(lengths)/sizeof(lengths[0]) )
{
if ( lengths[select_dp->T[i]] )
select_dp->L[i] = lengths[select_dp->T[i]];
else select_dp->L[i] += 5;
}
else select_dp->L[i] += 5;
select_dp->T[i] = 5;
select_dp->V[i] = (char *)malloc( select_dp->L[i] );
printf ("%s%.*s", i?",":"", MAX_VNAME_LEN, select_dp->S[i]);
}
EXEC SQL OPEN C;
for (;;)
{
EXEC SQL WHENEVER NOT FOUND DO break;
EXEC SQL FETCH C USING DESCRIPTOR select_dp;
for (i = 0; i < select_dp->F; i++)
printf( "%s%s", i?",":"",
*(select_dp->I[i])?"(null)":select_dp->V[i] );
printf( "\n" );
}
sqlclu(select_dp);
EXEC SQL CLOSE C;
EXEC SQL COMMIT WORK; return 1;
}
main( argc, argv )
int argc; char * argv[];
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR oracleid[50];
EXEC SQL END DECLARE SECTION; process_parms( argc, argv );
/* Connect to ORACLE. */
vstrcpy( oracleid, USERID );
EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
EXEC SQL CONNECT :oracleid;
printf("\nConnected to ORACLE as user: %s\n\n", oracleid.arr);
process( SQLSTMT );
/* 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 Fri Jun 05 1998 - 17:54:13 CEST