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

Source:

See also: