Logo Search packages:      
Sourcecode: netams version File versions  Download package

st_sql_oracle.c

/*************************************************************************
***   Authentication, authorization, accounting + firewalling package
***   Copyright 1998-2002 Anton Vinokurov <anton@netams.com>
***   Copyright 2002-2008 NeTAMS Development Team
***   This code is GPL v3
***   For latest version and more info, visit this project web page
***   located at http://www.netams.com
***
*************************************************************************/
/* $Id: st_sql_oracle.c,v 1.34 2009-08-01 09:23:55 anton Exp $ */

#ifdef USE_ORACLE

#include "netams.h"
#include <oci.h>
#include "st_any.h"

//////////////////////////////////////////////////////////////////////////
void *ora_stOpenSql(struct sql_config *cfg,st_conn_type type);
void *ora_stCheckSql(void *fd);
unsigned ora_stSaveSql(void *fd, char *filename, st_conn_type type);
void ora_stCloseSql(void *fd);
u_char ora_stObtainDbData(void *fd, char *query,
      void (*FillData)(void*, void* ,char* (*getRowData)(void*, void* , u_char)));
//////////////////////////////////////////////////////////////////////////
struct sql_data ora_sql_data = {
      0,
      &ora_stOpenSql,
      &ora_stCheckSql,
      &ora_stSaveSql,
      &ora_stCloseSql,
      &ora_stObtainDbData
};
//////////////////////////////////////////////////////////////////////////
char* ora_getRowData(void *res, void *row, u_char num) {
      /* aLog(DEBUG_STORAGE, "getRowData() = %s\n", (*(char ***)row)[num]); */
      return (*(char ***)row)[num];
}
//////////////////////////////////////////////////////////////////////////
/*
 * OCI is bit different from MySQl/PG in programming model.
 * First, we must allocate OCI environment, in which all
 * OCI calls will be executed. This can be global var, OCI
 * will take care of proper locking.
 *
 */

#define ORA_PREFETCH_ROWS   100
#define DEF_DATASTR_LEN     128

static OCIEnv *g_env;
/* we need protect initialization of g_env */
static pthread_mutex_t g_env_mtx = PTHREAD_MUTEX_INITIALIZER;
/* here goes Oracle version */
char    oraver[512];

typedef struct oracle_conn {
    OCISvcCtx *svcctx;
    OCIError *errh;
      OCIStmt  *stmth;
    OCIServer *srvh;
    char **row;
} ORAconn;

static int  ora_select(ORAconn *conn, char *query);
static int  ora_execute(ORAconn *conn, char *query);
static char **ora_fetch(ORAconn *conn);
static char **ora_alloc_row(ORAconn *conn);
static void ora_free_row(char **row);
static int  ora_chk_table(ORAconn *conn, char *tname);
static char *ora_conv_to_values(char *line);
static void ora_errprint(dvoid *errhp, ub4 htype, sb4 *errcodep);
static void ora_checkerr(dvoid *errhp, ub4 htype, sword status,
    text *note, sb4 state, text *file, sb4 line);
/* OCI_CHECK(errhp, ub4 errhptype, sb4 status, struct loadctl *ctlp,
 *          OCIfunction());
 * errhp is typically a (OCIError *), and errhptype is OCI_HTYPE_ERROR.
 * errhp in some cases may be an (OCIEnv *), and errhptype is OCI_HTYPE_ENV.
 */
#define OCI_CHECK(errhp, htype, status, ctlp, OCIfunc) \
if (OCI_SUCCESS != ((status) = (OCIfunc))) \
{ \
  checkerr((dvoid *)(errhp), (ub4)(htype), (sword)(status), NULL, \
           NULL, (text *)__FILE__, (sb4)__LINE__); \
  if ((status) != OCI_SUCCESS_WITH_INFO) \
    cleanup((struct loadctl *)ctlp, (sb4)1); \
} else

#define OCI_CHK(status, errhp)                                \
  ora_checkerr((dvoid *)errhp, (ub4)(OCI_HTYPE_ERROR), (sword)(status), NULL, \
              0, (text *)__FILE__, (sb4)__LINE__);

