SQL Exception: Invalid Use of group Function

[Inquisit0r]

I am getting this error that I can't quite wrap my head around. The code segment calls a mysql stored function that basically assigns an available id number.
This is the code:

try
{
  CallableStatement call_proc = conn.prepareCall( "{ ? = call getAvailableId(?, ?) }" );
						
  call_proc.registerOutParameter(1, Types.INTEGER);
  call_proc.setString(2, tableName);
  call_proc.setString(3, "vendor_id");
  call_proc.execute();
  int idNumber = call_proc.getInt(1);
  vendor.setVendorID(idNumber);	
  call_proc.close();
}catch (SQLException s)
{
  out.println(s.toString());
}

And testing has found that it crashes at cstmnt.execute() for some malicious reason. This is the exact error statement:

java.sql.SQLException: Invalid use of group function

Please help.

[Ezzaral]

First, I would recommend fixing your indentation in the posted code fragment. People are more willing to read well-formatted code.

Second, I would check the procedure itself. Sounds like it's unhappy about an aggregate function or group clause.

[Inquisit0r]

First, I would recommend fixing your indentation in the posted code fragment. People are more willing to read well-formatted code.

Second, I would check the procedure itself. Sounds like it's unhappy about an aggregate function or group clause.

Sorry about the indentation. I'm usually very neat but I was in a rush leaving my summer job when I posted this and I just hit the post button and split to my dad's car before he could give me the stink-eye. :$

This is my stored function:

CREATE DEFINER=`root`@`localhost` FUNCTION `getAvailableId`(
tableName varchar(15),
idColumn varchar(15)) 
RETURNS int(11)

BEGIN
    DECLARE availableId INT DEFAULT 1;

    IF min(idColumn) = null THEN
            SET availableId = 1;
    ELSE
            SET availableId = max(idColumn)+1;
    END IF;

    IF availableId < 1 THEN
            SET availableId = 1;
    END IF;

    RETURN availableId;
END

When I have the program print the ID number when the java function runs it outputs 0 then the error messages that I gave before.
And 0 is the initialised value of the variable before it jumps into the sql query.

[Ezzaral]

I don't see where you specify the table name anywhere in that function, just the column.

[Inquisit0r]

Thanks for the help but I managed to spot the error. I thought using variables in statements to substitute their string values made sense but apparently not.
So after modifying and testing the stored procedure line by line I got it to work...that and I almost overhauled my code since the logic was off.
Ah, me and simple maths.
But it was the stored function as you spotted.

CREATE DEFINER=`root`@`localhost` FUNCTION `getAvailableId`(
tableName varchar(15)) RETURNS int(11)
    DETERMINISTIC

BEGIN
    DECLARE availableId INT;
    DECLARE recordsCount INT;
    DECLARE maxId INT;

    IF tableName = "product" THEN
            SELECT COUNT(*) FROM device INTO recordsCount;
            SELECT max(product_id) FROM product INTO maxId;
    ELSE
            IF tableName = "vendor" THEN
                    SELECT COUNT(*) FROM vendor INTO recordsCount;
                    SELECT max(vendor_id) FROM vendor INTO maxId;
            ELSE
                    SELECT COUNT(*) FROM member INTO recordsCount;
                    SELECT max(member_id) FROM member INTO maxId;
            END IF;

    END IF;

    IF  recordsCount = 0 THEN
            SET availableId = 1;
    ELSE
            SET availableId = maxId+1;
    END IF;