Not sure if this should work or not but I have a BO with many BO Rules and many of those rules are updating with the SUM and COUNT functions.
These slow the updating of the BO down a lot so I was hoping to pass this off to a Stored Procedure...
But my concern is that we normally need to add a COMMIT TRANSACTION after the Stored Procedure has run.
But of course, in the BO Rules, Aware has not yet written everything back to the DB. So, the SP will be reading values from
the DB but some of these "may" have been updated in an earlier BO Rule. Likewise, subsequent BO Rules will not know the
values in the DB that the SP has updated.
This leads me to think that it is both a bad idea and most likely will not work.
Am I right in this thinking or is there a best practice way of doing it?
Calling Stored Procedure from BO Rules
-
- Posts: 2422
- Joined: Mon Jul 02, 2012 12:24 am
- Location: Ulaanbaatar, Mongolia
Calling Stored Procedure from BO Rules
Cheers,
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
![Image](http://imageshack.com/a/img922/5940/UmAdK9.png)
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
![Image](http://imageshack.com/a/img922/5940/UmAdK9.png)
Re: Calling Stored Procedure from BO Rules
Mark, generally I need a commit transaction BEFORE (not after) I call a stored procedure. That way any data that is either entered (or computed by a rule) in Aware are saved to the database. Since I do that before calling the SP, any aware processing afterwards will (re)read the appropriate records from the db. Most of the time, I will have a process calling a stored procedure. Even if I have a rule call it. I name my processes that call a stored procedure to start with SP_procedureName. That way I can always find them.
Bruce
Bruce
-
- Posts: 2422
- Joined: Mon Jul 02, 2012 12:24 am
- Location: Ulaanbaatar, Mongolia
Re: Calling Stored Procedure from BO Rules
Good suggestion Bruce.
I will try that out.
I will try that out.
Cheers,
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
![Image](http://imageshack.com/a/img922/5940/UmAdK9.png)
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
![Image](http://imageshack.com/a/img922/5940/UmAdK9.png)
Re: Calling Stored Procedure from BO Rules
I am busy with the same thing, would it not be better to use a Trigger for SUM and COUNT purposes ? ( based on UPDATE)
-
- Posts: 2422
- Joined: Mon Jul 02, 2012 12:24 am
- Location: Ulaanbaatar, Mongolia
Re: Calling Stored Procedure from BO Rules
Never used Triggers.
Bruce, do you put the COMMIT TRANSACTION in the BO Rule and then call the SP process or do you put the COMMIT TRANSACTION as the first action on the SP Process…. Or doesn’t it matter?
Bruce, do you put the COMMIT TRANSACTION in the BO Rule and then call the SP process or do you put the COMMIT TRANSACTION as the first action on the SP Process…. Or doesn’t it matter?
Cheers,
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
![Image](http://imageshack.com/a/img922/5940/UmAdK9.png)
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
![Image](http://imageshack.com/a/img922/5940/UmAdK9.png)