question on calculations

Classic Servoy
1

Hi,

This is a followup on http://www.servoy.com/forum/viewtopic.php?f=2&t=11535

We use quite a few calculations (unstored) in our solution.
Some of these had loops in them to calculate values based on 2 or 3 related tables/fields.
First of all these (heavy) calculations give errors in tableview and i created a case for this.

I noticed ( also thanks to Harjo) that these calculations also have a significant impact
on the performance of Servoy and the Oracle database.

So i changed the calculation into a Sql query and that was much faster and also gives no errors in tableview.

But using databaseManager.getDataSetByQuery in a calculation is not supported.

So i changed my calculation to use a stored function which is even faster then a sql-query.
AND it is supported in a calculation.

var args = new Array() ; 
args[0] = java.sql.Types.NUMERIC ; 
args[1] = inkpordkrt_id
var typesArray = new Array() ; 
typesArray[0] = 1;
typesArray[1] = 0;
var proc_declaration = '{?=call get_inkorder_inkbedr_tot(?)}' ;
var dmesp = plugins.rawSQL.executeStoredProcedure('berp2', proc_declaration, args, typesArray,1) ; 
return dmesp.getValue(1,1);

This al works great.
If I change one of the related table-fields (that are used in the stored function) in a child record the calculation is also updated.

But : if another user changes such a table-field of a child record the databroadcasting takes care of updating those
table-fields in the child records in my solution (on my screen), but the calculation (also on my screen) based on
the stored function is not updated.

if I do it the old-fashioned way ( looping trough foundset in calculation) the calculation does get updated if
one of the childrecords is updated by databroadcasting.

So it seems that if there are no relations / fields in the calculation pointing to the childrecords,
the calculation does nog get updated if childrecords are updated trough databroadcastring.

Is this standard behaviour or could this be a bug ?

2

I inserted some dummy lines to refer to the fields on the childrecords
and now the calcuation is also updated when the childrecords are updated trough databroadcasting.

if(utils.hasRecords(bvko_full_inkorderkaarten_to_inkorderspecregels))
{
 var _dum1 = bvko_full_inkorderkaarten_to_inkorderspecregels.inkorsrgl_aantal
 var _dum2 = bvko_full_inkorderkaarten_to_inkorderspecregels.inkorsrgl_ik_prijs
 var _dum3 = bvko_full_inkorderkaarten_to_inkorderspecregels.bvko_inkorderspecregels_to_verkorderregels$verkor_id.verkor_aantal
}

var args = new Array() ; 
args[0] = java.sql.Types.NUMERIC ; 
args[1] = inkpordkrt_id
var typesArray = new Array() ; 
typesArray[0] = 1;
typesArray[1] = 0;
var proc_declaration = '{?=call get_inkorder_inkbedr_tot(?)}' ;
var dmesp = plugins.rawSQL.executeStoredProcedure('berp2', proc_declaration, args, typesArray,1) ; 
return dmesp.getValue(1,1);

Is this a good workaround or ??

3

Hi Hans,

I think IMHO this is a good workaround, because you now reference the calc, to related data, the servoy way.
So Servoy now knows that it has to update this calculation because some of childeren has changed.

(PS If I look out of the window, I see your company! ;-) I’m working in Hengelo today :-) )

4

calcs know what the reference and based on that they get refreshed
so if you do rawSQL under the hood we dont know anything so that is normal behavior

Harjo: you have a new building start working in that!

5

Thanks !

There is one thing i still do not understand :

  • I put the calculation ( only raw sql, no dummy lines) in a masterform

  • I put the child records in a tabpanel on that masterform

  • then when I change data in the child records on the tabpanel and save, the calculation
    in the masterform is being updated by Servoy.
    And there is no reference in there, only rawsql !!
    Or do al calculations on screen get updated when you save data ?

  • i start a second smartclient with the same solution.

  • do the same actions in the second smartclient

  • in the second smartclient the calculation in the master form gets updated

  • in the first smartclient the childrecords are updated, but the calculation
    in the masterform is not updated.

Regards,

6

yes calculations are tested for records that are set in edit mode
So when you save records then all the stored calcs are recalculated of those records.

This way way make sure to be in sync with calcs like you use.