JDBC with H2 and MySQL mode: create procedure fails

Question

Description of code

Database connection

I try to to store Java object locally database without use external database. For this I use JDBC with H2 via Hibernate :

    /**
     * @param connection the connection to set
     */
    public static void setConnectionHibernate() {
        Properties connectionProps = new Properties();
        connectionProps.put("user", "sa");
        try {
            Class.forName("org.h2.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        url = "jdbc:h2:mem:db1;DB_CLOSE_DELAY=-1;MODE=MySQL;";
    }

Query

I store the PROCEDURE in String with this code :

    static final String CREATE_PROCEDURE_INITPSEUDOS = "CREATE OR REPLACE PROCEDURE init_pseudos (MaxPseudo INT) BEGIN WHILE MaxPseudo >= 0 DO"
            +
            " INSERT INTO Pseudos (indexPseudo)" +
            " VALUES (MaxPseudo);" +
            " SET MaxPseudo = MaxPseudo - 1;" +
            " END WHILE;" +
            " END init_pseudos;";

Query execution

And I execute the statement with this code :

    public static void initBaseDonneePseudos() {
        try (Connection connection = DriverManager.getConnection(url, connectionProps);
                Statement stmt = connection.createStatement()) {
            stmt.execute(RequetesSQL.CREATE_TABLE_PSEUDOS);
            stmt.execute(RequetesSQL.CREATE_PROCEDURE_INITPSEUDOS);
            stmt.execute(RequetesSQL.CREATE_FUNCTION_RECUPEREPSEUDO);
            stmt.execute(RequetesSQL.INIT_TABLE_PSEUDOS);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

Problem

Test

I execute this test to test statement :

    @Nested
    class BaseDonneeInteractionTest {

        @BeforeEach
        public void setUp() {
            BaseDonnee.setConnectionHibernate();
        }

        @Test
        void testInitBaseDonnee() {
            assertDoesNotThrow(() -> BaseDonnee.initBaseDonneePseudos());
        }

    }

Error

But I obtain this error

I didn't find the problem of the query, anybody have the solution to solve this ?

Answer 1

The "MySQL Compatibility Mode" doesn't make H2 100% compatible with MySQL. It just changes a few things. The documentation lists them:

• Creating indexes in the CREATE TABLE statement is allowed using INDEX(..) or KEY(..). Example: create table test(id int primary key, name varchar(255), key idx_name(name));
• When converting a floating point number to an integer, the fractional digits are not truncated, but the value is rounded.
• ON DUPLICATE KEY UPDATE is supported in INSERT statements, due to this feature VALUES has special non-standard meaning is some contexts.
• INSERT IGNORE is partially supported and may be used to skip rows with duplicate keys if ON DUPLICATE KEY UPDATE is not specified.
• REPLACE INTO is partially supported.
• Spaces are trimmed from the right side of CHAR values.
• REGEXP_REPLACE() uses \ for back-references.
• Datetime value functions return the same value within a command.
• 0x literals are parsed as binary string literals.
• Unrelated expressions in ORDER BY clause of DISTINCT queries are allowed.
• Some MySQL-specific ALTER TABLE commands are partially supported.
• TRUNCATE TABLE restarts next values of generated columns.
• If value of an identity column was manually specified, its sequence is updated to generate values after inserted.
• NULL value works like DEFAULT value is assignments to identity columns.
• Referential constraints don't require an existing primary key or unique constraint on referenced columns and create a unique constraint automatically if such constraint doesn't exist.
• LIMIT / OFFSET clauses are supported.
• AUTO_INCREMENT clause can be used.
• YEAR data type is treated like SMALLINT data type.
• GROUP BY clause can contain 1-based positions of expressions from the SELECT list.
• Unsafe comparison operators between numeric and boolean values are allowed.

That's all. There is nothing about procedures. As @jccampanero pointed out in the other answer, you must use the syntax specific to H2 if you want to create stored procedures.