#define FATAL(note, state) \
do \
{ \
  checkerr(NULL, (ub4)OCI_HTYPE_ERROR, (sword)OCI_SUCCESS,           \
           (text *)(note), (sb4)(state), (text *)__FILE__, (sb4)__LINE__); \
  cleanup((ctlp), (sb4)2); \
} while (0)


//////////////////////////////////////////////////////////////////////////
void *
ora_stOpenSql(struct sql_config *cfg, st_conn_type type)
{
//    FILE *debug;
    ORAconn *conn;
      char *st_conn = NULL;
      char *ora_user, *ora_pass, *ora_dbname;
    sword rc;

    /*
     * sanity check.
     */
    if (type > (ST_CONN_TYPES_NUM - 1)) {
        aLog(D_ERR, "Table type greater then ST_CONN_TYPES_NUM!\n");
        return (NULL);
    }

    /* check if this is first call to OCI */
    netams_mutex_lock(&g_env_mtx);
    if (g_env == NULL) {
        /* create OCI environment */
        if (OCIEnvCreate(&g_env, OCI_THREADED,
                NULL, NULL, NULL, NULL, 0, NULL)) {
            aLog(D_CRIT, "OCI environment creation failed!\n");
            return (NULL);
        }
    }
    netams_mutex_unlock(&g_env_mtx);

    ora_user=cfg->username;
    ora_pass=cfg->password;
    ora_dbname=cfg->dbname;

    conn = (ORAconn *)aMalloc(sizeof(ORAconn));

    /* create error report handle */
    rc = OCIHandleAlloc(g_env, (dvoid **)&conn->errh, OCI_HTYPE_ERROR, 0, NULL);
    OCI_CHK(rc, NULL);

    /* create handle for OCIServerVersion() call */
    rc = OCIHandleAlloc(g_env, (dvoid**)&conn->srvh, OCI_HTYPE_SERVER, 0, NULL);
    OCI_CHK(rc, NULL);
//    rc = OCIServerAttach(conn->srvh, conn->errh,
//        (CONST text*)ora_dbname, strlen(ora_dbname), OCI_DEFAULT);
    rc = OCIServerAttach(conn->srvh, conn->errh,
        NULL, 0, OCI_DEFAULT);
    OCI_CHK(rc, conn->errh);

    /* try connect to DB */
    rc = OCILogon(g_env, conn->errh, &conn->svcctx,
        (CONST text*)ora_user, strlen(ora_user),
        (CONST text*)ora_pass, strlen(ora_pass),
        (CONST text*)ora_dbname, strlen(ora_dbname));
    OCI_CHK(rc, conn->errh);

    /* get Oracle version */
    rc = OCIServerVersion(conn->srvh, conn->errh, (text*)oraver,
        sizeof(oraver), OCI_HTYPE_SERVER);
    OCI_CHK(rc, conn->errh);

    /* create statement handle */
    rc = OCIHandleAlloc(g_env, (dvoid **)&conn->stmth,
        OCI_HTYPE_STMT, 0, NULL);
    OCI_CHK(rc, NULL);

    rc = ora_chk_table(conn, (st_conn = st_table_name[type]));
    if (rc == 0) {
        aLog(D_CRIT, "Table %s doesn't exist. Please create DB scheme!\n",
            st_conn);
    }

      //res=PQexec(conn,"COMMIT");PQclear(res);
      //res=PQexec(conn,"BEGIN");PQclear(res);
      return (void*)conn;
}
//////////////////////////////////////////////////////////////////////////
void
*ora_stCheckSql(void *fd) {
      //put something here
      return fd;
}
//////////////////////////////////////////////////////////////////////////
void
ora_stCloseSql(void *fd)
{
    ORAconn *conn = (ORAconn *)fd;

    OCILogoff(conn->svcctx, conn->errh);
    OCIServerDetach(conn->srvh, conn->errh, OCI_DEFAULT);
    OCIHandleFree(conn->errh, OCI_HTYPE_ERROR);
    OCIHandleFree(conn->stmth, OCI_HTYPE_STMT);
    OCIHandleFree(conn->srvh, OCI_HTYPE_SERVER);
      if (conn->row != NULL)
            ora_free_row(conn->row);

      aFree(conn);

}

