oracledb.c

电力与银行通讯的源码 1.应用tuxedo中间件. 2.与银行的前置机通讯. 3.proc*c 的应用

/******************************************************
 * OracleDb.c: implementation of the CDatabase class.
 *
 */
#include "StdAfx.h"
#ifdef ORACLE
#include <stdio.h>
#include <string.h>
#include <time.h>
#include <stdarg.h>
#include "OracleDB.h"
#define CS_SUCCEED	0
#define CS_FAIL		1
#define RETURN_IF(pDatabase, bSuccess,szErrorMsg) \
	if(bSuccess != CS_SUCCEED) \
	{\
		DB_SetLastError(pDatabase, szErrorMsg);\
		return FALSE;\
	}
int strcompi(const char* pString1, const char* pString2)
{
	int i;
	int nRet = 0;
	for(i=0;pString1[i] && pString2[i];i++)
	{
		if(toupper(pString1[i]) != toupper(pString2[i]))
		{
			if(toupper(pString1[i])>toupper(pString2[i]))
				nRet = 1;
			else
				nRet = -1;
			break;
		}
	}
	if(nRet==0)
	{
		if(pString1[i])		 nRet = 1;
		else if(pString2[i]) nRet = -1;
	}
	return nRet;
}
int strncompi(char *pString1, char *pString2, int n)
{
	int i;
	int nRet = 0;
	for(i=0; i<n; i++)
	{
		if(toupper(pString1[i]) != toupper(pString2[i]))
		{
			if(toupper(pString1[i])>toupper(pString2[i]))
				nRet = 1;
			else
				nRet = -1;
			break;
		}
	}
	return nRet;
}
void StringReplace(char *pString, char *pSrc, char *pDest)
{
	char *ptr, *ptrSrc, *ptrDest;
	char *pTemp;
	if(pString == NULL || pSrc == NULL)
		return;
	ptr=strstr(pString, pSrc);
	if(!ptr) return;
	pTemp = strdup(pString);
	if(!pTemp) return;
	
	ptrSrc=pTemp;
	ptrDest=pString;
	ptrDest[0]=0;
	
	ptr=strstr(pTemp, pSrc);
	while(ptr)
	{
		ptr[0]=0;
		strcat(ptrDest, ptrSrc);
		strcat(ptrDest, pDest);
		ptrDest=ptrDest+strlen(ptrDest);
		ptrSrc=ptr+strlen(pSrc);
	
		ptr=strstr(ptrSrc, pSrc);
	}
	strcat(ptrDest, ptrSrc);
	free(pTemp);
}
/******************************************************
 * Construction/Destruction
 */
