OCI中繫結變數的實現例子

 

/*CREATE TABLE TMP
(
  HOSTNAME      VARCHAR2(61 BYTE),
  MODULENAME    VARCHAR2(20 BYTE),
  LOGTIME       DATE                            NOT NULL,
  LOGID         NUMBER(3),
  LOGLEVEL      NUMBER(3),
  LOGLEVELMASK  NUMBER(3),
  LOGCONTENT    CLOB
)*/

===========================================================

OCIBind *bnd1p = NULL; /* the first bind handle */
OCIBind *bnd2p = NULL; /* the second bind handle */
OCIBind *bnd3p = NULL; /* the third bind handle */
OCIBind *bnd4p = NULL; /* the fouth bind handle */
OCIBind *bnd5p = NULL;
OCIBind *bnd6p = NULL;
OCIBind *bnd7p = NULL;

 

bool DBAccess::do_bind_insert_log_to_db(
 char *hostname,
 char *modulename,
 char *logtime,
 int logid,
 int loglevel,
 int loglevelmask,
 char *logcontent)
{
    sword errr;
   
    if ( !DBConnected() )                      // if  DB not open, open it
            if(!OpenDbConnect(database)) return false;
 
    dlog_debug("DBAccess::do_bind_insert_log_to_db, OCIStmtPrepare,stmthp=%d,errhp=%d",stmthp,errhp);
 
 
    char *insertsql =  "INSERT INTO LOCATION_LOG(HOSTNAME, MODULENAME, LOGTIME,  LOGID, LOGLEVEL, LOGLEVELMASK, LOGCONTENT)\
        VALUES (:Vhostname, :Vmodulename, to_date(:Vlogtime,'YYYY-MM-DD HH24:MI:SS'), :Vlogid, :Vloglevel, :Vloglevelmask, :Vlogcontent)";
 
    if ((errr=OCIStmtPrepare(stmthp,errhp, reinterpret_cast(insertsql), (ub4)strlen((char *)insertsql),OCI_NTV_SYNTAX,OCI_DEFAULT))!=OCI_SUCCESS)
    {
        CheckError(errr);
        dlog_error("DBAccess::do_bind_insert_log_to_db Error when OCIStmtPrepare INSERT INTO LOCATION_LOG \n");
        return false;
    }
 
 //char *hostname,1
    if ((errr= OCIBindByName(stmthp, &bnd1p, errhp, (text *) ":Vhostname",strlen(":Vhostname"), (ub1 *)hostname , strlen(hostname)+1, SQLT_STR, (void *) 0,(ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT))!=OCI_SUCCESS)
    {
        CheckError(errr);
        dlog_error("DBAccess::do_bind_insert_log_to_db Error when OCIBindByName HOSTNAME \n");
        return false;
    }
 
 //char *modulename,2
 
    if ((errr= OCIBindByName(stmthp, &bnd2p, errhp, (text *) ":Vmodulename",strlen(":Vmodulename"), (ub1 *)modulename , strlen(modulename)+1, SQLT_STR, (void *) 0,(ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT))!=OCI_SUCCESS)
    {
        CheckError(errr);
        dlog_error("DBAccess::do_bind_insert_log_to_db Error when OCIBindByName MODULENAME \n");
        return false;
    }
 
 //char *logtime,3
    if ((errr= OCIBindByName(stmthp, &bnd3p, errhp, (text *) ":Vlogtime",strlen(":Vlogtime"), (ub1 *)logtime , strlen(logtime)+1, SQLT_STR, (void *) 0,(ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT))!=OCI_SUCCESS)
    {
        CheckError(errr);
        dlog_error("DBAccess::do_bind_insert_log_to_db Error when OCIBindByName LOGTIME \n");
        return false;
    }
 
 //int logid,4
  if ((errr= OCIBindByName(stmthp, &bnd4p, errhp, (text *) ":Vlogid",-1, (ub1 *) &logid, (sword) sizeof(logid), SQLT_INT,(void *) 0, (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0,OCI_DEFAULT))!=OCI_SUCCESS)
    {
        CheckError(errr);
        dlog_error("DBAccess::do_bind_insert_log_to_db Error when OCIBindByName LOGID \n");
        return false;
    }
 
 //int loglevel,5
  if ((errr= OCIBindByName(stmthp, &bnd5p, errhp, (text *) ":Vloglevel",-1, (ub1 *) &loglevel, (sword) sizeof(loglevel), SQLT_INT,(void *) 0, (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0,OCI_DEFAULT))!=OCI_SUCCESS)
    {
        CheckError(errr);
        dlog_error("DBAccess::do_bind_insert_log_to_db Error when OCIBindByName LOGLEVEL \n");
        return false;
    }
 
 //int loglevelmask,6
  if ((errr= OCIBindByName(stmthp, &bnd6p, errhp, (text *) ":Vloglevelmask",-1, (ub1 *) &loglevelmask, (sword) sizeof(loglevelmask), SQLT_INT,(void *) 0, (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0,OCI_DEFAULT))!=OCI_SUCCESS)
    {
        CheckError(errr);
        dlog_error("DBAccess::do_bind_insert_log_to_db Error when OCIBindByName  LOGLEVELMASK \n");
        return false;
    }
 

 //char *logcontent,7
    if ((errr= OCIBindByName(stmthp, &bnd7p, errhp, (text *) ":Vlogcontent",strlen(":Vlogcontent"), (ub1 *)logcontent , strlen(logcontent)+1, SQLT_STR, (void *) 0,(ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT))!=OCI_SUCCESS)
    {
        CheckError(errr);
        dlog_error("DBAccess::do_bind_insert_log_to_db Error when OCIBindByName LOGCONTENT \n");
        return false;
    }
 
 
 
    dlog_debug("DBAccess::do_bind_insert_log_to_db, OCIStmtExecute,stmthp=%d,errhp=%d \n",stmthp,errhp);
 
 
    if ((errr=OCIStmtExecute(svchp,stmthp,errhp,1,(ub4)0,NULL,NULL,(ub4) OCI_DEFAULT))!=OCI_SUCCESS) //execute
    {
        CheckError(errr);
        dlog_error("DBAccess::do_bind_insert_log_to_db Error when OCIStmtExecute \n");
        return false;
    }
 
    dlog_debug("DBAccess::do_bind_insert_log_to_db, OCIStmtExecute,done \n");
   
    if ((errr=OCITransCommit(svchp, errhp, (ub4) 0)) != OCI_SUCCESS) //commit
    {
        CheckError(errr);
        dlog_error("DBAccess::do_bind_insert_log_to_db Error when OCIStmtCommit \n");
        return false;
    }
       
    return true;
}

 

說明:

1. OCI中DateTime的處理相當麻煩, 可以使用char來替換DateTime使用. 在構造SQL的時候, 使用to_date進行轉換. to_date(:Vlogtime,'YYYY-MM-DD HH24:MI:SS')