ociadapter.cpp

封装了基于OCI的oracle数据库接口

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

#ifdef WIN32
#define snprintf _snprintf
#endif

#define OCI_PREFETCH_NUM	1000

struct _oci_connect
{
	OCIServer *svr;
	OCISvcCtx *svc;
	OCISession *svs;
	OCIError *err;
	OCIStmt *stmt;
	sb2 indicator[MAX_COLUNNS];
};

/*************************************************************/

COCIAdapter::COCIAdapter(CDbPool *pool)
:m_pPool(pool), m_pEnv(NULL), m_pServer(NULL), m_pCtx(NULL)
{
}

COCIAdapter::~COCIAdapter()
{
	release();
}

bool COCIAdapter::init(DbInit_t *init)
{
	if(m_pEnv != NULL)
		return false;
	if(OCIInitialize(OCI_THREADED, NULL, NULL, NULL, NULL) != OCI_SUCCESS)
	{
		m_pPool->PrintLog("initialize OCI failed.");
		return false;
	}
	if(OCIEnvInit((OCIEnv**)&m_pEnv, OCI_DEFAULT, 0, NULL) != OCI_SUCCESS)
	{
		m_pPool->PrintLog("create oci env failed.");
		return false;
	}
	return true;
}

void* COCIAdapter::newConnect(DbInfo_t *info)
{
	OCI_Connect_t *con;
	if(info==NULL || m_pEnv==NULL)
		return NULL;
	con = (OCI_Connect_t*)calloc(1, sizeof(OCI_Connect_t));
	if(con == NULL)
	{
		m_pPool->PrintLog("malloc space for oci connection failed.");
		goto error;
	}
	if(OCIHandleAlloc(m_pEnv, (void**)&con->svr, OCI_HTYPE_SERVER, 0, NULL) != OCI_SUCCESS)
	{
		m_pPool->PrintLog("allocate server handle failed.");
		goto error;
	}
	if(OCIHandleAlloc(m_pEnv, (void**)&con->svc, OCI_HTYPE_SVCCTX, 0, NULL) != OCI_SUCCESS)
	{
		m_pPool->PrintLog("allocate server context handle failed.");
		goto error;
	}
	if(OCIHandleAlloc(m_pEnv, (void**)&con->svs, OCI_HTYPE_SESSION, 0, NULL) != OCI_SUCCESS)
	{
		m_pPool->PrintLog("allocate session handle failed.");
		goto error;
	}
	if(OCIHandleAlloc(m_pEnv, (void**)&con->err, OCI_HTYPE_ERROR, 0, NULL) != OCI_SUCCESS)
	{
		m_pPool->PrintLog("allocate error handle failed.");
		goto error;
	}

	if(OCIServerAttach(con->svr, con->err, (OraText*)info->server, strlen(info->server), OCI_DEFAULT) != OCI_SUCCESS)
	{
		m_pPool->PrintLog("set database server failed.");
		goto error;
	}
	if(OCIAttrSet(con->svs, OCI_HTYPE_SESSION, (OraText*)info->user, strlen(info->user), OCI_ATTR_USERNAME, con->err) != OCI_SUCCESS)
	{
		m_pPool->PrintLog("set login user failed.");
		goto error;
	}
	if(OCIAttrSet(con->svs, OCI_HTYPE_SESSION, (OraText*)info->password, strlen(info->password), OCI_ATTR_PASSWORD, con->err) != OCI_SUCCESS)
	{
		m_pPool->PrintLog("set login password failed.");
		goto error;
	}
	if(OCIAttrSet(con->svc, OCI_HTYPE_SVCCTX, con->svr, 0, OCI_ATTR_SERVER, con->err) != OCI_SUCCESS)
	{
		m_pPool->PrintLog("set server context failed.");
		goto error;
	}
	if(OCISessionBegin(con->svc, con->err, con->svs, OCI_CRED_RDBMS, OCI_DEFAULT) != OCI_SUCCESS)
	{
		m_pPool->PrintLog("start session failed.");
		goto error;
	}
	if(OCIAttrSet(con->svc, OCI_HTYPE_SVCCTX, con->svs, 0, OCI_ATTR_SESSION, con->err) != OCI_SUCCESS)
	{
		m_pPool->PrintLog("set current session failed.");
		goto error;
	}
	return con;
error:
	if(con != NULL)
	{
		if(con->svr != NULL)
			OCIHandleFree(con->svr, OCI_HTYPE_SERVER);
		if(con->svc != NULL)
			OCIHandleFree(con->svc, OCI_HTYPE_SVCCTX);
		if(con->svs != NULL)
			OCIHandleFree(con->svs, OCI_HTYPE_SESSION);
		if(con->err != NULL)
			OCIHandleFree(con->err, OCI_HTYPE_ERROR);
		free(con);
	}
	return NULL;
}