//////////////////////////////////////////////////////////////////////////
unsigned
ora_stSaveSql(void *fd, char *filename, st_conn_type type)
{
    ORAconn *conn=(ORAconn*)fd;
    FILE *fp;
    char    *q = NULL, line[1024], *values;
    int qsize;
    int res = 0, lines = 0;
    sword rc;

        aDebug(DEBUG_STORAGE, "ora_stSaveSql: open connection 0x%08x\n",
            conn);
        // Oracle doesn't support COPY FROM or
        // LOAD DATA LOCAL INFILE, so we must load file
        // by hand, line by line.
        fp = fopen(filename, "r");
        while(fgets(line, sizeof(line), fp) != NULL) {
            values = ora_conv_to_values(line);
            q = (char*)realloc(q, (qsize = strlen(values) + 256));
            snprintf(q, qsize, "INSERT INTO %s VALUES (%s)",
                st_table_name[type], values);
            aFree(values);
            lines ++;
            if ((rc = ora_execute(conn, q)) != OCI_SUCCESS) {
                aLog(D_ERR, "ora_stSaveSql() failed for %s\n", q);
                OCI_CHK(rc, conn->errh);
                continue;
            }
            //aDebug(DEBUG_STORAGE, "ora_stSaveSql from '%s', line %d\n", filename, lines);
            res ++;
        }
        fclose(fp);
        free(q);

            // commit changes
        if ((rc = ora_execute(conn, "COMMIT")) != OCI_SUCCESS) {
            aLog(D_ERR, "ora_stSaveSql() failed for COMMIT\n");
            OCI_CHK(rc, conn->errh);
        }

        if (lines != res) {
            aLog(D_WARN, "ora_stSaveSql: %d lines read, but only %d inserted!\n",
                lines, res);
        }

    return (res);
}

//////////////////////////////////////////////////////////////////////////
u_char
ora_stObtainDbData(void *fd, char *query,
      void (*FillData)(void*, void* ,char* (*)(void*, void* , u_char))) {

      ORAconn *conn = (ORAconn *)fd;

      if (ora_select(conn, query) != OCI_SUCCESS) {
            return 0;
      }

            char **row;
      while((row=ora_fetch(conn)) != NULL)
            FillData( NULL, &row, &ora_getRowData);

      return 1;
}
//////////////////////////////////////////////////////////////////////////
/*
 * Oracle-specific functions.
 */

/*
 * Convert coma-delimeted string into string
 * which can be used as VALUES opeardn.
 *
 * XXX Can not handle empty fileds!!!
 */
static char *
ora_conv_to_values(char *line)
{
    char    *p, *strleft = line;
#define MAX_QSIZE   2048
    char    *values = (char*)aMalloc(MAX_QSIZE);
    char    *tmp = NULL;

    /*
     * Remove trailing '\n' if any
     */
     if ((p = strrchr(line, '\n')) != NULL)
      *p = '\0';

    /*
     * we use simplest algorithm possible -
     * if first char is not number - treat
     * it as a string.
     */
    while ((p = strchr(strleft,',')) != NULL) {
        *p = '\0';
        tmp = (char*)realloc(tmp, strlen(strleft) + 4);
        if (isdigit(strleft[0]))
            sprintf(tmp, "%s,", strleft);
        else
            sprintf(tmp, "'%s',", strleft);
        strleft = ++p;
        strcat(values, tmp);
    }

    /* use what is left in strleft */
    if (isdigit(strleft[0]))
        tmp = strdup(strleft);
    else {
        tmp = (char*)realloc(tmp, strlen(strleft) + 10);
        sprintf(tmp, "'%s'", strleft);
    }

    strcat(values, tmp);
    free(tmp);

    return (values);
}

/*
 * Execute given query
 */
