Stored procedures and functions

Classic Servoy
1

Hi,

Finally figured out how to call Oracle stored procedure and function from Servoy. (See attachment if interested).

Now i would like to fill an extra non-database field in the form(tableview) with the output of a function
So for each record displayed, an extra field has to be filled from the function. the prim.key of the table is input for the function.
I use this very often in Oracle Forms, its flexible and it’s fast because the server does the work and the client only display’s the result.

I got it working by creating a view where one of the columns is the function. Now i can create a relation from the forms base table to this view
and then i can create a calculated field which returns .<function_column>

I am not sure that this is the way to do this in Servoy.

Version info :

Version 3.5 rc2-build 507
Java version 1.6.0_01-b06 (Windows XP)
Database Oracle 10Gr2

Oracle_stored_proc_func.pdf (3.12 KB)

2

Can one of the Servoyans answer this one ??

3

I am not a Servoy but I guess this is a viable solution…

4

Oracle Stored procedure in Servoy Oracle Stored Procedure : get_rap_dat_tot(incallno in number, inrep_dat in number, rap_dat_seq out number,
rap_dat_sta out varchar2,rap_dat_dat out date, rap_dat_tyd out number)
Servoy Method :
//set arguments
var args = new Array() ;
args[0] = 34804; //input parameter 1
args[1] = 1; //input parameter 2
args[2] = java.sql.Types.NUMERIC ; //output parameter 1
args[3] = java.sql.Types.VARCHAR ; //output parameter 2
args[4] = java.sql.Types.DATE ; //output parameter 3
args[5] = java.sql.Types.NUMERIC ; //output parameter 4
//set arguments type 0=input; 1=output
var typesArray = new Array() ;
typesArray[0] = 0;
typesArray[1] = 0;
typesArray[2] = 1;
typesArray[3] = 1;
typesArray[4] = 1;
typesArray[5] = 1;
//Declare procedure; 2 input par’s; and 4 output pars; so 6 question marks
var proc_declaration = '{call biskpi.get_rap_dat_tot(?,?,?,?,?,?)}’ ;
//Get Result
var dmesp = databaseManager.executeStoredProcedure(controller.getServerName(),
proc_declaration, args, typesArray,1) ;
application.output(dmesp.getExceptionMsg());
application.output(dmesp.getValue(1,1)); //output parameter 1
application.output(dmesp.getValue(1,2)); //output parameter 2
application.output(dmesp.getValue(1,3)); //output parameter 3
application.output(dmesp.getValue(1,4)); //output parameter 4

Hi Hans Nieuwenhuis,

Can you please explain what the “biskpi” is?

5

Hi Hans,

Not sure if this will work in your Oracle case, but I do call a few stored procedures in PostgreSQL simply by using a normal SELECT statement…

var stock_level_book_id = arguments[0];
var stock_level_location_id = arguments[1];

if(! stock_level_book_id) return 0;
if(! stock_level_location_id) return 0;

var args = new Array(stock_level_book_id,var stock_level_location_id);
args.push(stock_level_book_id,stock_level_location_id);

var query = "SELECT acc_get_new_stock_qty(?,?)";
var dataset = databaseManager.getDataSetByQuery(currentcontroller.getServerName(), query, args, 1);
if(dataset) {
	return dataset.getValue(1,1);
} else {
	return 0;
}
6

@Ars :

biskpi is the name of the Oracle schema that owns the procedure get_rap_dat_tot.

@Christian :

I suppose this may work in Oracle also.
I know it works for functions, but i am not sure about procedures
Will try it and if succesfull, i will post a reply here.
Thanks.

Regards

Hans

7

A couple of month’s ago I had the same sort of problem.

This is a copy of my reply on the Servoy forum:

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