Looks like a call of proxy procedure in any case runs in autocommit mode on remote server side. How to call a procedure in a distributed transaction mode? Test Environment (tested in each one combination):Operation System:
SQL Anywhere Server:
Remote Database:
Oracle ODBC driver:
Test SQL on the Oracle:create table t_remote_call_param ( param_value varchar(32) ); create procedure t_remote_proc ( param varchar2 ) is begin insert into t_remote_call_param ( param_value ) values ( param ); end; Test SQL on the ASA:create server "oracle_odbc" class 'ORAODBC' using 'oracle_odbc_x64'; create existing table t_proxy_call_param at 'oracle_odbc;;dbname;t_remote_call_param'; create procedure t_proxy_to_oracle( @param varchar(32) ) at 'oracle_odbc;;dbname;t_remote_proc'; select * from t_proxy_call_param; /* output (0 rows selected): ------- PARAM_VALUE ----------- */ begin tran; call t_proxy_to_oracle( 'qwerty123' ); rollback; select * from t_proxy_call_param; /* output (1 rows selected): ------- PARAM_VALUE ----------- qwerty123 */ |
To determine if the call really is auto-committing, try creating a second Oracle procedure containing a rollback and calling that via a proxy procedure right after the call t_proxy_to_oracle( 'qwerty123' );
FWIW a SQL Anywhere 12 remote database does not exhibit this behavior; i.e., the rollback is done on the remote database.
AFAIK Oracle does not auto-commit itself, it's a client thing, which in this case might be the ODBC driver.
I agree that this would be uncomfortable here, but to auto-commit is ODBC's default behaviour...
...but not, apparently, for CREATE SERVER ... CLASS 'SAODBC' USING 'DRIVER=SQL Anywhere 12;...
With invoke ROLLBACK by calling oracle proxy procedure - same result:
Another test SQL:
Thus, delete through the same ODBC driver supports distributed transaction
Because the decision and is not found, suggest that we go on the other hand: someone is opportunity to check for similar configuration?
If the error only on my configuration will somehow find that leads to commiting. And if such working with proxy tables-SQL Server established Anywhere (for example, described in the documentation), then the solution will be completely different.
Sorry, my English is not very good.