Re: Pro*C (Method 4)
Date: 1996/09/23
Message-ID: <32460c3d.3712488_at_dcsun4>
On Sun, 22 Sep 1996 05:53:51 GMT, kwright1_at_vossnet.co.uk (Kevin Wright) wrote:
>Anybody know anything about writing method 4 programs with a simple >example included. The Oracle manuals make it look hideous and their >examples aren't much help either. >
I hope this helps. This is a full blown pro*c program that runs from the command line, takes any sql statement, and executes it. If it happens to be a select statement, it will print out the results on stdout using commas between the fields. You can change the printf statement so it prints out using quotes and commas very easily.
This is probably as small an example as you can get, there are two files, main.pc and meth4.pc. main just logs on and processes parms. meth4 does the query/statement.
After you build this, you can use it like:
meth4 userid=scott/tiger 'stmt=select * from emp'
(note the quotes around stmt= to prevent the shell from expanding the * and to keep the entire thing one parameter... )
- meth4.pc ----------------------------------
#include <stdio.h>
#include <string.h>
#define MAX_VNAME_LEN 30
#define MAX_INAME_LEN 30
#define SQLCA_STORAGE_CLASS extern
EXEC SQL INCLUDE sqlca;
EXEC SQL INCLUDE sqlda;
extern SQLDA *sqlald();
extern void sqlclu();
int do_stmt( char * stmt )
{
EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
EXEC SQL PREPARE S FROM :stmt;
EXEC SQL DECLARE C CURSOR FOR S;
EXEC SQL OPEN C;
process_select_list(stmt);
EXEC SQL CLOSE C;
}
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 * stmt)
{
SQLDA * select_dp;
int i;
int null_ok;
int precision;
int scale;
int size = 10;
/* we start by guessing that no more then 10 columns
are selected
*/
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 NO SELECT ITEMS, we must have just done an INSERT/UPDATE/DELETE
CREATE/PL-SQL block etc.
No further processing needed, we are done
*/
if ( !select_dp->F )
{
sqlclu(select_dp);
return 0;
}
/* If we 'guessed' wrong above, we are told so by having a negative
F value. abs(F) is the number of actual select list items. We
will reallocate
*/
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;
/* set up an area for NULL indicators for all output variables
*/
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);
/* Here we are fixing the length. The little array
above maps Oracle Types to the maximum length I will
handle for that type.
For example Type 2 is a number, I fetch everything into
strings. I will set aside 45 bytes to hold the biggest number
we can hold
*/
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;
/* Here we set the output type to 5, NULL TERMINATED C Strings
for all output types. Dates, Numbers, Raws, etc will be
converted into C-Strings for us
*/
select_dp->T[i] = 5;
select_dp->V[i] = (char *)malloc( select_dp->L[i] );
}
for (;;)
{
EXEC SQL WHENEVER NOT FOUND DO break;
EXEC SQL FETCH C USING DESCRIPTOR select_dp;
/* to make this print out with quotes and commas,
simply change "%s%s" into "%s'%s'"
*/
for (i = 0; i < select_dp->F; i++)
printf( "%s%s",
i ? ",":"",
*(select_dp->I[i])?"(null)":select_dp->V[i] );
printf( "\n" );
}
for( i = 0; i < select_dp->F; i++ )
{
free( select_dp->I[i] );
free( select_dp->V[i] );
}
sqlclu(select_dp);
}
---------------------- eof ------------------------------------ ---------------------- main.pc --------------------------------#include <ctype.h>
#include <stdio.h>
#include <string.h>
#include <assert.h>
static char * USERID = NULL; static char * STMT = NULL;
#define vstrcpy( a, b ) \
(strcpy( a.arr, b ), a.len = strlen( a.arr ), a.arr)
#define vstrcat( a, b ) \
(strcat( a.arr, b ), a.len = strlen( a.arr ), a.arr)
#define vmaxlen( a ) \
(a.len = sizeof(a.arr))
#define vnull( a ) \
(a.arr[ a.len ] = 0)
#define vstring( a ) \
(a.arr[a.len] = 0, a.arr)
#define SQLCA_INIT
EXEC SQL INCLUDE sqlca;
EXEC SQL BEGIN DECLARE SECTION;
int SQLCODE;
EXEC SQL END DECLARE SECTION;
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=......\n", argv[0] );
exit(1);
}
}
if ( USERID == NULL || STMT == NULL ) {
printf( "usage: %s userid=xxx/xxx stmt=......\n", argv[0] );
exit(1);
}
}
void sqlerror_hard( void )
{
EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("\nORACLE error detected:");
printf("\n% .70s \n", sqlca.sqlerrm.sqlerrmc);
EXEC SQL ROLLBACK WORK RELEASE;
exit(1);
}
void die( char * msg )
{
printf( "%s\n", msg );
exit(2);
}
static void sqlerror_soft()
{
EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("\nORACLE error detected:");
printf("\n% .70s \n", sqlca.sqlerrm.sqlerrmc);
}
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;
do_stmt( STMT );
/* Disconnect from ORACLE. */
EXEC SQL COMMIT WORK RELEASE;
exit(0);
}
--------------------------------- eof --------------------------------Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
- Check out Oracle Governments web site! -----
Follow the link to "Tech Center"
and then downloadable Utilities for some free software...
statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Mon Sep 23 1996 - 00:00:00 CEST