dbfunc.c

oracle技术手册供初学者

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <oci.h>

#include "dbcom.h"
#include "dbfunc.h"


int DbsEMP_SEL(int thrd_index, char *sql, emp_t *p_emp)
{
    DefaultDefinition;

    StartFunc;

    //sprintf(ThrdConn.sql, "select no,upd_ts,name,duty,age,salary from emp where no=:no");
    strcpy(ThrdConn.sql, sql);
    ReturnErr(StmtPrepare);
    ReturnErr(BindName(1, no, &p_emp->no, sizeof(p_emp->no),
        SQLT_FLT));
    ReturnErr(DefinePos(1, &p_emp->no, sizeof(p_emp->no),
        SQLT_FLT));
    ReturnErr(DefinePos(2, p_emp->upd_ts, sizeof(p_emp->upd_ts),
        SQLT_STR));
    ReturnErr(DefinePos(3, p_emp->name, sizeof(p_emp->name),
        SQLT_STR));
    ReturnErr(DefinePos(4, p_emp->duty, sizeof(p_emp->duty),
        SQLT_STR));
    ReturnErr(DefinePos(5, &p_emp->age, sizeof(p_emp->age),
        SQLT_INT));
    ReturnErr(DefinePos(6, &p_emp->salary, sizeof(p_emp->salary),
        SQLT_FLT));
    CheckErr(StmtExecute);
    if(status!=OCI_SUCCESS)
    {
        if(status==OCI_NO_DATA)  errcode=DB_NOTFOUND;
        goto ERROR;
    }

    EndFunc;
}


int DbsEMP_INS(int thrd_index, char *sql, emp_t *p_emp)
{
    DefaultDefinition;

    StartFunc;

    //sprintf(ThrdConn.sql, "insert into emp values(:no,sysdate,:name,:duty,:age,:salary)");
    strcpy(ThrdConn.sql, sql);
    ReturnErr(StmtPrepare);
    ReturnErr(BindName(1, no, &p_emp->no, sizeof(p_emp->no),
        SQLT_FLT));
    ReturnErr(BindName(2, NAME, p_emp->name, sizeof(p_emp->name),
        SQLT_STR));
    ReturnErr(BindName(3, DUTY, p_emp->duty, sizeof(p_emp->duty),
        SQLT_STR));
    ReturnErr(BindName(4, AGE, &p_emp->age, sizeof(p_emp->age),
        SQLT_INT));
    ReturnErr(BindName(5, SALARY, &p_emp->salary,
        sizeof(p_emp->salary), SQLT_FLT));
    ReturnErr(StmtExecute);

    EndFunc;
}


int DbsEMP_CUR(int thrd_index, char *sql, emp_t *p_emp,
    emp_t **pa_emp, int *p_emp_qt)
{
    DefaultDefinition;
    DefCurBuf(emp);

    StartFunc;

    //sprintf(ThrdConn.sql, "select no,name,age from emp where age<:age order by no");
    strcpy(ThrdConn.sql, sql);
    ReturnErr(StmtPrepare);
    ReturnErr(BindName(1, age, &p_emp->age, sizeof(p_emp->age),
        SQLT_INT));
    ReturnErr(StmtOpen);
    ReturnErr(DefinePos(1, &emp.no, sizeof(emp.no), SQLT_FLT));
    ReturnErr(DefinePos(2, emp.name, sizeof(emp.name), SQLT_STR));
    ReturnErr(DefinePos(3, &emp.age, sizeof(emp.age), SQLT_INT));

    ProcCursor(emp);

    EndFunc;
}


int DbsRESUME_INS(int thrd_index, char *sql, resume_t *p_resume)
{
    DefaultDefinition;

    StartFunc;
 
    sprintf(ThrdConn.sql, "insert into resume values(:no,empty_blob())");
    ReturnErr(StmtPrepare);
    ReturnErr(BindName(1, no, &p_resume->no, sizeof(p_resume->no),
        SQLT_FLT));
    ReturnErr(StmtExecute);
    OCIHandleFree((dvoid *)ThrdConn.p_hstmt, OCI_HTYPE_STMT);

    ReturnErr(OCIHandleAlloc((CONST dvoid *)p_henv,
        (dvoid **)&ThrdConn.p_hstmt, OCI_HTYPE_STMT,
        (size_t)0, (dvoid **)NULL));
    sprintf(ThrdConn.sql, "select resume from resume where no=:no");
    ReturnErr(StmtPrepare);
    ReturnErr(BindName(1, no, &p_resume->no, sizeof(p_resume->no),
        SQLT_FLT));
    ReturnErr(DefinePos(1, &ThrdConn.p_blob, sizeof(ThrdConn.p_blob),
        SQLT_BLOB));
    ReturnErr(StmtExecute);
    if(write_blob(thrd_index, p_resume->resume,
           (ub4)strlen(p_resume->resume), ThrdConn.p_blob, &errcode))
    {   goto ERROR;   }

    EndFunc;
}


int DbsRESUME_SEL(int thrd_index, char *sql, resume_t *p_resume)
{
    DefaultDefinition;
    ub4 size;

    StartFunc;
 
    sprintf(ThrdConn.sql, "select no,resume from resume where no=:no");
    ReturnErr(StmtPrepare);
    ReturnErr(BindName(1, NO, &p_resume->no, sizeof(p_resume->no),
        SQLT_FLT));
    ReturnErr(DefinePos(1, &p_resume->no, sizeof(p_resume->no),
        SQLT_FLT)); 
    ReturnErr(DefinePos(2, &ThrdConn.p_blob, sizeof(ThrdConn.p_blob),
        SQLT_BLOB));
    CheckErr(StmtExecute);
    if(status!=OCI_SUCCESS)
    {
        if(status==OCI_NO_DATA)  errcode=DB_NOTFOUND;
        goto ERROR;
    }
    if(read_blob(thrd_index, ThrdConn.p_blob,
           &p_resume->resume, &size, &errcode))
    {   goto ERROR;   }
    p_resume->resume[size]='\0';

    EndFunc;
}