Home » Developer & Programmer » Precompilers, OCI & OCCI » OCIStmtExecute blocks on an update query
icon9.gif  OCIStmtExecute blocks on an update query [message #159542] Mon, 20 February 2006 04:04 Go to next message
Herode
Messages: 12
Registered: February 2006
Location: Isère (France, 38)
Junior Member
Hi gentlemen,


I'm using a piece of code for SQL Statements with OCI. It works fine with a select statement, but the OCIStmtExecute function seems to lock (it does'nt return) on an update statement. I can't figure out the cause of this problem.

Here is the code around the blocking "execute" :
// select query... 
CString sz = "select count(*) from road.test"; 
st = ::OCIStmtPrepare(  m_pStmt, m_pErr, 
                       (text*) sz.GetBuffer( 0 ), sz.GetLength(), 
                        OCI_NTV_SYNTAX, OCI_DEFAULT ); 
if ( st != OCI_SUCCESS && st != OCI_SUCCESS_WITH_INFO ) { 
  return false; 
} 

// ---> this one is ok 
st = ::OCIStmtExecute( m_pService, m_pStmt, m_pErr, 0, 0, NULL, NULL, OCI_DEFAULT ); 
if ( st != OCI_SUCCESS && st != OCI_SUCCESS_WITH_INFO ) { 
  return false; 
} 

// update query 
sz = "update road.test set col1 = 3"; 
st = ::OCIStmtPrepare(  m_pStmt, m_pErr, 
                       (text*) sz.GetBuffer( 0 ), sz.GetLength(), 
                        OCI_NTV_SYNTAX, OCI_DEFAULT ); 
if ( st != OCI_SUCCESS && st != OCI_SUCCESS_WITH_INFO ) { 
  return false; 
} 

// ---> this one never returns.... 
st = ::OCIStmtExecute( m_pService, m_pStmt, m_pErr, 1, 0, NULL, NULL, OCI_DEFAULT ); 
if ( st != OCI_SUCCESS && st != OCI_SUCCESS_WITH_INFO ) { 
  return false; 
} 


I tried with and without an OCIStartTrans. I also tried freing and reallocating the statement handle. Nothing works. Any information will be a great help !

Best regards,
C.
Re: OCIStmtExecute blocks on an update query [message #159734 is a reply to message #159542] Tue, 21 February 2006 02:37 Go to previous messageGo to next message
Herode
Messages: 12
Registered: February 2006
Location: Isère (France, 38)
Junior Member
Ok, I've got the answer on a french forum.
The key problem was a session started and not closed on a previous test. This lost session locked the rows, hence the OCIStmtExecute(...). One could wonder why Oracle does not deliver any error message on such occasions, instead of sticking on the function call... ?

Best regards,
CB
Re: OCIStmtExecute blocks on an update query [message #159749 is a reply to message #159734] Tue, 21 February 2006 03:49 Go to previous messageGo to next message
Maaher
Messages: 7034
Registered: December 2001
Senior Member
How can Oracle know what you are planning to do next? The session was open as far as Oracle was concerned, so the locks remained. What error message did you expect?

MHE
Re: OCIStmtExecute blocks on an update query [message #159924 is a reply to message #159749] Wed, 22 February 2006 04:16 Go to previous messageGo to next message
Herode
Messages: 12
Registered: February 2006
Location: Isère (France, 38)
Junior Member
Well, you're right, Oracle can't guess what I planned to do. However, I consider locking functions as uncomfortable. I really prefer something returning codes like STILL_EXECUTING, LOCKED and so on, something you can process by your own by ignoring, avoiding, looping, etc...

AMOF, this kind of lock is manageable in a benchmark like the one I'm working on, because the "unsafe" code is isolated and because I know the problem is local to this code. In a big "real" application, at runtime, this kind of undocumented lock can be a real pain in the neck.

[Updated on: Wed, 22 February 2006 08:29]

Report message to a moderator

Re: OCIStmtExecute blocks on an update query [message #159993 is a reply to message #159924] Wed, 22 February 2006 09:44 Go to previous message
Herode
Messages: 12
Registered: February 2006
Location: Isère (France, 38)
Junior Member
BTW, I've just found this piece of documentation that gives me some new insights :
Quote:

The OCI provides the ability to establish a server connection in blocking mode or nonblocking mode. When a connection is made in blocking mode, an OCI call returns control to an OCI client application only when the call completes, either successfully or in error. With the nonblocking mode, control is immediately returned to the OCI program if the call could not complete, and the call returns a value of OCI_STILL_EXECUTING.

In nonblocking mode, an application must test the return code of each OCI function to see if it returns OCI_STILL_EXECUTING. In this case, the OCI client can continue to process program logic while waiting to retry the OCI call to the server.



There's some appearance that my above complains where unfair... Embarassed
Previous Topic: OCIDefineByPos && Retrieving a DATE column...
Next Topic: How to know what Oracle driver is installed?
Goto Forum:
  


Current Time: Thu Apr 17 06:50:45 CDT 2014

Total time taken to generate the page: 0.11476 seconds