OUT variable from stored procedure

Classic Servoy
1

Hello,
in a method I call a stored procedure (MySQL 5.0.45).

//Execute a stored procedure
var maxReturnedRows = 10000;//useful to limit number of rows
var procedure_declaration = '{call test_set_active(?,?)}'
var args = new Array()
args[0] = 'z'
args[1] = java.sql.Types.INTEGER

//	define the types and direction, in this case a 0 for input data
var typesArray = new Array();
typesArray[0]=0;
typesArray[1]=1;

var dataset = plugins.rawSQL.executeStoredProcedure(forms.obj_bp.controller.getServerName(), procedure_declaration , args, typesArray,maxReturnedRows);

plugins.dialogs.showInfoDialog( null ,  args[1],  'OK');

The stored procedure:

delimiter //

CREATE PROCEDURE test_set_active (IN param1 CHAR, OUT activated_rows INT)
MODIFIES SQL DATA
BEGIN

  UPDATE test
  SET    active = param1
  WHERE  active is null
  LIMIT  100;
  
  SELECT ROW_COUNT() INTO activated_rows;
  
END;
//


delimiter ;

If called in the command line:

mysql> call test_set_active ( 'g', @x );
Query OK, 0 rows affected (0.56 sec)

mysql> select @x;
+------+
| @x   |
+------+
| 100  |
+------+
1 rows in set (0.03 sec)

However, in Servoy I always get “4” back - java.sql.Types.INTEGER. The procedure itself is called and the update done. Did I miss something about the out param?

Thanks,
Reto

PS: To get the procedure to run, I had to add the following parameter to the DB connection string: noAccessToProcedureBodies=true

2

Hi, not sure if this will work in your case:

I use PostgreSQL and call stored procedures using

databaseManager.getDataSetByQuery()

where my query is something like

“SELECT stored_proc_name();”

3

This is frustrating

I can get databaseManager.getDataSetByQuery() to work, but only with a “Select * from myProcedure()”

I can not get plugins.rawSQL.executeStoredProcedure() to work at all ( the Procedure does not run )

Rather than waste any more time, I will use getDataSetByQuery()

My question:

My Sybase SQL Anywhere procedure has a lot of messages, to Client, to Console, and to Log

is there any way to get a message returned to Servoy ?

Since a Batch File Console can receive and display messages, as the procedure runs, it seems Servoy should as well

Is the reason for executeStoredProcedure() only to return OUT arguments ?

greg

4

Hi Greg,
the way that I have been getting results back from SQL calls in Sybase Sql Anywhere is to create Stored Functions and call them like this

	var query = 'SELECT myStoredFunction(?,?,?)';

	var args = new Array ( );
	args[0] = $order_id;
	args[1] = $status_id;
	args[2] = $username;

	var dataset = databaseManager.getDataSetByQuery ( server_name, query, args, maxReturnedRows );

	status_result = dataset;

	// strip out any extra blank lines from result
	status_result = utils.stringReplace ( status_result, '\n', '' );

not necessarily the most elegant solution (& you might get warnings about what you do with the ‘dataset’), but it works ;-)

Rafi