database_op.cxx

当前不少单位在进行系统改造升级时

/*
	database_op.cxx  
	
	负责兼容 sybase 和 oracle对数据库的操作
	
 */
#include "stdio.h"
#include "stdlib.h"
#include "string.h"

#include "database_op.h"


#ifdef SYBASE_PLATFORM
/*
 *	不需要任何动作
 */

#endif


#ifdef MSSQL_PLATFORM
#endif






#ifdef ORACLE_PLATFORM



EXEC SQL INCLUDE sqlca;

EXEC SQL INCLUDE sqlda;

EXEC SQL WHENEVER NOT FOUND CONTINUE ;
EXEC SQL WHENEVER SQLWARNING CONTINUE ;
EXEC SQL WHENEVER SQLERROR CONTINUE ;

char    dyn_statement[1024];

SQLDA *select_dp = NULL;


#define  MAXCOLNUM 126
#define  MAXCOLVALUE 1024

char chValueBuff[MAXCOLNUM][MAXCOLVALUE]={0};



/* Maximum lengths of the _names_ of the
select-list items or indicator variables. */
#define MAX_VNAME_LEN     80
#define MAX_INAME_LEN     80

typedef struct Securepair
{
	struct Securepair	*next;
	char		*labelname;
	char		*labelvalue;
} SECLABELS;


typedef struct loginrec
{
	char	chLogName[256];
	char	chLogPass[256];
} LOGINREC_STR;


enum SQLTYPE_ENUM{SQL_NULL=0,SQL_SELECT,SQL_INSERT,SQL_UPDATE,SQL_DELETE};

struct dbprocess {
	sql_context ctx;
	char connName[20];
	
	enum SQLTYPE_ENUM sqltype ;/*当前sql语句类型，包括查询select  insert update delete，通过在sqldata中查找上述字符串获得*/
	long	rowindex ;	/*执行本次sql后，被取出的行的数目/下次需要取出的索引*/
	long	rownum ;	/*本次sql得到的行数目*/
	long	colnum ;	/*当前列数目*/
	long	sqlerror ;/*SQL语句错误号*/
	void *	ppbuff[MAXPOINTNUM] ;	/*当前绑定了字段的内存地址*/
	long	pbufftype[MAXPOINTNUM] ;
	char	sqldata[SQLDATALEN] ; /*当前sql语句值，在执行后更改上面的rowindex,rownum,colnum*/
};
typedef struct dbprocess    DBPROC_STR;


DBPROC_STR * AllOpenProcess[20]={0};
EHANDLEFUNC ehandfunc = 0 ;
MHANDLEFUNC mhandfunc = 0 ;








RETCODE dbinit		(void)
{
	int i ;
	

	if( select_dp ) return SUCCEED ;

    if ((select_dp =sqlald (MAXPOINTNUM, MAX_VNAME_LEN, MAX_INAME_LEN)) == (SQLDA *) 0)
    {
        fprintf(stderr,"Cannot allocate memory for select descriptor.");
        return FAIL;
    }
	
    select_dp->N = MAXPOINTNUM;
	
    /* Allocate the pointers to the indicator variables, and the
	actual data. */
    for (i = 0; i < MAXPOINTNUM; i++) {
        select_dp->I[i] = (short *) malloc(sizeof(short));
        select_dp->V[i] = (char *) malloc(1);
    }
	
	return SUCCEED;
}


void errhandlefunc()
{
	if( ehandfunc )
		(*ehandfunc)(0,0,0,0,"","") ;
}
void mhandlefunc()
{
	if( mhandfunc )
		(*mhandfunc)(0,0,0,0,"","","",0) ;
}

EHANDLEFUNC dberrhandle  (EHANDLEFUNC handler)
{
	ehandfunc  =  handler ;
/*	EXEC SQL WHENEVER SQLERROR DO errhandlefunc() ;*/
	return 0 ;
}

MHANDLEFUNC dbmsghandle  (MHANDLEFUNC handler)
{
	mhandfunc = handler ;
/*	EXEC SQL WHENEVER SQLWARNING DO mhandlefunc() ;*/
	return 0 ;
}

void *	dblogin (void)
{
	LOGINREC_STR * rec ;
	rec = (LOGINREC_STR*)malloc(sizeof(LOGINREC_STR)) ;
	if( NULL == rec ) return rec ;
	memset(rec,0,sizeof(LOGINREC_STR));
	return rec;
}



