startTransaction and rollback with only select

, ,
1

I am using explicit LOCAL_TRANSACTION JDBC adapter in flows
to control commit and rollback for a set of transaction.
IF I start a transaction and read records from JDBCAdapter1
and only if certain criteria is met do I insert/update to JBDCAdapter2.
If I don’t insert/update to JDBCAdapter2 and run commit or rollback
on the selection only I get NULL transaction error.

How can I avoid this Null transaction as I need to loop over
multiple customers and when I startTransaction again in the loop I get
Unable to establish connection to connection error
as I have two JDBC adapters that I am using in the explicit transaction

Is there a way to start a transaction and then release it without
database insert/update for the select step?

Thank you.

2

I ran into something similar not too long ago - specifically in our case we had two separate adapter connections being used in the same flow. We had to change both to XA, rather than local, even though there was no true two-phase commit occuring.

Take a look at SR-1-53599861 on Advantage - that was the advice I implemented and it worked.

3

I wonder if this is the same as I have 2 local _tranaction adapters and I want to select from one and I may or may not update to the other in a transaction block.

loop customers
select ADP1 records
loop on selected records
criteria met then
select ADP2
update ADP2
commit selection for ADP1

If no criteria met then commit fails with NULL.
I can check for criteria and not do commit but Rollback would fail also
and next customer loop select ADP1 causes problem.

4

Yep - that’s very similar to what we were doing. Try making the adapter connections XA instead of LOCAL.

5

Can’t you try something like this?? Though, I may not have understood your issue fully.

loop customers

select ADP1 records

loop on selected records

     [INDENT][INDENT]criteria met then[/INDENT][/INDENT]

[INDENT][INDENT]start transcation for ADP2[/INDENT][/INDENT]

       [INDENT][INDENT][INDENT] select ADP2[/INDENT][/INDENT][/INDENT]
        [INDENT][INDENT][INDENT]update ADP2[/INDENT][/INDENT][/INDENT]

[INDENT][INDENT]commit transcation for ADP2[/INDENT][/INDENT]

6

Would something like this work without changing the connections to XA?

loop customers [INDENT]startTransaction ADP1
select ADP1 records
commit transaction ADP1
[/INDENT][INDENT]loop on selected records[/INDENT][INDENT][INDENT]criteria met then[/INDENT][/INDENT][INDENT][INDENT][INDENT]start transcation for ADP2[/INDENT][/INDENT][/INDENT][INDENT][INDENT][INDENT]select ADP2[/INDENT][/INDENT][/INDENT][INDENT][INDENT][INDENT]update ADP2[/INDENT][/INDENT][/INDENT][INDENT][INDENT][INDENT]commit transcation for ADP2[/INDENT][/INDENT][/INDENT]

7

Yes close: I have to start the transaction and rollback on the ADP1 as well
because 2 different database accessed:

loop customers

select ADP1 records[INDENT]START TRANS APD1
select ADP1 records
ROLLBACK TRANS ADP1
[/i]

[INDENT][/INDENT][INDENT]loop on selected records[/INDENT][INDENT][INDENT]criteria met then[/INDENT][/INDENT][INDENT][INDENT][INDENT]start transcation for ADP2[/INDENT][/INDENT][/INDENT][INDENT][INDENT][INDENT]select ADP2[/INDENT][/INDENT][/INDENT][INDENT][INDENT][INDENT]update ADP2[/INDENT][/INDENT][/INDENT][INDENT][INDENT][INDENT]commit transcation for ADP2[/INDENT][/INDENT][/INDENT]I got caught in the testing by stepping which makes each step a seperate thread.
I had to run it for it to work.