void OracleErrorReport(CDatabase *pDatabase, Cda_Def *cursor, char *pSql)
{
    sword n;
    char msg[1024];
	char msgerr[512];
    n = oerhms(&pDatabase->m_lda, cursor->rc, (text*)msg, (sword) sizeof msg);
    /*fprintf(stderr, "%s\n", msg);*/
    if(cursor->fc > 0)
		if(!pSql)
			sprintf(msgerr, "Processing OCI function %s\n", oci_func_tab[cursor->fc]);
		else
			sprintf(msgerr, "Processing OCI function %s\nSQL=%s\n", 
			oci_func_tab[cursor->fc], pSql);
	strcat((char*)msg, msgerr);
		
#ifdef WIN32
	MessageBeep(MB_ICONHAND);
	AfxMessageBox(msg);
#else
    fprintf(stderr, "%s\n", msg);
#endif
}
void DB_Construct(CDatabase *pDatabase)
{
	pDatabase->m_bOpened = FALSE;
}
void DB_Destruct(CDatabase *pDatabase)
{
	DB_Close(pDatabase, TRUE);
}
BOOL DB_Open(CDatabase *pDatabase,char *pServerName, char *pDatabaseName, char *pUserName, char *pPassword)
{
	char szUID[100];
	if(pDatabase->m_bOpened) 
	{
		DB_SetLastError(pDatabase, "ORACLE Client 已经打开一个数据库!");
		return FALSE;
	}
	
	sprintf(szUID, "%s/%s@%s", pUserName, pPassword, pServerName);
	if(orlon(&pDatabase->m_lda, (ub1 *)pDatabase->m_hda, (text*)szUID, -1, (text *) 0, -1, 0))
    {
        OracleErrorReport(pDatabase, &pDatabase->m_lda, NULL);
        return FALSE;
    }
    
    if(oopen(&pDatabase->m_cda, &pDatabase->m_lda, (text *) 0, -1, -1, (text *) 0, -1))
    {
        OracleErrorReport(pDatabase, &pDatabase->m_cda, NULL);
        return FALSE;
    }
    /* Turn on auto-commit. Default is off, however. */
    if(ocon(&pDatabase->m_lda))
    {
        DB_Close(pDatabase, TRUE);
		OracleErrorReport(pDatabase, &pDatabase->m_lda, NULL);
        return FALSE;
    }
	pDatabase->m_bOpened = TRUE;
	return TRUE;
}
BOOL DB_Close(CDatabase *pDatabase, BOOL bForceClose)
{
    int nError=0;
	if(pDatabase->m_bOpened == FALSE) return TRUE;
	if(oclose(&pDatabase->m_cda))
    {
        DB_SetLastError(pDatabase, "Error closing cursor.\n");
        nError++;
    }
    
	if(ologof(&pDatabase->m_lda))
    {
        DB_SetLastError(pDatabase, "Error on disconnect.\n");
        nError++;
    }
	pDatabase->m_bOpened = FALSE;
	
	if(nError) return FALSE;
	return TRUE;
}
BOOL DB_IsOpen(CDatabase *pDatabase)
{
	return(pDatabase->m_bOpened);
}
BOOL DB_ExecuteSQL(CDatabase *pDatabase, char *pSQL)
{
	static char szSql[1024];
	strcpy(szSql, pSQL);
	StringReplace(szSql, "convert(int,", "floor(");
	StringReplace(szSql, "getdate()", "sysdate");
	StringReplace(szSql, "#", "Tmp_");
	StringReplace(szSql, "numeric(10)", "number(11)");
	StringReplace(szSql, "numeric", "number");
	StringReplace(szSql, " datetime ", " date ");
	StringReplace(szSql, "varchar2", "vchar2");
	StringReplace(szSql, "varchar", "varchar2");
	StringReplace(szSql, "vchar2", "varchar2");
	StringReplace(szSql, "tinyint", "number(3)");
	StringReplace(szSql, "integer", "number(10)");
	StringReplace(szSql, "\"", "\'");
	
	if(!pDatabase->m_bOpened)
	RETURN_IF(pDatabase, CS_FAIL, "Oracle Client 数据库未打开!");
    if(oparse(&pDatabase->m_cda, (text*)szSql, (sb4) -1, DEFER_PARSE, (ub4) VERSION_7))
    {
		OracleErrorReport(pDatabase, &pDatabase->m_cda, szSql);
		return FALSE;
    }
	
	if(oexec(&pDatabase->m_cda))
	{
		if(strncompi(szSql, "DROP", 4))
			OracleErrorReport(pDatabase, &pDatabase->m_cda, szSql);
		return FALSE;
	}
	return TRUE;
}
void DB_SetLastError(CDatabase *pDatabase, char *pErrorMsg)
{
	if(strlen(pErrorMsg) > ERROR_BUFFER_LEN)
	{
		strncpy(pDatabase->m_szErrorBuffer, pErrorMsg, ERROR_BUFFER_LEN-4);
		strcpy(pDatabase->m_szErrorBuffer+ERROR_BUFFER_LEN-4,"...");
	}
	else
	{
		strcpy(pDatabase->m_szErrorBuffer, pErrorMsg);
	}
}
void DB_GetLastError(CDatabase *pDatabase, char *pErrorBuffer, int cbErrorBuffer)
{
	int nLen;
	nLen = strlen(pDatabase->m_szErrorBuffer);
	if(cbErrorBuffer <= nLen)
	{
		strncpy(pErrorBuffer, pDatabase->m_szErrorBuffer, nLen-4);
		strcpy(pErrorBuffer+nLen-4,"...");
	}
	else
	{
		strcpy(pErrorBuffer, pDatabase->m_szErrorBuffer);
	}
}
BOOL DB_SQLPrepare(CDatabase *pDatabase, char *pSQL, void *ppBindVar[], int cbBindVar)
{
	int i;
	static sb4 nColMaxLen,nColLen;
	static sb2 nColType;
	static sb1 szColName[100];
	static sb4 nColNameLen=sizeof(szColName);
	static char szSql[1024];
	
	sb2	nPrec, nScale, nNullOk;
	strcpy(szSql, pSQL);
	StringReplace(szSql, "convert(int,", "floor(");
	StringReplace(szSql, "getdate()", "sysdate");
	StringReplace(szSql, "#", "Tmp_");
	StringReplace(szSql, "numeric(10)", "number(11)");
	StringReplace(szSql, "numeric", "number");
	StringReplace(szSql, "varchar2", "vchar2");
	StringReplace(szSql, "varchar", "varchar2");
	StringReplace(szSql, "vchar2", "varchar2");
	StringReplace(szSql, "tinyint", "number(3)");
	StringReplace(szSql, "integer", "number(10)");
	StringReplace(szSql, "\"", "\'");
	
	if(!pDatabase->m_bOpened)
	RETURN_IF(pDatabase, CS_FAIL, "Oracle Client 数据库未打开!");
    if(oparse(&pDatabase->m_cda, (text*)szSql, (sb4) -1, DEFER_PARSE, (ub4) VERSION_7))
    {
		OracleErrorReport(pDatabase, &pDatabase->m_cda, szSql);
		return FALSE;
    }
    
	for(i=0; i<cbBindVar; i++)
	{
		if(odescr(&pDatabase->m_cda, i+1, &nColMaxLen,
				(sb2 *)&nColType, szColName, (sb4 *)&nColNameLen,
				(sb4 *)&nColLen, &nPrec, &nScale, &nNullOk))
		{
			OracleErrorReport(pDatabase, &pDatabase->m_cda, szSql);
			return FALSE;
		}
		if(nColType == SQLT_CHR || nColType == SQLT_STR)
		{
			nColType = SQLT_STR;
			nColLen = -1;
		}
		else if(nColType == SQLT_INT) nColLen = sizeof(sword);
		else if(nColType == SQLT_FLT) nColLen = sizeof(double);
		else if(nColType == SQLT_NUM)
		{
			if(nPrec>0 && nPrec<4 && nScale==0)
			{
				nColType = SQLT_UIN;
				nColLen = sizeof(BYTE);
			}
			else if(nPrec>3 && nPrec<6 && nScale==0)
			{
				nColType = SQLT_INT;
				nColLen = sizeof(short);
			}
			else if(nPrec==0 && nScale==0 && strncmp((char*)szColName, "MAX(", 4)==0)
			{
				nColType = SQLT_FLT;
				nColLen = sizeof(double);
			}
			else if(nPrec<10 && nScale==0)
			{
				nColType = SQLT_INT;
				nColLen = sizeof(int);
			}
			else
			{
				nColType = SQLT_FLT;
				nColLen = sizeof(double);
			}
		}
		if(odefin(&pDatabase->m_cda,
               i+1,
               (ub1 *)ppBindVar[i],
               nColLen,
               nColType,
               -1,
               (sb2 *) &pDatabase->m_nIndicator[i],
               (text *) 0,
               -1,
               -1,
               (ub2 *) 0,
               (ub2 *) 0))
		{
			OracleErrorReport(pDatabase, &pDatabase->m_cda ,szSql);
			return FALSE;
		}
	}
	
	if(oexec(&pDatabase->m_cda))
	{
		OracleErrorReport(pDatabase, &pDatabase->m_cda, szSql);
		return FALSE;
	}
	return TRUE;
}
BOOL DB_SQLFetch(CDatabase *pDatabase)
{
	if(ofetch(&pDatabase->m_cda))
	{
		if(pDatabase->m_cda.rc != 1403)
			OracleErrorReport(pDatabase, &pDatabase->m_cda, NULL);
		return FALSE;
	}
	return TRUE;
}
BOOL DB_SQLDone(CDatabase *pDatabase)
{
	ocan(&pDatabase->m_cda);
	return TRUE;
}
BOOL DB_BlkCopyInPrepare(CDatabase *pDatabase, char *pTableName, int nColumn)
{
	int i;
	sb4 nColMaxLen,nColLen;
	sb2 nColType;
	static sb1 szColName[50];
	sb4 nColNameLen=sizeof(szColName)-1;
	static char szSql[512], szSql2[512],szCat[50];
	
	sb2	nPrec, nScale, nNullOk;
	if(!pDatabase->m_bOpened)
	RETURN_IF(pDatabase, CS_FAIL, "Oracle Client 数据库未打开!");
    sprintf(szSql, "select * from %s", pTableName);
	StringReplace(szSql, "#", "Tmp_");
	if(oparse(&pDatabase->m_cda, (text*)szSql, (sb4) -1, DEFER_PARSE, (ub4) VERSION_7))
    {
		OracleErrorReport(pDatabase, &pDatabase->m_cda, szSql);
		return FALSE;
    }
    
	pDatabase->m_pColumn = (CColumn *)malloc((ub4)(nColumn * sizeof(CColumn)));
	sprintf(szSql2, "insert into %s values(", pTableName);
	StringReplace(szSql2, "#", "Tmp_");
	for(i=0; i<nColumn; i++)
	{
		if(odescr(&pDatabase->m_cda, i+1, &nColMaxLen,
				(sb2 *)&nColType, szColName, (sb4 *)&nColNameLen,
				(sb4 *)&nColLen, &nPrec, &nScale, &nNullOk))
		{
			OracleErrorReport(pDatabase, &pDatabase->m_cda, szSql);
			return FALSE;
		}
		if(nColType == SQLT_CHR || nColType == SQLT_STR || nColType == SQLT_AFC)
		{
			nColType = SQLT_STR;
			nColLen = -1;
		}
		else if(nColType == SQLT_INT) nColLen = sizeof(sword);
		else if(nColType == SQLT_FLT) nColLen = sizeof(double);
		else if(nColType == SQLT_NUM)
		{
			if(nPrec>0 && nPrec<4 && nScale==0)
			{
				nColType = SQLT_UIN;
				nColLen = sizeof(BYTE);
			}
			else if(nPrec>3 && nPrec<6 && nScale==0)
			{
				nColType = SQLT_UIN;
				nColLen = sizeof(short);
			}
			else if(nPrec<10 && nScale==0)
			{
				nColType = SQLT_UIN;
				nColLen = sizeof(int);
			}
			else
			{
				nColType = SQLT_FLT;
				nColLen = sizeof(double);
			}
		}
		if(nColType == SQLT_DAT)
			sprintf(szCat, "to_date(:col%d, \'YYYYMMDDHH24MISS\')", i);
		else
			sprintf(szCat, ":col%d", i);
		strcat(szSql2, szCat);
		if(i==nColumn-1) strcat(szSql2, ")");
		else strcat(szSql2, ",");
		pDatabase->m_pColumn[i].c_type = nColType;
		pDatabase->m_pColumn[i].c_size = (short)nColLen;
		
		pDatabase->m_pColumn[i].c_indp = NULL;
		pDatabase->m_pColumn[i].c_rlen = NULL;
		pDatabase->m_pColumn[i].c_rcode = NULL;
		pDatabase->m_pColumn[i].c_buf = NULL;
	}
	
	ocan(&pDatabase->m_cda);
	
	if(oparse(&pDatabase->m_cda, (text*)szSql2, (sb4) -1, DEFER_PARSE, (ub4) VERSION_7))
	{
			OracleErrorReport(pDatabase, &pDatabase->m_cda, szSql2);
			return FALSE;
	}
	pDatabase->m_nBlkRowCount = 0;
	pDatabase->m_nColumnCount = nColumn;
	pDatabase->m_bBind = FALSE;
	return TRUE;
}
	