RETCODE dbsetlname  (LOGINREC_STR *lptr,char *values,int type)
{
	if( NULL == lptr || NULL == values ) return FAIL ;
	if( (DBSETUSER != type) &&(DBSETPWD != type) ) return FAIL ;

	if( DBSETUSER == type )
	{
		if( strlen(values) >= sizeof(lptr->chLogName) ) return FAIL ;
		strcpy( lptr->chLogName,values) ;
	}
	
	if( DBSETPWD == type )
	{
		if( strlen(values) >= sizeof(lptr->chLogPass) ) return FAIL ;
		strcpy( lptr->chLogPass,values) ;
	}
	return SUCCEED ;
}







/*
该函数内进行数据库连接尝试
*/
DBPROCESS *dbopen	(LOGINREC_STR *logon_ptr,char *servername)
{
	int iLen ,iIndex ;
	DBPROC_STR * dbprocess ;

   EXEC SQL BEGIN DECLARE SECTION;
	VARCHAR  username[256];
	VARCHAR  password[256];
	VARCHAR	 dbstring[256];
    EXEC SQL END DECLARE SECTION;
	
	if( NULL == logon_ptr ) return NULL ;

	dbprocess = (DBPROC_STR*)malloc(sizeof(DBPROC_STR));
	if( NULL == dbprocess ) return NULL ;

	memset(dbprocess,0,sizeof(DBPROC_STR));

	iLen = sizeof(AllOpenProcess)/sizeof(DBPROC_STR * );
	
	for ( iIndex = 0 ; iIndex < iLen ; iIndex ++ )
	{
		if( NULL == AllOpenProcess[iIndex] ) 
		{
			AllOpenProcess[iIndex] = dbprocess ;
			sprintf(dbprocess->connName,"conn%d",iIndex);
			break ;
		}
	}
	
	if( iIndex == iLen ) 
	{
		free(dbprocess) ;
		dbprocess  = NULL ;
	}

/*
此处进行连接尝试!!!!!!!!!！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！	
 */
	strncpy((char *) username.arr, logon_ptr->chLogName, sizeof(username.arr));
	username.len = strlen((char *) username.arr);
	strncpy((char *) password.arr, logon_ptr->chLogPass, sizeof(password.arr));
	password.len = strlen((char *) password.arr);

    EXEC SQL CONTEXT ALLOCATE :dbprocess->ctx;
    EXEC SQL CONTEXT USE :dbprocess->ctx;

	if( NULL == servername )
	{
		EXEC SQL CONNECT :username IDENTIFIED BY :password AT:dbprocess->connName ;
	}
	else
	{
		strncpy((char*)dbstring.arr,servername,sizeof(dbstring.arr) ) ;
		dbstring.len = strlen((char*)dbstring.arr);
		EXEC SQL CONNECT :username IDENTIFIED BY :password  AT:dbprocess->connName USING :dbstring ;
	}
	
	if (sqlca.sqlcode < 0 ) 
	{
		printf("\n%d %s\n",sqlca.sqlcode,sqlca.sqlerrm.sqlerrmc);
		printf("CONNECT ERROR!");		
		return FAIL ;
	}

	return (DBPROCESS*)dbprocess ;
}

void	dbloginfree (LOGINREC_STR *loginptr)
{
	if( NULL != loginptr ) 
	{
		memset( loginptr,0,sizeof(LOGINREC_STR));
		free(loginptr) ;
	}
}


/*
该函数仅仅拷贝命令，做简单的命令检查
 */
