Tuesday, April 22, 2008

FOR UPDATE statement may not work with BizTalk Server 2006 Oracle Adapter ODBC

I created a simple test console application today for testing FOR UPDATE statement using different types of component.

1. Using ODP.Net - Working
Note : The second execution will wait until the first execution to finish first.

2. .Net Oracle Client - Working
Note : A slight different between odp.net and .net oracle client is that the second will wait until the first program ends, so eventhough the first program has finished the execution, it was still waiting until i close the first program, i suspect there is a different transaction handling between them.

3. ODBC - Not working
Note : Quite a surprising result, but as we expected. They seems to ignore the FOR UPDATE statement.

Then I found these information at MSDN and oracle sites for ODBC :

It says that before the statement is passed to the driver, the FOR UPDATE clause will be removed first and this seems to apply not only for Oracle ODBC, but as a standard ODBC Programming reference.

My conclusion : As long as we're using Oracle Adapter with ODBC, we'll stuck with this unless we use the new Oracle WCF LOB Adapter with ODP.Net which requires BizTalk Server 2006 R2 and .Net Framework 3.0.

Update : Below is the query result which displays the executed queries in the oracle, see that the query for TestOracleLock.exe (2nd row) didn't have the FOR UPDATE statement which I specified in the code earlier.

You can use this query below to get the result above :

Select sql_text, module, first_load_time, last_load_time, users_opening, users_executing
From v$SQL
Where last_load_time != ' '
Order By last_load_time DESC;


Post a Comment