Problems with executeStoredProcedure

Classic Servoy
1

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

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

Can you show the procedure code too?

3

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:

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:

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

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.