orafns.c

Oracle数据访问类

/* orafns.c

   "C" functions for simplified interface to Oracle OCI client
   library.

   Copyright (C) 2002 Alma Mater Software, Inc.
   Author: "John Kelley Hinsdale" <[e-mail]>

   This program is free software; you can redistribute it and/or modify
   it under the terms of the GNU General Public License version 2 as
   published by the Free Software Foundation; see file GNU-GPL.

   This program is distributed in the hope that it will be useful,
   but WITHOUT ANY WARRANTY; without even the implied warranty of
   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
   GNU General Public License for more details.

   You should have received a copy of the GNU General Public License
   along with this program; if not, write to the Free Software Foundation,
   Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307, USA.

   $Id: orafns.c,v 1.2 2002/09/29 12:01:58 hin Exp $

*/

/* Oracle OCI library */
#include <oci.h>

/* Unix calls (e.g., sprintf) */
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

/* Our exported functions and types */
#include "oiface.h"

/* Constants */

/* Default no. of bytes for row prefetch */
#define DEFAULT_PREFETCH_BYTES  1024 * 1024
/* Size of per-connection error buffer */
#define ERRBUF_BYTES            100000

/* Local routines, w/ wrappers passed to external interface */
static struct db_conn * connect(char *, char *, char *, char *, int, int);
static int              disconnect(struct db_conn *);
static int              exec_sql(struct db_conn *, char *, struct sqlparam **, int);
static int              fetch_row(struct db_conn *);
static int              ncol(struct db_conn *);
static int              eof(struct db_conn *);
static int              success(struct db_conn *);
static struct sqlcol ** column_info(struct db_conn *);
static struct sqlval ** row_values(struct db_conn *);
static int              rows_affected(struct db_conn *);
static int              commit(struct db_conn *);
static int              rollback(struct db_conn *);
static int              set_auto_commit(struct db_conn *, int);

/* Purely local routines */
static void      append_oci_error(char *, OCIError *);
static int       init_session(struct db_conn *, char *, char *, char *, char*, int);
static int       get_cols(struct db_conn *);
static int       get_param_attr(CONST dvoid *, int, struct db_conn *, dvoid *, ub4 *, char *, ub4);
static char *    decode_data_type(int);
static int       fetch_data_len(int, int);
static void      free_columns(struct db_conn *);
static void      free_column(struct column *);
static void      free_if_non_null(void *);
static void      reset_db_conn(struct db_conn *);
static int       check_active_statement(struct db_conn *, char *);
static int       check_active_select(struct db_conn *, char *);
static int       empty(char *);
static char *    valid_string(char *);

/* Static indicator variables for input data */
static sb4      null_indicator = -1;
static sb4      non_null_indicator = 0;

/* ------------------------------------------------------------------------------------------------------------- */

/* Main driver */
#if 0
int main(int argc, char **argv) {

  struct db_conn *  db = 0;

  int           success = 0;
  char *        sql     = 0;
  
  int               nrow            = 0;
  int               n               = 0;
  ub4               ncol            = 0;
  struct column *   col             = 0;

  int               is_command      = 0;

  /* Check usage */
  if ( argc != 7 ) {
    printf("Usage: %s <user> <schema> <pass> <sid> <cmdflag> <sql>\n", argv[0]);
    exit(1);
  }

  /* Connect to server */
  db = connect(argv[1], argv[2], argv[3], argv[4],
                      -1,   /* Prefetch bytes */
                      1);   /* Auto-commit? */
  if ( ! db ) {
    /* Should never really get this */
    printf("Error initting connection\n");
    return 0;
  }
  else if ( ! db->success ) {
    printf("%s", db->errmsg);
    return 0;
  }

  /* Execute SQL query or command */
  is_command = atoi(argv[5]);
  success = exec_sql(db, argv[6], is_command);
  if ( ! success ) {
    printf("Error: %s\n", db->errmsg);
    return 0;
  }

  /* Fetch data rows - data will show up in the "column" structs above */
  while ( 1 ) {

    int eof = 0;
    
    success = fetch_row(db);
    if ( ! success ) {
      strcat(db->errmsg, "A row-fetch error occured.\n");
      return 0;
    }
    else if ( success == 2 ) /* EOF */
      break;

    /* Successful fetch.  Extract column data */
    printf("-------  ROW ----------\n");
    nrow++;
  
    if ( nrow % 5000 == 0 )
      printf("Fetched %d rows ...\n", nrow);

    for ( n=0; n < db->ncol; n++ ) {
    
      col = &db->columns[n];
      
      printf("%-30s %4d %4d ", col->name, col->indicator, strlen(col->data));
      
      /* First check if null */
      if ( col->indicator == -1 ) {
        printf("<NULL>\n");
        continue;
      }
      /* Should never really get the other two cases (-2 = really huge, or positive truncation) */
      else if ( col->indicator != 0 ) {
        sprintf(db->errmsg, "Got unexpected indicator value %d in column '%s'\n", col->indicator, col->name);
        return 0;
      }
    
      /* Should have the result as a null-terminated string */
      printf("'%s'\n", (char *) col->data);
    }
  }

  printf("Fetched %d rows\n", nrow);

  /* Success */ 
  return 0;
}
#endif

