Re: Pro*C dynamic sql
Date: Tue, 26 Oct 1999 18:07:38 -0400
Message-ID: <+CQWOL=1dRjD7YHEymlQ6ltq9u5=_at_4ax.com>
A copy of this was sent to legarema_at_uqtr.uquebec.ca (if that email address didn't require changing) On Tue, 26 Oct 1999 17:00:42 -0400, you wrote:
>Hi,
>
>I am the original poster of the previosu message. I am sorry,
>please do not respond to the email address of my
>original message. For some unknown reason, my preferences
>weren't set with the correct pop account. please anwser to
>this address instead : legarema_at_uqtr.uquebec.ca
>
>Thanks and sorry again,
>==============================================================
>
>-oracle 8
>-Pro*C 2.2
>
Oracle8 != proc 2.2. proc 2.2 was with 7.2 of the database. are you sure of your versions....
>Hi,
>
>Here is my problem :
>
>-the program should be able to list a table from a criteria
>given by the user (at runtime).
>
>The criteria could be :
>
>"name=smith"
>"street=broadway"
>...
>
it is called dynamic sql method 4. see the pro*c manual.
>so, i need an sql statement that looks like :
>
>select name, street, ... from mytable where criteria;
>
>how could I do that ?
>
Here is a smallish example. You would have to build the sqlstmt yourself from the user input. this would then print it with comma's between the fields:
#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;
static char * ARRAY_SIZE = "10";
#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);
}
/*
this array contains a default mapping I am using to constrain the
lengths of returned columns. It is mapping, for example, the Oracle
NUMBER type (type code = 2) to be 45 characters long in a string.
*/
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
if ( !strncmp( argv[i], "arraysize=", 10 ) ) ARRAY_SIZE = argv[i]+10;
else
{
printf( "usage: %s userid=xxx/xxx sqlstmt=query arraysize=<NN>\n",
argv[0] );
exit(1);
}
}
if ( USERID == NULL || SQLSTMT == NULL ) {
printf( "usage: %s userid=xxx/xxx sqlstmt=query arraysize=<NN>\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 j; int null_ok; int precision; int scale; int size = 10; int array_size = atoi(ARRAY_SIZE);
short ind_value;
char * char_ptr;
int last_fetch_count; int row_count = 0;
printf( "Array size = %d\n", array_size );
EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
/*
* Dynamically prepare the sqlstmt passed in. Must be a select
* statement for this application to work
*/
EXEC SQL PREPARE S FROM :sqlstmt;
/*
* declare a cursor for the prepared statement
*/
EXEC SQL DECLARE C CURSOR FOR S;
/*
* allocate a select descriptor, have it default to "size" columns
* wide
*/
if ((select_dp = sqlald (size, MAX_VNAME_LEN, MAX_INAME_LEN)) == NULL )
die( "Cannot allocate memory for select descriptor." );
select_dp->N = size;
/*
* try to describe into this structure
*/
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
/*
* no selected columns, return...
*/
if ( !select_dp->F ) return 0;
/*
* More then "size" selected columns, need to reallocate sqlda and try
* again
*/
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;
/*
* Ok, for each column allocate indicator variables. Each select_dp->I[i]
* will point to an ARRAY of shorts -- we will fetch array_size rows
* hence our array will be array_size shorts in length
*/
for (i = 0; i < select_dp->N; i++)
select_dp->I[i] = (short *) malloc(sizeof(short) * array_size );
/*
* now, lets set up each column. This program will coerce ALL datatypes
* into null terminated strings of some length. Since all we want to
* do is print the values, this is OK
*/
for (i = 0; i < select_dp->F; i++)
{
sqlnul (&(select_dp->T[i]), &(select_dp->T[i]), &null_ok);
/*
* if the type is in the bounds of my array above, we will
* attempt to get a length for that type from the array. So
* the oracle datatype 2 (number) will get the length of 45, 45
* bytes is big enough to hold the largest number Oracle can
* hold plus the sign, decimal place, etc
*
* if the length is not in the array, we add 5 for good luck
* -- add 5 to allow for null termination
*/
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;
/*
* set the datatype for the fetched column to be 5 (null terminated
* string)
*
* set the pointer to where the data goes to be be a pointer to
* some space that is that LENGTH * number of rows to fetch in size
*/
select_dp->T[i] = 5;
select_dp->V[i] = (char *)malloc( select_dp->L[i] * array_size );
/*
* print out the column names.....
*/
for( j = MAX_VNAME_LEN-1; j > 0 && select_dp->S[i][j] == ' '; j--);
printf ("%s%.*s", i?",":"", j+1, select_dp->S[i]);
}
printf( "\n" );
/*
* now we will open the cursor and start fetching rows...
*/
EXEC SQL OPEN C;
for ( last_fetch_count = 0; ; last_fetch_count = sqlca.sqlerrd[2] ) {
/*
* for how many rows we have allocated space for, lets try
* to fetch that many....
*/
EXEC SQL FOR :array_size FETCH C USING DESCRIPTOR select_dp;
printf( "Apparently fetched %d rows this fetch...\n",
sqlca.sqlerrd[2]-last_fetch_count );
/*
* sqlca.sqlerrd[2], after the fetch, has the cumulative number
* of rows the cursor has fetched so far. So we can use that
* to figure out how many rows the last fetch actually fetched...
*/
for( j = 0; j < sqlca.sqlerrd[2]-last_fetch_count; j++ )
{
printf( "%d ) ", last_fetch_count+j+1 );
for (i = 0; i < select_dp->F; i++)
{
/*
* Now we do some 'tricky' pointer accesses. We have just
* fetched some number of rows. We want to print the i'th
* column of the j'th row we fetched.
*
* the indicator variable is in the array pointed to by
* select_dp->I[i] (the i'th column). It is in cell j of
* that row. Hence the indicator variable is:
*/
ind_value = *(select_dp->I[i]+j);
/*
* the data pointer of the I'th column of the J'th row
* is indexed in a similar fashion but we must add
* the 'width' of each column in addition to the row (j)
* we want.... so the data pointer is:
*/
char_ptr = select_dp->V[i] + (j*select_dp->L[i]);
/*
* now print it...
*/
printf( "%s%s", i?",":"", ind_value?"(null)":char_ptr );
}
row_count++;
printf( "\n" );
}
/*
* when the sqlcode is positive, that indicates NO MORE DATA, break
*/
if ( sqlca.sqlcode > 0 ) break;
}
printf( "%d rows\n", row_count );
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);
}
>Thanks a lot and have a nice day,
-- See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st [Quoted] Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA Opinions are mine and do not necessarily reflect those of Oracle CorporationReceived on Wed Oct 27 1999 - 00:07:38 CEST