static int
ora_execute(ORAconn *conn, char *query)
{
    sword rc;

    aDebug(DEBUG_STORAGE, "ORACLE DB:[0x%08x] Execute: %s\n", conn, query);
      rc = OCIStmtPrepare(conn->stmth, conn->errh,
        (CONST text*)query, strlen(query), OCI_NTV_SYNTAX, OCI_DEFAULT);
    OCI_CHK(rc, conn->errh);
    rc = OCIStmtExecute(conn->svcctx, conn->stmth, conn->errh,
        1, 0, NULL, NULL, 0);
    /* aDebug(DEBUG_STORAGE, "OCIStmtExecute() rc =%d\n", rc); */
    OCI_CHK(rc, conn->errh);
    return (rc);
}

/*
 * Execute SELECT query, prepare row for results.
 */
static int
ora_select(ORAconn *conn, char *query)
{
    sword rc;
    ub4 prefrows = 0;

    if (conn->row != NULL)
        ora_free_row(conn->row);
    conn->row = NULL;
/*    aDebug(DEBUG_STORAGE, "ORACLE DB:[0x%08x] Execute: %s\n", conn, query); */
      rc = OCIStmtPrepare(conn->stmth, conn->errh,
        (CONST text*)query, strlen(query), OCI_NTV_SYNTAX, OCI_DEFAULT);
    OCI_CHK(rc, conn->errh);

    /*
     * Don't prefetch rows since we will defined
     * output vars later.
     */
    rc = OCIAttrSet(conn->stmth, OCI_HTYPE_STMT, &prefrows,
        0, OCI_ATTR_PREFETCH_ROWS, conn->errh);
    OCI_CHK(rc, conn->errh);
    rc = OCIStmtExecute(conn->svcctx, conn->stmth, conn->errh,
        0, 0, NULL, NULL, 0);
    OCI_CHK(rc, conn->errh);
    if (rc == OCI_SUCCESS)
        conn->row = ora_alloc_row(conn);

    return (rc);
}

/*
 * Fetch SELECT results. Returns one row at time.
 * Must be called after ora_select()
 */
static char **
ora_fetch(ORAconn *conn)
{
    sword rc;

    if (conn->row == NULL)
        return (NULL);

    /* data goes to conn->row */
    rc = OCIStmtFetch2(conn->stmth, conn->errh, 1, OCI_FETCH_NEXT, 1, OCI_DEFAULT);
    /* aDebug(DEBUG_STORAGE, "fetch got rc = %d\n", rc); */
    /* OCI_CHK(rc, conn->errh); */
    if (rc == OCI_SUCCESS){
    /* aDebug(DEBUG_STORAGE, "fetch = %s\n", *(conn->row)); */
        return (conn->row);
            }
    else
        return (NULL);
}

static char **
ora_alloc_row(ORAconn *conn)
{
    ub4     col;
    char    **row;
    OCIParam *param;
    ub2     dtype, dsize;
    ub4     i;
    sword   rc;
    OCIDefine   *define;

    /*
     * Statement was executed and succedeed.
     * We need to figure out how many columns in set
     * and max length of each column.
     */
    rc = OCIAttrGet(conn->stmth, OCI_HTYPE_STMT, (dvoid *)&col,
        0, OCI_ATTR_PARAM_COUNT, conn->errh);
    /* aDebug(DEBUG_STORAGE, "got %d columns\n", col); */
    OCI_CHK(rc, conn->errh);
    row = (char **)aMalloc(sizeof(char *) * (col + 1));
    for (i = 0; i < col; i++) {
        rc = OCIParamGet(conn->stmth, OCI_HTYPE_STMT, conn->errh,
            (dvoid **)&param, i + 1);
        OCI_CHK(rc, conn->errh);
        rc = OCIAttrGet((dvoid*)param, OCI_DTYPE_PARAM,
            (dvoid*)&dtype, NULL, OCI_ATTR_DATA_TYPE,
            conn->errh);
        OCI_CHK(rc, conn->errh);
        dsize = DEF_DATASTR_LEN;

        /*
         * Use the retrieved length of dname to allocate an output
         * buffer, and then define the output variable (but only
         * for char/string type columns).
         */
        switch(dtype) {
        case SQLT_AFC:
        case SQLT_CHR:
        case SQLT_STR:
            rc = OCIAttrGet((dvoid*)param, (ub4) OCI_DTYPE_PARAM,
                (dvoid*) &dsize, NULL, (ub4) OCI_ATTR_DATA_SIZE,
                conn->errh);
                OCI_CHK(rc, conn->errh);
                /* FALLTHROUGH */
        case SQLT_DAT:
        case SQLT_INT:
        case SQLT_UIN:
        case SQLT_FLT:
        case SQLT_PDN:
        case SQLT_BIN:
        case SQLT_NUM:
            /* aDebug(DEBUG_STORAGE, "allocate %d bytes for row %d\n", dsize+1, i); */
            row[i] = (char*)aMalloc(dsize + 1);
            break;
        default:
            aLog(D_ERR, "got unknown dtype=%d for row=%d\n", dtype, i);
            dsize = 0;
            // TODO: rewrite logic because lead to create core dump later in ora_fetch()
            row[i]=NULL;
            break;
        }
        rc = OCIDefineByPos(conn->stmth, &define, conn->errh, i + 1,
            row[i], dsize + 1, SQLT_STR, NULL, NULL, NULL, OCI_DEFAULT);
        OCI_CHK(rc, conn->errh);
    }
    row[i] = NULL;

    return (row);
}

