Question Re: ASA: implicit commit after each call proxy procedure in Technology Q&A

ASA: implicit commit after each call proxy procedure

Former Member — Fri, 28 Oct 2011 14:01:49 GMT

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:

Windows Server Standart 2008 x64

SQL Anywhere Server:

ASA x64 9.0.2.3951
ASA x64 10.0.1.4239
ASA x64 11.0.1.2680
ASA x64 12.0.1.3457

Remote Database:

Oracle 10 / 11
Sybase ASE 12.5 / 15.5

Oracle ODBC driver:

iAnywhere Solutions 12 - Oracle
Oracle in OraClient11g_home1

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
*/

Re: ASA: implicit commit after each call proxy procedure

Breck_Carter — Sun, 30 Oct 2011 11:39:51 GMT

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.

Re: ASA: implicit commit after each call proxy procedure

VolkerBarth — Sun, 30 Oct 2011 16:22:37 GMT

"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...

Re: ASA: implicit commit after each call proxy procedure

Breck_Carter — Sun, 30 Oct 2011 17:05:16 GMT

...but not, apparently, for CREATE SERVER ... CLASS 'SAODBC' USING 'DRIVER=SQL Anywhere 12;...

Re: ASA: implicit commit after each call proxy procedure

Former Member — Mon, 31 Oct 2011 10:53:16 GMT

With invoke ROLLBACK by calling oracle proxy procedure - same result:

-- oracle alter sql:
create procedure t_remote_rollback
is
begin
    rollback;
end;

-- asa alter sql:
create procedure t_proxy_to_oracle_rollback( ) at 'etbos;;bos;t_remote_rollback'

-- asa test sql:
delete from t_proxy_call_param;
commit;

begin tran;
    call t_proxy_to_oracle( 'qwerty123' );
    call t_proxy_to_oracle_rollback( );
rollback;

select * from t_proxy_call_param; /* 1 row selected */

Another test SQL:

call t_proxy_to_oracle( 'qwerty123' );
commit;
select * from t_proxy_call_param; /* 1 row selected */

begin tran;
    delete from t_proxy_call_param;
    select * from t_proxy_call_param; /* 0 row selected */
rollback;
select * from t_proxy_call_param; /* >> 1 row selected << */

Thus, delete through the same ODBC driver supports distributed transaction

Re: ASA: implicit commit after each call proxy procedure

Former Member — Mon, 21 Nov 2011 03:31:47 GMT

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.