| 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: PRO*C && Dynamic SQL for newbie
A copy of this was sent to adairm_at_my-dejanews.com
(if that email address didn't require changing)
On Wed, 12 May 1999 23:57:47 GMT, you wrote:
> Hello all,
>
> I am starting a project that requires me to use Dynamic SQL instead of
>Static. I am familiar w/ static syntax, but I have been unable to find
>a decent reference for Dynamic SQL instructions. I would like it if
>someone would be kind enough to post a simple Dynamic SQL program.
>(Don't include main() or anything, just a function). All I really need
>to see is how to build/execute/and obtain the results. Thanks for the
>help in advance.
>
>Matt
>
>
>--== Sent via Deja.com http://www.deja.com/ ==--
>---Share what you know. Learn what you don't.---
Well, i'm going to include main anyway. the following is a method 4 dynamic sql program that does an 'unload' of a query to a comma output of sorts. If you compile this program and execute:
$ ./array_flat userid=scott/tiger 'sqlstmt=select * from emp' arraysize=50
Connected to ORACLE as user: scott/tiger
Array size = 50
EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
attempting to fetch 50 rows
Apparently fetched 14 rows this fetch...
1 ) 7369,SMITH,CLERK,7902,17-DEC-80,800,(null),20 2 ) 7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,900,30 3 ) 7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30 4 ) 7566,JONES,ANALYST,7839,02-APR-81,2975,(null),20 5 ) 7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30 6 ) 7698,BLAKE,MANAGER,7839,01-MAY-81,2850,(null),30 7 ) 7782,CLARK,MANAGER,7839,09-JUN-81,2450,(null),10 8 ) 7788,SCOTT,ANALYST,7566,09-DEC-82,3000,(null),20 9 ) 7839,KING,PRESIDENT,(null),17-NOV-81,5000,(null),10 10 ) 7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30 11 ) 7876,ADAMS,CLERK,7788,12-JAN-83,1100,(null),20 12 ) 7900,JAMES,CLERK,7698,03-DEC-81,950,(null),3013 ) 7902,FORD,ANALYST,7566,03-DEC-81,3000,(null),20 14 ) 7934,MILLER,CLERK,7782,23-JAN-82,1300,(null),10 14 rows
thats what you'll get. It is as follows:
#include <stdio.h> #include <string.h> #include <ctype.h> #define MAX_VNAME_LEN 30 #define MAX_INAME_LEN 30 static char * USERID = 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);
/*
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 SQLDA * process_1(char * sqlstmt, int array_size ) {
SQLDA * select_dp;
int i;
int j;
int null_ok;
int precision;
int scale;
int size = 10;
printf( "Array size = %d\n", array_size );
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 NULL;
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;
}
for (i = 0; i < select_dp->N; i++)
select_dp->I[i] = (short *) malloc(sizeof(short) * array_size );
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] * array_size );
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" );
EXEC SQL OPEN C;
return select_dp;
static void process_2( SQLDA * select_dp, int array_size ) {
int last_fetch_count; int row_count = 0; short ind_value; char * char_ptr; int i, j;
for ( last_fetch_count = 0; ; last_fetch_count = sqlca.sqlerrd[2] ) {
printf( "attempting to fetch %d rows\n", array_size );
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 );
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++)
{
ind_value = *(select_dp->I[i]+j);
char_ptr = select_dp->V[i] + (j*select_dp->L[i]);
printf( "%s%s", i?",":"", ind_value?"(null)":char_ptr );
}
row_count++;
printf( "\n" );
}
if ( sqlca.sqlcode > 0 ) break;
}
printf( "%d rows\n", row_count );
sqlclu(select_dp);
EXEC SQL CLOSE C;
EXEC SQL COMMIT WORK;
main( argc, argv )
int argc; char * argv[];
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);
select_dp = process_1( SQLSTMT, atoi(ARRAY_SIZE) );
process_2( select_dp , atoi(ARRAY_SIZE));
/* Disconnect from ORACLE. */
EXEC SQL COMMIT WORK RELEASE;
exit(0);
See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Wed May 12 1999 - 21:01:12 CDT
 |
 |