orafns.c

Oracle数据访问类

      return db->success = 0;
    }
    
    col = &db->columns[ncol-1];

    /* Get param attributes */
    success = 1;
    success = success && get_param_attr(param, ncol, db, &colnamep,       &colname_len, "name",      OCI_ATTR_NAME);
    success = success && get_param_attr(param, ncol, db, &col->dtype,     0,            "data type", OCI_ATTR_DATA_TYPE);
    success = success && get_param_attr(param, ncol, db, &col->dsize,     0,            "size",      OCI_ATTR_DATA_SIZE);
    success = success && get_param_attr(param, ncol, db, &col->precision, 0,            "precision", OCI_ATTR_PRECISION);
    success = success && get_param_attr(param, ncol, db, &col->scale,     0,            "scale",     OCI_ATTR_SCALE);
    success = success && get_param_attr(param, ncol, db, &col->null_ok,   0,            "is-null",   OCI_ATTR_IS_NULL);
    if ( ! success )
      return db->success = 0;

    /* Copy column name to our buffer */
    memcpy(colname, colnamep, colname_len);
    colname[colname_len] = '\0';
    col->name = (char *) strdup(colname);

    /*
      printf("Col %2d: type is %-10s name = %-20s size = %4d precision = %2d scale = %2d null-ok=%d\n",
             ncol, decode_data_type(col->dtype), col->name, col->dsize, col->precision, col->scale, col->null_ok);
    */

    /* Set up a define variable for this col to get its fetched value.
       Note that we ask Oracle to convert everything to
       null-terminated string, SQLT_STR. */
    fetch_buflen = fetch_data_len(col->dtype, col->dsize);
    col->data = malloc(fetch_buflen);
    *((char *) col->data) = '\0';
    col->indicator = 0;
    col->nfetched = 0;
    col->rcode = 0;
    define_status = OCIDefineByPos(db->stmt, &col->def, db->err, ncol, col->data, fetch_buflen, SQLT_STR,
                                   &col->indicator, &col->nfetched, &col->rcode, OCI_DEFAULT);
    if ( define_status != OCI_SUCCESS ) {
      sprintf(db->errmsg, "Error setting up define for column '%s':\n", col->name);
      append_oci_error(db->errmsg, db->err);
      return db->success = 0;
    }
  }
  while ( param_status == OCI_SUCCESS );

  /* Make sure the no. of params we extracted is the same as what Oracle thinks it has */
  if ( ncol - 1 != param_count ) {
    sprintf(db->errmsg, "Column count mismatch - we have %d and Oracle had %d\n", ncol - 1, param_count);
    return db->success = 0;
  }
  
  /* Copy column info to structures suitable for returning to external
     interface.  Allocate ncol+1 struct pointers, and terminate w/ a
     zero pointer. */
  db->sqlcols = (struct sqlcol **) malloc((db->ncol + 1) * sizeof(struct sqlcol *));
  for ( ncol=0; ncol < db->ncol; ncol++ ) {
    struct sqlcol * e = (struct sqlcol *) malloc(sizeof(struct sqlcol));
    struct column * c = &db->columns[ncol];

    e->name         = c->name;
    e->type         = decode_data_type(c->dtype);
    e->size         = c->dsize;
    e->scale        = c->scale;
    e->precision    = c->precision;
    e->null_ok      = c->null_ok;

    db->sqlcols[ncol] = e;
  }
  /* Terminate pointer array */
  db->sqlcols[ncol] = 0;
            
  /* Success */
  return db->success = 1;
}

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

/* Commit the current transaction */

static int commit(struct db_conn * db)
{
  sword status  = OCI_SUCCESS;

  *db->errmsg = '\0';
  db->success = 0;
 
  status = OCITransCommit(db->svc, db->err, OCI_DEFAULT);
  if ( status != OCI_SUCCESS ) {
    sprintf(db->errmsg, "Error commiting transaction:\n");
    append_oci_error(db->errmsg, db->err);
    return db->success = 0;
  }

  return db->success = 1;
}

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