static void
ora_free_row(char **row)
{
    for (char **ptr = row; *ptr != NULL; ptr++) {
        aFree(*ptr);
    }
    aFree(row);
}

/*
 * Check if table 'tname' exists.
 * Return 1 if exists, 0 othewise.
 */
static int
ora_chk_table(ORAconn *conn, char *tname)
{
    char query[256], **row;
    sword rc;

    snprintf(query, sizeof(query), "SELECT * FROM cat "
        "WHERE table_name = '%s'", tname);
    rc = ora_select(conn, query);
    row = ora_fetch(conn);
    if (rc == OCI_SUCCESS) {
        aDebug(DEBUG_STORAGE, "ORACLE DB: table %s exists\n",  tname);
        return (1);
    } else {
        return (0);
    }
}

static void
ora_errprint(dvoid *errhp, ub4 htype, sb4 *errcodep)
{
  text errbuf[512];

  if (errhp != NULL)
  {
    sb4  errcode;

    if (errcodep == NULL)
      errcodep = &errcode;

    (void) OCIErrorGet((dvoid *)errhp, (ub4) 1, (text *) NULL, errcodep,
                       errbuf, (ub4) sizeof(errbuf), htype);
    aLog(D_ERR, "OCI Error - %.*s\n", 512, errbuf);
  }
}

static void
ora_checkerr(dvoid *errhp, ub4 htype, sword status,
    text *note, sb4 state, text *file, sb4 line)
{
  sb4 errcode = 0;

  if (status == OCI_SUCCESS)
      return;

  (void) aLog(D_ERR, "OCI Error %ld occurred at File %s:%ld\n",
      (long)status, (char *)file, (long)line);

  switch (status)
  {
  case OCI_SUCCESS_WITH_INFO:
      (void) aLog(D_ERR, "Error - OCI_SUCCESS_WITH_INFO\n");
    ora_errprint(errhp, htype, &errcode);
    break;
  case OCI_NEED_DATA:
      (void) aLog(D_ERR, "Error - OCI_NEED_DATA\n");
    break;
  case OCI_NO_DATA:
      (void) aLog(D_ERR, "Error - OCI_NODATA\n");
    break;
  case OCI_ERROR:
    ora_errprint(errhp, htype, &errcode);
    break;
  case OCI_INVALID_HANDLE:
      (void) aLog(D_ERR, "Error - OCI_INVALID_HANDLE\n");
    break;
  case OCI_STILL_EXECUTING:
    (void) aLog(D_ERR, "Error - OCI_STILL_EXECUTE\n");
    break;
  case OCI_CONTINUE:
    (void) aLog(D_ERR, "Error - OCI_CONTINUE\n");
    break;
  default:
    break;
  }
}

#endif
//////////////////////////////////////////////////////////////////////////

Generated by  Doxygen 1.6.0   Back to index