| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Dynamic SQL .. in PRO-C !
Here is a method 4 dynamic sql program that has usfulness as a utility as well. You give it any query and it will dump it as a CSV file to stdout. For example:
$ dumptbl userid=scott/tiger 'stmt=select * from emp'
Hope it helps.
On Tue, 22 Jul 1997 09:24:15 +0100, David Mallarme <mallarme_at_univ-valenciennes.fr> wrote:
>Hello,
>
>I thank all that have already spent some time to answer me.
>But I have to precise that I'd like to have some sample of dynamic sql
>programming in PRO-C !
>You know the method4.. The manual is full of bug and even now I still
>have problem at the running time..
>
>Thank again in advance.
>
>Bye.
>
>David
#include <stdio.h> #include <string.h> #include <ctype.h> #include <assert.h> static char * USERID = NULL;
#define MAX_VNAME_LEN 30 #define MAX_INAME_LEN 30
#define SQLCA_INIT
EXEC SQL INCLUDE sqlca;
EXEC SQL INCLUDE sqlda;
extern SQLDA *sqlald();
extern void sqlclu();
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], "stmt=", 5 ) ) STMT = argv[i]+5;
else
{
printf( "usage: %s userid=xxx/xxx 'stmt=select * from emp'\n",
argv[0] );
exit(1);
}
printf( "usage: %s userid=xxx/xxx 'stmt=select * from emp'\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);
printf( "query: '%s'\n", STMT?STMT:"(null)");
EXEC SQL ROLLBACK WORK RELEASE;
exit(1);
}
static void process( char * sql_statement ) {
EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
/* take arbritrary SELECT statment and parse it */
EXEC SQL PREPARE S FROM :sql_statement;
/* declare the cursor used to process the parsed statement */
EXEC SQL DECLARE C CURSOR FOR S;
/* open it */
EXEC SQL OPEN C;
/* dump it */
process_select_list(sql_statement);
/* close it */
EXEC SQL CLOSE C;
}
/* This array is used to map the internal oracle
datatype lengths to a string length. For example
DATATYPE = 12 is a DATE. lengths[12] = 25 below.
I will therefore fetch dates into a character string
of at most 25 bytes in length.
*/
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 };
int process_select_list(char * sql_statement)
{
SQLDA * select_dp;
int i; int null_ok; int precision; int scale; int size = 10;
EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
/* allocate a descriptor that can hold 10 columns */ if ((select_dp = sqlald (size, MAX_VNAME_LEN, MAX_INAME_LEN)) == NULL ) {
fprintf( stderr, "Cannot allocate memory for select descriptor." );
exit(1);
select_dp->N = size;
/* try to describe the select list for the opened query
into the sqlda structure */
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
if ( !select_dp->F ) return 0;
/* if it is less then ZERO then more then 10 columns were selected,
re-allocate the sqlda and retry */
if (select_dp->F < 0)
{
size = -select_dp->F;
sqlclu( select_dp );
if ((select_dp = sqlald (size, MAX_VNAME_LEN, MAX_INAME_LEN)) == NULL )
{
fprintf( stderr, "Cannot allocate memory for select descriptor." );
exit(1);
}
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
}
/* allocate indicator variables for each column in the select
list */
for (i = 0; i < select_dp->N; i++)
select_dp->I[i] = (short *) malloc(sizeof(short));
/* for each column, coerce the datatype to be type 5 (null
terminated string. also, use the array above to define
default lengths for various datatypes (eg: 25 for dates,
45 for numbers, 2000 for longs and so on). for types i
don't have a length for in the array, simply add 5 bytes
to the internal length (comfort zone for null terminator
that will be added */
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] );
}
/* Loop over every row and print it with comma's */
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" );
/* deallocate storage and return... */
for( i = 0; i < select_dp->F; i++ )
{
free( select_dp->I[i] );
free( select_dp->V[i] );
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. */
strcpy( oracleid.arr, USERID );
oracleid.len = strlen( USERID );
EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
EXEC SQL CONNECT :oracleid;
fprintf( stderr, "\nConnected to ORACLE as user: %s\n\n", oracleid.arr);
process( STMT );
/* Disconnect from ORACLE. */
EXEC SQL COMMIT WORK RELEASE;
exit(0);
}
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |