Sample Program: Dynamic SQL Method 4
This program shows the basic steps required to use dynamic SQL with Method 4. After connecting to Oracle, the program allocates memory for the descriptors using SQLSQLDAAlloc(), prompts the user for a SQL statement, PREPAREs the statement, DECLAREs a cursor, checks for any bind variables using DESCRIBE BIND, OPENs the cursor, and DESCRIBEs any select-list items. If the input SQL statement is a query, the program FETCHes each row of data, then CLOSEs the cursor. This program is available on-line in the demo directory, in the file sample10.pc. /*******************************************************************
Sample Program 10: Dynamic SQL Method 4This program connects you to ORACLE using your username and
password, then prompts you for a SQL statement. You can enter
any legal SQL statement. Use regular SQL syntax, not embedded SQL.
Your statement will be processed. If it is a query, the rows
fetched are displayed.
You can enter multi-line statements. The limit is 1023 characters.
This sample program only processes up to MAX_ITEMS bind variables and
MAX_ITEMS select-list items. MAX_ITEMS is #defined to be 40.
*******************************************************************/#include <stdio.h>
#include <string.h>
#include <setjmp.h>
#include <sqlda.h>
#include <stdlib.h>
#include <sqlcpr.h>/* Maximum number of select-list items or bind variables. */
#define MAX_ITEMS 40/* Maximum lengths of the _names_ of the
select-list items or indicator variables. */
#define MAX_VNAME_LEN 30
#define MAX_INAME_LEN 30#ifndef NULL
#define NULL 0
#endif/* Prototypes */
#if defined(__STDC__)
void sql_error(void);
int oracle_connect(void);
int alloc_descriptors(int, int, int);
int get_dyn_statement(void);
void set_bind_variables(void);
void process_select_list(void);
void help(void);
#else
void sql_error(/*_ void _*/);
int oracle_connect(/*_ void _*/);
int alloc_descriptors(/*_ int, int, int _*/);
int get_dyn_statement(/* void _*/);
void set_bind_variables(/*_ void -*/);
void process_select_list(/*_ void _*/);
void help(/*_ void _*/);
#endifchar *dml_commands[] = {"SELECT", "select", "INSERT", "insert",
"UPDATE", "update", "DELETE", "delete"};EXEC SQL INCLUDE sqlda;
EXEC SQL INCLUDE sqlca;EXEC SQL BEGIN DECLARE SECTION;
char dyn_statement[1024];
EXEC SQL VAR dyn_statement IS STRING(1024);
EXEC SQL END DECLARE SECTION;
SQLDA *bind_dp;
SQLDA *select_dp;/* Define a buffer to hold longjmp state info. */
jmp_buf jmp_continue;/* A global flag for the error routine. */
int parse_flag = 0;void main()
{
int i; /* Connect to the database. */
if (oracle_connect() != 0)
exit(1); /* Allocate memory for the select and bind descriptors. */
if (alloc_descriptors(MAX_ITEMS, MAX_VNAME_LEN, MAX_INAME_LEN) != 0)
exit(1); /* Process SQL statements. */
for (;;)
{
(void) setjmp(jmp_continue); /* Get the statement. Break on "exit". */
if (get_dyn_statement() != 0)
break; /* Prepare the statement and declare a cursor. */
EXEC SQL WHENEVER SQLERROR DO sql_error(); parse_flag = 1; /* Set a flag for sql_error(). */
EXEC SQL PREPARE S FROM :dyn_statement;
parse_flag = 0; /* Unset the flag. */ EXEC SQL DECLARE C CURSOR FOR S; /* Set the bind variables for any placeholders in the
SQL statement. */
set_bind_variables(); /* Open the cursor and execute the statement.
* If the statement is not a query (SELECT), the
* statement processing is completed after the
* OPEN.
*/ EXEC SQL OPEN C USING DESCRIPTOR bind_dp; /* Call the function that processes the select-list.
* If the statement is not a query, this function
* just returns, doing nothing.
*/
process_select_list(); /* Tell user how many rows processed. */
for (i = 0; i < 8; i++)
{
if (strncmp(dyn_statement, dml_commands[i], 6) == 0)
{
printf("\n\n%d row%c processed.\n", sqlca.sqlerrd[2],
sqlca.sqlerrd[2] == 1 ? '\0' : 's');
break;
}
}
} /* end of for(;;) statement-processing loop */ /* When done, free the memory allocated for
pointers in the bind and select descriptors. */
for (i = 0; i < MAX_ITEMS; i++)
{
if (bind_dp->V[i] != (char *) 0)
free(bind_dp->V[i]);
free(bind_dp->I[i]); /* MAX_ITEMS were allocated. */
if (select_dp->V[i] != (char *) 0)
free(select_dp->V[i]);
free(select_dp->I[i]); /* MAX_ITEMS were allocated. */
} /* Free space used by the descriptors themselves. */
SQLSQLDAFree( SQL_SINGLE_RCTX, bind_dp);
SQLSQLDAFree( SQL_SINGLE_RCTX, select_dp); EXEC SQL WHENEVER SQLERROR CONTINUE;
/* Close the cursor. */
EXEC SQL CLOSE C; EXEC SQL COMMIT WORK RELEASE;
puts("\nHave a good day!\n"); EXEC SQL WHENEVER SQLERROR DO sql_error();
return;
}
This program shows the basic steps required to use dynamic SQL with Method 4. After connecting to Oracle, the program allocates memory for the descriptors using SQLSQLDAAlloc(), prompts the user for a SQL statement, PREPAREs the statement, DECLAREs a cursor, checks for any bind variables using DESCRIBE BIND, OPENs the cursor, and DESCRIBEs any select-list items. If the input SQL statement is a query, the program FETCHes each row of data, then CLOSEs the cursor. This program is available on-line in the demo directory, in the file sample10.pc. /*******************************************************************
Sample Program 10: Dynamic SQL Method 4This program connects you to ORACLE using your username and
password, then prompts you for a SQL statement. You can enter
any legal SQL statement. Use regular SQL syntax, not embedded SQL.
Your statement will be processed. If it is a query, the rows
fetched are displayed.
You can enter multi-line statements. The limit is 1023 characters.
This sample program only processes up to MAX_ITEMS bind variables and
MAX_ITEMS select-list items. MAX_ITEMS is #defined to be 40.
*******************************************************************/#include <stdio.h>
#include <string.h>
#include <setjmp.h>
#include <sqlda.h>
#include <stdlib.h>
#include <sqlcpr.h>/* Maximum number of select-list items or bind variables. */
#define MAX_ITEMS 40/* Maximum lengths of the _names_ of the
select-list items or indicator variables. */
#define MAX_VNAME_LEN 30
#define MAX_INAME_LEN 30#ifndef NULL
#define NULL 0
#endif/* Prototypes */
#if defined(__STDC__)
void sql_error(void);
int oracle_connect(void);
int alloc_descriptors(int, int, int);
int get_dyn_statement(void);
void set_bind_variables(void);
void process_select_list(void);
void help(void);
#else
void sql_error(/*_ void _*/);
int oracle_connect(/*_ void _*/);
int alloc_descriptors(/*_ int, int, int _*/);
int get_dyn_statement(/* void _*/);
void set_bind_variables(/*_ void -*/);
void process_select_list(/*_ void _*/);
void help(/*_ void _*/);
#endifchar *dml_commands[] = {"SELECT", "select", "INSERT", "insert",
"UPDATE", "update", "DELETE", "delete"};EXEC SQL INCLUDE sqlda;
EXEC SQL INCLUDE sqlca;EXEC SQL BEGIN DECLARE SECTION;
char dyn_statement[1024];
EXEC SQL VAR dyn_statement IS STRING(1024);
EXEC SQL END DECLARE SECTION;
SQLDA *bind_dp;
SQLDA *select_dp;/* Define a buffer to hold longjmp state info. */
jmp_buf jmp_continue;/* A global flag for the error routine. */
int parse_flag = 0;void main()
{
int i; /* Connect to the database. */
if (oracle_connect() != 0)
exit(1); /* Allocate memory for the select and bind descriptors. */
if (alloc_descriptors(MAX_ITEMS, MAX_VNAME_LEN, MAX_INAME_LEN) != 0)
exit(1); /* Process SQL statements. */
for (;;)
{
(void) setjmp(jmp_continue); /* Get the statement. Break on "exit". */
if (get_dyn_statement() != 0)
break; /* Prepare the statement and declare a cursor. */
EXEC SQL WHENEVER SQLERROR DO sql_error(); parse_flag = 1; /* Set a flag for sql_error(). */
EXEC SQL PREPARE S FROM :dyn_statement;
parse_flag = 0; /* Unset the flag. */ EXEC SQL DECLARE C CURSOR FOR S; /* Set the bind variables for any placeholders in the
SQL statement. */
set_bind_variables(); /* Open the cursor and execute the statement.
* If the statement is not a query (SELECT), the
* statement processing is completed after the
* OPEN.
*/ EXEC SQL OPEN C USING DESCRIPTOR bind_dp; /* Call the function that processes the select-list.
* If the statement is not a query, this function
* just returns, doing nothing.
*/
process_select_list(); /* Tell user how many rows processed. */
for (i = 0; i < 8; i++)
{
if (strncmp(dyn_statement, dml_commands[i], 6) == 0)
{
printf("\n\n%d row%c processed.\n", sqlca.sqlerrd[2],
sqlca.sqlerrd[2] == 1 ? '\0' : 's');
break;
}
}
} /* end of for(;;) statement-processing loop */ /* When done, free the memory allocated for
pointers in the bind and select descriptors. */
for (i = 0; i < MAX_ITEMS; i++)
{
if (bind_dp->V[i] != (char *) 0)
free(bind_dp->V[i]);
free(bind_dp->I[i]); /* MAX_ITEMS were allocated. */
if (select_dp->V[i] != (char *) 0)
free(select_dp->V[i]);
free(select_dp->I[i]); /* MAX_ITEMS were allocated. */
} /* Free space used by the descriptors themselves. */
SQLSQLDAFree( SQL_SINGLE_RCTX, bind_dp);
SQLSQLDAFree( SQL_SINGLE_RCTX, select_dp); EXEC SQL WHENEVER SQLERROR CONTINUE;
/* Close the cursor. */
EXEC SQL CLOSE C; EXEC SQL COMMIT WORK RELEASE;
puts("\nHave a good day!\n"); EXEC SQL WHENEVER SQLERROR DO sql_error();
return;
}
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR username[128];
VARCHAR password[32];
EXEC SQL END DECLARE SECTION; printf("\nusername: ");
fgets((char *) username.arr, sizeof username.arr, stdin);
username.arr[strlen((char *) username.arr)-1] = '\0';
username.len = (unsigned short)strlen((char *) username.arr); printf("password: ");
fgets((char *) password.arr, sizeof password.arr, stdin);
password.arr[strlen((char *) password.arr) - 1] = '\0';
password.len = (unsigned short)strlen((char *) password.arr);
EXEC SQL WHENEVER SQLERROR GOTO connect_error; EXEC SQL CONNECT :username IDENTIFIED BY :password; printf("\nConnected to ORACLE as user %s.\n", username.arr); return 0;connect_error:
fprintf(stderr, "Cannot connect to ORACLE as user %s\n", username.arr);
return -1;
}
/*
* Allocate the BIND and SELECT descriptors using SQLSQLDAAlloc().
* Also allocate the pointers to indicator variables
* in each descriptor. The pointers to the actual bind
* variables and the select-list items are realloc'ed in
* the set_bind_variables() or process_select_list()
* routines. This routine allocates 1 byte for select_dp->V[i]
* and bind_dp->V[i], so the realloc will work correctly.
*/alloc_descriptors(size, max_vname_len, max_iname_len)
int size;
int max_vname_len;
int max_iname_len;
{
int i; /*
* The first SQLSQLDAAlloc parameter is the runtime context. * The second parameter determines the maximum number of
* array elements in each variable in the descriptor. In
* other words, it determines the maximum number of bind
* variables or select-list items in the SQL statement.
*
* The third parameter determines the maximum length of
* strings used to hold the names of select-list items
* or placeholders. The maximum length of column
* names in ORACLE is 30, but you can allocate more or less
* as needed.
*
* The fourth parameter determines the maximum length of
* strings used to hold the names of any indicator
* variables. To follow ORACLE standards, the maximum
* length of these should be 30. But, you can allocate
* more or less as needed.
*/ if ((bind_dp =
SQLSQLDAAlloc(SQL_SINGLE_RCTX, size, max_vname_len, max_iname_len)) ==
(SQLDA *) 0)
{
fprintf(stderr,
"Cannot allocate memory for bind descriptor.");
return -1; /* Have to exit in this case. */
} if ((select_dp =
SQLSQLDAAlloc (SQL_SINGLE_RCTX, size, max_vname_len, max_iname_len)) ==
(SQLDA *) 0)
{
fprintf(stderr,
"Cannot allocate memory for select descriptor.");
return -1;
}
select_dp->N = MAX_ITEMS; /* Allocate the pointers to the indicator variables, and the
actual data. */
for (i = 0; i < MAX_ITEMS; i++) {
bind_dp->I[i] = (short *) malloc(sizeof (short));
select_dp->I[i] = (short *) malloc(sizeof(short));
bind_dp->V[i] = (char *) malloc(1);
select_dp->V[i] = (char *) malloc(1);
}
return 0;
}
int get_dyn_statement()
{
char *cp, linebuf[256];
int iter, plsql;
for (plsql = 0, iter = 1; ;)
{
if (iter == 1)
{
printf("\nSQL> ");
dyn_statement[0] = '\0';
}
fgets(linebuf, sizeof linebuf, stdin); cp = strrchr(linebuf, '\n');
if (cp && cp != linebuf)
*cp = ' ';
else if (cp == linebuf)
continue; if ((strncmp(linebuf, "EXIT", 4) == 0) ||
(strncmp(linebuf, "exit", 4) == 0))
{
return -1;
} else if (linebuf[0] == '?' ||
(strncmp(linebuf, "HELP", 4) == 0) ||
(strncmp(linebuf, "help", 4) == 0))
{
help();
iter = 1;
continue;
} if (strstr(linebuf, "BEGIN") ||
(strstr(linebuf, "begin")))
{
plsql = 1;
} strcat(dyn_statement, linebuf); if ((plsql && (cp = strrchr(dyn_statement, '/'))) ||
(!plsql && (cp = strrchr(dyn_statement, ';'))))
{
*cp = '\0';
break;
}
else
{
iter++;
printf("%3d ", iter);
}
}
return 0;
}
{
int i, n;
char bind_var[64]; /* Describe any bind variables (input host variables) */
EXEC SQL WHENEVER SQLERROR DO sql_error(); bind_dp->N = MAX_ITEMS; /* Initialize count of array elements. */
EXEC SQL DESCRIBE BIND VARIABLES FOR S INTO bind_dp; /* If F is negative, there were more bind variables
than originally allocated by SQLSQLDAAlloc(). */
if (bind_dp->F < 0)
{
printf ("\nToo many bind variables (%d), maximum is %d\n.",
-bind_dp->F, MAX_ITEMS);
return;
} /* Set the maximum number of array elements in the
descriptor to the number found. */
bind_dp->N = bind_dp->F;
/* Get the value of each bind variable as a
* character string.
*
* C[i] contains the length of the bind variable
* name used in the SQL statement.
* S[i] contains the actual name of the bind variable
* used in the SQL statement.
*
* L[i] will contain the length of the data value
* entered.
*
* V[i] will contain the address of the data value
* entered.
*
* T[i] is always set to 1 because in this sample program
* data values for all bind variables are entered
* as character strings.
* ORACLE converts to the table value from CHAR.
*
* I[i] will point to the indicator value, which is
* set to -1 when the bind variable value is "null".
*/
for (i = 0; i < bind_dp->F; i++)
{
printf ("\nEnter value for bind variable %.*s: ",
(int)bind_dp->C[i], bind_dp->S[i]);
fgets(bind_var, sizeof bind_var, stdin); /* Get length and remove the new line character. */
n = strlen(bind_var) - 1; /* Set it in the descriptor. */
bind_dp->L[i] = n; /* (re-)allocate the buffer for the value.
SQLSQLDAAlloc() reserves a pointer location for
V[i] but does not allocate the full space for
the pointer. */ bind_dp->V[i] = (char *) realloc(bind_dp->V[i],
(bind_dp->L[i] + 1)); /* And copy it in. */
strncpy(bind_dp->V[i], bind_var, n); /* Set the indicator variable's value. */
if ((strncmp(bind_dp->V[i], "NULL", 4) == 0) ||
(strncmp(bind_dp->V[i], "null", 4) == 0))
*bind_dp->I[i] = -1;
else
*bind_dp->I[i] = 0;
/* Set the bind datatype to 1 for CHAR. */
bind_dp->T[i] = 1;
}
return;
}
Error at line 145, column 5 in file dyn4.pc
SQLSQLDAFree( SQL_SINGLE_RCTX, bind_dp);
....1
PCC-S-02201, Encountered the symbol ")" when expecting one of the following: ( * & + - ~ ! ^ ++ -- ... sizeof, an identifier,
a quoted string, a numeric constant,Syntax error at line 145, column 44, file dyn4.pc:
Error at line 145, column 44 in file dyn4.pc
SQLSQLDAFree( SQL_SINGLE_RCTX, bind_dp);
...........................................1
PCC-S-02201, Encountered the symbol ";" when expecting one of the following: , = ( ) [ * ? | & < > + - / % . ^ *= /= %= += -= <<= >>= &&=
||= ^= | & == != <= >= << >> ++ -- ->
The symbol ")" was substituted for ";" to continue.Syntax error at line 146, column 5, file dyn4.pc:
Error at line 146, column 5 in file dyn4.pc
SQLSQLDAFree( SQL_SINGLE_RCTX, select_dp);
....1
PCC-S-02201, Encountered the symbol ")" when expecting one of the following: ( * & + - ~ ! ^ ++ -- ... sizeof, an identifier,
a quoted string, a numeric constant,Syntax error at line 146, column 46, file dyn4.pc:
Error at line 146, column 46 in file dyn4.pc
SQLSQLDAFree( SQL_SINGLE_RCTX, select_dp);
.............................................1
PCC-S-02201, Encountered the symbol ";" when expecting one of the following: , = ( ) [ * ? | & < > + - / % . ^ *= /= %= += -= <<= >>= &&=
||= ^= | & == != <= >= << >> ++ -- ->
The symbol ")" was substituted for ";" to continue.Syntax error at line 231, column 8, file dyn4.pc:
Error at line 231, column 8 in file dyn4.pc
SQLSQLDAAlloc(SQL_SINGLE_RCTX, size, max_vname_len, max_iname_len)) ==
.......1
PCC-S-02201, Encountered the symbol ")" when expecting one of the following: ( * & + - ~ ! ^ ++ -- ... sizeof, an identifier,
a quoted string, a numeric constant,Syntax error at line 231, column 8, file dyn4.pc:
Error at line 231, column 8 in file dyn4.pc
SQLSQLDAAlloc(SQL_SINGLE_RCTX, size, max_vname_len, max_iname_len)) ==
.......1
PCC-S-02201, Encountered the symbol ")" when expecting one of the following: ; , = ( [ * ? | & < > + - / % . ^ *= /= %= += -= <<= >>= &&=
||= ^= | & == != <= >= << >> ++ -- ->
The symbol "( was inserted before ")" to continue.Syntax error at line 232, column 21, file dyn4.pc:
Error at line 232, column 21 in file dyn4.pc
(SQLDA *) 0)
....................1
PCC-S-02201, Encountered the symbol ")" when expecting one of the following: ; , ( [ * ? | & < > + - / % . ^ | & == != <= >= << >> ++ --
->
The symbol "; was inserted before ")" to continue.Syntax error at line 240, column 9, file dyn4.pc:
Error at line 240, column 9 in file dyn4.pc
SQLSQLDAAlloc (SQL_SINGLE_RCTX, size, max_vname_len, max_iname_len)) ==
........1
PCC-S-02201, Encountered the symbol ")" when expecting one of the following: ( * & + - ~ ! ^ ++ -- ... sizeof, an identifier,
a quoted string, a numeric constant,Syntax error at line 240, column 9, file dyn4.pc:
Error at line 240, column 9 in file dyn4.pc
SQLSQLDAAlloc (SQL_SINGLE_RCTX, size, max_vname_len, max_iname_len)) ==
........1
PCC-S-02201, Encountered the symbol ")" when expecting one of the following: ; , = ( [ * ? | & < > + - / % . ^ *= /= %= += -= <<= >>= &&=
||= ^= | & == != <= >= << >> ++ -- ->
The symbol "( was inserted before ")" to continue.Syntax error at line 241, column 23, file dyn4.pc:
Error at line 241, column 23 in file dyn4.pc
(SQLDA *) 0)
......................1
PCC-S-02201, Encountered the symbol ")" when expecting one of the following: ; , ( [ * ? | & < > + - / % . ^ | & == != <= >= << >> ++ --
->
The symbol "; was inserted before ")" to continue.Syntax error at line 263, column 1, file dyn4.pc:
Error at line 263, column 1 in file dyn4.pc
{
1
PCC-S-02201, Encountered the symbol "{" when expecting one of the following: ; , = ( [
The symbol ";" was substituted for "{" to continue.Syntax error at line 325, column 1, file dyn4.pc:
Error at line 325, column 1 in file dyn4.pc
{
1
PCC-S-02201, Encountered the symbol "{" when expecting one of the following: ; , = ( [
The symbol ";" was substituted for "{" to continue.Syntax error at line 409, column 1, file dyn4.pc:
Error at line 409, column 1 in file dyn4.pc
{
1
PCC-S-02201, Encountered the symbol "{" when expecting one of the following: ; , = ( [
The symbol ";" was substituted for "{" to continue.Parser error at line 482, column 9, file dyn4.pc:
Error at line 482, column 9 in file dyn4.pc
SQLColumnNullCheck ((unsigned short *)&(select_dp->T[i]),
........1
PCC-F-02209, Macro invocation has incorrect number of arguments
Syntax error at line 493, column 17, file dyn4.pc:
Error at line 493, column 17 in file dyn4.pc
SQLNumberPrecV6( SQL_SINGLE_RCTX,
................1
PCC-S-02201, Encountered the symbol ")" when expecting one of the following: ( * & + - ~ ! ^ ++ -- ... sizeof, an identifier,
a quoted string, a numeric constant,Syntax error at line 493, column 17, file dyn4.pc:
Error at line 493, column 17 in file dyn4.pc
SQLNumberPrecV6( SQL_SINGLE_RCTX,
................1
PCC-S-02201, Encountered the symbol ")" when expecting one of the following: ; , = ( [ * ? | & < > + - / % . ^ *= /= %= += -= <<= >>= &&=
||= ^= | & == != <= >= << >> ++ -- ->
The symbol "(" was substituted for ")" to continue.Syntax error at line 605, column 1, file dyn4.pc:
Error at line 605, column 1 in file dyn4.pc
{
1
PCC-S-02201, Encountered the symbol "{" when expecting one of the following: ; , = ( [
The symbol ";" was substituted for "{" to continue.Syntax error at line 618, column 1, file dyn4.pc:
Error at line 618, column 1 in file dyn4.pc
{
1
PCC-S-02201, Encountered the symbol "{" when expecting one of the following: ; , = ( [
The symbol ";" was substituted for "{" to continue.Syntax error at line 0, column 0, file dyn4.pc:
Error at line 0, column 0 in file dyn4.pc
PCC-S-02201, Encountered the symbol "<eof>" when expecting one of the following: ; { } ( * & + - ~ ! ^ ++ -- ... auto, break, case, char,
const, continue, default, do, double, enum, extern, float,
for, goto, if, int, long, ulong_varchar, OCIBFileLocator
OCIBlobLocator, OCIClobLocator, OCIDateTime,
OCIExtProcContext, OCIInterval, OCIRowid, OCIDate, OCINumber,
OCIRaw, OCIString, register, return, short, signed, sizeof,
sql_context, sql_cursor, static, struct, switch, typedef,
union, unsigned, utext, uvarchar, varchar, void, volatile,
while, an identifier, a typedef name, a precompiled header,
a quoted string, a numeric constant, exec oracle,
exec oracle begin, exec, exec sql, exec sql begin,
exec sql type, exec sql var, exec sql include,