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

Hello,
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();
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:

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

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?

Thanks,
Reto

PS: To get the procedure to run, I had to add the following parameter to the DB connection string: noAccessToProcedureBodies=true
xtsr
 
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

databaseManager.getDataSetByQuery()

where my query is something like

"SELECT stored_proc_name();"
Christian Batchelor
Certified Servoy Developer
Batchelor Associates Ltd, London, UK
http://www.batchelorassociates.co.uk

http://www.postgresql.org - The world's most advanced open source database.
User avatar
swingman
 
Posts: 1173
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 ?

greg
File Shoppe
gdurniak
 
Posts: 223
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 ;-)

Rafi
Servoy Certified Developer
Image
rafig
 
Posts: 481
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