Page 1 of 1

OUT variable from stored procedure

PostPosted: Wed Mar 19, 2008 6:22 pm
by xtsr
in a method I call a stored procedure (MySQL 5.0.45).

Code: Select all
//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();

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:

Code: Select all
delimiter //

CREATE PROCEDURE test_set_active (IN param1 CHAR, OUT activated_rows INT)

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

delimiter ;

If called in the command line:

Code: Select all
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?


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

PostPosted: Wed Mar 19, 2008 8:46 pm
by swingman
Hi, not sure if this will work in your case:

I use PostgreSQL and call stored procedures using


where my query is something like

"SELECT stored_proc_name();"

Re: OUT variable from stored procedure

PostPosted: Sun May 29, 2016 2:07 pm
by gdurniak
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 ?


Re: OUT variable from stored procedure

PostPosted: Wed Jun 01, 2016 12:54 pm
by rafig
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
Code: Select all
   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 ;-)