/* Rollback (abort) the current transaction */

static int rollback(struct db_conn * db)
{
  sword status  = OCI_SUCCESS;

  *db->errmsg = '\0';
  db->success = 0;
  
  /* Make sure we are not in "auto-commit" mode */
  if ( db->auto_commit ) {
    sprintf(db->errmsg, "Rollback called while autocommit turned on.");
    return db->success = 0;
  }

  status = OCITransRollback(db->svc, db->err, OCI_DEFAULT);
  if ( status != OCI_SUCCESS ) {
    sprintf(db->errmsg, "Error rolling back transaction:\n");
    append_oci_error(db->errmsg, db->err);
    return db->success = 0;
  }

  return db->success = 1;
}

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

/* Prepare and execute a SQL command.  Return a new statment handle,
   which MUST be eventually OCIHandleFree()'s by the caller.
*/
static int exec_sql(struct db_conn * db, char * sql, struct sqlparam ** params, int is_command)
{
  sword                 status  = 0;
  ub4                   iters   = 0;
  int                   nparam  = 0;
  struct sqlparam **    params_save = params;
  
  /* Clear out results */
  *db->errmsg = '\0';
  db->success = 0;

  /* Reset EOF indicator  and row count */
  db->eof = 0;
  db->rows_affected = 0;
  
  /* Make sure we are connected */
  if ( ! db->svc ) {
    sprintf(db->errmsg, "Attempt to run SQL statement when not connected");
    return db->success = 0;
  }

  /* For SELECT's do zero iters (just parse), for destructive commands, do one */
  iters = is_command ? 1 : 0;

  /* Parse the SQL query or command */
  if ( empty(sql) ) {
    sprintf(db->errmsg, "Null SQL query or command given");
    return db->success = 0;
  }

  /* Save the SQL away for later reporting */
  if ( db->sql )
    free(db->sql);
  db->sql = (char *) strdup(sql);
  db->is_command = is_command;
  
  /* Get a new statement handle - ISSUE: will Oracle roll back an
     active transaction currently in progress with the old handle?  It
     probably should.  */
  if ( db->stmt ) {
	/* Note - freeing the statement handle recursively frees its
	   children "define" handles, and since we may have kept around
	   copies of those handles, we need to clear out our copies
	   first. */
	free_columns(db);
    OCIHandleFree(db->stmt, OCI_HTYPE_STMT);
    db->stmt = 0;
  }
  if ( OCI_SUCCESS !=  OCIHandleAlloc(db->env, (dvoid **) &db->stmt, OCI_HTYPE_STMT, 0, 0) ) {
    sprintf(db->errmsg, "Error allocating statement handle\n");
    append_oci_error(db->errmsg, db->err);
    return db->success = 0;
  }

  /* "Prepare" the statement - this rarely returns an error */
  status = OCIStmtPrepare(db->stmt, db->err, sql, strlen(sql), OCI_V8_SYNTAX, OCI_DEFAULT);
  if ( status != OCI_SUCCESS ) {
    sprintf(db->errmsg, "Error parsing SQL text:\n%s\n", sql);
    append_oci_error(db->errmsg, db->err);
    return db->success = 0;
  }

  /* Bind the input parameters - need to do this after the "prepare"
     so Oracle knows what vars there are */
  while ( params && *params ) {

    OCIBind *           dummy   = 0;
    dvoid *             indp    = 0;
    struct sqlparam *   p       = (struct sqlparam *) *params;
    char *              pname   = p->name;
    struct sqlval *     val     = &p->value;
    dvoid *             data    = 0;
    
    if ( empty(pname) ) {
      sprintf(db->errmsg, "Null bind-parameter name given\n");
      return db->success = 0;
    }
    
    if ( ! val->data || val->is_null ) {
      indp = (dvoid *) &null_indicator;
      data = (dvoid * ) "";     /* Shouldn't really need this */
    }
    else {
      indp = (dvoid *) &non_null_indicator;
      data = val->data;
    }
    
    status = OCIBindByName(db->stmt,                /* Statment handle */
                           &dummy,                  /* Output bind handle (Oracle will free it with db->stmt) */
                           db->err,                 /* Error handle */
                           (text *) pname,          /* Param name */
                           (sb4) strlen(pname),     /* Param len */
                           (dvoid *) data,          /* Data value */
                           (sb4) strlen(data) + 1,  /* Data len */
                           SQLT_STR,                /* Data type */
                           indp,                    /* Null indicator */
                           0, 0, 0, 0,              /* alenp, rcodep, maxarra_len, curelep */
                           OCI_DEFAULT);            /* Mode */

    if ( status != OCI_SUCCESS ) {
      sprintf(db->errmsg, "Error binding SQL input parameter '%s' to value '%s' in query:\n---\n%s\n---\n",
              pname, data, sql);
      append_oci_error(db->errmsg, db->err);
      return db->success = 0;
    }

    nparam++;
    params++;
  }

  /* "Execute" the statement - here is where an error is signalled if there is a SQL syntax error */
  status = OCIStmtExecute(db->svc, db->stmt, db->err, iters,
                          0,        /* Row offset */
                          0, 0,     /* Snapshot in/out */
                          OCI_DEFAULT);

  if ( OCI_SUCCESS != status ) {

    ub4 err_offset = 0;

    sprintf(db->errmsg, "Error executing SQL %s:\n---\n%s\n---\n", is_command ? "command" : "query", sql);

    /* Also show what params were given */
    if ( nparam ) {
      int i;
      char buf[50000], buf2[10000];

      sprintf(buf, "SQL bind parameters (%d) given:\n", nparam);
      strcat(db->errmsg, buf);
      params = params_save;
      for (i=0; i < nparam; i++) {
        sprintf(buf, "'%s' -> '%s'\n", params[i]->name, params[i]->value.data);
        strcat(db->errmsg, buf);
      }
      strcat(db->errmsg, "---\n");
    }
    
    append_oci_error(db->errmsg, db->err);

    /* Get the parse error offset so we can print an indicator as to
       where the error occurred */
    status = OCIAttrGet(db->stmt, OCI_HTYPE_STMT, (dvoid *) &err_offset, (ub4 *) 0, OCI_ATTR_PARSE_ERROR_OFFSET, db->err);
    if ( OCI_SUCCESS != status ) {
      strcat(db->errmsg, "Could not get parse error offset:\n");
      append_oci_error(db->errmsg, db->err);
    }
    else {
      char p[100];
      sprintf(p, "At character %d, near ==> indicator in:\n----\n", err_offset);
      strcat(db->errmsg, p);

      strncat(db->errmsg, sql, err_offset);
      strcat(db->errmsg, "\n==> ");
      strcat(db->errmsg, sql + err_offset);
      strcat(db->errmsg, "\n----\n");
    }
    return db->success = 0;
  }

  /* For query, get column info and set up for fetching */
  if ( ! is_command ) {
    if ( ! get_cols(db) ) {
      strcat(db->errmsg, "An error occurred getting column info.\n");
      return db->success = 0;
    }
  }
  /* For command, commit (if auto-commit is on) and get rows affected */
  else {
    sb4 attr_status = OCI_SUCCESS;

    /* Commit */
    if ( db->auto_commit && ! commit(db) ) {
      strcat(db->errmsg, "Error auto-commiting transaction.\n");
      return db->success = 0;
    }

    /* Get rows affected */
    db->rows_affected = 0;
    attr_status = OCIAttrGet((dvoid *) db->stmt, OCI_HTYPE_STMT, &db->rows_affected, 0, OCI_ATTR_ROW_COUNT, db->err);
    if ( attr_status != OCI_SUCCESS ) {
      sprintf(db->errmsg, "Error getting row count for command:\n");
      append_oci_error(db->errmsg, db->err);
      return db->success = 0;
    }
  }

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

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

/* Connect to Oracle, setting up the handles.  Return true for
   success, false for error.  On success, result handles are set.
   On error, the error message is filled in saying what went wrong.
*/
static int init_session(struct db_conn * db, char *user, char * schema, char *password, char *sid, int prefetch_bytes)
{

  sword         status  = 0;        /* Error status return from OCI calls */
  int           success = 0;
  ub4           attval  = 0;

  char sqlbuf[1000];
  
  /* Clear out results */
  db->success = 0;

  /* If already connected, drop and re-connect */
  if ( db->env )
    disconnect(db);

  /* Clear out results */
  *db->errmsg = '\0';
  db->env = 0;
  db->err = 0;
  db->svc = 0;
  
  /* VALIDATE INPUTS */
  /* Default schema to userid */
  if ( empty(schema) )
    schema = user;

  /* Check user, passwd and SID are non-null */
  sid = valid_string(sid);
  password = valid_string(password);
  if ( empty(user) ) {
    sprintf(db->errmsg, "Null user ID given connecting to Oracle server '%s'", sid);
    return db->success = 0;
  }
  
  /* Init Oracle library */
  /* TODO: use mode OCI_OBJECT so that we can describe tables w/ BLOB cols */
  status = OCIInitialize(OCI_OBJECT,
                         0,         /* Context for mode OCI_OBJECT */
                         0,         /* Our malloc() func */
                         0,         /* Our realloc() func */
                         0);        /* Our free() func */

  /* Init an Oracle "environment handle" */
  status = OCIEnvInit(&db->env,     /* Returned: new handle */
                      OCI_DEFAULT,  /* Use mutexes */
                      0,            /* Our memory size */
                      0);           /* Returned: alloc'ed memory */
  if ( OCI_SUCCESS != status ) {
    sprintf(db->errmsg, "Error initializing OCI library: OCIEnvInit() returned %s", status);
    return db->success = 0;
  }

  /* Get an error handle */
  status = OCIHandleAlloc(db->env, (dvoid **) &db->err, OCI_HTYPE_ERROR, 0, 0);
  if ( OCI_SUCCESS != status ) {
    sprintf(db->errmsg, "Error initializing OCI error handle: OCIHandleAlloc(OCI_HTYPE_ERROR) returned %s", status);
    OCIHandleFree(db->env, OCI_HTYPE_ENV);
    db->env = 0;
    return db->success = 0;
  }

  /* Connect to the database */
  status = OCILogon(db->env, db->err, &db->svc,
                    user, strlen(user), password, strlen(password), sid, strlen(sid));
  if ( OCI_SUCCESS != status ) {
    sprintf(db->errmsg, "Error logging on to Oracle service '%s' as user '%s':\n", sid, user);
    append_oci_error(db->errmsg, db->err);
    OCIHandleFree(db->env, OCI_HTYPE_ENV);
    db->env = 0;
    return db->success = 0;
  }

  /* Save login params for later error reporting */
  db->user      = (char *) strdup(user);
  db->schema    = (char *) strdup(schema);
  db->sid       = (char *) strdup(sid);
  
  /* Set up for ANSI date formatting. */
  success = exec_sql(db, "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'", 0, 1);
  if ( ! success ) {
    strcat(db->errmsg, "Error setting date format\n");
    return db->success = 0;
  }

  /* Switch to given schema, if given */
  if ( schema && *schema ) {
    sprintf(sqlbuf, "ALTER SESSION SET CURRENT_SCHEMA = %s", schema);
    success = exec_sql(db, sqlbuf, 0, 1);
    if ( ! success ) {
      strcat(db->errmsg, "Error setting current schema\n");
      return db->success = 0;
    }
  }

  /* Set pre-fetch memory and row count for SELECT queries */
  if ( prefetch_bytes < 0 )
    attval = DEFAULT_PREFETCH_BYTES;
  else
    attval = prefetch_bytes;

  status = OCIAttrSet(db->stmt, OCI_HTYPE_STMT, &attval, 0, OCI_ATTR_PREFETCH_MEMORY, db->err);
  if ( status != OCI_SUCCESS ) {
    sprintf(db->errmsg, "Error %d setting prefetch memory:\n", status);