关于oci 事务处理的问题C/C++ code/*关于oci 事务处理的问题1.能否实现在程序退出前没有调用 OCITransComm
关于oci 事务处理的问题
- C/C++ code
/* 关于oci 事务处理的问题 1.能否实现在程序退出前没有调用 OCITransCommit,则所有执行的语句(例如insert,update)无效，即自动回滚？ 2.怎样在发生不可意料的错误时，让事务自动回滚？ 表结构： create table tst(id varchar(20),name varchar(20),datecreated date default sysdate);*/#include "oci.h"#include "iostream"#include "string"using namespace std ;class OciConnection {private: sword m_returnvalue;public: OCIEnv *envhp; OCIServer *svrhp; OCISvcCtx *svchp; OCIError *errhp; OCISession *sesshp; OCIDescribe *deshp; OCIStmt *seshp; OCIBind *bndhp; bool Open() { string DB="orcl",UserName="test",PWD="test"; OCIEnvCreate(&envhp, OCI_DEFAULT, (void *)0,0, 0, 0, (size_t) 0, (void **)0); OCIHandleAlloc((void *)envhp,(void**)&svrhp,OCI_HTYPE_SERVER,0, (void **)0 ); OCIHandleAlloc((void *)envhp,(void**)&errhp,OCI_HTYPE_ERROR,0, (void **)0 ); OCIServerAttach(svrhp,errhp,(text *)DB.c_str(),(sb4)strlen(DB.c_str()),OCI_DEFAULT); OCIHandleAlloc((void *)envhp,(void**)&svchp,OCI_HTYPE_SVCCTX,0,(void **)0); OCIAttrSet((void*)svchp,(ub4)OCI_HTYPE_SVCCTX,(void*) svrhp,(ub4)0,OCI_ATTR_SERVER,errhp); OCIHandleAlloc((void*)envhp,(void**)&sesshp,OCI_HTYPE_SESSION,0,(void **)0); OCIAttrSet((void*)sesshp,OCI_HTYPE_SESSION,(void*)UserName.c_str(),(ub4)strlen(UserName.c_str()),OCI_ATTR_USERNAME,errhp); OCIAttrSet((void*)sesshp,OCI_HTYPE_SESSION,(void*)PWD.c_str(),(ub4)strlen(PWD.c_str()),OCI_ATTR_PASSWORD,errhp); m_returnvalue=OCISessionBegin(svchp,errhp,sesshp,OCI_CRED_RDBMS,OCI_DEFAULT); if (m_returnvalue) { return false; } OCIAttrSet((void*)svchp,OCI_HTYPE_SVCCTX,(void*)sesshp,0,OCI_ATTR_SESSION,errhp); OCIHandleAlloc((void*)envhp,(void**)&deshp,OCI_HTYPE_DESCRIBE,0,(void **)0); OCIHandleAlloc((void *)envhp,(dvoid**)&seshp,OCI_HTYPE_STMT,0,0); return true; } void Close() { //结束会话 OCISessionEnd(svchp, errhp, sesshp, (ub4)0); OCIServerDetach(svrhp, errhp, OCI_DEFAULT); OCIHandleFree((void*)svrhp, OCI_HTYPE_SERVER); OCIHandleFree((void*)svchp, OCI_HTYPE_SVCCTX); OCIHandleFree((void*)errhp, OCI_HTYPE_ERROR); OCIHandleFree((void*)sesshp, OCI_HTYPE_SESSION ); OCIHandleFree((void*)deshp, OCI_HTYPE_DESCRIBE); OCIHandleFree((void*)deshp, OCI_HTYPE_DESCRIBE); OCIHandleFree((dvoid *) envhp, (ub4) OCI_HTYPE_ENV); } public: OciConnection() { m_returnvalue=0; } ~OciConnection() { if (m_returnvalue) { return; } Close(); } bool Execute(string strSQLString) { text strSQL[1024]; int sreturn=0; sprintf((char*)strSQL,strSQLString.c_str()); OCIStmtPrepare(seshp,errhp,strSQL,strlen((char*)strSQL),OCI_NTV_SYNTAX,OCI_DEFAULT); OCIStmtExecute(svchp,seshp,errhp,(ub4)1,0,NULL,NULL,OCI_DEFAULT); return true; }};int main(){ string strSQL=""; OciConnection cn; sword returnvalue=0; if (!cn.Open()) { cout<<"数据库未连接"<<endl; system("pause"); return 0; } returnvalue=OCITransStart(cn.svchp,cn.errhp,30,OCI_TRANS_NEW); //returnvalue的值为什么是-2 ？ cout<<returnvalue<<endl; strSQL="delete from tst"; cn.Execute(strSQL); strSQL="insert into tst(id,name) values('001','人员1')"; cn.Execute(strSQL); int a=0,b=1,c=0; //这里发生意外错误，'001','人员1' 的数据还是更新到表tst中了，怎么实现此时让数据自动回滚? c=b/a; strSQL="insert into tst(id,name) values('002','人员2')"; cn.Execute(strSQL); //OCITransRollback(cn.svchp, cn.errhp, OCI_DEFAULT); //假设未发生意外错误，即未执行“c=b/a;”，能否实现在不执行OCITransCommit时，让数据自动回滚？ //OCITransCommit(cn.svchp,cn.errhp,(ub4)OCI_DEFAULT); system("pause"); return 0;}
[解决办法]
OCI文档中的示例：
- C/C++ code
int main()
{
OCIEnv *envhp;
OCIServer *srvhp;
OCIError *errhp;
OCISvcCtx *svchp;
OCISession *usrhp;
OCIStmt *stmthp1, *stmthp2;
OCITrans *txnhp1, *txnhp2;
void *tmp;
XID gxid;
text sqlstmt[128];
OCIEnvCreate(&envhp, OCI_DEFAULT, (void *)0, 0, 0, 0,
(size_t)0, (void *)0);
OCIHandleAlloc( (void *) envhp, (void **) &errhp, (ub4)
OCI_HTYPE_ERROR, 52, (void **) &tmp);
OCIHandleAlloc( (void *) envhp, (void **) &srvhp, (ub4)
OCI_HTYPE_SERVER, 52, (void **) &tmp);
OCIServerAttach( srvhp, errhp, (text *) 0, (sb4) 0, (ub4) OCI_DEFAULT);
OCIHandleAlloc( (void *) envhp, (void **) &svchp, (ub4) OCI_HTYPE_SVCCTX,
52, (void **) &tmp);
OCIHandleAlloc((void *)envhp, (void **)&stmthp1, OCI_HTYPE_STMT, 0, 0);
OCIHandleAlloc((void *)envhp, (void **)&stmthp2, OCI_HTYPE_STMT, 0, 0);
OCIAttrSet((void *)svchp, OCI_HTYPE_SVCCTX, (void *)srvhp, 0,
OCI_ATTR_SERVER, errhp);
/* set the external name and internal name in server handle */
OCIAttrSet((void *)srvhp, OCI_HTYPE_SERVER, (void *) "demo", 0,
OCI_ATTR_EXTERNAL_NAME, errhp);
OCIAttrSet((void *)srvhp, OCI_HTYPE_SERVER, (void *) "txn demo", 0,
OCI_ATTR_INTERNAL_NAME, errhp);
/* allocate a user context handle */
OCIHandleAlloc((void *)envhp, (void **)&usrhp, (ub4) OCI_HTYPE_SESSION,
(size_t) 0, (void **) 0);
OCIAttrSet((void *)usrhp, (ub4)OCI_HTYPE_SESSION, (void *)"HR",(ub4)strlen("HR"), OCI_ATTR_USERNAME, errhp);
OCIAttrSet((void *)usrhp, (ub4)OCI_HTYPE_SESSION, (void *)"HR",
(ub4)strlen("HR"),OCI_ATTR_PASSWORD, errhp);
OCISessionBegin (svchp, errhp, usrhp, OCI_CRED_RDBMS, 0);
OCIAttrSet((void *)svchp, (ub4)OCI_HTYPE_SVCCTX,
(void *)usrhp, (ub4)0, OCI_ATTR_SESSION, errhp);
/* allocate transaction handle 1 and set it in the service handle */
OCIHandleAlloc((void *)envhp, (void **)&txnhp1, OCI_HTYPE_TRANS, 0, 0);
OCIAttrSet((void *)svchp, OCI_HTYPE_SVCCTX, (void *)txnhp1, 0,
OCI_ATTR_TRANS, errhp);
/* start a transaction with global transaction id = [1000, 123, 1] */
gxid.formatID = 1000; /* format id = 1000 */
gxid.gtrid_length = 3; /* gtrid = 123 */
gxid.data[0] = 1; gxid.data[1] = 2; gxid.data[2] = 3;
gxid.bqual_length = 1; /* bqual = 1 */
gxid.data[3] = 1;
OCIAttrSet((void *)txnhp1, OCI_HTYPE_TRANS, (void *)&gxid, sizeof(XID),
OCI_ATTR_XID, errhp);
/* start global transaction 1 with 60 second time to live when detached */
OCITransStart(svchp, errhp, 60, OCI_TRANS_NEW);
/* update hr.employees employee_id=7902, increment salary */
sprintf((char *)sqlstmt, "UPDATE EMPLOYEES SET SALARY = SALARY + 1 \
WHERE EMPLOYEE_ID = 7902");
OCIStmtPrepare(stmthp1, errhp, sqlstmt, strlen((char *)sqlstmt),
OCI_NTV_SYNTAX, 0);
OCIStmtExecute(svchp, stmthp1, errhp, 1, 0, 0, 0, 0);
/* detach the transaction */
OCITransDetach(svchp, errhp, 0);
/* allocate transaction handle 2 and set it in the service handle */
OCIHandleAlloc((void *)envhp, (void **)&txnhp2, OCI_HTYPE_TRANS, 0, 0);
OCIAttrSet((void *)svchp, OCI_HTYPE_SVCCTX, (void *)txnhp2, 0,
OCI_ATTR_TRANS, errhp);
/* start a transaction with global transaction id = [1000, 124, 1] */
gxid.formatID = 1000; /* format id = 1000 */
gxid.gtrid_length = 3; /* gtrid = 124 */
gxid.data[0] = 1; gxid.data[1] = 2; gxid.data[2] = 4;
gxid.bqual_length = 1; /* bqual = 1 */
gxid.data[3] = 1;
OCIAttrSet((void *)txnhp2, OCI_HTYPE_TRANS, (void *)&gxid, sizeof(XID),
OCI_ATTR_XID, errhp);
/* start global transaction 2 with 90 second time to live when detached */
OCITransStart(svchp, errhp, 90, OCI_TRANS_NEW);
/* update hr.employees employee_id=7934, increment salary */
sprintf((char *)sqlstmt, "UPDATE EMPLOYEES SET SALARY = SALARY + 1 \
WHERE EMPLOYEE_ID = 7934");
OCIStmtPrepare(stmthp2, errhp, sqlstmt, strlen((char *)sqlstmt),OCI_NTV_SYNTAX, 0);
OCIStmtExecute(svchp, stmthp2, errhp, 1, 0, 0, 0, 0);
/* detach the transaction */
OCITransDetach(svchp, errhp, 0);
/* Resume transaction 1, increment salary and commit it */
/* Set transaction handle 1 into the service handle */
OCIAttrSet((void *)svchp, OCI_HTYPE_SVCCTX, (void *)txnhp1, 0,
OCI_ATTR_TRANS, errhp);
/* attach to transaction 1, wait for 10 seconds if the transaction is busy */
/* The wait is clearly not required in this example because no other */
/* process/thread is using the transaction. It is only for illustration */
OCITransStart(svchp, errhp, 10, OCI_TRANS_RESUME);
OCIStmtExecute(svchp, stmthp1, errhp, 1, 0, 0, 0, 0);
OCITransCommit(svchp, errhp, (ub4) 0);
/* attach to transaction 2 and commit it */
/* set transaction handle2 into the service handle */
OCIAttrSet((void *)svchp, OCI_HTYPE_SVCCTX, (void *)txnhp2, 0,
OCI_ATTR_TRANS, errhp);
OCITransCommit(svchp, errhp, (ub4) 0);
}