RETCODE dbcmd		(DBPROC_STR *dbproc,const char *cmdstring)
{
	long l_len ;
	char * pSelect , *pInsert , *pUpdate,*pDelete ,*pMin;

	if( NULL == dbproc || NULL == cmdstring ) return FAIL ;
	/*
	仅仅拷贝命令，做简单的命令检查！！！！！！！！！！！！！！！！！！！！！
	*/

	l_len = strlen(cmdstring) ;
	if(	l_len >= sizeof(dbproc->sqldata) )  return FAIL ;
	memcpy( dbproc->sqldata, cmdstring , l_len + 1) ;
	strupr(dbproc->sqldata);

	dbproc->colnum  =0 ;
	dbproc->rowindex = 0 ;
	dbproc->rownum = 0 ;
	memset(dbproc->ppbuff,0,sizeof(dbproc->ppbuff));
	memset(dbproc->pbufftype,0,sizeof(dbproc->pbufftype));
	
/*分析命令类型*/	
	pSelect = strstr(dbproc->sqldata,"SELECT");
	pInsert = strstr(dbproc->sqldata,"INSERT");
	pUpdate = strstr(dbproc->sqldata,"UPDATE");
	pDelete = strstr(dbproc->sqldata,"DELETE");
	
	pMin = dbproc->sqldata + strlen(dbproc->sqldata) ;
	dbproc->sqltype = SQL_NULL ;
	if( (pSelect) && (pSelect < pMin) ) { pMin = pSelect ;dbproc->sqltype = SQL_SELECT ; }
	if( (pInsert) && (pInsert < pMin) ) { pMin = pInsert ;dbproc->sqltype = SQL_INSERT ; }
	if( (pUpdate) && (pUpdate < pMin) ) { pMin = pUpdate ;dbproc->sqltype = SQL_UPDATE ; }
	if( (pDelete) && (pDelete < pMin) ) { pMin = pDelete ;dbproc->sqltype = SQL_DELETE ; }

	if( SQL_NULL == dbproc->sqltype ) 
	{
		dbproc->sqlerror = -1 ;
		return FAIL ;
	}

	return SUCCEED ;
}


/*
该函数	执行命令
 */
RETCODE dbsqlexec	(DBPROC_STR *dbproc)
{
	int i,null_ok, precision, scale;
	EXEC SQL BEGIN DECLARE SECTION;
	VARCHAR  chSqlStr[SQLDATALEN];
    EXEC SQL END DECLARE SECTION;

	if( NULL == dbproc ) return FAIL ;
	
	if( SQL_NULL == dbproc->sqltype ) return FAIL ;
	
	dbproc->colnum = 0 ;
	dbproc->rowindex = 0 ;
	dbproc->rownum = 0 ;
	dbproc->sqlerror = 0 ;
	memset( dbproc->ppbuff,0,sizeof(dbproc->ppbuff) );
	memset(dbproc->pbufftype,0,sizeof(dbproc->pbufftype));

	strcpy(chSqlStr.arr,dbproc->sqldata);
	chSqlStr.len = strlen(chSqlStr.arr);
	
	if( SQL_INSERT == dbproc->sqltype ||
		SQL_UPDATE == dbproc->sqltype ||
		SQL_DELETE == dbproc->sqltype 
		)
	{
		EXEC SQL CONTEXT USE :dbproc->ctx;
		EXEC SQL AT :dbproc->connName execute immediate :chSqlStr ;
		dbproc->sqlerror = sqlca.sqlcode ;
		if( dbproc->sqlerror < 0 )
		{
			printf("\n%s\n",sqlca.sqlerrm.sqlerrmc);
			EXEC SQL ROLLBACK ;
			return FAIL ;
		}
		dbproc->rownum =  sqlca.sqlerrd[2]  ;
		EXEC SQL CONTEXT USE :dbproc->ctx;
		EXEC SQL AT :dbproc->connName COMMIT ;
		return SUCCEED ;
	}
	

	
	/*采用CURSOR游标方式*/
	/*
	采用动态sql 4 方法
	 */
	if( SQL_SELECT == dbproc->sqltype )
	{
		sqlca.sqlcode = 0 ;

		if (sqlca.sqlcode != 0 ) 
		{
			printf("\nDECLARE  err = %d\n%s\n",sqlca.sqlcode , sqlca.sqlerrm.sqlerrmc);
			return FAIL ;
		}

		EXEC SQL CONTEXT USE :dbproc->ctx;

		EXEC SQL AT :dbproc->connName  PREPARE SSS FROM :chSqlStr;
		
		if (sqlca.sqlcode != 0 ) 
		{
			printf("\nPREPARE err = %d\n%s\n",sqlca.sqlcode , sqlca.sqlerrm.sqlerrmc);
			return FAIL ;
		}
		EXEC SQL CONTEXT USE :dbproc->ctx;
		EXEC SQL AT :dbproc->connName  DECLARE CCC CURSOR FOR SSS;
		if (sqlca.sqlcode != 0 ) 
		{
			printf("\nDECLARE CCC err = %d\n%s\n",sqlca.sqlcode , sqlca.sqlerrm.sqlerrmc);
			return FAIL ;
		}
		EXEC SQL CONTEXT USE :dbproc->ctx;
		EXEC SQL AT :dbproc->connName  OPEN CCC ; 
		if (sqlca.sqlcode != 0 ) 
		{
			printf("\nOPEN CCC err = %d\n%s\n",sqlca.sqlcode , sqlca.sqlerrm.sqlerrmc);