/* ------------------------------------------------------------------------------------------------------------- */

/* Init a new connection, given user, schema, password and SID */

static struct db_conn * connect(char * user, char * schema, char * password, char * sid, int prefetch_bytes, int auto_commit)
{
  sb4       status  = OCI_SUCCESS;

  /* Set up the Oracle environment */
  struct db_conn * db = (struct db_conn *) malloc(sizeof (struct db_conn));
  if ( ! db )
    return 0;

  /* Init everything to sane values */
  reset_db_conn(db);

  /* First alloc the error message */
  db->errmsg = (char *) malloc(ERRBUF_BYTES);
  if ( ! db->errmsg )
    return 0;

  /* Connect to database */
  db->success = init_session(db, user, schema, password, sid, 0);
  if ( ! db->success )
    return db;

  /* Copy buffer size and auto-commit */
  db->prefetch_bytes = prefetch_bytes;
  db->auto_commit = auto_commit;

  /* Success */
  db->success = 1;
  return db;
};

/* ------------------------------------------------------------------------------------------------------------- */

  
/* Fetch a row of data.  Data will be placed in the structures pointed
   to by columns[] array.  Return 0 for failure, 1 for fetch, 2 for EOF
   Once EOF is returned, it is an error to fetch again.
*/
static int fetch_row(struct db_conn * db)
{

  int               null            = 0,    /* Flag: null column(s) exist in fetched row */
                    truncated       = 0;    /* Flag: truncated column(s) exist in fetched row */

  struct column *   col             = 0;
  int               n               = 0;
  sb4               fetch_status    = OCI_SUCCESS;
  
  /* Clear out results */
  *db->errmsg = '\0';
  db->success = 0;
  
  /* Make sure we have a connection, and active SELECT */
  if ( ! check_active_select(db, "fetch result row") )
    return db->success = 0;

  /* Reset fetch info for all columns (Oracle does not do it for us) */
  for ( n=0; n < db->ncol; n++ ) {
    col = &db->columns[n];
    *((char *) col->data) = '\0';
    col->indicator = 0;
    col->nfetched = 0;
  }
  
  /* Init row data if not already.  The presence of a non-NULL
     db->currow indicates the first fetch has been called.  Some
     functions are not valid until that time. */
  if ( ! db->currow ) {
    db->currow = (struct sqlval **) malloc((db->ncol + 1) * sizeof(struct sqlval *));
    for ( n=0; n < db->ncol; n++ ) {
      struct sqlval * r = (struct sqlval *) malloc(sizeof(struct sqlval));

      r->data     = db->columns[n].data;
      r->is_null      = 0;
      db->currow[n] = r;
    }
    /* Terminate pointer array */
    db->currow[n] = 0;
  }

  /* Make sure not already at EOF */
  if ( db->eof ) {
    sprintf(db->errmsg,"Fetch: already at EOF\n");
    return db->success = 0;
  }

  /* Get one row at a time */
  fetch_status = OCIStmtFetch(db->stmt, db->err, 1, OCI_FETCH_NEXT, OCI_DEFAULT);
  
  if ( fetch_status == OCI_NO_DATA ) {
    /* EOF */
    db->eof = 1;
    return db->success = 2;
  }
  
  if ( fetch_status != OCI_SUCCESS && fetch_status != OCI_SUCCESS_WITH_INFO ) {
    /* Error fetching */
    sprintf(db->errmsg, "Fetch error %d:\n", fetch_status);
    append_oci_error(db->errmsg, db->err);
    return db->success = 0;
  }
  
  /* If have success "with info" could mean null value, truncated
     data, or EOF.  Error codes are 01405 (null), 01406 (truncated
     data) and 01403 (EOF).  For null and truncated data, have to
     look at each column's "indicator variable" for what
     happened. */
  
  if ( fetch_status == OCI_SUCCESS_WITH_INFO ) {
    char * dummy = 0;
    sb4 errcode = 0;
    sb4 err_status = OCIErrorGet(db->err, 1, dummy, &errcode, 0, 0, OCI_HTYPE_ERROR);
    if ( err_status != OCI_SUCCESS ) {
      sprintf(db->errmsg, "Error getting error status checking for EOF\n");
      return db->success = 0;
    }
    
    /* Check for EOF */
    if ( errcode == 1403 ) {
      db->eof = 1;
      return db->success = 2; /* EOF */
    }
    /* Null value(s) */
    else if ( errcode == 1405 )
      null = 1;
    /* Truncated value(s) */
    else if ( errcode == 1406 )
      truncated = 1;
    /* Null OR truncated */
    else if ( errcode == 24345 ) {
      truncated = 1;
      null = 1;
    }
    else {
      sprintf(db->errmsg, "Unknown Oracle warning %d\n", errcode);
      append_oci_error(db->errmsg, db->err);
      return db->success = 0;
    }
  }
  
  /* Copy is-null indicator status to external structures.  */
  for ( n=0; n < db->ncol; n++ ) {
    struct sqlval * r = db->currow[n];
    col = &db->columns[n];
    if ( col->indicator == 0 )
      r->is_null = 0;
    else if ( col->indicator == -1 )
      r->is_null = 1;
    else {
      sprintf(db->errmsg, "Bad indicator value %d for column %d\n", col->indicator, n);
      append_oci_error(db->errmsg, db->err);
    }
  }
  
  /* Maintain row count */
  db->rows_affected++;

  /* Success */
  return db->success = 1;
}

