db.c

公司业务而自己写的网关通讯程序,网关是平台的.不过对OCI有兴趣的不妨看看. Oracle Oci select exec update 多线程 IVR 网关通讯 linux 日志文件操作

#include <stdio.h>
#include "oci.h" 
#include "log.h"
#include "db.h"

#define MAXFIELDS 20

struct field{
    text* pBuffer;
    ub2 buflen;
};
sword checkerr (OCIError *errhp, sword status);

static OCICPool   *poolhp;

OCIError   *errhp;
OCIEnv     *envhp;
static OraText *poolName;
static sb4 poolNameLen;
static OraText database[100] = {"ora9.talkweb.com"};
static OraText username[100] ={"scott"};
static OraText password[100] ={"tiger"};
static OraText appusername[100] ={"APPUSER"}; 
static OraText apppassword[100] ={"APPPASSWORD"};
/* Max,Min, and increment connections */
static ub4 conMin = 1;
static ub4 conMax = 5;
static ub4 conIncr = 1;

int initpool(){
	FILE *pCfgFile;
	pCfgFile = fopen("db.ini","r");
	if(pCfgFile){
		char buf[100];
		char *pResult;
		if(pResult = fgets(buf,100,pCfgFile))memcpy(database,pResult,strlen(pResult)-1);
		if(pResult = fgets(buf,100,pCfgFile))memcpy(username,pResult,strlen(pResult)-1);
		if(pResult = fgets(buf,100,pCfgFile))memcpy(password,pResult,strlen(pResult)-1);
		if(pResult = fgets(buf,100,pCfgFile))memcpy(appusername,pResult,strlen(pResult)-1);
		if(pResult = fgets(buf,100,pCfgFile))memcpy(apppassword,pResult,strlen(pResult)-1);
		fclose(pCfgFile);
	}else{
		INFO("Can't open db.ini!");
		return 1;
	}
	INFO(database);
	INFO(username);
	INFO(password);
	INFO(appusername);
	INFO(apppassword);

    sword lstat;
    if((lstat=OCIEnvCreate (&envhp, OCI_THREADED, (dvoid *)0,  NULL,NULL, NULL, 0, (dvoid *)0)) ||
    (lstat=OCIHandleAlloc((dvoid *) envhp, (dvoid **) &errhp, OCI_HTYPE_ERROR,(size_t) 0, (dvoid **) 0))||
    (lstat=OCIHandleAlloc((dvoid *) envhp, (dvoid **) &poolhp, OCI_HTYPE_CPOOL,(size_t) 0, (dvoid **) 0)))
    {
        INFO("Alloc Handle Error!\n");
        return lstat;
    }
 
    /* CREATE THE CONNECTION POOL */
    if (lstat = OCIConnectionPoolCreate(envhp,
                     errhp,poolhp, &poolName, &poolNameLen,
                     database,(sb4)strlen((signed char *)database),
                     conMin, conMax, conIncr,
                     appusername,(sb4)strlen((signed char *)appusername),
                     apppassword,(sb4)strlen((signed char *)apppassword)
                     ,OCI_DEFAULT))
    {
      checkerr(errhp,lstat);
    }
    return lstat;
    
}
int exec(char *sql){
    if(!sql)return 0;
    OCISvcCtx *svchp = (OCISvcCtx *) 0;
    OCIStmt *stmthp = (OCIStmt *)0;
    sword lstat;
    if (lstat = OCILogon2(envhp, errhp, &svchp,
      (CONST OraText *)username, (ub4)strlen((const signed char *)username),
      (CONST OraText *)password, (ub4)strlen((const signed char *)password),
      (CONST OraText *)poolName, (ub4)poolNameLen,OCI_CPOOL))
    {
      checkerr(errhp,lstat);
      return lstat;
    }

    if((lstat = checkerr(errhp,OCIHandleAlloc(envhp, (dvoid **)&stmthp, OCI_HTYPE_STMT, (size_t)0,(dvoid **)0)))||
    (lstat = checkerr(errhp, OCIStmtPrepare (stmthp, errhp, (CONST OraText *)sql,(ub4)strlen(sql), OCI_NTV_SYNTAX, OCI_DEFAULT)))||
    (lstat =checkerr(errhp, OCIStmtExecute (svchp, stmthp, errhp, (ub4)1, (ub4)0,(OCISnapshot *)0, (OCISnapshot *)0, OCI_DEFAULT )))||
    (lstat =checkerr(errhp, OCITransCommit(svchp,errhp,(ub4)0)))){
        checkerr(errhp,OCITransRollback(svchp,errhp,(ub4)0));
    }
    checkerr(errhp, OCIHandleFree((dvoid *) stmthp, OCI_HTYPE_STMT));
    checkerr(errhp, OCILogoff((dvoid *) svchp, errhp));    
}

