Home » Developer & Programmer » Precompilers, OCI & OCCI » Using OCI and distributed transactions with ProC
Using OCI and distributed transactions with ProC [message #514954] Thu, 07 July 2011 08:13 Go to next message
viscroad
Messages: 7
Registered: July 2011
Junior Member
i found this thread, and recently i want to embeded oci in pro*c for distributed transaction reason.

can pro*c deal with distributed transaction?

i want to use mts in pro*c and anyone can tell how to do this?
Re: Using OCI with ProC [message #514971 is a reply to message #514954] Thu, 07 July 2011 08:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ What are your database Oracle and Pro*C and OCI versions ?
2/ Pro*C/C++ Programmer's Guide
Chapter 3 Database Concepts
Section 2 Advanced Connection Options
Paragraph 2 Concurrent Logons

Quote:
Pro*C/C++ supports distributed processing through Oracle Net. Your application can concurrently access any combination of local and remote databases or make multiple connections to the same database.


3/ If by MTS you mean Mutli-Threaded Server, this has nothing to do with the program but with the connection string you use, and if you use the recommanded tnsnames.ora way, what's inside this file for the entry you will use to connect.

Regards
Michel

Re: Using OCI with ProC [message #515056 is a reply to message #514971] Fri, 08 July 2011 00:09 Go to previous messageGo to next message
viscroad
Messages: 7
Registered: July 2011
Junior Member
Hi Michel,

Thanks for you reply.
>>1/ What are your database Oracle and Pro*C and OCI versions ?
Oracle -> 10g, Pro*c -> 10.2.0.1.0, OCI V8


>>2/ Pro*C/C++ Programmer's Guide
I read this guide, it said pro*c need dblink to performace distributed transaction. But, I can't create dblink on my side now. So, I need to catch another method to control distributed transaction in pro*c, and OCI is a choice?

>>3/ If by MTS you mean Mutli-Threaded Server, this has nothing to do with the program but with the connection string you use, and if you use the recommanded tnsnames.ora way, what's inside this file for the entry you will use to connect.

I'm not very clear about this question.
here is my code:
BOOL DBTest() { BOOL bret = FALSE; Lda_Def lda_1; Lda_Def lda_2; OCIEnv *myenvh = NULL; OCISvcCtx *mysvch = NULL; OCIError *myerrh = NULL; sword retCode; sword swOCIStat; HRESULT hr; ITransactionDispenser *pTransactionDispenser = NULL; ITransaction *pTransaction = NULL; EXEC SQL BEGIN DECLARE SECTION; char *username; char *password; char *connectid = "my_connect_id"; char *dbname; EXEC SQL END DECLARE SECTION; username = "myusername"; password = "mypassword"; dbname = "mydbname"; EXEC SQL CONNECT :username IDENTIFIED BY: password AT :connectid USING :dbname; // with oracle manual, call OCI in pro*c, must need this line sqllda( &lda_1 ); retCode = SQLEnvGet( SQL_SINGLE_RCTX, &myenvh ); retCode = SQLSvcCtxGet( SQL_SINGLE_RCTX, (text *)connectid, (ub4)strlen(connectid), &mysvch ); hr = DtcGetTransactionManager( NULL, NULL, IID_ITransactionDispenser, 0, 0, NULL, (void **)&pTransactionDispenser ); OraMTSSvcEnlist( mysvch, myerrh, (void *)pTransaction, ORAMTS_ENFLG_DEFAULT ); swOCIStat = OCIHandleAlloc( myenvh, (dvoid **)&myerrh, OCI_HTYPE_ERROR, 0, NULL ); EXEC SQL AT: connectid // my update and insert sql update ... ... hr = pTransaction->Commit( FALSE, XACTTC_SYNC_PHASEONE, 0 ); pTransaction->Release(); bret = TRUE; return bret }




Re: Using OCI with ProC [message #515078 is a reply to message #515056] Fri, 08 July 2011 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
read this guide, it said pro*c need dblink to performace distributed transaction. But, I can't create dblink on my side now. So, I need to catch another method to control distributed transaction in pro*c, and OCI is a choice?

You have to implement a 2PC protocol in your program.
If Oracle is not aware that you are executing a distributed transaction, it can't manage it.

Your code, all in ine line, is not readable.
Please read How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Using OCI with ProC [message #515111 is a reply to message #515078] Fri, 08 July 2011 02:18 Go to previous messageGo to next message
viscroad
Messages: 7
Registered: July 2011
Junior Member
Hi Michel,

I'm a newbie to orafas.com, and I'm sorry bog you down into my messed pasted code.
I re-posted my formatted code.


>>You have to implement a 2PC protocol in your program.
If Oracle is not aware that you are executing a distributed transaction, it can't manage it.

what does 2PC protocol mean? And is it illegable to control distributed transaction in pro*c without dblink, and using mts?


BOOL DBTest() 
{
	BOOL bret = FALSE;
	Lda_Def lda_1;
	Lda_Def lda_2;
	OCIEnv *myenvh = NULL;
	OCISvcCtx *mysvch = NULL;
	OCIError *myerrh = NULL;
	sword retCode;
	sword swOCIStat;
	HRESULT hr;
	ITransactionDispenser *pTransactionDispenser = NULL;
	ITransaction *pTransaction = NULL;
	EXEC SQL BEGIN DECLARE SECTION;
	char *username;
	char *password;
	char *connectid = "my_connect_id";
	char *dbname;
	EXEC SQL END DECLARE SECTION;
	username = "myusername";
	password = "mypassword";
	dbname = "mydbname";
	EXEC SQL CONNECT :username IDENTIFIED BY: password
	AT :connectid USING :dbname;
	// with oracle manual, call OCI in pro*c, must need this line
	sqllda( &lda_1 );
	retCode = SQLEnvGet( SQL_SINGLE_RCTX, &myenvh );
	retCode = SQLSvcCtxGet( SQL_SINGLE_RCTX, (text *)connectid,
                                (ub4)strlen(connectid), &mysvch );
	hr = DtcGetTransactionManager( NULL, NULL, IID_ITransactionDispenser, 0, 
                                       0, NULL, (void **)&pTransactionDispenser );
	OraMTSSvcEnlist( mysvch, myerrh, (void *)pTransaction,
                         ORAMTS_ENFLG_DEFAULT );
	swOCIStat = OCIHandleAlloc( myenvh, (dvoid **)&myerrh, 
                                    OCI_HTYPE_ERROR, 0, NULL );
	EXEC SQL AT: connectid
	// my update and insert sql
	update ... ...
	hr = pTransaction->Commit( FALSE, XACTTC_SYNC_PHASEONE, 0 );
	pTransaction->Release();
	bret = TRUE;
	return bret
} 



Best regards

Thanks!

[Updated on: Fri, 08 July 2011 03:22] by Moderator

Report message to a moderator

Re: Using OCI with ProC [message #515115 is a reply to message #515111] Fri, 08 July 2011 02:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I asked you what do you mean with MTS.
mechanical testing and simulation?
Metropolitan Transit System?
Mod The Sims?

Please answer our questions if you want answers to yours.

Regards
Michel

Re: Using OCI with ProC [message #515122 is a reply to message #515115] Fri, 08 July 2011 02:57 Go to previous messageGo to next message
viscroad
Messages: 7
Registered: July 2011
Junior Member
Hi Michel,

MTS => I mean the Microsoft Transaction Server. I want to use the distributed transaction.

Best Regars,
Thanks!
Re: Using OCI with ProC [message #515131 is a reply to message #515122] Fri, 08 July 2011 03:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use it to do distributed transaction as it handles the two phase commit (2PC) processing, this is what I mean by "your program has to implement it" but this is MTS specific programming I can't help you in this.

Regards
Michel

[Updated on: Fri, 08 July 2011 03:23]

Report message to a moderator

Re: Using OCI with ProC [message #515246 is a reply to message #515131] Sat, 09 July 2011 04:52 Go to previous messageGo to next message
viscroad
Messages: 7
Registered: July 2011
Junior Member
Hi Michel,

Thanks for your help.

Best Regars
Re: Using OCI with ProC [message #515263 is a reply to message #515246] Sat, 09 July 2011 12:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This one will help you:
Oracle® Services for Microsoft Transaction Server Developer's Guide
Especially Chapter 5 Programming with Microsoft Transaction Server and an Oracle Database
but it talks about OCI, ODBC, OLE DB and OO4O but not Pro*C; this is a kind of surprise for me.

I searched but I didn't find any reference using MTS and Pro*C, so I think I was wrong thinking they can work together.
Did you get any error with your code?

Regards
Michel

Re: Using OCI with ProC [message #515323 is a reply to message #515263] Sun, 10 July 2011 09:20 Go to previous messageGo to next message
viscroad
Messages: 7
Registered: July 2011
Junior Member
Hi Michel,

very thanks for your help.

I also surfed google, and get no message about proc*c with MTS.

1. There is no error in my pasted code, but it does no effect of db, it can't be committed.
2. In my code, I got the OCIEnv in pro*c, and then use MTS with OCI, but it don't have any effect.

Maybe we can't use MTS in Pro*c? If that, how can i handle the distributed transaction in pro*c.

Best Regards,
Thanks
Re: Using OCI with ProC [message #515337 is a reply to message #515323] Sun, 10 July 2011 11:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In this case, I don't think you can handle in Pro*C distributed transactions that are not in Oracle itself, that is using database links.
But I'm surprised you can't do it embedding OCI inside your Pro*C, I don't this programming part but in the examples of the link I gave you there is a call to a "BeginTransaction" procedure, maybe this is what is missing in your code.

Regards
Michel
Re: Using OCI with ProC [message #516788 is a reply to message #515337] Wed, 20 July 2011 20:37 Go to previous messageGo to next message
viscroad
Messages: 7
Registered: July 2011
Junior Member
Hi Michel,

I messed other thing down these days, and now, I continue to this topic.
I appended "Begintransaction" in my code, but got the same result. Crazy. So I think, it's no method handle pro*c with MTS together also distributed transaction without oracle db link.

But, I really want to konw why? because, I can get the oci env handler. ... ...

Best Regards,

Thanks!
Re: Using OCI with ProC [message #516800 is a reply to message #516788] Wed, 20 July 2011 23:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thannks to feedback, we are also interested in your success and to know the solution.

Regards
Michel
Re: Using OCI with ProC [message #517754 is a reply to message #516800] Wed, 27 July 2011 23:55 Go to previous message
vicenzo
Messages: 28
Registered: December 2007
Location: Paris
Junior Member
Hi,

You can use Oracle XA with PRO*C. See the documentation there : http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28424/adfns_xa.htm#sthref1098

You can try to retrieve the OCI handle from a PRO*C XA connection with SQLEnvGet() and SQLSvcCtxGet(). But I've never tried this way from PRO*C XA connections.

Unless you use use OCI directly. In this case, with XA connection, the environment handle has to be retreived with the OCI method xaoEnv(). Then you can retrieve then service handle using xaoSvcCtx().

[Updated on: Thu, 28 July 2011 15:48]

Report message to a moderator

Previous Topic: Protecting your SQL queries
Next Topic: INTERNAL ERROR
Goto Forum:
  


Current Time: Thu Mar 28 17:10:26 CDT 2024