OUT variable from stored procedure

Questions, tips and tricks and techniques for scripting in Servoy

OUT variable from stored procedure

Postby xtsr » Wed Mar 19, 2008 6:22 pm

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
Posts: 101
Joined: Wed Jan 21, 2004 11:47 am

Postby swingman » Wed Mar 19, 2008 8:46 pm

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();"
Christian Batchelor
Certified Servoy Developer
Batchelor Associates Ltd, London, UK

http://www.postgresql.org - The world's most advanced open source database.
User avatar
Posts: 1439
Joined: Wed Oct 01, 2003 10:20 am
Location: London

Re: OUT variable from stored procedure

Postby gdurniak » Sun May 29, 2016 2:07 pm

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 ?

File Shoppe
Posts: 232
Joined: Thu Jul 08, 2004 7:25 pm
Location: Bayside, NY, USA

Re: OUT variable from stored procedure

Postby rafig » Wed Jun 01, 2016 12:54 pm

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 ;-)

Servoy Certified Developer
Posts: 659
Joined: Mon Dec 22, 2003 12:58 pm
Location: Watford, UK

Return to Methods

Who is online

Users browsing this forum: No registered users and 3 guests