int Select(char*sql,char*result){
    //----------------------------INIT-----------------------------------------------
    if(!result)return 0;
    if(!sql)return 0;
    OCISvcCtx *svchp = (OCISvcCtx *) 0;
    OCIStmt *stmthp = (OCIStmt *)0;
    sword lstat;
    if (lstat = OCILogon2(envhp, errhp, &svchp,
      (CONST OraText *)username, (ub4)strlen((const signed char *)username),
      (CONST OraText *)password, (ub4)strlen((const signed char *)password),
      (CONST OraText *)poolName, (ub4)poolNameLen,
                          OCI_CPOOL))
    {
      checkerr(errhp,lstat);
      return lstat;
    }
    OCIHandleAlloc(envhp, (dvoid **)&stmthp, OCI_HTYPE_STMT, (size_t)0,
                   (dvoid **)0);
    /* initialize svchp, stmhp, errhp, rowoff, iters, snap_in, snap_out */
    /* set the execution mode to OCI_DESCRIBE_ONLY. Note that setting the mode to
    OCI_DEFAULT does an implicit describe of the statement in addition to executing
    the statement */
    checkerr(errhp, OCIStmtPrepare (stmthp, errhp, (CONST OraText *)sql,
             (ub4)strlen(sql), OCI_NTV_SYNTAX, 
             OCI_DEFAULT));
    checkerr(errhp, OCIStmtExecute (svchp, stmthp, errhp, (ub4)0, (ub4)0,
             (OCISnapshot *)0, (OCISnapshot *)0, OCI_DEFAULT ));
            	// If it returns OCI_NO_DATA then no need to define recordset
	if ( lstat == OCI_NO_DATA)
	{
        checkerr(errhp, OCIHandleFree((dvoid *) stmthp, OCI_HTYPE_STMT));
        checkerr(errhp, OCILogoff((dvoid *) svchp, errhp));    
		return 0;
	}else if(lstat>0){
        checkerr(errhp, OCIHandleFree((dvoid *) stmthp, OCI_HTYPE_STMT));
        checkerr(errhp, OCILogoff((dvoid *) svchp, errhp));    
		return lstat;
	} 
	//----------------------------Describe ------------------------------------
	ub4 numcols;
	ub2 coltype;
    OCIParam *colhd; /* column handle */
	OCIDefine *pDefn;     /* Get the number of columns in the query */
    checkerr(errhp, OCIAttrGet(stmthp, OCI_HTYPE_STMT, &numcols,
    0, OCI_ATTR_PARAM_COUNT, errhp));
    /* go through the column list and retrieve the data type of each column. We
    start from pos = 1 */
    struct field fields[MAXFIELDS];
    int i;
    for (i = 1; i <= numcols && i<MAXFIELDS; i++)
    {
        /* get parameter for column i */
        checkerr(errhp, OCIParamGet(stmthp, OCI_HTYPE_STMT, errhp, (dvoid *)&colhd, i));
        /* get data-type of column i */
        checkerr(errhp, OCIAttrGet(colhd, OCI_DTYPE_PARAM,
        &coltype, 0, OCI_ATTR_DATA_TYPE, errhp));
        checkerr(errhp, OCIAttrGet(colhd, OCI_DTYPE_PARAM,
        &fields[i-1].buflen, 0, OCI_ATTR_DATA_SIZE, errhp));
		if(coltype == SQLT_CHR)
		{
//		    coltype = SQLT_STR;
//		    fields[i-1].buflen = 100;
	    }else if ( coltype == SQLT_DAT )
		{
			// String is bound to DATE
//			coltype = SQLT_STR;
			fields[i-1].buflen = 50;
		}
		else if ( coltype == SQLT_NUM )
		{
			// String is bound to NUMBER
//			coltype = SQLT_STR;
			fields[i-1].buflen +=1; // allow for null-terminator
		}else if(coltype != SQLT_STR){
		    char buf[50];
		    sprintf(buf,"Unsuport Col Type:%d,Size:%d\n",coltype,fields[i-1].buflen);
		    INFO(buf);
//		    break;
		}
		coltype = SQLT_STR;

		fields[i-1].pBuffer = (text *)malloc((size_t)fields[i-1].buflen);
		if(!(fields[i-1].pBuffer)){INFO("malloc error");}
		pDefn = NULL;
		checkerr(errhp,OCIDefineByPos(stmthp,
			&pDefn, // function allocs and gives back a pointer to field definition
			errhp,
			i, // position in select starting at 1
			(dvoid *)fields[i-1].pBuffer, // storage area for field result
			(sword)fields[i-1].buflen, // sizeof storage area
			SQLT_STR, // field type
			(dvoid *) 0, // indp, null indicator
			(ub2 *)0, // rlenp
			(ub2 *)0, // indp, null indicator

			OCI_DEFAULT )
			);
    }
    /*-------------------------------fetch-------------------------------*/
    	int rtnv = 0;
	while((lstat=checkerr(errhp,OCIStmtFetch( stmthp,
		errhp,
		1, // 'nrows' i.e. max rows
		OCI_FETCH_NEXT, 
		OCI_DEFAULT )
		))!=OCI_NO_DATA){
        if(lstat>0 && lstat!=OCI_NO_DATA){
    		rtnv = lstat;
    		break;
    	}else{
        	for(i = 0;i<numcols;i++){
            	strcat(result,fields[i].pBuffer);
            	strcat(result,"#");
        	}
        	strcat(result,"&");
        }
    }
    	for(i = 0;i<numcols;i++){
        	free(fields[i].pBuffer);
        	fields[i].pBuffer = NULL;
    	}
        checkerr(errhp, OCIHandleFree((dvoid *) stmthp, OCI_HTYPE_STMT));
        checkerr(errhp, OCILogoff((dvoid *) svchp, errhp));    
    
    return rtnv;
}


