Is Postgresql INOUT REFCURSOR Supported?

Hibernate ORM
1

Does latest hibernate codebase supports INOUT REFCURSOR as parameter to a function?
I tried below function to execute by putting it in org.hibernate.orm.test.procedure.PostgreSQLStoredProcedureTest

CREATE OR REPLACE FUNCTION public.cursor_func(
	INOUT c1 refcursor)
    RETURNS refcursor   
AS $$
  BEGIN    OPEN c1 FOR SELECT * FROM phone;    END; 
$$ LANGUAGE plpgsql;;

And it throws below exception:

org.hibernate.NotYetImplementedFor6Exception: org.hibernate.procedure.internal.ProcedureParameterImpl
	at app//org.hibernate.procedure.internal.ProcedureParameterImpl.getParameterBinder(ProcedureParameterImpl.java:191)
	at app//org.hibernate.procedure.internal.ProcedureParameterImpl.toJdbcParameterRegistration(ProcedureParameterImpl.java:152)
	at app//org.hibernate.procedure.internal.PostgresCallableStatementSupport.interpretCall(PostgresCallableStatementSupport.java:86)
	at app//org.hibernate.procedure.internal.ProcedureCallImpl.buildOutputs(ProcedureCallImpl.java:589)
	at app//org.hibernate.procedure.internal.ProcedureCallImpl.getOutputs(ProcedureCallImpl.java:564)
	at app//org.hibernate.procedure.internal.ProcedureCallImpl.outputs(ProcedureCallImpl.java:854)
	at app//org.hibernate.procedure.internal.ProcedureCallImpl.execute(ProcedureCallImpl.java:838)
	at app//org.hibernate.orm.test.procedure.PostgreSQLStoredProcedureTest.lambda$testStoredProcedureINOUTRefCursor$25(PostgreSQLStoredProcedureTest.java:290)
	at app//org.hibernate.testing.transaction.TransactionUtil.doInJPA(TransactionUtil.java:256)
	at app//org.hibernate.testing.transaction.TransactionUtil.doInJPA(TransactionUtil.java:297)
	at app//org.hibernate.orm.test.procedure.PostgreSQLStoredProcedureTest.testStoredProcedureINOUTRefCursor(PostgreSQLStoredProcedureTest.java:284)
	at java.base@17.0.2/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base@17.0.2/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
	at java.base@17.0.2/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base@17.0.2/java.lang.reflect.Method.invoke(Method.java:568)
	at app//org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:59)
	at app//org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at app//org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:56)
	at app//org.hibernate.testing.junit4.ExtendedFrameworkMethod.invokeExplosively(ExtendedFrameworkMethod.java:45)
	at app//org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at app//org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
	at app//org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
	at app//org.junit.internal.runners.statements.FailOnTimeout$CallableStatement.call(FailOnTimeout.java:299)
	at app//org.junit.internal.runners.statements.FailOnTimeout$CallableStatement.call(FailOnTimeout.java:293)
	at java.base@17.0.2/java.util.concurrent.FutureTask.run(FutureTask.java:264)
	at java.base@17.0.2/java.lang.Thread.run(Thread.java:833)

Is REFCURSOR as INOUT parameter to function in the roadmap? How can we provide custom support for functions taking REFCURSOR as INOUT parameter?

Thanks,

2

Hey, thanks for trying this out. We don’t have it on our radar right now, but we would be very grateful if you could create a JIRA issue for this, contribute the test you wrote and maybe even work on a fix for this :slight_smile:

3

Sure. Let me refine the test case little further and start planning to work on this.

Thanks for your reply.

4

@beikov INOUT refcursor not supported by hibernate-core itself or it is something for which we need to modify relevant PostgresDialect?

5

I don’t know what you have to modify exactly, but Hibernate generally does support INOUT parameters. It seems PostgreSQL is special in that regard though and requires some extra work.

6

Hi @beikov, I am trying to add test case to org.hibernate.orm.test.procedure.PostgreSQLStoredProcedureTest. Before add that I ran the single test case method and found the test cases are being ignored. I was expecting the execution of test case as there is no @ignore annotation on neither class level nor on method level.
Is it being set somewhere else in any its parent classes?
Thanks,

7

How did you run the test? It’s only skipped if you don’t run it with the proper dialect. See the readme for details about how to run tests: GitHub - hibernate/hibernate-orm: Hibernate's core Object/Relational Mapping functionality

8

Below is how I am running.
./gradlew clean build
./gradlew setDataBase -Pdb=pgsql
cd hibernate-core
../gradlew test --tests org.hibernate.orm.test.procedure.PostgreSQLStoredProcedureTest.testFunctionWithJDBCByName

PostgreSQLStoredProcedureTest is by default set to run using PostgreSQLDialect ( @RequiresDialect(PostgreSQLDialect.class)

NOTE: I am not running it using docker, rather I have local running postgresql.

9

The you should be running just the following:

./gradlew :hibernate-core:test --tests org.hibernate.orm.test.procedure.PostgreSQLStoredProcedureTest.testFunctionWithJDBCByName -Pdb=pgsql
10

When I change value for @RequiresDialect to PostgresPlusDialect, it skips the test but with PostgreSQLDialect it works fine.

How hibernate-orm checks if provided dialect is proper or not?

I need to make PostgreSQLStoredProcedureTest not to skip tests with PostgresPlusDialect.
Thanks,