xasimplepositive.out

derby database source code.good for you.

ij> xa_datasource 'wombat';
ij> ---------------------------------------------
-- a single connection and 1 phase commit
---------------------------------------------
xa_connect ;
ij> xa_start xa_noflags 0;
ij> xa_getconnection;
ij(XA)> drop table foo;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'FOO' because it does not exist.
ij(XA)> create table foo (a int);
0 rows inserted/updated/deleted
ij(XA)> insert into foo values (0);
1 row inserted/updated/deleted
ij(XA)> select * from foo;
A          
-----------
0          
ij(XA)> run resource '/org/apache/derbyTesting/functionTests/tests/store/global_xactTable.view';
ij(XA)> create view global_xactTable as
select 
    cast(global_xid as char(2)) as gxid,
    status,
    case when first_instant is NULL then 'NULL' else 'false' end as readOnly,
    cast (username as char(10)) as username,
    type 
from new org.apache.derby.diag.TransactionTable() t;
0 rows inserted/updated/deleted
ij(XA)> select * from global_xactTable where gxid is not null order by gxid;
GXID|STATUS  |READ&|USERNAME  |TYPE                          
-------------------------------------------------------------
(0  |ACTIVE  |false|APP       |UserTransaction               
ij(XA)> xa_end xa_success 0;
ij(XA)> xa_commit xa_1phase 0;
ij(XA)> xa_datasource 'wombat' shutdown;
ERROR 08006: Database 'wombat' shutdown.
ij(XA)> ---------------------------------------------
-- two interleaving connections and prepare/commit prepare/rollback
---------------------------------------------
xa_datasource 'wombat';
ij(XA)> xa_connect user 'sku' password 'testxa' ;
ij(XA)> xa_start xa_noflags 1;
ij(XA)> xa_getconnection;
ij(XA)> insert into APP.foo values (1);
1 row inserted/updated/deleted
ij(XA)> xa_end xa_suspend 1;
ij(XA)> xa_start xa_noflags 2;
ij(XA)> insert into APP.foo values (2);
1 row inserted/updated/deleted
ij(XA)> xa_end xa_suspend 2;
ij(XA)> xa_start xa_resume 1;
ij(XA)> insert into APP.foo values (3);
1 row inserted/updated/deleted
ij(XA)> xa_end xa_suspend 1;
ij(XA)> xa_start xa_resume 2;
ij(XA)> insert into APP.foo values (4);
1 row inserted/updated/deleted
ij(XA)> select * from APP.global_xactTable where gxid is not null order by gxid;
GXID|STATUS  |READ&|USERNAME  |TYPE                          
-------------------------------------------------------------
(1  |ACTIVE  |false|SKU       |UserTransaction               
(2  |ACTIVE  |false|SKU       |UserTransaction               
ij(XA)> -- this prepare won't work since transaction 1 has been suspended - XA_PROTO
xa_prepare 1;
IJ ERROR: XAER_PROTO 
ij(XA)> select * from APP.global_xactTable where gxid is not null order by gxid;
GXID|STATUS  |READ&|USERNAME  |TYPE                          
-------------------------------------------------------------
(1  |ACTIVE  |false|SKU       |UserTransaction               
(2  |ACTIVE  |false|SKU       |UserTransaction               
ij(XA)> xa_end xa_success 2;
ij(XA)> -- this assumes a resume
xa_end xa_success 1;
ij(XA)> xa_prepare 1;
ij(XA)> xa_prepare 2;
ij(XA)> -- both transactions should be prepared
select * from APP.global_xactTable where gxid is not null order by gxid;
GXID|STATUS  |READ&|USERNAME  |TYPE                          
-------------------------------------------------------------
(1  |PREPARED|false|SKU       |UserTransaction               
(2  |PREPARED|false|SKU       |UserTransaction               
ij(XA)> -- NOTE: The following call to "xa_recover xa_startrscan" is apt to
-- return the result set rows in reverse order when changes to
-- the Derby engine affect the number of transactions that it takes
-- to create a database.  The transactions are stored in a hash table
-- based on a global and local id, and when the number of transactions
-- changes, the (internal) local id can change, which may lead to a
-- change in the result set order.  This order is determined by the
-- JVM's hashing algorithm. Examples of changes to the engine that
-- can affect this include ones that cause more commits or that
-- change the amount of data being stored, such as changes to the
-- metadata statements (which is what prompted this explanation in
-- the first place).  Ultimately, the problem is that there is no
-- way to order the return values from "xa_recover" since it is an
-- ij internal statement, not SQL...
xa_recover xa_startrscan;
Recovered 2 in doubt transactions
Transaction 1 : (1,FILTERED,FILTERED)
Transaction 2 : (2,FILTERED,FILTERED)
ij(XA)> xa_recover xa_noflags;
Recovered 0 in doubt transactions
ij(XA)> xa_commit xa_2Phase 1;
ij(XA)> xa_rollback 2;
ij(XA)> -- check results
xa_start xa_noflags 3;
ij(XA)> select * from APP.global_xactTable where gxid is not null order by gxid;
GXID|STATUS  |READ&|USERNAME  |TYPE                          
-------------------------------------------------------------
(3  |IDLE    |NULL |SKU       |UserTransaction               
ij(XA)> select * from APP.foo;
A          
-----------
0          
1          
3          
ij(XA)> xa_end xa_success 3;
ij(XA)> xa_prepare 3;
ij(XA)> -- should fail with XA_NOTA because we prepared a read only transaction 
xa_commit xa_1Phase 3;
IJ ERROR: XAER_NOTA 
ij(XA)> disconnect;
ij> ---------------------------------------------
-- 3 interleaving xa connections and a local connection
---------------------------------------------
xa_start xa_noflags 4;
ij> xa_end xa_suspend 4;
ij> xa_start xa_noflags 5;
ij> xa_end xa_suspend 5;
ij> xa_start xa_noflags 6;
ij> xa_end xa_suspend 6;
ij> connect 'wombat' as local;
ij(LOCAL)> select * from foo;
A          
-----------
0          
1          
3          
ij(LOCAL)> xa_start xa_resume 4;
ij(LOCAL)> xa_getconnection;
ij(XA)> insert into APP.foo values (4);
1 row inserted/updated/deleted
ij(XA)> disconnect;
ij> set connection local;
ij(LOCAL)> insert into foo values (77);
1 row inserted/updated/deleted
ij(LOCAL)> xa_end xa_suspend 4;
ij(LOCAL)> xa_end xa_success 4;
ij(LOCAL)> -- this getconnection should get a local connection
-- this has problems
--xa_getconnection;
--insert into APP.foo values (88);
--commit;
--disconnect;
xa_start xa_resume 5;
ij(LOCAL)> xa_getconnection;
ij(XA)> insert into APP.foo values (5);
1 row inserted/updated/deleted
ij(XA)> xa_end xa_success 5;
ij(XA)> xa_start xa_resume 6;
ij(XA)> insert into APP.foo values (6);
1 row inserted/updated/deleted
ij(XA)> select * from APP.global_xactTable where gxid is not null order by gxid;
GXID|STATUS  |READ&|USERNAME  |TYPE                          
-------------------------------------------------------------
(4  |ACTIVE  |false|SKU       |UserTransaction               
(5  |ACTIVE  |false|SKU       |UserTransaction               
(6  |ACTIVE  |false|SKU       |UserTransaction               
ij(XA)> xa_commit xa_1Phase 4;
ij(XA)> insert into APP.foo values (6);
1 row inserted/updated/deleted
ij(XA)> select * from APP.global_xactTable where gxid is not null order by gxid;
GXID|STATUS  |READ&|USERNAME  |TYPE                          
-------------------------------------------------------------
(5  |ACTIVE  |false|SKU       |UserTransaction               
(6  |ACTIVE  |false|SKU       |UserTransaction               
ij(XA)> xa_end xa_fail 6;
IJ ERROR: XA_RBROLLBACK 
ij(XA)> xa_rollback 6;
ij(XA)> xa_start xa_join 5;
ij(XA)> select * from APP.global_xactTable where gxid is not null order by gxid;
GXID|STATUS  |READ&|USERNAME  |TYPE                          
-------------------------------------------------------------
(5  |ACTIVE  |false|SKU       |UserTransaction               
ij(XA)> select * from APP.foo;
A          
-----------
0          
1          
3          
4          
77         
5          
ij(XA)> xa_end xa_success 5;
ij(XA)> xa_prepare 5;
ij(XA)> xa_commit xa_2Phase 5;
ij(XA)> ---------------------------------------------
-- Test procedure with server-side JDBC
---------------------------------------------
--- local transaction
create table t1(i int not null primary key, b char(15));
0 rows inserted/updated/deleted
ij(XA)> insert into t1 values (1,'one'), (2, 'two'), (3,'three');
3 rows inserted/updated/deleted
ij(XA)> create procedure DRS(p1 int) parameter style JAVA READS SQL DATA dynamic result sets 1 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.selectRows';
0 rows inserted/updated/deleted
ij(XA)> call DRS(1);
selectRows - 1 arg - 1 rs
I          |B              
---------------------------
1          |one            
ij(XA)> drop table t1;
0 rows inserted/updated/deleted
ij(XA)> drop procedure DRS;
0 rows inserted/updated/deleted
ij(XA)> commit;
ij(XA)> --- global transaction
xa_start xa_noflags 6;
ij(XA)> create table t1(i int not null primary key, b char(15));
0 rows inserted/updated/deleted
ij(XA)> insert into t1 values (1,'one'), (2, 'two'), (3,'three');
3 rows inserted/updated/deleted
ij(XA)> create procedure DRS(p1 int) parameter style JAVA READS SQL DATA dynamic result sets 1 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.selectRows';
0 rows inserted/updated/deleted
ij(XA)> call DRS(1);
selectRows - 1 arg - 1 rs
I          |B              
---------------------------
1          |one            
ij(XA)> xa_end xa_success 6;
ij(XA)> xa_commit xa_1Phase 6;
ij(XA)>