Worked Example of Stored Proc

[PointsWell]

Thanks to Bruce et al's posts on EXEC_SP

I wasn't smart enough to fully understand how EXEC_SP worked initially so I had to go on my own personal development journey.

I have a number of BO in my model:
Contact
Contact_Bank
Contact_Bank_Statement
Finance_Cashbook

A Contact may have one or more Contact_Bank (accounts) and a Contact_Bank may have one or more Contact_Bank_Statement. A Contact_Bank_Statement has one or more statement lines which appear as records in the Finance_Cashbook. And then there is some linkage to reference data.

The (abridged) ERD looks something like this (this is obviously not an ERD but the image from the query builder I used):

Abridged ERD

Screen Shot 2018-02-14 at 13.02.59.png (23.62 KiB) Viewed 44381 times

To calculate the current and historic balance requires a SUM across each and every transaction and that to be updated every time that a new Finance_Cashbook line is entered. Too expensive and complex to do in AIM, but not that expensive as a native SQL query.

It looks something like this in a stored procedure (the stored proc is not optimal in its method of calculating the running balance but it is fit for purpose for figuring out the tricky bits).
DEPRECATED FOR mySQL 8.0 SEE ALTERNATIVE BELOW

Code: Select all

SET @runtot:=0;
SELECT  `MonthTot`.`Acc` AS `Account`,
        `MonthTot`.`Curr` AS `Curr`,
        `MonthTot`.`TXNYEAR` AS `Year`,
        `MonthTot`.`TXNMTH` AS `Month`,
         CONCAT(`MonthTot`.`TXNYEAR`, '/', `MonthTot`.`TXNMTH`) AS `Period`,
        `MonthTot`.`DEBITS` AS `Money Out`,
        `MonthTot`.`CREDITS` AS `Money In`,  
         @runtot:=@runtot+(`MonthTot`.`CREDITS`-`MonthTot`.`DEBITS`) AS `Balance`

FROM
   (SELECT
        `CONTACT_BANK`.`AccountNickname` AS `Acc`,
        `REF_GENERAL_CURRENCY`.`ISO3` AS `Curr`,
         YEAR(`FCB`.`DateTransaction`) AS `TXNYEAR`,
         MONTH(`FCB`.`DateTransaction`) AS `TXNMTH`,
         SUM(`FCB`.`AmtDebit`)  AS `DEBITS`,
         SUM(`FCB`.`AmtCredit`) AS `CREDITS`
    FROM  `REF_GENERAL_CURRENCY` 
INNER JOIN `CONTACT_BANK`  ON `REF_GENERAL_CURRENCY`.`ID` = `CONTACT_BANK`.`ps_Currency_RID` 
INNER JOIN `CONTACT_BANK_STATEMENT`  ON `CONTACT_BANK`.`ID` = `CONTACT_BANK_STATEMENT`.`ob_Bank_RID` 
INNER JOIN `FINANCE_CASHBOOK` `FCB`  ON `FCB`.`ob_Statement_RID` = `CONTACT_BANK_STATEMENT`.`ID`
WHERE `CONTACT_BANK`.`ID`=22712
    GROUP  BY `TXNYEAR` ASC, `TXNMTH` ASC, `ACC`, `CURR`
    ) AS `MonthTot`;

And this then generates this output:

Code: Select all

Account,        Curr,  Year,   Month,  Period,  Money Out, Money In,  Balance
"EURO Account",  EUR,  2018,       1,  2018/1,         20,      500,      480
"EURO Account",  EUR,  2018,       3,  2018/3,        120,      200,      560
"EURO Account",  EUR,  2018,       5,  2018/5          50,        0,      510
"EURO Account",  EUR,  2018,       6,  2018/6,          0,      100,      610
"EURO Account",  EUR,  2018,       7,  2018/7,          0,      400,     1010
"EURO Account",  EUR,  2018,       8,  2018/8,        250,        0,      760

My misunderstanding was the approach to get this into AIM, as I didn't realise that AIM would capture that output exactly and then just needs somewhere to be put.

