Functions | |
int | sqlo_prepare (sqlo_db_handle_t dbh, const char *stmt) |
Parse a statement. | |
int | sqlo_bind_by_name (sqlo_stmt_handle_t sth, const char *name, int param_type, const void *param_addr, unsigned int param_size, short *ind_addr, int is_array) |
Bind a variable by name. | |
int | sqlo_bind_ref_cursor (sqlo_stmt_handle_t sth, const char *cursor_name, int *sth2p) |
Bind a REF CURSOR. | |
int | sqlo_bind_by_pos (sqlo_stmt_handle_t sth, int position, int param_type, const void *param_addr, unsigned int param_size, short *ind_addr, int is_array) |
Bind a variable by position. | |
int | sqlo_bind_by_pos2 (sqlo_stmt_handle_t sth, int position, int param_type, const void *param_addr, unsigned int param_size, short *ind_addr, unsigned short *rcode_addr, unsigned int skip_size) |
Bind a variable by position. | |
int | sqlo_define_by_pos (sqlo_stmt_handle_t sth, int value_pos, int value_type, const void *value_addr, unsigned int value_size, short *ind_addr, short *rlen_addr, int is_array) |
Define a output variable of the select list. | |
int | sqlo_define_by_pos2 (sqlo_stmt_handle_t sth, int value_pos, int value_type, const void *value_addr, unsigned int value_size, short *ind_addr, unsigned short *rlen_addr, unsigned short *rcode_addr, unsigned int skip_size) |
Define a output variable of the select list. | |
int | sqlo_define_ntable (sqlo_stmt_handle_t sth, unsigned int pos, int *sth2p) |
Define a nested table Please visit the example for details. | |
int | sqlo_execute (sqlo_stmt_handle_t sth, unsigned int iterations) |
Execute a statement. |
Use these functions if you want to execute PL/SQL or stored procedures.
int sqlo_bind_by_name | ( | sqlo_stmt_handle_t | sth, | |
const char * | name, | |||
int | param_type, | |||
const void * | param_addr, | |||
unsigned int | param_size, | |||
short * | ind_addr, | |||
int | is_array | |||
) |
Bind a variable by name.
Use this to bind a variable in a query or a stored procedure call.
If is_array is 1, the parameters param_addr and ind_addr must point to arrays. ind_addr is optional and can be passed a NULL. The param_size is still the size of one array element, not the whole array size!
sth | I - The statement handle. | |
name | I - The bind parameter name. | |
param_type | I - The datatype of the bind parameter (see sqlo_data_types). | |
param_addr | I - The address of a variable or array. | |
param_size | I - The size of the object at param_addr in bytes. | |
ind_addr | I - The pointer to the NULL indicator variable (optional). | |
is_array | I - 1 means param_addr points to an array, 0 means a single variable. |
/* $Id: ex9.c 221 2002-08-24 12:54:47Z kpoitschke $ */ #include <stdio.h> #include <stdlib.h> #include "examples.h" static void create_sp __P((sqlo_db_handle_t dbh)); int call_plsql(sqlo_db_handle_t dbh) { double ip1; int ip2; char op[80]; sqlo_stmt_handle_t sth = SQLO_STH_INIT; /* the stored procecdure call */ char * stmt = "BEGIN\n" " EX9(:ip1, :ip2, :op);\n" "END;\n"; create_sp(dbh); /* create the test stored procedure */ ip1 = 1.5; ip2 = 20; /* parse the statement */ if ( 0 <= (sth = sqlo_prepare(dbh, stmt))) { /* bind all variables */ if (SQLO_SUCCESS != (sqlo_bind_by_name(sth, ":ip1", SQLOT_FLT, &ip1, sizeof(ip1), 0, 0) || sqlo_bind_by_name(sth, ":ip2", SQLOT_INT, &ip2, sizeof(ip2), 0, 0) || sqlo_bind_by_name(sth, ":op", SQLOT_STR, &op, sizeof(op), 0, 0) )) { error_exit(dbh, "sqlo_bind_by_name"); } else { /* execute the call */ if (SQLO_SUCCESS != sqlo_execute(sth, 1)) error_exit(dbh, "sqlo_execute"); } /* print the result */ if (atof(op) != (ip1 + ip2)) printf("Stored procudure returned wrong result %s, expected %6.2f\n", op, (ip1 + ip2)); if (SQLO_SUCCESS != sqlo_close(sth)) error_exit(dbh, "sqlo_execute"); } else { error_exit(dbh, "sqlo_prepare"); } if ( 0 > sqlo_exec(dbh, "DROP PROCEDURE EX9")) error_exit(dbh, "sqlo_exec"); return 1; } /* creates the stored procedure used above */ static void create_sp(sqlo_db_handle_t dbh) { char * stmt = "CREATE OR REPLACE PROCEDURE EX9(ipNum1 IN NUMBER, ipNum2 IN NUMBER, opStr OUT VARCHAR)\n" "IS\n" "BEGIN\n" " opStr := TO_CHAR(ipNum1 + ipNum2);\n" "END;\n"; if (SQLO_SUCCESS != sqlo_exec(dbh, stmt)) error_exit(dbh, stmt); } /* $Id: ex9.c 221 2002-08-24 12:54:47Z kpoitschke $ */
int sqlo_bind_by_pos | ( | sqlo_stmt_handle_t | sth, | |
int | position, | |||
int | param_type, | |||
const void * | param_addr, | |||
unsigned int | param_size, | |||
short * | ind_addr, | |||
int | is_array | |||
) |
Bind a variable by position.
If is_array is 1, the parameters param_addr and ind_addr must point to arrays. ind_addr is optional and can be passed a NULL. The param_size is still the size of one array element, not the whole array size!
sth | I - The statement handle | |
position | I - The bind parameter position in the string. Starts with 1 for the first. | |
param_type | I - The datatype of the bind parameter (see sqlo_data_types). | |
param_addr | I - The pointer to the parameter data. | |
param_size | I - The size of the object at param_addr in bytes. | |
ind_addr | I - The pointer to the NULL indicator variable (optional). | |
is_array | I - 1 means param_addr points to an array, 0 means a single variable. |
/* $Id: ex10.c 221 2002-08-24 12:54:47Z kpoitschke $ */ #include <stdio.h> #include <stdlib.h> #include "examples.h" sqlo_stmt_handle_t prepare_cursor(sqlo_db_handle_t dbh, double * min_salary) { sqlo_stmt_handle_t sth; /* statement handle */ if (0 > (sth = sqlo_prepare(dbh, "SELECT ENAME, SAL FROM EMP WHERE SAL >= :salary ORDER BY 2,1"))) error_exit(dbh, "sqlo_prepare"); if (SQLO_SUCCESS != sqlo_bind_by_pos(sth, 1, SQLOT_FLT, min_salary, sizeof(double), 0, 0)) error_exit(dbh, "sqlo_bind_by_pos"); return sth; } /* $Id: ex10.c 221 2002-08-24 12:54:47Z kpoitschke $ */
int sqlo_bind_by_pos2 | ( | sqlo_stmt_handle_t | sth, | |
int | position, | |||
int | param_type, | |||
const void * | param_addr, | |||
unsigned int | param_size, | |||
short * | ind_addr, | |||
unsigned short * | rcode_addr, | |||
unsigned int | skip_size | |||
) |
Bind a variable by position.
Bind the input variables. This new version supports arrays of structures. Set the skip_size to the size of the structure. rcode and ind must be part of the structure.
sth | I - The statement handle | |
position | I - The bind parameter position in the string. Starts with 1 for the first. | |
param_type | I - The datatype of the bind parameter (sqlo_data_types). | |
param_addr | I - The pointer to the parameter data. | |
param_size | I - The size of the object at param_addr in bytes. | |
ind_addr | I - The pointer to the NULL indicator variable (optional). | |
rcode_addr | I - The pointer to the variable that should return the column level | |
skip_size | I - In case into an array of structures, set to sizeof(your_struct), otherwise set it to 0. |
int sqlo_bind_ref_cursor | ( | sqlo_stmt_handle_t | sth, | |
const char * | cursor_name, | |||
int * | sth2p | |||
) |
Bind a REF CURSOR.
Binds a ref cursor and returns a new sth, which you can use to retrieve the data.
sth | I - The statement handle | |
cursor_name | I - The bind name of the cursor | |
sth2p | O - The new statement handle for the ref cursor. |
/* $Id: ex17.c 221 2002-08-24 12:54:47Z kpoitschke $ */ #include <stdio.h> #include <stdlib.h> #include "examples.h" int select_refcursor(sqlo_db_handle_t dbh, double min_salary) { sqlo_stmt_handle_t sth = SQLO_STH_INIT; sqlo_stmt_handle_t st2h; /* handle of the ref cursor */ double sal = min_salary; CONST char **v; int status; CONST char * stmt = "BEGIN\n" " OPEN :c1 FOR SELECT ENAME, SAL FROM EMP WHERE SAL >= :min_sal ORDER BY 2,1;\n" "END;\n"; /* parse the statement */ if ( 0 <= (sth = sqlo_prepare(dbh, stmt))) { /* bind all variables */ if (SQLO_SUCCESS != ( sqlo_bind_ref_cursor(sth, ":c1", &st2h)) || (sqlo_bind_by_name(sth, ":min_sal", SQLOT_FLT, &sal, sizeof(sal), 0, 0) ) ) { error_exit(dbh, "sqlo_bind_by_name"); } else { /* execute the PL/SQL block */ if (SQLO_SUCCESS != sqlo_execute(sth, 1)) error_exit(dbh, "sqlo_execute"); } /* execute the refcursor */ if (SQLO_SUCCESS != sqlo_execute(st2h, 1)) { error_exit(dbh, "sqlo_execute(ref)"); } while (SQLO_SUCCESS == (status = sqlo_fetch(st2h, 1))) { v = sqlo_values(st2h, NULL, 1); printf("Name=%-8s Salary= %-6s\n", v[0], v[1]); } if (status != SQLO_NO_DATA) error_exit(dbh, "sqlo_fetch(st2)"); if (SQLO_SUCCESS != sqlo_close(sth)) error_exit(dbh, "sqlo_close(1)"); if (SQLO_SUCCESS != sqlo_close(st2h)) error_exit(dbh, "sqlo_close(2)"); } else { error_exit(dbh, "sqlo_prepare"); } return 1; } /* $Id: ex17.c 221 2002-08-24 12:54:47Z kpoitschke $ */
/* $Id: ex18.c 221 2002-08-24 12:54:47Z kpoitschke $ */ #include <stdio.h> #include <stdlib.h> #include "examples.h" int select_refcursor2(sqlo_db_handle_t dbh, double min_salary) { sqlo_stmt_handle_t sth = SQLO_STH_INIT; sqlo_stmt_handle_t st2h; /* handle of the ref cursor */ double sal = min_salary; int status; char name[30]; double salary; short nind, sind; CONST char * stmt = "BEGIN\n" " OPEN :c1 FOR SELECT ENAME, SAL FROM EMP WHERE SAL >= :min_sal ORDER BY 2,1;\n" "END;\n"; /* parse the statement */ if ( 0 <= (sth = sqlo_prepare(dbh, stmt))) { /* bind all variables */ if (SQLO_SUCCESS != (sqlo_bind_by_name(sth, ":c1", SQLOT_RSET, &st2h, 0, 0, 0)) || (sqlo_bind_by_name(sth, ":min_sal", SQLOT_FLT, &sal, sizeof(sal), 0, 0) ) ) { error_exit(dbh, "sqlo_bind_by_name"); } else { /* execute the PL/SQL block */ if (SQLO_SUCCESS != sqlo_execute(sth, 1)) error_exit(dbh, "sqlo_execute"); } /* execute the second cursor */ if(SQLO_SUCCESS != sqlo_execute(st2h, 1)) error_exit(dbh, "sqlo_execute"); if (SQLO_SUCCESS != ( sqlo_define_by_pos(st2h, 1, SQLOT_STR, name, sizeof(name), &nind, 0, 0) ) || ( sqlo_define_by_pos(st2h, 2, SQLOT_FLT, &salary, sizeof(salary), &sind, 0, 0) ) ) { error_exit(dbh, "sqlo_define_by_pos"); } while (SQLO_SUCCESS == (status = sqlo_fetch(st2h, 1))) { printf("Name=%-8s Salary= %6.2f\n", name, salary); } if (status != SQLO_NO_DATA) error_exit(dbh, "sqlo_fetch(st2)"); if (SQLO_SUCCESS != sqlo_close(sth)) error_exit(dbh, "sqlo_close(1)"); if (SQLO_SUCCESS != sqlo_close(st2h)) error_exit(dbh, "sqlo_close(2)"); } else { error_exit(dbh, "sqlo_prepare"); } return 1; } /* $Id: ex18.c 221 2002-08-24 12:54:47Z kpoitschke $ */
int sqlo_define_by_pos | ( | sqlo_stmt_handle_t | sth, | |
int | value_pos, | |||
int | value_type, | |||
const void * | value_addr, | |||
unsigned int | value_size, | |||
short * | ind_addr, | |||
short * | rlen_addr, | |||
int | is_array | |||
) |
Define a output variable of the select list.
Use this to define the output variables.
If is_array is 1, the parameters value_addr, rlen_addr and ind_addr must point to arrays. ind_addr is optional and can be passed a NULL. Passing NULL is only usefull for NOT NULL columns. If you ommit the indicator and a NULL is fetched, sqlo_execute will fail with an Oracle error (FETCHED COLUMN VALUE IS NULL).
The value_size is still the size of one array element, not the whole array size!
sth | I - The statement handle | |
value_pos | I - The bind parameter position in the string. Starts with 1 for the first. | |
value_type | I - The datatype of the bind parameter (sqlo_data_types). | |
value_addr | I - The pointer to the parameter data. | |
value_size | I - The size of the object at param_addr in bytes. | |
ind_addr | I - The pointer to the NULL indicator variable (optional). | |
rlen_addr | I - The pointer where sqlo_execute writes the actual returned length. | |
is_array | I - 1 means param_addr points to an array, 0 means a single variable. |
/* $Id: ex11.c 221 2002-08-24 12:54:47Z kpoitschke $ */ #include <stdio.h> #include <stdlib.h> #include "examples.h" sqlo_stmt_handle_t do_select2(sqlo_db_handle_t dbh, double min_salary) { sqlo_stmt_handle_t sth; /* statement handle */ int status; /* return code of sqlo_... */ char name[64]; /* output variable for NAME */ short name_rlen; /* returned length of NAME */ short name_ind; /* NULL indicator for NAME */ short sal_ind; /* NULL indicator for SALARY */ double salary = min_salary; /* input variable for SALARY */ sth = prepare_cursor(dbh, &salary); /* see ex10.c */ /* define output */ if (SQLO_SUCCESS != (sqlo_define_by_pos(sth, 1, SQLOT_STR, name, sizeof(name), &name_ind, &name_rlen, 0)) || (sqlo_define_by_pos(sth, 2, SQLOT_FLT, &salary, sizeof(salary), &sal_ind, 0, 0))) { error_exit(dbh, "sqlo_define_by_pos"); } status = sqlo_execute(sth, 0); if ( 0 > status) error_exit(dbh, "sqlo_execute"); while ( SQLO_SUCCESS == (status = sqlo_fetch(sth, 1))) { printf("Name=%-8s Salary=%6.2f\n", name, salary); } if (status != SQLO_NO_DATA) error_exit(dbh, "sqlo_fetch"); return sth; } /* $Id: ex11.c 221 2002-08-24 12:54:47Z kpoitschke $ */
int sqlo_define_by_pos2 | ( | sqlo_stmt_handle_t | sth, | |
int | value_pos, | |||
int | value_type, | |||
const void * | value_addr, | |||
unsigned int | value_size, | |||
short * | ind_addr, | |||
unsigned short * | rlen_addr, | |||
unsigned short * | rcode_addr, | |||
unsigned int | skip_size | |||
) |
Define a output variable of the select list.
Use this to define where the result of the query should go. This new version supports filling arrays of structures. If skip_size is not 0, the parameter value_addr must point to an array of structures. If used, the structure must contain variables for ind, rlen and rcode.
The value_size is still the size of one array element, not the whole array size!
sth | I - The statement handle | |
value_pos | I - The bind parameter position in the string. Starts with 1 for the first. | |
value_type | I - The datatype of the bind parameter (sqlo_data_types). | |
value_addr | I - The pointer to the parameter data. | |
value_size | I - The size of the object at param_addr in bytes. | |
ind_addr | I - The pointer to the NULL indicator variable (optional). | |
rlen_addr | I - The pointer where library puts the actual return length. | |
rcode_addr | I - The address where the library puts the return code for the column | |
skip_size | I - In case into an array of structures, set to sizeof(your_struct), otherwise set it to 0. |
/* $Id: ex12.c 221 2002-08-24 12:54:47Z kpoitschke $ */ #include <stdio.h> #include <stdlib.h> #include "examples.h" enum { MAX_NAME_LEN = 64, MAX_ARRAY_SIZE = 4 /* Note: usually this should be 100 or so. * Used a smaller one to do test all paths in the code */ }; typedef struct _result_t { char name[MAX_NAME_LEN+1]; /* output ENAME */ double salary; /* output SAL */ short name_ind; /* indicator variable of ENAME */ short sal_ind; /* indicator variable of SAL */ unsigned short name_rlen; /* the actual length of ENAME */ unsigned short name_rcode; /* the return code of ENAME */ } result_t; void print_record __P((result_t * r)); int do_array_select(sqlo_db_handle_t dbh, double min_salary) { sqlo_stmt_handle_t sth; /* statement handle */ int i; /* loop variable */ int status; /* return code of sqlo_... */ result_t result[MAX_ARRAY_SIZE]; int rows_fetched; /* number of rows fetched per execute */ int rows_fetched_total = 0; /* total number of rows */ int done_fetching = 0; /* flag indicating end of fetch */ double salary = min_salary; /* input variable for SAL */ int skip_size = sizeof(result[0]); /* The skip size */ sth = prepare_cursor(dbh, &salary); /* see ex10.c */ /* define output */ if (SQLO_SUCCESS != (sqlo_define_by_pos2(sth, 1, SQLOT_STR, result[0].name, sizeof(result[0].name), &result[0].name_ind, &result[0].name_rlen, &result[0].name_rcode, skip_size)) || (sqlo_define_by_pos2(sth, 2, SQLOT_FLT, &result[0].salary, sizeof(result[0].salary), &result[0].sal_ind, 0, 0, skip_size))) { error_exit(dbh, "sqlo_define_by_pos"); } /* execute and fetch the result */ status = sqlo_execute(sth, 0); while (!done_fetching) { rows_fetched = MAX_ARRAY_SIZE; /* get the next set */ status = sqlo_fetch(sth, MAX_ARRAY_SIZE); if (0 > status) error_exit(dbh, "sqlo_execute(NEXT)"); else if (SQLO_NO_DATA == status) { rows_fetched = sqlo_prows(sth); /* sqlo_prows returns now the total number of fetched rows * the difference to the previous total fechted rows is * the number of rows fetched in this last call to sqlo_execute */ rows_fetched = rows_fetched - rows_fetched_total; done_fetching = 1; } /* print the records */ for (i = 0; i < rows_fetched; ++i) print_record(&result[i]); rows_fetched_total += rows_fetched; } printf("Selected %d employees\n", rows_fetched_total); /* finished. */ sqlo_close(sth); return 1; } /* print record */ void print_record(result_t * r /* I - The record */ ) { printf("Name=%-8s Salary= %6.2f\n", (r->name_ind == SQLO_NULL_IND ? "NULL" : r->name), (r->sal_ind == SQLO_NULL_IND ? -1.0 : r->salary)); } /* $Id: ex12.c 221 2002-08-24 12:54:47Z kpoitschke $ */
int sqlo_define_ntable | ( | sqlo_stmt_handle_t | sth, | |
unsigned int | pos, | |||
int * | sth2p | |||
) |
Define a nested table Please visit the example for details.
sth | I - The statement handle | |
pos | I - The define position of the nested table | |
sth2p | O - The new statement handle for the nested table. |
/* $Id: ex19.c 221 2002-08-24 12:54:47Z kpoitschke $ */ #include <stdio.h> #include <stdlib.h> #include "examples.h" int select_ntable(sqlo_db_handle_t dbh) { sqlo_stmt_handle_t sth = SQLO_STH_INIT; sqlo_stmt_handle_t st2h; /* handle of the ref cursor */ int status; char ename[11]; char dname[15]; char loc[14]; short eind, dind, lind; int deptno = 10; /* don't know why the bind variable for deptno causes a crash */ CONST char * stmt = "SELECT ENAME, CURSOR(SELECT DNAME, LOC FROM DEPT)\n" " FROM EMP WHERE DEPTNO = :deptno"; /* parse the statement */ if ( 0 <= (sth = sqlo_prepare(dbh, stmt))) { /* bind all variables */ if (SQLO_SUCCESS != (sqlo_bind_by_name(sth, ":deptno", SQLOT_INT, &deptno, sizeof(deptno), 0, 0) ) ) { error_exit(dbh, "sqlo_bind_by_name"); } /* Do the defines */ /* You could also do: sqlo_define_by_pos(sth, 2, SQLOT_RSET, &st2h, 0, 0, 0, 0) */ if (SQLO_SUCCESS != ( sqlo_define_by_pos(sth, 1, SQLOT_STR, ename, sizeof(ename), &eind, 0, 0) ) || ( sqlo_define_ntable(sth, 2, &st2h))) error_exit(dbh, "sqlo_define_ntable"); /* execute the main statement */ if (SQLO_SUCCESS != sqlo_execute(sth, 1)) error_exit(dbh, "sqlo_execute"); /* fetch from the main statement */ while (SQLO_SUCCESS == (status = sqlo_fetch(sth, 1))) { printf("ENAME=%11s\n", ename); /* define the output of the second cursor */ if (SQLO_SUCCESS != ( sqlo_define_by_pos(st2h, 1, SQLOT_STR, dname, sizeof(dname), &dind, 0, 0) ) || ( sqlo_define_by_pos(st2h, 2, SQLOT_STR, loc, sizeof(loc), &lind, 0, 0) ) ) { error_exit(dbh, "sqlo_define_by_pos"); } /* execute the cursor */ if(SQLO_SUCCESS != sqlo_execute(st2h, 1)) error_exit(dbh, "sqlo_execute"); /* fetch from the second cursor */ while (SQLO_SUCCESS == (status = sqlo_fetch(st2h, 1))) { printf(" DNAME=%15s LOC=%15s\n", dname, loc); } if (status != SQLO_NO_DATA) error_exit(dbh, "sqlo_fetch(st2)"); } if (SQLO_SUCCESS != sqlo_close(sth)) error_exit(dbh, "sqlo_close(1)"); if (SQLO_SUCCESS != sqlo_close(st2h)) error_exit(dbh, "sqlo_close(2)"); } else { error_exit(dbh, "sqlo_prepare"); } return 1; } /* $Id: ex19.c 221 2002-08-24 12:54:47Z kpoitschke $ */
int sqlo_execute | ( | sqlo_stmt_handle_t | sth, | |
unsigned int | iterations | |||
) |
Execute a statement.
Execute a PL/SQL block or a statement after you prepared it with sqlo_prepare and bound input and output variables. If you are fetching into arrays, you can set iterations to the actual array size. For PL/SQL blocks it must be set to 1.
sth | I - A parsed statement handle | |
iterations | I - How many times the statement should be exectuted. Must be 1 if you execute a PL/SQL block |
int sqlo_prepare | ( | sqlo_db_handle_t | dbh, | |
const char * | stmt | |||
) |
Parse a statement.
This functions must be used to parse a statement if you want to bind manually the parameters. By doing this you can use native datatypes. This is the more complex form of sqlo_open2.
If the stmt is a SELECT statement, the function sets the attribute OCI_ATTR_PREFETCH rows to the max arraysize parameter of the library. This is a kind of internal array fetch Oracle provides to speed up the fetching.
dbh | I - A database handle | |
stmt | I - sql statement. |