E9.2 Power Edit form and transaction management, are tableIO statements working in transaction within an APPL ?

[ccornagg]

Hi forum,

in a custom "Power Edit Form" (see OK button under transaction) I'm succesfully using standard BSFN B4200450.AdvancedSODetailLineSplit which is splitting an existing F4211 line.
For example: I'm assigning lot A, B and C to a single F4211 line with quantity 3.

While doing this kind of exercise (it's a while statement looping a specific cache) I also need to perform a fetch and an update tableIO statement hitting the same F4211 record just created by B4200450 BSFN in transaction (for example: line 1.010 not yet committed to the database).

I spent a lot of time trying with table IO using TBLE object, BSVW object, using "open handle statement" ... and so fort.
It appears JDE is using a different "transaction boundary" compared with the other BSFN in the same piece of code (OK event), thus I did not manage to have it working.

Finally I did manage to succeed by simply running the same fetch and update tableIO statements in a custom NER executed with "transaction flag" within my custom form.

Is there an explanation for that ?
Do you know about special rules to be used to run tableIO in transaction within APPL events ?

I also noticed within UBEs you have the freedom to decide when to execute a specific system fuction to commit or rollback your transaction.
I did not notice the same flexibility with APPL objects (but indeed there's an interesting "Post Commit" event really useful for F41021WF table management).

Thanks,

Carlo
TR 9.2.7.3

 

[DaveWagoner]

Wow. This is crazy advanced stuff, I would love it if an oracle dev who understands that stuff would show up.

 

[BOster]

I usually do any table IO like that in a BSFN like you did for better control of transaction processing. I have always wondered how Oracle managed to have a JAS JDBC connection on one server be in the same transaction scope as a separate ODBC connection on a different Enterprise Server. Like Dave, I would like someone that knows explain it... probably an ELI5 version would be needed.

UBEs do give you better control over TP and give you more options (like explicit commit/rollback), but I am assuming (and this is a huge assumption) that is because the ER code and the BSFNs can share the same ODBC connection. You can get that type of control over table IO and called BSFNs in an APPL via a very hacky C BSFN technique.

 

[DaveWagoner]

Like Dave, I would like someone that knows explain it... probably an ELI5 version would be needed.

Coming to your Tech SIG starting in June 2024.... stay tuned ;-) (no full promises, but we're working on it)

 

[jdelisths]

I usually do any table IO like that in a BSFN like you did for better control of transaction processing. I have always wondered how Oracle managed to have a JAS JDBC connection on one server be in the same transaction scope as a separate ODBC connection on a different Enterprise Server. Like Dave, I would like someone that knows explain it... probably an ELI5 version would be needed.

UBEs do give you better control over TP and give you more options (like explicit commit/rollback), but I am assuming (and this is a huge assumption) that is because the ER code and the BSFNs can share the same ODBC connection. You can get that type of control over table IO and called BSFNs in an APPL via a very hacky C BSFN technique.

To my knowledge, Form/FDA ER code (running on JAS/HTML) and the BSFNS (running on the server) do not share transaction boundaries.

Finally I did manage to succeed by simply running the same fetch and update tableIO statements in a custom NER executed with "transaction flag" within my custom form.

This worked because the NER and the other BSFNs are running on the same call object kernel on the server.

 

[ccornagg]

To my knowledge, Form/FDA ER code (running on JAS/HTML) and the BSFNS (running on the server) do not share transaction boundaries.



This worked because the NER and the other BSFNs are running on the same call object kernel on the server.

Thank you very much Hari,
your statement is really explaining why the new record added by standard B4200450 BSFN (in transaction) was not visible by TableIO statements included in my custom APPL Event Rules.

Regards.

Carlo

 

[BOster]

I will have to do some testing. Again usually all my table I/O is done in BSFNs but I feel like at some point I had a mix between ER code and BSFNs that was in the same transaction but I could be wrong about that.

This thread did get me to wondering how I would implement that if I was Oracle so started going down the Google rabbit hole. Didn't spend a lot of time on it but it did look like there was a way to get the Transaction ID from the DB server through standard T-SQL. It also looked like with JDBC you can issue statements under multiple JDBC connections within one transaction scope but I didn't get very far into that. Most of the stuff I was finding was about Xa and two-phase commit, not the other way around - separate connections using one TP boundary against a single DB. But I did get the sense it was possible in JDBC. If so, I would assume the same functionality exists in ODBC and if you can acquire some DB transaction ID I can see where theoretically it would be possible to have an APPL and a BSFN execute SQL statements within a single transaction scope as in APPL does a BEGIN TRANSACTION, gets the transaction ID, passes that to ES server for BSFN processing and then BSFN processing uses the same passed transaction ID to issue SQL statements, control returns to APPL which does its SQL statements along with the COMMIT or ROLLBACK using the shared transaction ID.

 

[JohnDanter2]

I'm on the same TR as you and noticed some things I've never noticed before (or never had an with until recently). Namely if the APPL calling a BSFN was in Update Mode the called BSFNs knew that and it could affect it's behaviour!!!!

If I need to do things like you've mentioned, updates to a table I am in a loop over, I use table handles or like you've done, move it to a BSFN (which spawns it's own thread/boundary)
The include in transaction flag attempts to group all the IO into one tramsaction, but it depends on what the BSFN is actually doing and what it intialises. NERs are a bit simpler to control
You have my sympathies with a Power Form though

 

[jdelisths]

I will have to do some testing. Again usually all my table I/O is done in BSFNs but I feel like at some point I had a mix between ER code and BSFNs that was in the same transaction but I could be wrong about that.

This thread did get me to wondering how I would implement that if I was Oracle so started going down the Google rabbit hole. Didn't spend a lot of time on it but it did look like there was a way to get the Transaction ID from the DB server through standard T-SQL. It also looked like with JDBC you can issue statements under multiple JDBC connections within one transaction scope but I didn't get very far into that. Most of the stuff I was finding was about Xa and two-phase commit, not the other way around - separate connections using one TP boundary against a single DB. But I did get the sense it was possible in JDBC. If so, I would assume the same functionality exists in ODBC and if you can acquire some DB transaction ID I can see where theoretically it would be possible to have an APPL and a BSFN execute SQL statements within a single transaction scope as in APPL does a BEGIN TRANSACTION, gets the transaction ID, passes that to ES server for BSFN processing and then BSFN processing uses the same passed transaction ID to issue SQL statements, control returns to APPL which does its SQL statements along with the COMMIT or ROLLBACK using the shared transaction ID.

I was headed the same way .. (to do some testing). I will wait for your results instead.

I asked ChatGPT about this, it mentioned XA and MSDTC. I specifically asked if it was possible to share transaction IDs between connections. Here is the response:

The scenario about sharing a transaction ID across different connections and potentially different technologies (JDBC, ODBC, and Oracle Net) is theoretically interesting but practically very challenging and generally not supported directly by database systems or their respective APIs like JDBC or ODBC.

The direct manipulation of transactions across different database connections and technologies by passing transaction IDs as you mentioned is not feasible with standard database and data access technologies. Instead, this needs to be handled using established distributed transaction protocols and possibly integrating with an enterprise transaction manager that supports all required technologies.

Edit: The FDA guide mentions this in the section "Defining Transaction Processing for a Report"

You can also extend your transaction boundaries to include business functions and table I/O.

This leads me to the assumption that this functionality does not extend to forms.

 

[ccornagg]

I'm on the same TR as you and noticed some things I've never noticed before (or never had an with until recently). Namely if the APPL calling a BSFN was in Update Mode the called BSFNs knew that and it could affect it's behaviour!!!!

If I need to do things like you've mentioned, updates to a table I am in a loop over, I use table handles or like you've done, move it to a BSFN (which spawns it's own thread/boundary)
The include in transaction flag attempts to group all the IO into one tramsaction, but it depends on what the BSFN is actually doing and what it intialises. NERs are a bit simpler to control
You have my sympathies with a Power Form though

Thank you very much John,
I think you touched an important topic I missed in my first post: the "Update on Grid Business View" flag at "form properties" level.

At the very beginning of my "power edit" form development, I decided to switch off such a flag because my intention was to insert / update F4211 table, and possibly different tables, under OK button but without using my main form grid (or other subform grids I did implement).
In other words: my intention was to disable the "embedded engine" able to handle db updates via grid, just as a precaution.

Since I made tons of tests and changes to have my "precious" ok button working ... I cannot recall exactly the sequence of various setup, but indeed it's now activated.

I agree with you saying it would be important to clarify if and how that flags impacts transaction hadling for BSFN and tableIO under ok button.

Kind regards,

Carlo

 

[BOster]

This leads me to the assumption that this functionality does not extend to forms.

Based on your post I would tend to agree. I have not had a chance to test yet. Even if it *appears* to work I wonder then if Oracle somehow kind of rolled their own quasi TP between APPL / BSFN... treating it almost like a two phase commit somehow.

 

[BOster]

Quick update. Did some quick initial testing. It did not go well. Tried to effectively do:

begin transaction;
insert into jde_development.dvdta.f59tst49 values (1, 'a',' ',' ');                            --  insert from grid
update jde_development.dvdta.f59tst49 set tpdl02 = 'ER Code Table I/O' where tpukid = 1        -- update from ER Code table i/o
update jde_development.dvdta.f59tst49 set tpdl03 = 'BSFN Table I/O' where tpukid = 1        -- update from BSFN
commit transaction

Both the ER code and the BSFN dead locked.

 

[jdelisths]

Thanks for testing this out, @BOster!