Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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:

×59

question asked: 28 Oct '11, 14:01

question was seen: 6,323 times

last updated: 21 Nov '11, 03:31