I created an object (which I named the same as the SP in the database):

AIM BO

Screen Shot 2018-02-19 at 13.05.38.png (28.76 KiB) Viewed 44381 times

In order to get AIM to play nicely with the returned output the SP needs to be modified slightly to add in the Happy AIM columns: BASVERSION, BASTIMESTAMP and ID:

Code: Select all

SET @runtot:=0;
SET @ID:=0;
SELECT 1 AS `BASVERSION`,
		  CURRENT_TIMESTAMP AS `BASTIMESTAMP`,
        @ID:=@ID+1 AS `ID`,
		  `MonthTot`.`Acc` AS `AccountNick`,
        `MonthTot`.`Curr` AS `CurrISO`,
        `MonthTot`.`TXNYEAR` AS `Year`,
        `MonthTot`.`TXNMTH` AS `Month`,
        CONCAT(`MonthTot`.`TXNYEAR`, '/', `MonthTot`.`TXNMTH`) AS `Period`,
        `MonthTot`.`DEBITS`*-1 AS `MoneyOut`,
        `MonthTot`.`CREDITS` AS `MoneyIn`,  
		  @runtot:=@runtot+(`MonthTot`.`CREDITS`-`MonthTot`.`DEBITS`) AS `Balance`

I added BASVERSION as a Constant, BASTIMESTAMP as CURRENT_TIMESTAMP and ID as an incremental counter. I also multiplied MoneyOut by -1 to make it look nice in a graph.

Last step was to set up the query in AIM:

Code: Select all

EXEC_SP 'CB_GetTxnBalByMth' WITH '@AccountID'=LoggedInRegularUser.VPL_FinBankAcc.ID RETURN CB_GetTxnBalByMth

I then created a graph to show the Money In, Money Out and Running Balance by month.

Graph Output

Screen Shot 2018-02-19 at 13.11.46.png (34.48 KiB) Viewed 44381 times

I wanted to put all the various contributions, suggestions and experience posts by everyone else explaining all the moving parts into one worked example and hopefully save anyone coming behind me some time.

My reading list:
Bruce's Post on his journey through Stored Procedures https://www.awareim.com/forum/viewtopic ... 40&p=43493

UnionSystems work around for converting arrays into strings for passing to input parameters in stored Procedures (and a link to Bruce's video on SP use, which I've only just seen) https://www.awareim.com/forum/viewtopic ... 22&p=43414

Capturing the BAS fields https://www.awareim.com/forum/viewtopic ... 63&p=43078

Some Speed questions re FIND v SP https://www.awareim.com/forum/viewtopic ... 69&p=39290

[customaware]

Nice post Sean.

Glad you solved it.

[PointsWell]

So, when you do something then realise your logic was a little bit off and mySQL 8 has deprecated the use of variables and introduced windows.

This has made everything a lot simpler

I made adjustments to my data model

Adjusted Data Model

Screen Shot 2021-09-28 at 12.19.07.png (28.58 KiB) Viewed 39392 times

And have renamed the receiving BO to make everything more generic.

Generic Receiving BO

Screen Shot 2021-09-28 at 12.22.02.png (25.15 KiB) Viewed 39392 times

The structure of the query changes in mySQL 8 to be FIND DATA then CREATE THE RESULT - previously it was a query and a sub query.

Now the weekly Balance calculation looks like:

Code: Select all

CREATE  PROCEDURE `FI_AccountsGetBalanceWeekly`(IN AccountID INT)
    READS SQL DATA
