关于oci 事务处理的问题

CHID 2011-01-15 11:52:54 


/*

	关于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;

}
...全文
735 6 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
yoyo_alex_lw 2012-12-17
  • 打赏
  • 举报
 回复
只要没用提交事务，数据库会自动回滚数据。所有数据库在事务方面都有这个要求，并且oracle实现了这个功能。
CHID 2012-06-29
  • 打赏
  • 举报
 回复
[Quote=引用 4 楼 的回复:]

请问楼主：oci中怎么设置自动提交和手动提交模式的，谢谢
[/Quote]
我的理解，OCI并没有直接提供所谓的“自动提交”以及“手动提交”模式，自动提交就是语句开始时开始事物，语句执行结束时提交或者回滚事物，要手工实现的。
xushengfaheyuanli 2012-06-09
  • 打赏
  • 举报
 回复
请问楼主：oci中怎么设置自动提交和手动提交模式的，谢谢
lgs666 2011-02-13
  • 打赏
  • 举报
 回复
OCI文档中的示例：


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);

}
bluesky12312388 2011-01-15
  • 打赏
  • 举报
 回复
自己去看帮助文档吧。
这种问题难道你让我们去看完整个帮助文档，
在来回答么。
CHID 2011-01-15
  • 打赏
  • 举报
 回复
各位帮忙看看啊。
简易之PHP通过OCI访问Oracle数据库可做事务处理
功能一：通过oci，PHP成功访问Oracel数据库》功能二：使用PHP代码创建和删除表》功能三：使用PHP代码进行表的增删改查操作》功能四：使用PHP代码进行事务处理》附加项：Oracle客户端的配置
OCI函数 OCI函数
网上很少有OCI的中文文档，一般英文文档大家可能看了也不太顺，我整理了一份，写了一 些常用的OCI函数，供大家参考。
OCI接口类 OCI接口类
OCI接口类 OCI接口类 OCI接口类
OCI官方参考文档
OCI开发的官方参考文档，里面内容非常详细，涵盖了所用OCI的知识点和API函数的调用说明，是OCI开发的最好参考手册。
对Linux平台OCI接口断网阻塞问题的处理.pdf
对Linux平台OCI接口断网阻塞问题的处理.pdf
C++ 语言

65,189

社区成员

250,526

社区内容

发帖
与我相关
我的任务
社区描述
C++ 语言相关问题讨论，技术干货分享，前沿动态等
c++ 技术论坛（原bbs）
社区管理员
  • C++ 语言社区
  • encoderlee
  • paschen
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
  1. 请不要发布与C++技术无关的贴子
  2. 请不要发布与技术无关的招聘、广告的帖子
  3. 请尽可能的描述清楚你的问题，如果涉及到代码请尽可能的格式化一下

试试用AI创作助手写篇文章吧

+ 用AI写文章