Frank4DD, @2008
Introduction
The following 'C' example program can make SQL test queries against a Oracle database, using the sqlora library functions. It also demonstrates how to use the sqlora interface with Oracle.
/* -------------------------------------------------------------------------- *
* file: oratest.c *
* purpose: tests SQL query of a Oracle database with libsqlora *
* author: 04/23/2006 Frank4DD *
* *
* gcc -O3 -Wall -g -I/home/oracle/include -Wall -c -o oratest.o oratest.c *
* ^^^^^^^^^^^^^^^^^^^^^ *
* location of sqlora.h and Oracle instantclient headers *
* *
* gcc -L/home/oracle/lib -L/home/oracle/lib32 -lclntsh -lsqlora8 oratest.o \ *
* ^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^ *
* location of libsqlora location of instantclient libs *
* *
* -o oratest *
* -------------------------------------------------------------------------- */
#include <stdio.h>
#include <stdlib.h>
#include <sqlora.h>
static int _abort_flag = 0;
#define TESTTABLE "DUAL"
#define TNS_STRING "scott/tiger@work1"
#define ORACLEHOME_ENV "ORACLE_HOME=/home/oracle"
#define error_exit(_dbh, _msg) do_error_exit(_dbh, __FILE__, __LINE__, _msg)
#define RETURN_ON_ABORT if (_abort_flag) { sqlo_rollback(dbh);
return EXIT_FAILURE; }
/* function headers */
void sigint_handler(void);
void do_error_exit __P((sqlo_db_handle_t dbh, CONST char *file, int line,
CONST char * msg));
int col_count __P((sqlo_db_handle_t dbh, char * table_name));
/* functions */
void sigint_handler(void) {
printf("Catched SIGINT\n");
_abort_flag++;
}
/* col_count() returns how many columns are in a table */
int col_count(sqlo_db_handle_t dbh, char * table_name) {
int stat;
if ( 0 > (stat = sqlo_count(dbh, "USER_TAB_COLUMNS", "TABLE_NAME",
table_name, NULL)))
error_exit(dbh, "sqlo_count");
return stat;
}
/* do_error_exit() prints error and exits with failure code */
void do_error_exit(sqlo_db_handle_t dbh, const char * file,
int line, const char * msg) {
printf("%s (line: %d):\n%s: %s\n", file, line, msg, sqlo_geterror(dbh));
sqlo_rollback(dbh);
sqlo_finish(dbh);
exit(EXIT_FAILURE);
}
int main() {
sqlo_db_handle_t dbh; /* database handle */
int stat; /* status of sqlo calls */
sqlo_stmt_handle_t sth; /* statement handle */
char server_version[1024]; /* string for returned server version */
char sqlquery_str[1024]; /* SQL query string */
int handle; /* handle of the interrupt handler */
const char ** v; /* values */
const char ** n; /* column names */
CONST unsigned int *nl; /* column name lengths */
CONST unsigned short *vl; /* value lengths */
unsigned int nc; /* number of columns */
unsigned int i, j; /* loop variable, why is it always i? */
/* ------------------------------------------------------------------------ *
* ORACLE_HOME is needed for OCI8 to find tnsnames.ora *
* ------------------------------------------------------------------------ */
putenv(ORACLEHOME_ENV);
/* initialize the library */
if (SQLO_SUCCESS != sqlo_init(SQLO_OFF, 1, 100)) {
printf("Failed to init libsqlora8\n");
return EXIT_FAILURE;
}
/* register the interrupt handler */
sqlo_register_int_handler(&handle, sigint_handler);
/* login to the database */
if (SQLO_SUCCESS != sqlo_connect(&dbh, TNS_STRING)) {
printf("Cannot login with %s\n", TNS_STRING);
return EXIT_FAILURE;
}
RETURN_ON_ABORT; /* finish if SIGINT was catched */
if (SQLO_SUCCESS != sqlo_server_version(dbh, server_version,
sizeof(server_version))) {
printf("Failed to get the server version: %s\n", sqlo_geterror(dbh));
return EXIT_FAILURE;
}
RETURN_ON_ABORT; /* finish if SIGINT was catched */
printf("Connected to:\n%s\n\n", server_version);
/* ------------------------------------------------------------------------ *
* Normally we can check if the table exists before we query. Since we test *
* against DUAL which is not a user table, the test for it in USER_TABLES *
* would fail. *
* ------------------------------------------------------------------------ */
/* ------------------------------------------------------------------------ *
* Prepare and execute the SQL command here: *
* ------------------------------------------------------------------------ */
snprintf(sqlquery_str, sizeof(sqlquery_str), "SELECT * FROM %s", TESTTABLE);
/* get the statement handle for the SQL query */
sth = SQLO_STH_INIT;
if ( 0 > (sqlo_open2(&sth, dbh, sqlquery_str, 0, NULL)))
error_exit(dbh, "sqlo_open");
/* get the output column names */
n = sqlo_ocol_names(sth, &nc);
/* get the output column name lengths */
nl = sqlo_ocol_name_lens(sth, NULL);
printf("number of output columns: %d \n\n", nc);
/* print the table column header(s) */
for (i = 0; i < nc; ++i) printf("%-*s ", nl[i], n[i]);
printf("\n");
for (i = 0; i < nc; ++i) {
for (j = 0; j < nl[i]; ++j) putchar('-');
putchar('+');
}
putchar('\n');
/* fetch the data */
while ( SQLO_SUCCESS == (stat = (sqlo_fetch(sth, 1)))) {
/* get one record */
v = sqlo_values(sth, NULL, 1);
/* get the length of the data items */
vl = sqlo_value_lens(sth, NULL);
/* print the column values */
for (i = 0; i < nc; ++i)
printf("%-*s ", (vl[i] > nl[i] ? vl[i] : nl[i]), v[i]);
printf("\n");
}
if (0 > stat)
error_exit(dbh, "sqlo_fetch");
if ( SQLO_SUCCESS != sqlo_close(sth))
error_exit(dbh, "sqlo_close");
return 1;
}
The test program output with TESTTABLE "DUAL":
susie:/home/oracle/src # ./oratest Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options number of output columns: 1 DUMMY -----+ X
The test program output with TESTTABLE "EMP":
susie:/home/oracle/src # ./oratest Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options number of output columns: 8 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO -----+-----+---+---+--------+---+----+------+ 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10