BEGIN
SET @ID:=0;
WITH DATA AS
( SELECT
        `COBANK`.`AccountNickname` AS `Acc`,
        `RGECURRENCY`.`ISO3` AS `Curr`,
        YEAR(`FICASHBOOK`.`DateTransaction`) AS `PMAJOR`,
        WEEK(`FICASHBOOK`.`DateTransaction`) AS `PMINOR`,
        SUM(`FICASHBOOK`.`AmtDebit`)  AS `DEBITS`,
        SUM(`FICASHBOOK`.`AmtCredit`) AS `CREDITS`
    FROM  `RGECURRENCY` 
INNER JOIN `COBANK`  ON `RGECURRENCY`.`ID` = `COBANK`.`psCurrency_RID` 
INNER JOIN `FICASHBOOK`  ON `COBANK`.`ID` = `FICASHBOOK`.`obCOBank_RID` 
WHERE `COBANK`.`ID`= AccountID
GROUP BY `Acc`, `Curr`, `PMAJOR`, `PMINOR`
ORDER BY `Acc` ASC, `PMAJOR` ASC, `PMINOR` ASC
    ) 
    
    SELECT 
        @ID:=@ID+1 AS `ID`, 
        1 AS `BASVERSION`,
        CURRENT_TIMESTAMP AS `BASTIMESTAMP`,
	`Acc` AS `Account`,
        `Curr` AS `CurrISO`,
        `PMAJOR` AS `PeriodMajor`,
        LPAD(`PMINOR`,2,0) AS `PeriodMinor`,
        CONCAT(`PMAJOR`, '/', LPAD(`PMINOR`,2,0)) AS `Period`,
        IFNULL(`DEBITS`*-1,0) AS `MoneyOut`,
        IFNULL(`CREDITS`,0) AS `MoneyIn`,
         SUM(IFNULL(`CREDITS`,0)-IFNULL(`DEBITS`,0)) over (PARTITION BY `PMAJOR` ORDER BY `PMINOR` ASC) AS `Balance` 
         from DATA
         
      AS `FICBAccountBalances`;
END

There are a few interesting things in here that are different between 5 and 8.

• If there are any NULL values in the results then the CREDIT-DEBIT calculation becomes NULL. Need to put IFNULL() statement in to capture those
• The SUM ... OVER (PARTITION BY ... ORDER BY ...) is managing the cumulative balance calculation

This SP is highly generic now, this means that if you just change the line

Code: Select all

WEEK(`FICASHBOOK`.`DateTransaction`) AS `PMINOR`,

to

Code: Select all

MONTH(`FICASHBOOK`.`DateTransaction`) AS `PMINOR`,

then you have a changed the grouping from Week to Month without any other changes being needed in AIM other than pointing to the different SP name.

I only noticed this had stopped working by accident.

[PointsWell]

And this is what the output looks like:

Daily

Screen Shot 2021-09-28 at 12.36.52.png (39.17 KiB) Viewed 39391 times

Weekly

Screen Shot 2021-09-28 at 12.36.37.png (35.27 KiB) Viewed 39391 times

Monthly

Screen Shot 2021-09-28 at 12.36.21.png (32.01 KiB) Viewed 39391 times

[ACDC]

Thanks for this , its exactly what I need for a pending deep dive into running queries using stored procedures (not to mention all the other inputs links)

I have numerous queries providing real time transaction data at a customer account level, running them inside AIM was the first step , now i need to get them running more efficiently as a SP

The problem i have going forward is maintaining the remote stored procedure, its no longer embedded in the BSV, so extra disciplines have to be in place to maintain them. It would be nice if there was a way to design a stored procedure inside AwareIM and then at the time of publishing it does the necessary update in the DB - Maybe that's asking to much from AIM , but it does make sense doesn't it.

BTW, i see your example using foreign exchange elements, are you getting the daily values from an external REST financial service. I would be keen to know more if you are. My Application has become multi currency and needs an automated rate of exchange service, Any advice on this would be really appreciated

[PointsWell]

Thanks for this , its exactly what I need for a pending deep dive into running queries using stored procedures (not to mention all the other inputs links)

I have numerous queries providing real time transaction data at a customer account level, running them inside AIM was the first step , now i need to get them running more efficiently as a SP

The problem i have going forward is maintaining the remote stored procedure, its no longer embedded in the BSV, so extra disciplines have to be in place to maintain them. It would be nice if there was a way to design a stored procedure inside AwareIM and then at the time of publishing it does the necessary update in the DB - Maybe that's asking to much from AIM , but it does make sense doesn't it.

