I have a function I created in my PostgreSQL DB that I want to call using JPA 2.1's StoredProcedureQuery method.
Here is my PostgreSQL query:
CREATE OR REPLACE FUNCTION get_values(date text) returns refcursor
AS $$
DECLARE tuples refcursor;
BEGIN OPEN tuples FOR
SELECT user, COUNT(*)
FROM my_table
WHERE date_ = date
GROUP BY user;
return tuples;
END;
$$
LANGUAGE plpgsql
This is just a simple query to count users on a particular day. This is just a demo query to test how the StoredProcedureQueries work. And in fact, it works just fine when used via postgreSQL alone.
Now, let's try and call this using JPA 2.1 and in Javaland:
StoredProcedureQuery storedProcedure = em.createStoredProcedureQuery("get_values");
storedProcedure.registerStoredProcedureParameter(2, String.class, ParameterMode.IN);
storedProcedure.registerStoredProcedureParameter(1, Object.class, ParameterMode.REF_CURSOR);
storedProcedure.setParameter(2, "2015-02-01");
storedProcedure.execute();
When I do this, I get back the following exception:
org.hibernate.HibernateException: PostgreSQL supports only one REF_CURSOR parameter, but multiple were registered
There is only a single ref cursor declared! In fact, if I just register the single REF_CURSOR parameter and hardcode in a value for my Postgresql function for the WHERE date_ = date, this call works just fine.
So it would seem adding any additional parameters to a storedprocedurequery with a ref_cursor breaks the functionality. Alone, the ref_cursor parameters works fine.
Anybody see why this would happen?? Why is it that adding parameters to the StoredProcedureQuery for my PostgreSQL function breaks it?
Example of when it works:
CREATE OR REPLACE FUNCTION get_values(date text) returns refcursor
AS $$
DECLARE tuples refcursor;
BEGIN OPEN tuples FOR
SELECT user, COUNT(*)
FROM my_table
WHERE date_ = '2015-02-01'
GROUP BY user;
return tuples;
END;
$$
LANGUAGE plpgsql
and in javaland:
StoredProcedureQuery storedProcedure = em.createStoredProcedureQuery("get_values");
storedProcedure.registerStoredProcedureParameter(1, Object.class, ParameterMode.REF_CURSOR);
storedProcedure.execute();