dbpool_oracle.c

The Kannel Open Source WAP and SMS gateway works as both an SMS gateway, for implementing keyword b

    OracleConf *conf = theconf->oracle;

    octstr_destroy(conf->username);
    octstr_destroy(conf->password);
    octstr_destroy(conf->tnsname);

    gw_free(conf);
    gw_free(theconf);
}


static int oracle_select(void *theconn, const Octstr *sql, List *binds, List **res)
{
    List *row;
    OCIStmt *stmt;
    OCIParam *dparam;
    sword status;
    ub4 columns;
    ub4 i;
    struct data_s {
        text *data;
        ub2 size;
        sb2 ind;
        ub2 type;
    };
    struct data_s *data;
    struct ora_conn *conn = (struct ora_conn*) theconn;
    int binds_len = (binds ? list_len(binds) : 0);

    *res = NULL;

    /* allocate statement handle */
    status = OCIHandleAlloc(conn->envp, (dvoid**)&stmt, OCI_HTYPE_STMT, 0,0);
    if (OCI_SUCCESS != status) {
        oracle_checkerr(conn->errhp, status);
        return -1;
    }
    /* prepare statement */
    status = OCIStmtPrepare(stmt, conn->errhp, octstr_get_cstr(sql), 
                            octstr_len(sql), OCI_NTV_SYNTAX, OCI_DEFAULT);
    if (OCI_SUCCESS != status) {
        oracle_checkerr(conn->errhp, status);
        OCIHandleFree(stmt, OCI_HTYPE_STMT);
        return -1;
    }

    /* bind variables */
    for (i = 0; i < binds_len; i++) {
        OCIBind *bndhp = NULL;
        Octstr *bind = list_get(binds, i);
        status = OCIBindByPos(stmt, &bndhp, 
                              conn->errhp, (i+1), (dvoid *) octstr_get_cstr(bind),
                              (sword) octstr_len(bind)+1, SQLT_STR, (dvoid *) 0, (ub2 *)0,
                              (ub2 *)0, (ub4)0, (ub4 *)0, OCI_DEFAULT);
        if (OCI_SUCCESS != status) {
            oracle_checkerr(conn->errhp, status);
            OCIHandleFree(stmt, OCI_HTYPE_STMT);
            return -1;
        }
    }
    /* execute our statement */
    status = OCIStmtExecute(conn->svchp, stmt, conn->errhp, 0, 0, NULL, NULL, 
                            OCI_DEFAULT);
    if (OCI_SUCCESS != status && OCI_NO_DATA != status) {
        oracle_checkerr(conn->errhp, status);
        OCIHandleFree(stmt, OCI_HTYPE_STMT);
        return -1;
    }
    /* receive column count */
    status = OCIAttrGet(stmt, OCI_HTYPE_STMT, &columns, 0, OCI_ATTR_PARAM_COUNT, 
                        conn->errhp);
    if (status != OCI_SUCCESS) {
        oracle_checkerr(conn->errhp, status);
        OCIHandleFree(stmt, OCI_HTYPE_STMT);
        return -1;
    }

    debug("dbpool.oracle",0,"SQL has %d columns", columns);

    /* allocate array of pointers */
    debug("dbpool.oracle",0,"alloc size=%d",sizeof(text*)*columns);
    data = gw_malloc(sizeof(struct data_s)*columns);

    debug("dbpool.oracle",0,"retrieve data_size");
    /* retrieve data size for every column and allocate it */
    for (i=0 ; i < columns; i++) {
        OCIDefine *defh;

        status = OCIParamGet(stmt, OCI_HTYPE_STMT, conn->errhp, 
                             (dvoid**) &dparam, i+1);
        if (status != OCI_SUCCESS) {
            oracle_checkerr(conn->errhp, status);
            columns = i;
            for (i = 0; i < columns; i++)
                gw_free(data[i].data);
            gw_free(data);
            OCIHandleFree(stmt, OCI_HTYPE_STMT);
            return -1;
        }

        status = OCIAttrGet(dparam, OCI_DTYPE_PARAM, (dvoid*) &data[i].size, 
                            0, OCI_ATTR_DATA_SIZE, conn->errhp);
        if (status != OCI_SUCCESS) {
            oracle_checkerr(conn->errhp, status);
            columns = i;
            for (i = 0; i < columns; i++)
                gw_free(data[i].data);
            gw_free(data);
            OCIHandleFree(stmt, OCI_HTYPE_STMT);
            return -1;
        }

        status = OCIAttrGet(dparam, OCI_DTYPE_PARAM, (dvoid*) &data[i].type, 
                            0, OCI_ATTR_DATA_TYPE, conn->errhp);
        if (status != OCI_SUCCESS) {
            oracle_checkerr(conn->errhp, status);
            columns = i;
            for (i = 0; i < columns; i++)
                gw_free(data[i].data);
            gw_free(data);
            OCIHandleFree(stmt, OCI_HTYPE_STMT);
            return -1;
        }

        /* convert all data types to C-Strings except DATE */
        if (data[i].type != SQLT_DAT) {
            data[i].size++; /* terminating zero */
            data[i].type = SQLT_STR;
        }

        debug("dbpool.oracle",0,"alloc size=%d", data[i].size);
        data[i].data = gw_malloc(data[i].size);

        /* bind allocated values to statement handle */
        status = OCIDefineByPos(stmt, &defh, conn->errhp, i+1, data[i].data, 
                                data[i].size, data[i].type, &data[i].ind, 
                                0, 0, OCI_DEFAULT);
        if (status != OCI_SUCCESS) {
            oracle_checkerr(conn->errhp, status);
            columns = i;
            for (i = 0; i <= columns; i++)
                gw_free(data[i].data);
            gw_free(data);
            OCIHandleFree(stmt, OCI_HTYPE_STMT);
            return -1;
        }
    }

    *res = list_create();
    /* fetch data */
    while ((status = OCIStmtFetch(stmt, conn->errhp, 1, 
                                  OCI_FETCH_NEXT, OCI_DEFAULT)) == OCI_SUCCESS ||
            status == OCI_SUCCESS_WITH_INFO) {

        row = list_create();
        for (i = 0; i < columns; i++) {
            if (data[i].data == NULL || data[i].ind == -1) {
                list_insert(row, i, octstr_create(""));
            } else {
                list_insert(row, i, octstr_create_from_data(data[i].data, data[i].size));
            }
            /* debug("dbpool.oracle",0,"inserted value = '%s'", 
                     octstr_get_cstr(list_get(row,i))); */
        }
        list_append(*res, row);
    }

    /* ignore OCI_NO_DATA error */
    if (status != OCI_NO_DATA) {
        List *row;
        oracle_checkerr(conn->errhp, status);
        for (i = 0; i < columns; i++)
            gw_free(data[i].data);
        gw_free(data);
        while ((row = list_extract_first(*res)) != NULL)
            list_destroy(row, octstr_destroy_item);
        list_destroy(*res, NULL);
        *res = NULL;
        OCIHandleFree(stmt, OCI_HTYPE_STMT);
        return -1;
    }

    for (i = 0; i < columns; i++)
        gw_free(data[i].data);

    gw_free(data);
    OCIHandleFree(stmt, OCI_HTYPE_STMT);

    return 0;
}


