ASA: implicit commit after each call proxy procedure

[Former Member]

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

[Breck_Carter]

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.

[VolkerBarth]

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

[Breck_Carter]

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

[Former Member]

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

[Former Member]

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.