Result of a stored function in a calculated dataprovider

Classic Servoy
1

Hello,
the call to the stored function in MySQL works fine in a method

var l_age = 99;

if ( dob )
{
var function_declaration = ‘{?= call calc_age(?)}’

var args = new Array()
args[0] = java.sql.Types.INTEGER
args[1] = dob;

var typesArray = new Array();
typesArray[0]=1;
typesArray[1]=0; // input data

var ds = plugins.rawSQL.executeStoredProcedure(controller.getServerName(), function_declaration, args, typesArray, 1);

l_age = ds.getValue(1,1);
}

return l_age;

Instead of “return” I use a dialog to check the result in a method…

When I use the code in a calculated field, it returns null if dob is not empty.
Does calling a stored function work in calculated fields? If yes, what could be the problem here? Is there a way to debug calculations?

Thanks,
Reto

2

In general it is not a good idea to run Stored Procedures, or SQL Queries in a calculation. Calculations fire very often, and using stored procedures like that have the potential to slow down your solution.

So, I would try to see if you can convert that MySQL stored procedure into some Servoy javascript logic. If it is just a simple age calculation, that can easily be done in javascript. As a rule of thumb, I only use stored procedures to perform some calculations or analysis are very large amounts of data, where the time to transfer the data to perform the calculation makes the calculation take too long in Servoy. So…

Simple business logic: Use javascript in Servoy to do the calculation
Analysis/Calculation that requires lots of data to perform the calc: use a stored procedure or calculation and call it on an event, or with a button (not with a calculation)