CODEWITHSUNDEEP
javamysqljdbc
Ricardo Ortiz
Ricardo Ortiz

Reputation: 21

"call" for calling MySQL function work on JDBC but not on MySQL itself

I have a function in Java that uses a callable statement and calls a function of an schema in MySQL with the word "call". It works fine.

But if in MySQL I write:

call myFunction(); ( in this case :call getNumberOfIdeasDB();)

It won't work, I get:

PROCEDURE ... doesnt exist.

It's weird to me that it works in Java but not in MySQL. Could you please help me understand why.

Here is my function in Java even though I don't believe it is relevant.

public static double getNumberOfIdeasDB() {
    Connection co = riverManager.getConnection("jdbc:...");
    CallableStatement ps = co.prepareCall("{?= call getNumberOfIdeasThisWeek()}"); //10

    ps.registerOutParameter(1, java.sql.Types.DOUBLE);

    ps.execute();

    return ps.getDouble(1);
}

It is inside a try-catch and works fine I just don't know why here that word "call" works.

Upvotes: 1

Views: 260

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 109239

The {?= call ...} is a JDBC escape (it is defined in the JDBC specification) to execute stored procedures. This escape allows you to write stored procedure calls in a - relatively - database-independent way.

The JDBC driver then has to translate that to the right statement for the underlying database.

Whether functions should be callable is a totally different matter, but the implementation of MySQL Connector/J thinks it does, and provides a translation (likely to something like select getNumberOfIdeasThisWeek()). If instead you had used call getNumberOfIdeasThisWeek() (that is, without the {?= and }), you would likely have received the same error you get when querying MySQL directly.

Upvotes: 3

Related Questions