/* 关闭连接 */
int closepool(){
    checkerr(errhp, OCIConnectionPoolDestroy(poolhp, errhp, OCI_DEFAULT));
    checkerr(errhp, OCIHandleFree((dvoid *)poolhp, OCI_HTYPE_CPOOL));
    checkerr(errhp, OCIHandleFree((dvoid *)errhp, OCI_HTYPE_ERROR));
    return 0;
}
/* 打印错误信息 */
sword checkerr(errhp, status)
OCIError *errhp;
sword status;
{
    text errbuf[512];
    sb4 errcode = 0;
 
    switch (status)
    {
    case OCI_SUCCESS:
      break;
    case OCI_SUCCESS_WITH_INFO:
      INFO("Error - OCI_SUCCESS_WITH_INFO\n");
      break;
    case OCI_NEED_DATA:
      INFO("Error - OCI_NEED_DATA\n");
      break;
    case OCI_NO_DATA:
//      INFO("Error - OCI_NODATA\n");
      break;
    case OCI_ERROR:
    {
        text errbuf2[512];
      OCIErrorGet((dvoid *)errhp, (ub4) 1, (text *) NULL, &errcode,
                          errbuf2, (ub4) sizeof(errbuf2), OCI_HTYPE_ERROR);
             sprintf(errbuf,"Error - %.*s\n", 512, errbuf2);
             INFO(errbuf);
     }           
      break;
    case OCI_INVALID_HANDLE:
      INFO("Error - OCI_INVALID_HANDLE\n");
      break;
    case OCI_STILL_EXECUTING:
      INFO("Error - OCI_STILL_EXECUTE\n");
      break;
    case OCI_CONTINUE:
      INFO("Error - OCI_CONTINUE\n");
      break;
    default:
      break;
    }
    return status;
}