Creating a SQL function from java causes Exception

Question

I'm trying to create a Database using JDBC from Java.

The DB and Table creation goes easily, however now I need to create functions and later a trigger and I keep hitting the wall of exceptions with this function as example:

USE ratercases;
DROP FUNCTION IF EXISTS `IMC`;
DELIMITER $$
CREATE FUNCTION `IMC` (altura INT,peso INT)
  RETURNS DOUBLE
BEGIN
  DECLARE AltM DOUBLE;
  DECLARE AltSqr DOUBLE;
  SET AltM=(altura/100);
  SET AltSqr=AltM*AltM;
  RETURN peso/AltSqr;
END $$
DELIMITER ;

If i run the script above on MySQL Workbench there are no problems; However runnning it from Java gets me SQL Syntax Error Exceptions.

I use the following Java code:

// ps is a SQL Statement; ratercases is the target Database;
ps.execute("USE ratercases;"
                + "DROP FUNCTION IF EXISTS `IMC`;"
                + " DELIMITER $$"
                + " CREATE FUNCTION `IMC` (altura INT,peso INT)"
                + " RETURNS DOUBLE"
                + " BEGIN"
                + " DECLARE AltM DOUBLE;"
                + " DECLARE AltSqr DOUBLE;"
                + " SET AltM=(altura/100);"
                + " SET AltSqr=AltM*AltM;"
                + " RETURN peso/AltSqr;"
                + " END $$"
                + " DELIMITER ;");
//

Java/SQL is apparently having problems with the Delimiter $$ ...

Can anyone enlighten me?

Thanks

Answer 1

An alternative approach would be to connect to the ratercases database directly by specifying it in the connection URL and then execute two separate statements for DROP and CREATE, avoiding the requirement to use DELIMITER at all:

ps.execute("DROP FUNCTION IF EXISTS `IMC`");

ps.execute("CREATE FUNCTION `IMC` (altura INT,peso INT)"
           + " RETURNS DOUBLE"
           + " BEGIN"
           + " DECLARE AltM DOUBLE;"
           + " DECLARE AltSqr DOUBLE;"
           + " SET AltM=(altura/100);"
           + " SET AltSqr=AltM*AltM;"
           + " RETURN peso/AltSqr;");