| 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 Unknown # of variables, Unknown Types
Here is an example that works on Windows, the ideas are the same though
regardless of platform. OCI is much more elegant when it comes to doing
this sort of thing...
/*begin DM4SAM.H*/
/*
Dynamic SQL statement parser written in Pro*C
using Dynamic SQL Method 4
Pass it an arbitrary SQL Satement and it will write the results to
a file.
*/
int Disconnect(void);
int Connect(char *username, char *password, char *alias);
void RecordToFile( int *rc, long *RecordSelected, char *TempFile, char
*stmt);
void RemoveSpace(char *s);
/*end DM4SAM.H*/
/*begin DM4SAM.PC*/
/*
Example of dynamic SQL method 4 using Pro*C, you send it a select statement and it writes the results to a file...*/
#include <sqlproto.h> /*Pro*C headers*/
#include "DM4SAM.H"
#define MAX_PATHNAME_SIZE 127
/* This is maximum number of columns allowable in an single
table -but- not necessarily the maximum number of columns
in a return set due to joins, calculated columns, etc */
#define MAX_ITEMS 254
#define MAX_VNAME_LEN 30 /*Maximum variable name size*/
#define MAX_INAME_LEN 30 /*Maximum Indicator variable name size*/
/* char WinMsg[128]; */
EXEC ORACLE OPTION (ORACA=YES);
EXEC ORACLE OPTION (RELEASE_CURSOR=YES);
/* host variables used in embedded SQL*/
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR user[20];
VARCHAR pswd[20];
VARCHAR rdbms[20];
char sql_statement[1024];
EXEC SQL VAR sql_statement IS STRING(1024);
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE sqlca; EXEC SQL INCLUDE sqlda; EXEC SQL INCLUDE oraca;
/* further SQLLIB function prototype definitions */
extern SQLDA *sqlald();
extern void sqlnul();
/*
Macro to copy a target NULL terminated string into a destination VARCHAR2 structure, this presumes that the VARCHAR variable was declared large enough to hold the target string. */
#define V_COPY(v,s) strcpy(v.arr,s); \
v.len = strlen(s); /* establish an RDBMS connection
V_COPY(user,username)
V_COPY(pswd,password)
V_COPY(rdbms,alias)
return 0; } return 1;
/*
disconnect from the RDBMS
*/
int Disconnect(void)
{
EXEC SQL COMMIT WORK RELEASE;
if (sqlca.sqlcode < 0) {
return 0; } return 1;
/*
record the data to file...
*/
void RecordToFile( int *rc,
long *RecordSelected,
char *TempFile,
char *stmt)
{
FILE *fp; /* handle to output file */
char *pSQL; /* local pointer to SQL stmt */
char *pFILE; /* local pointer to file name */
SQLDA *bind_descriptor; /* descriptor area for bind variables
*/
SQLDA *select_descriptor; /* descriptor area for select list items */
int i, null_ok, precision, scale;
char buf[128]; /* temporary work buffer */
char *tbuf; /* temporary work buffer */
#define SQLCA_INIT
pSQL = stmt;
pFILE = TempFile;
if ((fp = fopen( pFILE,"w+")) == NULL) {
MessageBox(NULL, "Problem opening output File", pFILE, MB_OK);
*rc = -1;
return;
}
oraca.orastxtf = ORASTFERR;
/*allocate the Bind descriptor*/
if ((bind_descriptor =
sqlald(MAX_ITEMS, MAX_VNAME_LEN, MAX_INAME_LEN)) == (SQLDA *) 0)
{
MessageBox(NULL,"Cannot allocate memory for Bind descriptor.","test Src",MB_OK);
*rc = -1;
return;
/*allocate the select descriptor*/
if ((select_descriptor =
sqlald (MAX_ITEMS, MAX_VNAME_LEN, MAX_INAME_LEN)) == (SQLDA *) 0)
{
MessageBox(NULL,"Cannot allocate memory for Select descriptor.","test Src",MB_OK);
*rc = -1;
return;
/* Allocate the pointers to the indicator variables, and the
actual data. */
for (i = 0; i < MAX_ITEMS; i++) {
bind_descriptor->I[i] = (short *) malloc(sizeof (short));
select_descriptor->I[i] = (short *) malloc(sizeof(short));
bind_descriptor->V[i] = (char *) malloc(1);
select_descriptor->V[i] = (char *) malloc(1);
}
memset(sql_statement,0,sizeof(sql_statement)); memcpy(sql_statement,pSQL,strlen(pSQL)); MessageBox(NULL, sql_statement, "SQLSTMT", MB_OK);
EXEC SQL PREPARE StmtName FROM :sql_statement;
if(sqlca.sqlcode != 0) {
char buf[100];
/* ORACLE error handler */
EXEC SQL WHENEVER SQLERROR CONTINUE;
sprintf (buf,"%25.70s\n",sqlca.sqlerrm.sqlerrmc);
MessageBox(NULL,buf,"Error",MB_OK);
*rc = -1;
sprintf
(buf,"Parse error at character offset %25d in SQL
statement.\n",
sqlca.sqlerrd[4]);
MessageBox(NULL,buf,"Error",MB_OK);
EXEC SQL ROLLBACK WORK;
return;
}
EXEC SQL DECLARE Cursor1 CURSOR FOR StmtName;
bind_descriptor->N = MAX_ITEMS; /* Initialize count of array elements. */
EXEC SQL DESCRIBE BIND VARIABLES FOR StmtName INTO bind_descriptor;
/*
If F is negative, there were more bind variables
than originally allocated by sqlald().
*/
if (bind_descriptor->F < 0)
{
MessageBox(NULL,"Too many bind variables","test Src",MB_OK);
*rc = -1;
return;
/* Set the maximum number of array elements in the
descriptor to the number found. */
bind_descriptor->N = bind_descriptor->F;
EXEC SQL OPEN Cursor1 USING DESCRIPTOR bind_descriptor; {
select_descriptor->N = MAX_ITEMS;
EXEC SQL DESCRIBE SELECT LIST FOR StmtName INTO select_descriptor;
/* If F is negative, there were more select-list
items than originally allocated by sqlald(). */
if (select_descriptor->F < 0)
{
MessageBox(NULL,"Too many bind variables","test Src",MB_OK);
*rc = -1;
return;
/*
Set the maximum number of array elements in the
descriptor to the number found.
*/
select_descriptor->N = select_descriptor->F;
for (i = 0; i < select_descriptor->F; i++) {
/* Turn off high-order bit of datatype (in this example,
it does not matter if the column is NOT NULL). */
sqlnul (&(select_descriptor->T[i]), &(select_descriptor->T[i]),
&null_ok);
/*
This currently supports CHAR/VARCHAR, NUMBER amd DATE
dataypes
*/
switch (select_descriptor->T[i])
{
case 1 : /* CHAR datatype: no change in length
needed, except possibly for TO_CHAR
conversions (not handled here). */
break;
case 2 : /* NUMBER datatype: use sqlprc() to
extract precision and scale. */
sqlprc (&(select_descriptor->L[i]), &precision, &scale);
/* Allow for maximum size of NUMBER. */
if (precision == 0) precision = 40;
/* Also allow for decimal point and
possible sign. */
/* convert NUMBER datatype to FLOAT if scale > 0,
INT otherwise. */
if (scale > 0)
select_descriptor->L[i] = sizeof(float);
else
select_descriptor->L[i] = sizeof(int);
break;
case 12 : /* DATE datatype */
select_descriptor->L[i] = 9;
break;
}
if (select_descriptor->T[i] != 2) {
select_descriptor->V[i] = (char *) realloc(select_descriptor-
>V[i],select_descriptor->L[i] + 1);
select_descriptor->V[i] = memset(select_descriptor->V[i], 0,
sizeof(select_descriptor->L[i])+1);
}
else {
select_descriptor->V[i] = (char *) realloc(select_descriptor-
>V[i],select_descriptor->L[i]);
select_descriptor->V[i] = memset(select_descriptor->V[i], 0,
sizeof(select_descriptor->L[i]));
}
/*
Print column headings, right-justifying number
column headings.
*/
tbuf = malloc(select_descriptor->M[i] + 1);
memset(tbuf,0,select_descriptor->M[i] + 1);
strncpy(tbuf,select_descriptor->S[i],select_descriptor-
>M[i]);
fprintf (fp,"%25s,", tbuf);
free(tbuf);
/*
Coerce ALL datatypes except for LONG RAW and NUMBER to
character.
*/
if (select_descriptor->T[i] != 24 && select_descriptor->T[i] !=
2)
select_descriptor->T[i] = 1;
/*
Coerce the datatypes of NUMBERs to float or int depending on
the scale.
*/
if (select_descriptor->T[i] == 2)
if (scale > 0)
select_descriptor->T[i] = 4; /* float */
else
select_descriptor->T[i] = 3; /* int */
}
MessageBox(NULL,"Fetch","Src",MB_OK); EXEC SQL WHENEVER NOT FOUND GOTO end_select_loop;
for (;;)
{
EXEC SQL FETCH Cursor1 USING DESCRIPTOR select_descriptor;
for (i = 0; i < select_descriptor->F; i++)
{
int radix = 10;
if (*select_descriptor->I[i] < 0){
fprintf (fp,",");
}
else {
tbuf = malloc(select_descriptor->L[i] +
1);
memset(tbuf,0,select_descriptor->L[i] +
1);
if (select_descriptor->T[i] == 3)
{ /* int datatype */
fprintf (fp,"%25d,", *(int *)select_descriptor->V[i]);
}
else if (select_descriptor->T[i] == 4) { /* float
datatype */
fprintf (fp,"%25.2f,", *(float *)select_descriptor->V
[i]);
}
else if (select_descriptor->T[i] == 12) { /* date
data type */
fprintf (fp,"%25s,", select_descriptor->V[i]);
}
else { /* character string */
int len;
len = select_descriptor->L[i];
strncpy(tbuf,select_descriptor->V
[i],len);
RemoveSpace(tbuf);
fprintf(fp,"%25s,", tbuf);
free(tbuf);
}
}
}
fprintf (fp,"\n");
}
end_select_loop:
;
}
/* Tell user how many rows processed. */
for (i = 0; i < 8; i++)
{
sprintf(buf,"%25d row%25c processed.\n", sqlca.sqlerrd
[2],
sqlca.sqlerrd[2] == 1 ? '\0' : 's');
MessageBox(NULL,buf,"Oracle",MB_OK);
*RecordSelected = sqlca.sqlerrd[2];
break;
}
/* When done, free the memory allocated for
pointers in the bind and select descriptors. */
for (i = 0; i < MAX_ITEMS; i++)
if (bind_descriptor->V[i] != (char *) 0)
free(bind_descriptor->V[i]);
free(bind_descriptor->I[i]); /* MAX_ITEMS were allocated. */
if (select_descriptor->V[i] != (char *) 0)
free(select_descriptor->V[i]);
free(select_descriptor->I[i]); /* MAX_ITEMS were allocated. */
}
/* Free space used by the descriptors themselves. */
sqlclu(bind_descriptor);
sqlclu(select_descriptor);
/* EXEC SQL WHENEVER SQLERROR CONTINUE; */
/* Close the cursor. */
EXEC SQL CLOSE Cursor1;
fclose(fp);
} /* end of Record to File */
/* remove trailing spaces */
void RemoveSpace(char *s)
{
int len;
len = strlen(s);
len--;
while (len >= 0) {
if( *(s + len) == ' ' ){
len--;
}
else {
break;
}
/*
params: username password connect string
int rc;
long RecordSelected;
char TempFile[128];
char stmt[256];
char user[100];
char pass[100];
char dbms[100];
memset(TempFile,0,128);
memset(stmt,0,256);
memset(user,0,100);
memset(pass,0,100);
memset(dbms,0,100);
for(rc = 1;rc < argc;rc++) {
switch(rc) {
case 1:
strcpy(user,argv[rc]);
break;
case 2:
strcpy(pass,argv[rc]);
break;
case 3:
strcpy(dbms,argv[rc]);
break;
}
}
if(strlen(user) == 0)
strcpy(user,"scott");
if(strlen(pass) == 0);
strcpy(pass,"tiger");
if(strlen(dbms) == 0)
strcpy(dbms,"orlnt5.world");
/*
This could be any arbitrary select statement...
*/
strcpy(stmt,"SELECT ENAME, EMPNO FROM EMP");
strcpy(TempFile,"testme.txt");
Connect(user,pass,dbms);
RecordToFile(&rc,&RecordSelected,TempFile,stmt);
Disconnect();
return 0;
--
-cheers
DW
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Tue Mar 07 2000 - 12:27:27 CST
 |
 |