# Problems with executeStoredProcedure

**URL:** <https://forum.servoy.com/t/problems-with-executestoredprocedure/6022>\
**Category:** Classic Servoy\
**Created:** [June 1, 2006, 6:05pm UTC](https://forum.servoy.com/t/problems-with-executestoredprocedure/6022 "2006-06-01T18:05:41Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![jmeunier](https://avatars.discourse-cdn.com/v4/letter/j/5e9695/32.png) [@jmeunier](https://forum.servoy.com/u/jmeunier)\
**Post date:** [June 1, 2006, 6:05pm UTC](https://forum.servoy.com/t/problems-with-executestoredprocedure/6022/1 "2006-06-01T18:05:41Z")

</div>

Hi all,

I am having a problem with executeStoredProcedure on a MySQL database that I am having a tough time figuring out. I am trying to run a function that I created in MySQL 5.0.18 that is working in Query Browser but not in Servoy. Whenever I try to run it, the dataset returned has an error: “Unable to retrieve metadata for procedure. Unable to retrieve metadata for procedure.” Yes it lists it twice.

I am not sure what is going on, but I have supplied my code below. The function takes 8 parameters, so it is a bit lengthy.

Does anyone have an idea?

Thanks,  
Jason

```auto
var locus = plugins.dialogs.showInputDialog( 'Locus', 'Locus');
var race = plugins.dialogs.showInputDialog( 'race', 'race');
var m1 = plugins.dialogs.showInputDialog( 'm1', 'm1');
var m2 = plugins.dialogs.showInputDialog( 'm2', 'm2');
var c1 = plugins.dialogs.showInputDialog( 'c1', 'c1');
var c2 = plugins.dialogs.showInputDialog( 'c2', 'c2');
var af1 = plugins.dialogs.showInputDialog( 'af1', 'af1');
var af2 = plugins.dialogs.showInputDialog( 'af2', 'af2');

//Execute a stored procedure
var maxReturnedRows = 10;
var procedure_declaration = '{?=PICALCTRIO(?,?,?,?,?,?,?,?)}';
var args = new Array();
args[0] = java.sql.Types.NUMERIC;
args[1] = locus;
args[2] = race;
args[3] = m1;
args[4] = m2;
args[5] = c1;
args[6] = c2;
args[7] = af1;
args[8] = af2;

var typesArray = new Array();
typesArray[0]=1;
typesArray[1]=0;
typesArray[2]=0;
typesArray[3]=0;
typesArray[4]=0;
typesArray[5]=0;
typesArray[6]=0;
typesArray[7]=0;
typesArray[8]=0;

var dataset = databaseManager.executeStoredProcedure(forms.allelefrequency.controller.getServerName(), 
              procedure_declaration, args, typesArray,maxReturnedRows);

plugins.dialogs.showInfoDialog( null , args[0], 'OK');

```

---

<div class="post-metadata">

**Author:** ![ngervasi](https://avatars.discourse-cdn.com/v4/letter/n/f9ae1b/32.png) [@ngervasi](https://forum.servoy.com/u/ngervasi)\
**Post date:** [June 2, 2006, 2:54pm UTC](https://forum.servoy.com/t/problems-with-executestoredprocedure/6022/2 "2006-06-02T14:54:08Z")

</div>

Can you show the procedure code too?

---

<div class="post-metadata">

**Author:** ![jmeunier](https://avatars.discourse-cdn.com/v4/letter/j/5e9695/32.png) [@jmeunier](https://forum.servoy.com/u/jmeunier)\
**Post date:** [June 2, 2006, 7:53pm UTC](https://forum.servoy.com/t/problems-with-executestoredprocedure/6022/3 "2006-06-02T19:53:43Z")

</div>

I cannot give you the code for the function, but I made a test one that I can give you the code that give the same error in the dataset. Keep in mind that I am seeing this error in the debugger when I look at the dataset right after it runs executeStoredProcedure.

Here is the test method:

```auto
var x = plugins.dialogs.showInputDialog( 'x', 'x');
var y = plugins.dialogs.showInputDialog( 'y', 'y');
var z = plugins.dialogs.showInputDialog( 'z', 'z');

//Execute a stored procedure
var maxReturnedRows = 10;
var procedure_declaration = '{?= TEST(?,?,?)}';
var args = new Array();
args[0] = java.sql.Types.NUMERIC;
args[1] = x;
args[2] = y;
args[3] = z;

var typesArray = new Array();
typesArray[0]=1;
typesArray[1]=0;
typesArray[2]=0;
typesArray[3]=0;

var dataset = databaseManager.executeStoredProcedure(forms.allelefrequency.controller.getServerName(), 
              procedure_declaration, args, typesArray,maxReturnedRows);

plugins.dialogs.showInfoDialog( null , args[0], 'OK');

```

Here is the function created in MySQL:

```auto
DELIMITER $

DROP FUNCTION IF EXISTS `lab`.`TEST` $
CREATE FUNCTION `TEST`(x VARCHAR(20), y VARCHAR(30), z INTEGER) RETURNS double
    READS SQL DATA
    DETERMINISTIC
BEGIN
      DECLARE Cnt DOUBLE;
      SELECT (1/Sum(testnbr)) INTO Cnt FROM test
      WHERE testx = x AND testy = y AND
             testz = z;
      RETURN Cnt;
END $

DELIMITER ;

```

---

<div class="post-metadata">

**Author:** ![ngervasi](https://avatars.discourse-cdn.com/v4/letter/n/f9ae1b/32.png) [@ngervasi](https://forum.servoy.com/u/ngervasi)\
**Post date:** [June 4, 2006, 6:09pm UTC](https://forum.servoy.com/t/problems-with-executestoredprocedure/6022/4 "2006-06-04T18:09:41Z")

</div>

I’m not an expert in stored procedures, I only used a stored function once to perform a live backup of a iAnyware DB but looks like you are returning a number from the database and not a dataset, that could explain the error.