BOOL DB_BlkCopyInExecute(CDatabase *pDatabase, void *ppVarArray[], long nLenArray[])
{
	static char szVar[100];
	static CColumn *ptrColumn;
	static int nColIndex;
	static int nColumnType;
	static time_t timeDate;
	static struct tm tmDate;
	static CS_DATETIME dtDate;
	static char szTime[20];
	static ub4  nMaxRows = MAX_ROWS_PER_INSERT;
	
	ptrColumn = pDatabase->m_pColumn;
	if(pDatabase->m_bBind == FALSE)
	{
		pDatabase->m_bBind = TRUE;
		for(nColIndex = 0; nColIndex < pDatabase->m_nColumnCount; nColIndex++, ptrColumn++)
		{
			if(nLenArray[nColIndex] == CS_UNUSED) 
				ptrColumn->c_size = sizeof(double);
			else if(ptrColumn->c_type == SQLT_STR)
				ptrColumn->c_size = (short)nLenArray[nColIndex]+1;
			else
				ptrColumn->c_size = (short)nLenArray[nColIndex];
			sprintf(szVar, ":col%d", nColIndex);
			
			if(ptrColumn->c_type == SQLT_DAT)
			{
				nColumnType = SQLT_STR;
				ptrColumn->c_size = 15;
			}
			else
			{
				nColumnType = ptrColumn->c_type;
			}
			
			ptrColumn->c_indp  = (sb2*)malloc((ub4)(MAX_ROWS_PER_INSERT*sizeof(sb2)));
			memset(ptrColumn->c_indp, 0, MAX_ROWS_PER_INSERT * sizeof(sb2));