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