void COCIAdapter::closeConnect(void *con)
{
	OCI_Connect_t *conn = (OCI_Connect_t*)con;
	if(con == NULL)
		return;
	OCISessionEnd(conn->svc, conn->err, conn->svs, OCI_DEFAULT);
	OCIServerDetach(conn->svr, conn->err, OCI_DEFAULT);
	OCIHandleFree(conn->svr, OCI_HTYPE_SERVER);
	OCIHandleFree(conn->svc, OCI_HTYPE_SVCCTX);
	OCIHandleFree(conn->svs, OCI_HTYPE_SESSION);
	OCIHandleFree(conn->err, OCI_HTYPE_ERROR);
	free(conn);
}

bool COCIAdapter::testConnect(void *con, int timeout)
{
	void *stmt;
	bool ret = false;
	DbField_t record[1];
	DbDesc_t desc[1];
	int cols;

	if(con==NULL || m_pEnv==NULL)
		return false;
	stmt = newStatement(con);
	if(stmt == NULL)
		return false;
	if(Query(stmt, "select sysdate from dual", record, desc, &cols, timeout) && cols==1)
		ret = true;	
	closeStatement(stmt);
	return ret;
}

void* COCIAdapter::newStatement(void *con)
{
	OCI_Connect_t *conn = (OCI_Connect_t*)con;
	if(conn==NULL || conn->svr==NULL || conn->svc==NULL || conn->svs==NULL || conn->err==NULL)
		return NULL;
	if(OCIHandleAlloc(m_pEnv, (void**)&conn->stmt, OCI_HTYPE_STMT, 0, NULL) != OCI_SUCCESS)
	{
		m_pPool->PrintLog("allocate statement handle failed.");
		return NULL;
	}
	return conn;
}

void COCIAdapter::closeStatement(void *stmt)
{
	OCI_Connect_t *conn = (OCI_Connect_t*)stmt;
	if(stmt==NULL || conn->stmt==NULL)
		return;
	OCIHandleFree(conn->stmt, OCI_HTYPE_STMT);
	conn->stmt = NULL;
}

