calling a Oracle stored function without parameters/argument

Classic Servoy
1

Hi,

I’m stuck in calling a method an Oracle stored function without argumets/parameters.
In SQL : select schema.name_function() from dual ;
– it just returns a string value like ‘0219.4’

but in calling databaseManager.executeStoredProcedure I’m stuck with the parameters Object, number, numberofRows.

Any suggestions,
Best regards,
Jan Willem

2

Jan Willem,

You have to create a stored procedure with OUT parameter.
Use the ? in the call for input and output parameters.
The returned dataset will contain the values for the output parameters.

Look at the sample code.

Rob

3

Rob,

From your reply I understand, that Servoy does not support function call’s, only stored procedures. Is that correct?

If that’s the case, then the documentation is ‘misleading’ in the way it mentions explictly functions besides procedures :(

Is using the getdatasetbyquery a better solution, if I do not want to create a stored procedure, because the function is already there?

regards,
Jan Willem

4

Rob,

OK, I defined a stored procedure confrom your suggestion based on the method sample. But I get an persistance error
Here’s the script part:

var typesArray = new Array() ;
typesArray[0] = 1;
typesArray[1] = 0;
//var nieuwAanvraagNR = databaseManager.executeStoredProcedure(controller.getServerName(),
// ‘bvg.bvg_get_next_aanvraagnr’,args,typesArray,2) ;

//Execute a stored procedure
var maxReturnedRows = 10;//useful to limit number of rows
var procedure_declaration = ‘?={bvg.bvg_getupd_next_aanvraagnr(?)}’
var args = new Array() ;
args[0] = java.sql.Types.VARCHAR ;
args[1] = ‘’;
// define the types and direction, in this case a 2 for output data according Servoy documentation
typesArray[1]=2;
var nieuwdataset = databaseManager.executeStoredProcedure(controller.getServerName(), procedure_declaration, args, typesArray,maxReturnedRows);

var nieuwAanvraagNR = args[1] ;
//zet in veld aanvraagnr
aanvraagnr = nieuwAanvraagNR ;

Any suggestions on either the procedure_declaration or the arg. types

Jan Willem

5

Problem solved, here is the script with an Oracle stored function. Solution found by studing Oralce jdbc documentation on the subject and by trial and error.

var args = new Array() ;
args[0] = java.sql.Types.VARCHAR ;
var typesArray = new Array() ;
typesArray[0] = 1; // output
var nieuwAanvraagNR = ‘0222.01’ ;
var function_declaration = “{ ?=call bvg.bvg_get_next_aanvraagnr() }” ;
//var function_declaration = “begin ? := bvg.bvg_get_next_aanvraagnr(); end;” ;
var dmesp = databaseManager.executeStoredProcedure(controller.getServerName(), function_declaration, args, typesArray,1) ;
nieuwAanvraagNR = dmesp.getValue(1,1) ;

Conclusion: the Reference Guide on this method .executeStoredProcedure is in my humble opinion not correct in the example procedure_declaration and on the typeArray values.

From a jdbc article on internet:

CallableStatement stproc_stmt = conn.prepareCall
(“{call procname(?,?,?)}”);
CallableStatement stproc_stmt = conn.prepareCall
(“{ ? = call _funcname(?,?,?)}”);

The difference is the call part

Regards,
Jan Willem