# OUT variable from stored procedure

**URL:** <https://forum.servoy.com/t/out-variable-from-stored-procedure/8887>\
**Category:** Classic Servoy\
**Created:** [March 19, 2008, 4:22pm UTC](https://forum.servoy.com/t/out-variable-from-stored-procedure/8887 "2008-03-19T16:22:01Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![xtsr](https://avatars.discourse-cdn.com/v4/letter/x/c67d28/32.png) [@xtsr](https://forum.servoy.com/u/xtsr)\
**Post date:** [March 19, 2008, 4:22pm UTC](https://forum.servoy.com/t/out-variable-from-stored-procedure/8887/1 "2008-03-19T16:22:01Z")

</div>

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

```auto
//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:

```auto
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:

```auto
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

---

<div class="post-metadata">

**Author:** ![swingman](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/swingman/32/4536_2.png) [@swingman](https://forum.servoy.com/u/swingman)\
**Post date:** [March 19, 2008, 6:46pm UTC](https://forum.servoy.com/t/out-variable-from-stored-procedure/8887/2 "2008-03-19T18:46:18Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![gdurniak](https://avatars.discourse-cdn.com/v4/letter/g/a698b9/32.png) [@gdurniak](https://forum.servoy.com/u/gdurniak)\
**Post date:** [May 29, 2016, 12:07pm UTC](https://forum.servoy.com/t/out-variable-from-stored-procedure/8887/3 "2016-05-29T12:07:02Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![rafig](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/rafig/32/4463_2.png) [@rafig](https://forum.servoy.com/u/rafig)\
**Post date:** [June 1, 2016, 10:54am UTC](https://forum.servoy.com/t/out-variable-from-stored-procedure/8887/4 "2016-06-01T10:54:31Z")

</div>

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

```auto
	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 ![;-)]( "Wink")

Rafi