Well as my todo comment says:
// todo : how to identify calls which should be in the form `{? = call
procName...}` ??? (note leading param marker)
// more than likely this will need to be a method on the native
API. I can see this as a trigger to
// both: (1) add the `? = ` part and also (2) register a REFCURSOR
parameter for DBs (Oracle, PGSQL) that
// need it.
:)
On Mon, Feb 15, 2016 at 7:54 AM Vlad Mihalcea <mihalcea.vlad(a)gmail.com>
wrote:
Hi,
While writing the stored procedure section, I found a way to improve the
current implementation to FUNCTIONS as well.
Considering the following function:
CREATE FUNCTION fn_post_comments(postId integer)
RETURNS integer
DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE commentCount integer;
SELECT COUNT(*) INTO commentCount
FROM post_comment
WHERE post_comment.post_id = postId;
RETURN commentCount;
END
We could call this function and fetch the result ith plain-old JDBC:
session.doWork(connection -> {
try (CallableStatement function = connection.prepareCall("{ ? =
call fn_count_comments(?) }")) {
function.registerOutParameter(1, Types.INTEGER);
function.setInt(2, 1);
function.execute();
int commentCount = function.getInt(1);
assertEquals(2, commentCount);
}
});
When using the JPA 2.1 API:
StoredProcedureQuery query =
entityManager.createStoredProcedureQuery("fn_count_comments");
query.registerStoredProcedureParameter("postId", Long.class,
ParameterMode.IN);
query.setParameter("postId", 1L);
Long commentCount = (Long) query.getSingleResult();
We get a "PROCEDURE fn_count_comments does not exist" exception because the
SQL statement is built as "{call fn_count_comments(?)}" instead of "{ ? =
call fn_count_comments(?) }".
I think we could define a hint like this:
query.setHint(QueryHints.HINT_CALL_FUNCTION, true);
So we could adjust the callable statement to work like a function.
What do you think of this?
Vlad
_______________________________________________
hibernate-dev mailing list
hibernate-dev(a)lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev