The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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

asked 28 Oct '11, 14:01

iiv77's gravatar image

iiv77
61124
accept rate: 0%

edited 29 Oct '11, 18:19

Comment Text Removed

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.

(30 Oct '11, 11:39) Breck Carter
Replies hidden

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

(30 Oct '11, 16:22) Volker Barth

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

(30 Oct '11, 17:05) Breck Carter

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

(31 Oct '11, 10:53) iiv77
Comment Text Removed

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.

(21 Nov '11, 03:31) iiv77
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×47

question asked: 28 Oct '11, 14:01

question was seen: 1,950 times

last updated: 21 Nov '11, 03:31