bool COCIAdapter::Query(void *stmt, char *sql, DbField_t *record, DbDesc_t *desc, int *cols, int timeout)
{
	OCI_Connect_t *conn;
	ub2 stmttype;
	int i, prenum=OCI_PREFETCH_NUM;
	OCIParam *fielddesc;
	OCIDefine *p;
	ub2 sqltype;
	ub4 len;
	char *fieldname;

	if(stmt==NULL || sql==NULL || record==NULL || cols==NULL || timeout<0 || m_pEnv==NULL)
		return false;
	conn = (OCI_Connect_t*)stmt;
	OCIReset(conn->svc, conn->err);
	if(OCIStmtPrepare(conn->stmt, conn->err, (OraText*)sql, strlen(sql), OCI_NTV_SYNTAX, OCI_DEFAULT) != OCI_SUCCESS)
	{
		m_pPool->PrintLog("prepare query sql statement failed.");
		return false;
	}
	if(OCIAttrGet(conn->stmt, OCI_HTYPE_STMT, &stmttype, NULL, OCI_ATTR_STMT_TYPE, conn->err) != OCI_SUCCESS)
	{
		m_pPool->PrintLog("get query select type failed.");
		return false;
	}
	if(stmttype != OCI_STMT_SELECT)
	{
		m_pPool->PrintLog("query sql is not select");
		return false;
	}
	if(OCIStmtExecute(conn->svc, conn->stmt, conn->err, 0, 0, NULL, NULL, OCI_COMMIT_ON_SUCCESS) != OCI_SUCCESS)
	{
		m_pPool->PrintLog("query statement failed.");
		return false;
	}
	if(OCIAttrGet(conn->stmt, OCI_HTYPE_STMT, cols, NULL, OCI_ATTR_PARAM_COUNT, conn->err) != OCI_SUCCESS)
	{
		m_pPool->PrintLog("get select fields failed.");
		return false;
	}
	if(*cols<=0 || *cols>MAX_COLUNNS)
	{
		m_pPool->PrintLog("field number is invalid.");
		return false;
	}
	for(i=0; i<*cols; i++)
	{
		if(desc != NULL)
		{
			memset(&desc[i], 0, sizeof(desc[i]));
			if(OCIParamGet(conn->stmt, OCI_HTYPE_STMT, conn->err, (void**)&fielddesc, i+1) != OCI_SUCCESS)
			{
				m_pPool->PrintLog("get query field parameter failed.");
				return false;
			}
			if(OCIAttrGet(fielddesc, OCI_DTYPE_PARAM, &sqltype, NULL, OCI_ATTR_DATA_TYPE, conn->err) != OCI_SUCCESS)
			{
				m_pPool->PrintLog("get field type failed.");
				return false;
			}
			if(OCIAttrGet(fielddesc, OCI_DTYPE_PARAM, (dvoid**)&fieldname, &len, OCI_ATTR_NAME, conn->err)!=OCI_SUCCESS || len>=sizeof(desc[i].fieldname))
			{
				m_pPool->PrintLog("get field name failed.");
				return false;
			}
			strncpy(desc[i].fieldname, fieldname, sizeof(desc[i].fieldname));
			desc[i].fieldname[len] = 0;
			if(sqltype == SQLT_NUM)
			{
				if(OCIAttrGet(fielddesc, OCI_DTYPE_PARAM, &desc[i].precision, NULL, OCI_ATTR_PRECISION, conn->err) != OCI_SUCCESS)
				{
					m_pPool->PrintLog("get field precision failed.");
					return false;
				}
				if(OCIAttrGet(fielddesc, OCI_DTYPE_PARAM, &desc[i].scale, NULL, OCI_ATTR_SCALE, conn->err) != OCI_SUCCESS)
				{
					m_pPool->PrintLog("get field scale failed.");
					return false;
				}
			}
			else
			{
				if(OCIAttrGet(fielddesc, OCI_DTYPE_PARAM, &desc[i].precision, NULL, OCI_ATTR_DATA_SIZE, conn->err) != OCI_SUCCESS)
				{
					m_pPool->PrintLog("get field length failed.");
					return false;
				}
			}
			desc[i].type = DbType(sqltype);
		}
		if(OCIDefineByPos(conn->stmt, &p, conn->err, i+1, record[i].buf, MAX_VALUE_LENGTH, SQLT_STR, &conn->indicator[i], NULL, NULL, OCI_DEFAULT) != OCI_SUCCESS)
		{
			m_pPool->PrintLog("bind field buffer failed.");
			return false;
		}
	}
	if(OCIAttrSet(conn->stmt, OCI_HTYPE_STMT, &prenum, sizeof(int), OCI_ATTR_PREFETCH_ROWS, conn->err) != OCI_SUCCESS)
	{
		m_pPool->PrintLog("set preread number failed.");
		return false;
	}
	return true;
}

bool COCIAdapter::Next(void *stmt, DbField_t *record, int cols)
{
	sword ret;
	int i;
	OCI_Connect_t *conn = (OCI_Connect_t*)stmt;
	if(stmt==NULL || record==NULL || cols<=0 || m_pEnv==NULL)
		return false;
	ret = OCIStmtFetch(conn->stmt, conn->err, 1, OCI_FETCH_NEXT, OCI_DEFAULT);
	if(ret == OCI_SUCCESS)
	{
		for(i=0; i<cols; i++)
		{
			if(conn->indicator[i] < 0)
				record[i].null = true;
			else
				record[i].null = false;
		}
	}
	else
	{
		if(ret != OCI_NO_DATA)
			m_pPool->PrintLog("fetch record failed.");
		return false;
	}
	return true;
}

int COCIAdapter::Execute(void *stmt, char *sql, int timeout, int row)
{
	char sql2[2048];
	OCI_Connect_t *conn = (OCI_Connect_t*)stmt;
	int rows;
	if(stmt==NULL || sql==NULL || timeout<0 || row<0 || m_pEnv==NULL)
		return -1;
	OCIReset(conn->svc, conn->err);
	if(row > 0)
	{
		if(strstr(sql, "where") == NULL)
			snprintf(sql2, sizeof(sql2), "%s where rownum<=%d", sql, row);
		else
			snprintf(sql2, sizeof(sql2), "%s and rownum<=%d", sql, row);
	}
	else
	{
		strncpy(sql2, sql, sizeof(sql2));
	}
	if(OCIStmtPrepare(conn->stmt, conn->err, (OraText*)sql2, strlen(sql2), OCI_NTV_SYNTAX, OCI_DEFAULT) != OCI_SUCCESS)
	{
		m_pPool->PrintLog("prepare execute sql statement failed.");
		return -1;
	}
	if(OCIStmtExecute(conn->svc, conn->stmt, conn->err, 1, 0, NULL, NULL, OCI_COMMIT_ON_SUCCESS) != OCI_SUCCESS)
	{
		m_pPool->PrintLog("execute statement failed.");
		return -1;
	}
	if(OCIAttrGet(conn->stmt, OCI_HTYPE_STMT, &rows, NULL, OCI_ATTR_ROW_COUNT, conn->err) != OCI_SUCCESS)
	{
		m_pPool->PrintLog("get affected rows failed.");
		return -1;
	}
	return rows;
}

