OraFAQ Forum: Precompilers, OCI & OCCI » OCIStmtExecute blocks on an update query

Home » Developer & Programmer » Precompilers, OCI & OCCI » OCIStmtExecute blocks on an update query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

OCIStmtExecute blocks on an update query [message #159542]

Mon, 20 February 2006 04:04

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.

Report message to a moderator

Re: OCIStmtExecute blocks on an update query [message #159734 is a reply to message #159542]

Tue, 21 February 2006 02:37

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

Report message to a moderator

Re: OCIStmtExecute blocks on an update query [message #159749 is a reply to message #159734]

Tue, 21 February 2006 03:49

Maaher
Messages: 7065
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

Report message to a moderator

Re: OCIStmtExecute blocks on an update query [message #159924 is a reply to message #159749]

Wed, 22 February 2006 04:16

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

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

Report message to a moderator

Previous Topic:	OCIDefineByPos && Retrieving a DATE column...
Next Topic:	How to know what Oracle driver is installed?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jan 22 23:02:23 CST 2025