Stored function call from Java

[amolna...@gmail.com]

Hi Mark!

What is the JDBC standard for calling a stored function?

Statement/PreparedStatement.executeQuery() works with

"SELECT func(...) FROM rdb$database"

Connection.prepareCall() / CallableStatement currently only supports stored procedure execution.

CallableStatement should support stored functions?

"{? = call func(...)}" should work?

Thank You!

[Mark Rotteveel]

On 03/09/2024 15:37, amolna...@gmail.com wrote:
> What is the JDBC standard for calling a stored function?
>
> Statement/PreparedStatement.executeQuery() works with
> "SELECT func(...) FROM rdb$database"

Yes, that is how you should call a stored function (or better yet, call
it in the context of a broader statement against actual tables rather
than against RDB$DATABASE).

> Connection.prepareCall() / CallableStatement currently only supports
> stored procedure execution.
>
> CallableStatement should support stored functions?
> "{? = call func(...)}" should work?

No, that doesn't work. The call escape in Jaybird is for executing
stored procedures only, not stored functions.

Formally, the JDBC specification *optionally* allows a driver to support
calling stored functions in the same way as stored procedures, but that
isn't implemented in Jaybird, and I'm not planning to do so either.

To quote from JDBC 4.3, section 13.4.4 Stored Procedures and Functions:

"""
JDBC drivers may optionally provide support for invoking user-defined or
vendor defined functions using the escape syntax for stored procedures.

The method DatabaseMetaData.supportsStoredFunctionsUsingCallSyntax
returns true if the database supports invoking user-defined or vendor
defined functions using the escape syntax for stored procedures. Please
consult the documentation for your JDBC driver for additional information
"""

In Jaybird, DatabaseMetaData.supportsStoredFunctionsUsingCallSyntax
always returns false.

Mark
--
Mark Rotteveel

[amolna...@gmail.com]

Ok, thank you Mark!