/* ------------------------------------------------------------------------------------------------------------- */

/* Get column info for a SELECT query just executed by stmt handle.
   Get column type info and number of columns.   */

static int get_cols(struct db_conn * db)
{
  sb4               attr_status         = OCI_SUCCESS;
  sb4               param_status        = OCI_SUCCESS;
  OCIParam *        param               = 0;
  ub4               param_count         = 0;
  ub4               ncol                = 0;
  struct column *   col                 = 0;
  text *            colnamep            = 0;
  ub4               colname_len         = 0;
  char              colname[10000];
  int               fetch_buflen        = 0;
  int               success             = 0;
  sb4               define_status       = OCI_SUCCESS;

  /* Clear out previous results */
  free_columns(db);
  db->ncol = 0;
  *db->errmsg = '\0';

  /* Get the number of columns in the output.  We also will calculate
     this as we retreive parameter info below, but having its value
     now is useful for allocating buffers. */
  attr_status = OCIAttrGet((dvoid *) db->stmt, OCI_HTYPE_STMT, &param_count, 0, OCI_ATTR_PARAM_COUNT, db->err);
  if ( attr_status != OCI_SUCCESS ) {
    /* Error getting param info */
    sprintf(db->errmsg, "Error getting param count:\n");
    append_oci_error(db->errmsg, db->err);
    return db->success = 0;
  }

  /* Allocate buffers for column info */
  db->ncol = param_count;
  db->columns = (struct column *) malloc(db->ncol * sizeof(struct column));
  
  /* Successfully parsed: now get the names, types and lengths of the result columns */
  ncol = 0;

  do {

    ncol++;
    param_status = OCIParamGet(db->stmt, OCI_HTYPE_STMT, db->err, (dvoid **) &param, ncol);

    /* See if done -- the Oracle docs say that OCIParamGet() will
       return OCI_NO_DATA (== 100) when at the end of the parameters.
       However by observation, it seems to really return status of
       OCI_ERROR, with an error context of "ORA-24334: no descriptor
       for this position".  So we have to check the error code in the
       error handle rather than the return status of OCIParamGet().
       What a pain in the ass.  */

    /* This is what should normally happend according to Oracle docs */
    if ( param_status == OCI_NO_DATA )
      break;
    else if ( param_status != OCI_SUCCESS ) {

      /* Error getting param info -- might not be an error if the
         error code is 24334 ("no descriptor for this position").
         Check if that is the case and break normally. */

      char * dummy = 0;
      sb4 errcode = 0;
      sb4 err_status = OCIErrorGet(db->err, 1, dummy, &errcode, 0, 0, OCI_HTYPE_ERROR);
      if ( err_status != OCI_SUCCESS ) {
        sprintf(db->errmsg, "Error getting error status checking OCI_NO_DATA on col %d\n", ncol);
        return db->success = 0;
      }
      else if ( errcode == 24334 ) {
        /* OK, really a "no more data" situation */
        break;
      }
      else {
        /* A real error */
        sprintf(db->errmsg, "Error getting param col %d:\n", ncol);
        append_oci_error(db->errmsg, db->err);
        return db->success = 0;
      }
    }
      
    /* Set up for a new column */

    /* Make sure we are not overflowing our buffer (i.e., that Oracle
     is giving us more columns than it said before it has) */
    if ( ncol > param_count ) {
      sprintf(db->errmsg, "Error: more than expected count of %d columns returned", param_count);