Oracle problems: "insert" done after "End"

Hi all !

I'm experiencing a new kind of problem I never had before: I have several INSERT on my Oracle DB ... but they effect the DB just AFTER the "End" of my action.

If I stop my processes with a breakpoint on the "End" and I look into the DB, I don't have any records.

Just after the "End" ... the records appear in my DB table.

The problem is that I have to refresh the number of lines in the table ... and the result is not correct ...


Do you have any suggestion for me ?

Hi Luca,

Do you know what a database transaction is? All database actions in OutSystems are within a transaction, and that's exactly the reason you don't see the database is updated while you're inside an Action. Note this is fully intentional, because this allows you to rollback in case of an error.

I'm not sure I understand why this gives you problems, and what this has to do with "refreshing the number of lines in the table". Can you explain a bit more?

Hi Kilian ! I missed a little part ...


I thought that, putting a "Commit Transaction" action before the end, I could force the INSERT ... but it doesn't work.


My problem is that, before the "End" I need to know the "SELECT COUNT ..." of some items, and I don't have the result I need.

Hi Luca,

  1. A CommitTransaction actually works (the way you think it would).
  2. Any query (whether Aggregate or SQL) you perform after a database action within the same Action, is also inside the database transaction, so should yield the right result even without the commit.

Giving the above, I'd say your query (or logic) has some error, because otherwise it should just work.

Kilian Hekhuis wrote:

Hi Luca,

  1. A CommitTransaction actually works (the way you think it would).
  2. Any query (whether Aggregate or SQL) you perform after a database action within the same Action, is also inside the database transaction, so should yield the right result even without the commit.

Giving the above, I'd say your query (or logic) has some error, because otherwise it should just work.

Hi,

Its how Kilian Hekhuis says: even though the action will only commit what you have done in the 'End' node, if you query your table with a Count you should already get also the inserted value, even though the transaction it's not committed. And also as Kilian Hekhuis this is what we want by default because our logic should see the results updated, but in case of error we should be able to rollback all the info that was updated/inserted/deleted from the database or else you would have inconsistent information.

I don't know what DBMS are you using but follow this link that will explain how OS manages DB transactions:

https://www.outsystems.com/forums/discussion/2830/transactions-in-the-outsystems-platform/

also this one:

https://success.outsystems.com/Documentation/10/Reference/OutSystems_Language/Data/Database_Reference/Handling_Transactions


Cheers and good luck my friend

VC

first, thank you all for your answers ...


I have my main action "Print" that contains 4 actions "PrintPage1", "PrintPage2", "PrintPage3", "PrintPage4" (where every "PrintPageX" make an INSERT in a DB table).


At the end of my "Print" I have a refresh of the records I have in my table ... but the number of my records is not already "updated" yet ...


Even if I put a "Commit" in every "PrintPageX" nothing change ...

Hi,

I really want to understand your use case but I can't. 

All the logic you need to do with the updated DB can be done inside that actions. If you refresh your Table Records widget the values will be updated, why would you need to see that value updated while debugging before the 'End' node? Are you having concurrency issues?

You say that  you need to have the lines refreshed:


The problem is that I have to refresh the number of lines in the table ...

So you need to do logic with updated values before the end node? 

Can you please share an OML with your use case? using example tables?

Cheers 

Hi Vincent,


if you take a look in the PNG I attached ... the action begins > I have the first INSERT (Widget_Click1) > I have the second INSERT (Widget_Click2) > I have the third INSERT (Widget_Click3) > then the last INSERT (Widget_Click4).


After these actions I need to make a SELECT COUNT from the DB table (if I had 4 records, now I could have 6/7/8 records) ... that's the reason why you see a SQL refresh after the 4 Widget_Click.


If I put a breakpoint in the "End" and I take a look in my table, the records are not "inserted" yet (even with the "CommitTransaction" in every Widget_Click) ...

Hi Luca de Mori,

Tell me something,

if you put a breakpoint inside one of the actions PrintPage4X, and you debug, when will that action be called? will it be called before the 'End' node of your main action?

If not, you need to do a different approach to call those 4 actions. Can't you do all the logic in the same action? Using ifs maybe? Even if you have to repeat logic...

Cheers

Hi Vincent, if you take a look at the PNG I attached, you'll see.


The action "Print" launch "PrintPage1", then "PrintPage2", then "PrintPage3", then "PrintPage4" ... every "PrintPage" make different actions.


I put the logic in different actions because they make a lot of stuffs ... just to keep the logic clear

At least when I debug in my OS ( version 9 ) if I do a widget_click on a button that calls a screen action, that will only happen in the end of my main action, for example:

Main:

The actions that are being called from the widget_click ( which I assume you are clicking in buttons ) will only happen after the main actions finish. Try to accomplish this with server actions calling them all directly from your main action or try to find another way to call those actions,

Cheers


Hi Vincent ... thanks again ...


Yes ... the structure it's quite like the one you sent me.


The four "Clicks" launch the four actions ... I don't have the aggregate (I have it in my Preparation of the main page) ... just the SQL refresh.


The main problem is still the one I told you in the beginning: I don't have the records in my table ... so the SQL refresh return me the original number of records ...

Solution

Luca De Mori wrote:

Hi Vincent ... thanks again ...


Yes ... the structure it's quite like the one you sent me.


The four "Clicks" launch the four actions ... I don't have the aggregate (I have it in my Preparation of the main page) ... just the SQL refresh.


The main problem is still the one I told you in the beginning: I don't have the records in my table ... so the SQL refresh return me the original number of records ...

Please take some time to read my sentence and confirm me this:

When you debug, when the actions that are associated with your widget click run? For me, they only run when my main action ends. So the Inserts will only happen after you have refreshed your records. 

What you need to do to avoid this is to use Server Actions and call them instead of calling widget clicks


Cheers


Solution

YOU'RE RIGHT !!! THAT'S THE POINT ... now I understand !

It's like you tell me: the Widget Click run the procedures ... AT THE END OF THE ACTION !


I have to review my entire process ...

Hey

It's so good you were able to figure it out :)

probably now you will be able to do it,

please don't forget to mark as answer so we can help others,

this is something that I didn't know also so I also learned something with this, and this is one of the reasons we got to try to get into things and try to help each other and make this a real community

Cheers and the best of luck for you

Done !


Thank you again