将Sybase C程序快速移植到Oracle Pro*C程序
2006-07-23 11:17:13 来源：WEB开发网核心提示： database_op.cxx/*database_op.cxx负责兼容 sybase 和 oracle对数据库的操作*/#include "stdio.h"#include "stdlib.h"#include "string.h&quo
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];
/*当前sql语句类型，包括查询select insert update delete，*/
/*通过在sqldata中查找上述字符串获得*/
enum SQLTYPE_ENUM sqltype ;
long rowindex ; /*执行本次sql后，被取出的行的数目/下次需要取出的索引*/
long rownum ; /*本次sql得到的行数目*/
long colnum ; /*当前列数目*/
long sqlerror ;/*SQL语句错误号*/
void * ppbuff[MAXPOINTNUM] ; /*当前绑定了字段的内存地址*/
long pbufftype[MAXPOINTNUM] ;
/*当前sql语句值，在执行后更改上面的rowindex,rownum,colnum*/
char sqldata[SQLDATALEN] ;
};
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);
return FAIL ;
}
select_dp->N = MAXPOINTNUM;
EXEC SQL CONTEXT USE :dbproc->ctx;
EXEC SQL AT :dbproc->connName DESCRIBE SELECT LIST FOR SSS INTO select_dp;
dbproc->sqlerror = sqlca.sqlcode ;
if( dbproc->sqlerror < 0 )
{
printf("\nerr = %d\n%s\n",
sqlca.sqlcode ,
sqlca.sqlerrm.sqlerrmc);
EXEC SQL CONTEXT USE :dbproc->ctx;
EXEC SQL AT :dbproc->connName CLOSE CCC ;
EXEC SQL CONTEXT USE :dbproc->ctx;
EXEC SQL AT :dbproc->connName ROLLBACK ;
return FAIL ;
}
if (select_dp->F < 0 )
{
printf("\nerr = %d\n%s\n",
sqlca.sqlcode ,
sqlca.sqlerrm.sqlerrmc);
EXEC SQL CONTEXT USE :dbproc->ctx;
EXEC SQL AT :dbproc->connName CLOSE CCC ;
EXEC SQL CONTEXT USE :dbproc->ctx;
EXEC SQL AT :dbproc->connName ROLLBACK ;
return FAIL ;
}
if (select_dp->F > MAXPOINTNUM )
{
printf("\nerr = %d\n%s\n",
sqlca.sqlcode ,
sqlca.sqlerrm.sqlerrmc);
EXEC SQL CONTEXT USE :dbproc->ctx;
EXEC SQL AT :dbproc->connName CLOSE CCC ;
EXEC SQL CONTEXT USE :dbproc->ctx;
EXEC SQL AT :dbproc->connName ROLLBACK ;
return FAIL ;
}
select_dp->N = select_dp->F;
dbproc->colnum = select_dp->N ;
/*不能直接取到所有行的数目，所以直接采用当前行加1的方式处理*/
dbproc->rownum = dbproc->rowindex + 1 ;
for (i = 0; i < select_dp->F; i++)
{
/*char title[MAX_VNAME_LEN]; */
/* Turn off high-order bit of datatype (in this example, it does not */
/* matter if the column is NOT NULL). */
sqlnul ((unsigned short *)&(select_dp->T[i]),
(unsigned short *)&(select_dp->T[i]),
&null_ok);
/*
VARCHAR2 1 char[n]
NUMBER 2 char[n] ( n <= 22)
INTEGER 3 int
FLOAT 4 float
STRING 5 char[n+1]
VARNUM 6 char[n] (n <= 22)
DECIMAL 7 float
LONG 8 char[n]
VARCHAR 9 char[n+2]
ROWID 11 char[n]
DATE 12 char[n]
VARRAW 15 char[n]
RAW 23 unsigned char[n]
LONG RAW 24 unsigned char[n]
UNSIGNED 68 unsigned int
DISPLAY 91 char[n]
LONG VARCHAR 94 char[n+4]
LONG VARRAW 95 unsigned char[n+4]
CHAR 96 char[n]
CHARF 96 char[n]
CHARZ 97 char[n+1]
*/
switch (select_dp->T[i])
{
/* CHAR datatype: no change in length needed, except */
/* possibly for TO_CHAR conversions (not handled here). */
case 1 :
break;
/* NUMBER datatype: use sqlprc() to extract precision and scale. */
case 2 :
sqlprc ((unsigned long *)&(select_dp->L[i]),
&precision, &scale);
/* Allow for maximum size of NUMBER. */
if (precision == 0) precision = 40;
/* Also allow for decimal point and possible sign. */
/* convert NUMBER datatype to FLOAT if scale > 0,*/
/* INT otherwise. */
if (scale > 0) select_dp->L[i] = sizeof(float);
else select_dp->L[i] = sizeof(int);
break;
case 8 : /* LONG datatype */
select_dp->L[i] = 240;
break;
case 11 : /* ROWID datatype */
case 104 : /* Universal ROWID datatype */
select_dp->L[i] = 18;
break;
case 12 : /* DATE datatype */
select_dp->L[i] = 9;
break;
case 23 : /* RAW datatype */
break;
case 24 : /* LONG RAW datatype */
select_dp->L[i] = 240;
break;
}
/* Allocate space for the select-list data values.sqlald() */
/* reserves a pointer location for V[i] but does not allocate */
/* the full space for the pointer. */
if (select_dp->T[i] != 2)
{
select_dp->V[i] = (char *) realloc(select_dp->V[i],
select_dp->L[i] + 1);
}
else
select_dp->V[i] = (char *) realloc(select_dp->V[i],
select_dp->L[i]);
/* Print column headings, right-justifying number column headings. */
/* Copy to temporary buffer in case name is null-terminated */
/* strncpy(title, select_dp->S[i], select_dp->C[i]);
if (select_dp->T[i] == 2)
if (scale > 0)
printf ("%.*s ", select_dp->L[i]+3, title);
else
printf ("%.*s ", select_dp->L[i], title);
else
printf("%-.*s ", select_dp->L[i], title);
*/
/* Coerce ALL datatypes except for LONG RAW and NUMBER to character. */
if (select_dp->T[i] != 24 && select_dp->T[i] != 2)
select_dp->T[i] = 1;
/* Coerce the datatypes of NUMBERs to float or int */
/* depending on the scale. */
if (select_dp->T[i] == 2)
{
if (scale > 0)
select_dp->T[i] = 4; /* float */
else
select_dp->T[i] = 3; /* int */
}
}
}
return SUCCEED ;
}
/*
该函数判断数据库返回结果，操作DBPROC_STR的特定字段
SUCCEED NO_MORE_RESULTS
*/
RETCODE dbresults (DBPROC_STR *dbproc)
{
if( NULL == dbproc ) return NO_MORE_RESULTS;
if( dbproc->sqlerror < 0 ) return NO_MORE_RESULTS ;
if( dbproc->rowindex != dbproc->rownum ) return SUCCEED ;
return NO_MORE_RESULTS ;
}
/*
绑定值，绑定存储值的位子
*/
RETCODE dbbind (DBPROC_STR *dbproc,int colnum,int vartype,DBINT varlen,BYTE *destvar)
{
if( colnum <= 0 ) return FAIL ;
if( colnum > dbproc->colnum ) return FAIL ;
if( colnum >= MAXPOINTNUM ) return FAIL ;
/*
当前仅支持此类型
*/
if( NTBSTRINGBIND != vartype )
{
printf("\n!!!!!!!!!!!!!UNKNOWN TYPE %d !!!!!!!!\n\n",vartype);
exit(0);/*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
}
dbproc->ppbuff[colnum - 1 ] = destvar ;
dbproc->pbufftype[colnum - 1 ] = vartype ;
return FAIL ;
}
/*
获得本次执行返回多少列
*/
int dbnumcols (DBPROC_STR *dbproc)
{
return dbproc->colnum ;
}
/*
将下一条值赋值到绑定的变量上
*/
STATUS dbnextrow (DBPROC_STR *dbprocess)
{
int i ;
if( NULL == dbprocess ) return NO_MORE_ROWS ;
/* FETCH each row selected and print the column values. */
EXEC SQL CONTEXT USE :dbprocess->ctx;
EXEC SQL WHENEVER NOT FOUND GOTO end_select_loop;
EXEC SQL CONTEXT USE :dbprocess->ctx;
EXEC SQL AT :dbprocess->connName FETCH CCC USING DESCRIPTOR select_dp;
/* Since each variable returned has been coerced to a character string, */
/* int, or float very little processing is required here. This routine */
/* just prints out the values on the terminal. */
for (i = 0; i < select_dp->F; i++)
{
if( NULL == dbprocess->ppbuff[i] ) continue ;
if (*select_dp->I[i] < 0)
{
switch(dbprocess->pbufftype[i])
{
case NTBSTRINGBIND :
/*
if (select_dp->T[i] == 4)
sprintf ("%-*c ",(int)select_dp->L[i]+3, '' '');
else
printf ("%-*c ",(int)select_dp->L[i], '' '');
*/
strcpy(dbprocess->ppbuff[i],"");
break;
case CHARBIND:
*((char*)dbprocess->ppbuff[i]) = 0 ;
break ;
case INTBIND:
*((int*)dbprocess->ppbuff[i]) = 0 ;
break ;
default:
/*
* ERROR ! not support type!
*/
dbprocess->sqlerror = -50001;
return NO_MORE_ROWS ;
}
}
else
{
switch(dbprocess->pbufftype[i])
{
case NTBSTRINGBIND :
if (select_dp->T[i] == 3) /* int datatype */
sprintf (dbprocess->ppbuff[i],
"%*d", (int)select_dp->L[i],
*(int *)select_dp->V[i]);
else if (select_dp->T[i] == 4) /* float datatype */
sprintf (dbprocess->ppbuff[i],
"%*.2f", (int)select_dp->L[i],
*(float *)select_dp->V[i]);
else /* character string */
sprintf (dbprocess->ppbuff[i],
"%-*.*s",
(int)select_dp->L[i],
(int)select_dp->L[i],
select_dp->V[i]);
break;
case CHARBIND:
*((char*)dbprocess->ppbuff[i]) = *(char*) select_dp->V[i] ;
break ;
case INTBIND:
*((int*)dbprocess->ppbuff[i]) = *(int*) select_dp->V[i] ;
break ;
default:
/*
* ERROR ! not support type!
*/
dbprocess->sqlerror = -50001;
return NO_MORE_ROWS ;
}
}
}
return SUCCEED ;
end_select_loop :
dbprocess->rownum = dbprocess->rowindex ;
return NO_MORE_ROWS ;
}
/*
判断该连接是否正常
*/
DBBOOL dbdead (DBPROC_STR *a)
{
char chbuff[8];
chbuff[0]=''\0'';
EXEC SQL WHENEVER NOT FOUND GOTO end_select_loop;
EXEC SQL CONTEXT USE :a->ctx;
EXEC SQL AT :a->connName SELECT ''A'' INTO :chbuff FROM DUAL ;
if (''A'' != chbuff[0])
{
return FAIL ;
}
return SUCCEED ;
end_select_loop:
return FAIL ;
}
/*
该函数释放dbopen产生的内存，同时断开数据库连接
需要进一步的考虑！
*/
void dbexit (void)
{
int i,iLen ,iIndex ;
EXEC SQL WHENEVER SQLERROR CONTINUE;
iLen = sizeof(AllOpenProcess)/sizeof(DBPROC_STR * );
for ( iIndex = 0 ; iIndex < iLen ; iIndex ++ )
{
if( NULL != AllOpenProcess[iIndex] )
{
/*
* 此处开始断开数据库连接！！！！！！！！！！！！！！
*/
EXEC SQL CONTEXT USE : AllOpenProcess[iIndex]->ctx;
EXEC SQL WHENEVER NOT FOUND GOTO end_select_loop;
/* Close the cursor. */
EXEC SQL AT :AllOpenProcess[iIndex]->connName CLOSE CCC;
EXEC SQL AT :AllOpenProcess[iIndex]->connName COMMIT WORK RELEASE;
free(AllOpenProcess[iIndex]);
AllOpenProcess[iIndex] = NULL ;
}
}
if( select_dp )
{
for (i = 0; i < MAXPOINTNUM; i++)
{
if (select_dp->V[i] != (char *) 0)
free(select_dp->V[i]);
free(select_dp->I[i]); /* MAXPOINTNUM were allocated. */
}
/* Free space used by the descriptors themselves. */
sqlclu(select_dp);
select_dp = NULL ;
}
return;
}
#endif
#ifdef MSSQL_PLATFORM
#endif
中查找“将Sybase C程序快速移植到Oracle Pro*C程序”更多相关内容
中查找“将Sybase C程序快速移植到Oracle Pro*C程序”更多相关内容更多精彩
赞助商链接