static int oracle_update(void *theconn, const Octstr *sql, List *binds)
{
    OCIStmt *stmt;
    sword status;
    ub4 rows = 0, i;
    struct ora_conn *conn = (struct ora_conn*) theconn;
    int binds_len = (binds ? list_len(binds) : 0);
    
    /* allocate statement handle */
    status = OCIHandleAlloc(conn->envp, (dvoid**)&stmt, OCI_HTYPE_STMT, 0,0);
    if (OCI_SUCCESS != status) {
        oracle_checkerr(conn->errhp, status);
        return -1;
    }
    debug("dbpool.oracle",0,"OCIStmt allocated");
    /* prepare statement */
    status = OCIStmtPrepare(stmt, conn->errhp, octstr_get_cstr(sql), 
                            octstr_len(sql), OCI_NTV_SYNTAX, OCI_DEFAULT);
    if (OCI_SUCCESS != status) {
        oracle_checkerr(conn->errhp, status);
        OCIHandleFree(stmt, OCI_HTYPE_STMT);
        return -1;
    }
    debug("dbpool.oracle",0,"OCIStmtPrepare done");
   
    /* bind variables */
    for (i = 0; i < binds_len; i++) {
        Octstr *bind = list_get(binds, i);
        OCIBind *bndhp = NULL;
        status = OCIBindByPos(stmt, &bndhp, 
                              conn->errhp, (i+1), (dvoid *) octstr_get_cstr(bind),
                              (sword) octstr_len(bind)+1, SQLT_STR, (dvoid *) 0, (ub2 *)0,
                              (ub2 *)0, (ub4)0, (ub4 *)0, OCI_DEFAULT);
        if (OCI_SUCCESS != status) {
            oracle_checkerr(conn->errhp, status);
            OCIHandleFree(stmt, OCI_HTYPE_STMT);
            return -1;
        }
    }
    
    /* execute our statement */
    status = OCIStmtExecute(conn->svchp, stmt, conn->errhp, 1, 0, NULL, NULL, 
                            /*OCI_DEFAULT*/ OCI_COMMIT_ON_SUCCESS);
    if (OCI_SUCCESS != status && OCI_NO_DATA != status) {
        oracle_checkerr(conn->errhp, status);
        OCIHandleFree(stmt, OCI_HTYPE_STMT);
        return -1;
    }
    debug("dbpool.oracle",0,"OCIStmtExecute done");
    /* retrieve #rows processed so far */
    status = OCIAttrGet(stmt, OCI_HTYPE_STMT, &rows, 0, OCI_ATTR_ROW_COUNT, 
                        conn->errhp);
    if (status != OCI_SUCCESS) {
        oracle_checkerr(conn->errhp, status);
        /* we doesn't return error here, because sql is executed and commited already */
    }
    debug("dbpool.oracle",0,"rows processed = %d", rows);

    OCIHandleFree(stmt, OCI_HTYPE_STMT);
    
    return (int) rows;
}

static struct db_ops oracle_ops = {
    .open = oracle_open_conn,
    .close = oracle_close_conn,
    .check = oracle_check_conn,
    .conf_destroy = oracle_conf_destroy,
    .select = oracle_select,
    .update = oracle_update
};

#endif