int COCIAdapter::Procedure(void *stmt, char *procname, DbProc_t *params, int nparam, int timeout)
{
	int i, ins;
	sword ret;
	char sql[1024];
	OCI_Connect_t *conn = (OCI_Connect_t*)stmt;
	OCIBind *bind = NULL;
	if(stmt==NULL || procname==NULL || nparam<0 || nparam>0 && params==NULL || timeout<0)
		return -1;

	OCIReset(conn->svc, conn->err);
	if(nparam > 0)
		ins = sprintf(sql, "begin %s(:v1", procname);
	else
		ins = sprintf(sql, "begin %s(", procname);
	for(i=0; i<nparam-1; i++)
		ins += sprintf(&sql[ins], ", :v%d", i+2);
	sprintf(&sql[ins], "); end;");

	if(OCIStmtPrepare(conn->stmt, conn->err, (OraText*)sql, strlen(sql), OCI_NTV_SYNTAX, OCI_DEFAULT) != OCI_SUCCESS)
	{
		m_pPool->PrintLog("prepare query sql statement failed.");
		return -1;
	}
	ins = 0;
	for(i=0; i<nparam; i++)
	{
		if(params[i].io == DBIO_IN)
			ins++;
		if(params[i].null)
			ret = OCIBindByPos(conn->stmt, &bind, conn->err, i+1, NULL, 0, SQLT_STR, &conn->indicator[i], 0, NULL, 0, NULL, OCI_DEFAULT);
		else
			ret = OCIBindByPos(conn->stmt, &bind, conn->err, i+1, params[i].value, MAX_VALUE_LENGTH, SQLT_STR, &conn->indicator[i], 0, NULL, 0, NULL, OCI_DEFAULT);
		if(ret != OCI_SUCCESS)
		{
			m_pPool->PrintLog("set procedure parameter failed.");
			return -1;
		}
	}
	if(OCIStmtExecute(conn->svc, conn->stmt, conn->err, 1, 0, NULL, NULL, OCI_COMMIT_ON_SUCCESS) != OCI_SUCCESS)
	{
		m_pPool->PrintLog("execute procedure failed.");
		return -1;
	}
	for(i=ins; i<nparam; i++)
	{
		if(conn->indicator[i] < 0)
			params[i].null = true;
		else
			params[i].null = false;
	}
	return ins;
}

DB_TYPE COCIAdapter::DbType(int type)
{
	DB_TYPE res;
	switch(type)
	{
	case SQLT_CHR:
	case SQLT_STR:
	case SQLT_VCS:
		res = DBTYPE_STRING;
		break;
	case SQLT_DAT:
		res = DBTYPE_DATETIME;
		break;
	case SQLT_NUM:
	case SQLT_PDN:
	case SQLT_INT:
	case SQLT_LNG:
		res = DBTYPE_INT;
		break;
	case SQLT_FLT:
		res = DBTYPE_FLOAT;
		break;
	default:
		res = DBTYPE_STRING;
		break;
	}
	return res;
}

int COCIAdapter::DbType(DB_TYPE type)
{
	int res;
	switch(type)
	{
	case DBTYPE_INT:
		res = SQLT_INT;
		break;
	case DBTYPE_FLOAT:
		res = SQLT_FLT;
		break;
	case DBTYPE_STRING:
		res = SQLT_VCS;
		break;
	case DBTYPE_DATETIME:
		res = SQLT_DAT;
		break;
	default:
		res = SQLT_VCS;
		break;
	}
	return res;
}

/***************************************************************/

void COCIAdapter::release()
{
	if(m_pEnv != NULL)
	{
		OCIHandleFree(m_pEnv, OCI_HTYPE_ENV);
		m_pEnv = NULL;
		OCITerminate(OCI_DEFAULT);
	}
}