PRO*C Wizards and Users Help Wanted
Date: 15 Oct 1994 07:09:55 GMT
Message-ID: <37nv83$787_at_carbon.denver.colorado.edu>
/*
This is a question for all you PRO*C Wizards !!!
The first section runs( no oracle errors or wedges), but
the second section generates a oracle error at run time.
The code in both examples uses two cursors.
SUN, PRO*C 1.5
Both sections of code perform the same function, taken from
a oracle sample.
The second sample is modular :)
Give it your best shot, I need help on this simple task :(
Maybe I have made a dumb mistake in the code, I have not
one looking myself.
ghharrac_at_ouray.denver.colorado.edu
Oct 14, 1994
*/
/**************************************************************************/
/**************************************************************************/
/* CUT HERE */
/**************************************************************************/
/**************************************************************************/
#include <stdio.h>
#include <ctype.h>
#include <string.h>
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR username[21];
/* let us take a very simple query */
char *query = "Select * from REPORT ";
char *query2 = "SELECT * FROM CLIENT_REQUEST";
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE sqlca.h;
EXEC SQL INCLUDE sqlda.h;
SQLDA *bind_dp;
SQLDA *select_dp;
SQLDA *bind_dp2;
SQLDA *select_dp2;
extern SQLDA *sqlald(); extern SQLDA *sqlclu();
void query_one ( void );
void query_two ( void );
/**************************************************************************/
main ()
{
int i = 0;
short l;
/* Set up Oracle error handling */
EXEC SQL WHENEVER SQLERROR GOTO sqlerror;
/* Logon to Oracle */
strcpy (username.arr, "RDF/RDF");
username.len = strlen(username.arr);
EXEC SQL CONNECT :username;
/* allocate the bind and select descriptor */
bind_dp = sqlald (40, 30, 30);
select_dp = sqlald (40, 30, 30);
bind_dp2 = sqlald (40, 30, 30);
select_dp2 = sqlald (40, 30, 30);
/* Prepare the statement and Declare a cursor */
EXEC SQL PREPARE S FROM :query; EXEC SQL PREPARE S2 FROM :query2;
EXEC SQL DECLARE C CURSOR FOR S;
EXEC SQL DECLARE C2 CURSOR FOR S2;
EXEC SQL DESCRIBE BIND VARIABLES FOR S INTO bind_dp;
EXEC SQL DESCRIBE BIND VARIABLES FOR S2 INTO bind_dp2;
/* Set the max number of array elements to the number found */
bind_dp->N = bind_dp->F;
bind_dp2->N = bind_dp2->F;
EXEC SQL OPEN C USING DESCRIPTOR bind_dp;
EXEC SQL OPEN C2 USING DESCRIPTOR bind_dp2;
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp; EXEC SQL DESCRIBE SELECT LIST FOR S2 INTO select_dp2;
/* Set the max number of elements in descriptor to that found */
select_dp->N = select_dp->F;
select_dp2->N = select_dp2->F;
for ( i = 0 ; i < select_dp->N; i++ )
select_dp->V[i] = (char *)malloc(select_dp->L[i]);
for ( i = 0 ; i < select_dp2->N; i++ )
select_dp2->V[i] = (char *)malloc(select_dp2->L[i]);
query_one ();
query_two ();
end_main:
printf ("\n\n# of rows processed = %d\n", sqlca.sqlerrd[2]);
for (i = 0; i < select_dp-> F; i++)
{
free (select_dp->V[i]);
free (select_dp->I[i]);
}
for (i = 0; i < select_dp2-> F; i++)
{
free (select_dp2->V[i]);
free (select_dp2->I[i]);
}
/* Free up space used by the descriptors themselves */
sqlclu (bind_dp);
sqlclu (select_dp);
sqlclu (bind_dp2);
sqlclu (select_dp2);
EXEC SQL CLOSE C;
EXEC SQL CLOSE C2;
EXEC SQL COMMIT WORK RELEASE;
return;
/* Oracle Error Handler */
sqlerror:
printf ("\n%.70s",sqlca.sqlerrm.sqlerrmc);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK RELEASE;
return;
} /* end of program */
/*****************************************************************************/
void query_one ( void )
{
int i;
#ifdef HEADERS
for ( i = 0 ; i < select_dp->N; i++ )
printf("%-*.*s ",(int)select_dp->L[i],(int)select_dp->L[i],select_dp->S[i]);
printf ("\n\n");
#endif
EXEC SQL WHENEVER NOT FOUND GOTO end_main;
for ( ; ; )
{
EXEC SQL FETCH C USING DESCRIPTOR select_dp;
for ( i = 0 ; i < select_dp->N; i++ )
{
if ( select_dp->L[i] > 20 )
{
select_dp->V[i][20]= (char)NULL;
}
else
{
select_dp->V[i][select_dp->L[i]] = (char)NULL;
}
printf("%s ",select_dp->V[i]);
}
printf ("\n");
}
end_main:
printf ("\n\n# of rows processed = %d\n", sqlca.sqlerrd[2]);
}
/*****************************************************************************/
void query_two ( void )
{
int i;
#ifdef HEADERS
for ( i = 0 ; i < select_dp2->N; i++ )
printf("%-*.*s ",(int)select_dp2->L[i],(int)select_dp2->L[i],
select_dp2->S[i]);
printf ("\n\n");
#endif
EXEC SQL WHENEVER NOT FOUND GOTO end_main;
for ( ; ; )
{
EXEC SQL FETCH C2 USING DESCRIPTOR select_dp2;
for ( i = 0 ; i < select_dp2->N; i++ )
{
if ( select_dp2->L[i] > 20 )
{
select_dp2->V[i][20]= (char)NULL;
}
else
{
select_dp2->V[i][select_dp2->L[i]] = (char)NULL;
}
printf("%s ",select_dp2->V[i]);
}
printf ("\n");
}
end_main:
printf ("\n\n# of rows processed = %d\n", sqlca.sqlerrd[2]);
}
/**************************************************************************/ /**************************************************************************/ /* CUT HERE */ /**************************************************************************/ /**************************************************************************/#include <string.h>
#include <stdio.h>
#include <ctype.h>
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR username[21];
/* let us take a very simple query */
char *query = "SELECT * from REPORT ";
char *query2 = "SELECT * FROM CLIENT_REQUEST";
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE sqlca.h;
EXEC SQL INCLUDE sqlda.h;
SQLDA *bind_dp;
SQLDA *select_dp;
SQLDA *bind_dp2;
SQLDA *select_dp2;
extern SQLDA *sqlald();
extern SQLDA *sqlclu();
void query_one ( void );
void query_two ( void );
void prepare_select_one ( void );
void prepare_select_two ( void );
void free_cursor_one ( void );
void free_cursor_two ( void );
void build_cursor_one ( void );
void build_cursor_two ( void );
void connect ( void );
void error ( char * mess );
/**************************************************************************/
main ()
{
int i = 0;
short l;
connect ();
build_cursor_one ();
build_cursor_two ();
prepare_select_one();
prepare_select_two();
query_one ();
query_two ();
free_cursor_one ();
free_cursor_two ();
EXEC SQL COMMIT WORK RELEASE; }
/********************************************************************/
void error( char *mess )
{
printf ("Module: %s\n",mess);
printf ("%.70s\n",sqlca.sqlerrm.sqlerrmc);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK RELEASE;
exit ( 0 );
}
/********************************************************************/
void connect ( void )
{
/* Logon to Oracle */
EXEC SQL WHENEVER SQLERROR GOTO sqlerror;
strcpy (username.arr, "RDF/RDF");
username.len = strlen(username.arr);
EXEC SQL CONNECT :username;
return;
sqlerror:
error ( "connect" );
}
/**************************************************************************/
void build_cursor_one ( void )
{
EXEC SQL WHENEVER SQLERROR GOTO sqlerror;
bind_dp = sqlald (40, 30, 30);
select_dp = sqlald (40, 30, 30);
EXEC SQL PREPARE S FROM :query;
EXEC SQL DECLARE C CURSOR FOR S; EXEC SQL DESCRIBE BIND VARIABLES FOR S INTO bind_dp;
bind_dp->N = bind_dp->F;
EXEC SQL OPEN C USING DESCRIPTOR bind_dp;
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
return;
sqlerror:
error ("build_cursor_one");
}
/**************************************************************************/
void build_cursor_two ( void )
{
EXEC SQL WHENEVER SQLERROR GOTO sqlerror;
bind_dp2 = sqlald (40, 30, 30);
select_dp2 = sqlald (40, 30, 30);
EXEC SQL PREPARE S2 FROM :query2;
EXEC SQL DECLARE C2 CURSOR FOR S2;
EXEC SQL DESCRIBE BIND VARIABLES FOR S2 INTO bind_dp2;
/* Set the max number of array elements to the number found */
bind_dp2->N = bind_dp2->F;
EXEC SQL OPEN C2 USING DESCRIPTOR bind_dp2;
EXEC SQL DESCRIBE SELECT LIST FOR S2 INTO select_dp2;
return;
sqlerror:
error ("build_cursor_two ");
}
/********************************************************************/
void prepare_select_one ( void )
{
int i;
EXEC SQL WHENEVER SQLERROR GOTO sqlerror;
select_dp->N = select_dp->F;
for ( i = 0 ; i < select_dp->N; i++ )
{
select_dp->V[i] = (char *)malloc(select_dp->L[i]);
}
return;
sqlerror:
error ("prepare_select_one");
}
/*********************************************************************/
void prepare_select_two ( void )
{
int i;
EXEC SQL WHENEVER SQLERROR GOTO sqlerror;
select_dp2->N = select_dp2->F;
for ( i = 0 ; i < select_dp2->N; i++ )
{
select_dp2->V[i] = (char *)malloc(select_dp2->L[i]);
}
return;
sqlerror:
error ("prepare_select_two");
}
/*********************************************************************/
void free_cursor_one ( void )
{
int i;
EXEC SQL WHENEVER SQLERROR GOTO sqlerror;
for (i = 0; i < select_dp-> F; i++)
{
free (select_dp->V[i]);
free (select_dp->I[i]);
}
sqlclu (bind_dp);
sqlclu (select_dp);
EXEC SQL CLOSE C;
return;
sqlerror:
error ("free_cursor_one");
}
/*********************************************************************/
void free_cursor_two ( void )
{
int i;
EXEC SQL WHENEVER SQLERROR GOTO sqlerror;
for (i = 0; i < select_dp2-> F; i++)
{
free (select_dp2->V[i]);
free (select_dp2->I[i]);
}
sqlclu (bind_dp2);
sqlclu (select_dp2);
EXEC SQL CLOSE C2;
return;
sqlerror:
error ("free_cursor_two");
}
/*********************************************************************/
void query_one ( void )
{
int i;
#ifdef HEADERS
for ( i = 0 ; i < select_dp->N; i++ )
printf("%-*.*s ",(int)select_dp->L[i],(int)select_dp->L[i],select_dp->S[i]);
printf ("\n\n");
#endif
EXEC SQL WHENEVER SQLERROR GOTO sqlerror;
EXEC SQL WHENEVER NOT FOUND GOTO end_main;
for ( ; ; )
{
EXEC SQL FETCH C USING DESCRIPTOR select_dp;
for ( i = 0 ; i < select_dp->N; i++ )
{
if ( select_dp->L[i] > 20 )
{
select_dp->V[i][20]= (char)NULL;
}
else
{
select_dp->V[i][select_dp->L[i]] = (char)NULL;
}
printf("%s ",select_dp->V[i]);
}
printf ("\n");
}
end_main:
printf ("\n\n# of rows processed = %d\n", sqlca.sqlerrd[2]);
return;
sqlerror:
error ("query_one");
}
/*****************************************************************************/
void query_two ( void )
{
int i;
#ifdef HEADERS
for ( i = 0 ; i < select_dp2->N; i++ )
printf("%-*.*s ",(int)select_dp2->L[i],(int)select_dp2->L[i],
select_dp2->S[i]);
printf ("\n\n");
#endif
EXEC SQL WHENEVER SQLERROR GOTO sqlerror;
EXEC SQL WHENEVER NOT FOUND GOTO end_main;
for ( ; ; )
{
EXEC SQL FETCH C2 USING DESCRIPTOR select_dp2;
for ( i = 0 ; i < select_dp2->N; i++ )
{
if ( select_dp2->L[i] > 20 )
{
select_dp2->V[i][20]= (char)NULL;
}
else
{
select_dp2->V[i][select_dp2->L[i]] = (char)NULL;
}
printf("%s ",select_dp2->V[i]);
}
printf ("\n");
}
end_main:
printf ("\n\n# of rows processed = %d\n", sqlca.sqlerrd[2]);
return;
sqlerror:
error ("query_two");
}
Received on Sat Oct 15 1994 - 08:09:55 CET