This is an issue - the DB scripts only get passed on the very first publish after that you are SOOL - I lodged a feature request on this. One thing I haven't considered is EXEC_SQL, you may be able to run the SQL for the SP in the Query window. Have not investigated how feasible that is or if that is the camel version of a SP.

BTW, i see your example using foreign exchange elements, are you getting the daily values from an external REST financial service. I would be keen to know more if you are. My Application has become multi currency and needs an automated rate of exchange service, Any advice on this would be really appreciated

There are a bunch of 'free' FX services that I have been looking at. Free versions tend to provide you with a single daily download. There is a good resource at AlphaVantage.co which will give you a pretty full range of historic pricing and then you can just move to daily a point in time price. It is nigh on impossible to get the historic pricing in by REST call due to the dynamic labelling of the result set (it look like it was designed by someone converting a spreadsheet into JSON rather than designing a JSON record a thought given away by the fact that you can download a CSV file - which I have not figured out how to make AIM deal with) See this. I haven't fully implemented any of this yet.

[nhofkes]

Thanks for the updated SQL procedure. I am intending to implement something similar.
If I read your posts correctly, you are not storing the output of the SP into a persisted object in the database, but rather returning the output directly to the call from the query in AIM?

In your earlier post, you wrote that you called the SP by:

Code: Select all

EXEC_SP 'CB_GetTxnBalByMth' WITH '@AccountID'=LoggedInRegularUser.VPL_FinBankAcc.ID RETURN CB_GetTxnBalByMth

So with your revised data model and the worked SP, does that become something like:

Code: Select all

EXEC_SP 'FI_AccountsGetBalanceWeekly' WITH '@AccountID'=LoggedInRegularUser.VPL_FinBankAcc.ID RETURN FICBAccountBalances

The reason for asking is the following sentence in the AIM Rule Language Reference for the EXEC_SP action (version 8.6):

ObjectName if the stored procedure returns a result set (the result of the SQL SELECT statement) Aware IM can convert each record to the instance of the specified object. An object that is persisted in the database table of the SELECT statement must be defined.

The highlighted sentence is a bit confusing to me. Does it mean that there must be a persisted table for the relevant object in the database, but the relevant data do not have to be actually stored in the database but can simply be returned dynamically by the SP? Or does the data also have to be stored in the database for AIM to be able to retrieve it? From your posts I understand the former, but I would appreciate your views.

[PointsWell]

If I read your posts correctly, you are not storing the output of the SP into a persisted object in the database, but rather returning the output directly to the call from the query in AIM?

That is correct

In your earlier post, you wrote that you called the SP by:

Code: Select all

EXEC_SP 'CB_GetTxnBalByMth' WITH '@AccountID'=LoggedInRegularUser.VPL_FinBankAcc.ID RETURN CB_GetTxnBalByMth

So with your revised data model and the worked SP, does that become something like:

Code: Select all

EXEC_SP 'FI_AccountsGetBalanceWeekly' WITH '@AccountID'=LoggedInRegularUser.VPL_FinBankAcc.ID RETURN FICBAccountBalances

Yes these are essentially the same just different SP name and BOs

The reason for asking is the following sentence in the AIM Rule Language Reference for the EXEC_SP action (version 8.6):

ObjectName if the stored procedure returns a result set (the result of the SQL SELECT statement) Aware IM can convert each record to the instance of the specified object. An object that is persisted in the database table of the SELECT statement must be defined.

The highlighted sentence is a bit confusing to me. Does it mean that there must be a persisted table for the relevant object in the database, but the relevant data do not have to be actually stored in the database but can simply be returned dynamically by the SP? Or does the data also have to be stored in the database for AIM to be able to retrieve it? From your posts I understand the former, but I would appreciate your views.

I have the object persisted in the db but not the instances. So AIM sort of believes it is getting BO back but it is not being saved into the database.