Hibernate ORM
  1. Hibernate ORM
  2. HHH-8445

Implement REF_CURSOR support for StoredProcedureQuery

    Details

    • Type: Task Task
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 4.3.0.Beta5
    • Component/s: None
    • Labels:
      None
    • Last commented by a user?:
      true

      Description

      Session session = em.unwrap(Session.class);
      org.hibernate.procedure.ProcedureCall call =
          session.createStoredProcedureCall("FIND_ITEMS");
      
      call.registerParameter(1, Object.class, ParameterMode.REF_CURSOR);
      
      org.hibernate.result.ResultSetOutput resultSetOutput =
          (org.hibernate.result.ResultSetOutput) call.getResult().getCurrent();
      
      List<Item> result = resultSetOutput.getResultList();
      assertEquals(result.size(), 3);
      for (Item item : result) {
          // ...
      }
      
      create function FIND_ITEMS() returns refcursor as '
          declare someCursor refcursor;
          begin
              open someCursor for select * from ITEM;
              return someCursor;
          end;' language plpgsql;
      
      org.hibernate.cfg.NotYetImplementedException: Support for REF_CURSOR parameters not yet supported
      	at org.hibernate.procedure.internal.AbstractParameterRegistrationImpl.prepare(AbstractParameterRegistrationImpl.java:220)
      	at org.hibernate.procedure.internal.ProcedureCallImpl.buildOutputs(ProcedureCallImpl.java:421)
      	at org.hibernate.procedure.internal.ProcedureCallImpl.getResult(ProcedureCallImpl.java:375)
      

        Issue Links

          Activity

          Hide
          Steve Ebersole added a comment - - edited

          Started playing with this some today. Here is what I found for PostgreSQL. Given a function defined to return a refcursor, here is what seems like is needed:

          // Note the need for the leading '? ='...
          CallableStatement callableStatement = connection.prepareCall( "{? = call allPersons()}" );
          // Have not tried with Java 8 and the explicit Types#REF_CURSOR added there...
          callableStatement.registerOutParameter( 1, Types.OTHER );
          
          boolean hasResults = callableStatement.execute();
          assertFalse( hasResults );
          assertEquals( -1, callableStatement.getUpdateCount() );
          
          // Can't even use getObject( 1, ResultSet.class ) since the postgresql jdbc driver does not implement it...
          ResultSet results = (ResultSet) callableStatement.getObject( 1 );
          

          So against postgresql, when a parameter is registered with mode of REF_CURSOR we need to:

          1. Know to append the `? ='
          2. Validate that the REF_CURSOR is registered positionally and that its position is 1
          3. Disallow named parameters since the spec does not allow named/positional mixing and the REF_CURSOR parameter is implicitly positional
          4. Register the JDBC parameter as Types#OTHER
          Show
          Steve Ebersole added a comment - - edited Started playing with this some today. Here is what I found for PostgreSQL. Given a function defined to return a refcursor, here is what seems like is needed: // Note the need for the leading '? ='... CallableStatement callableStatement = connection.prepareCall( "{? = call allPersons()}" ); // Have not tried with Java 8 and the explicit Types#REF_CURSOR added there... callableStatement.registerOutParameter( 1, Types.OTHER ); boolean hasResults = callableStatement.execute(); assertFalse( hasResults ); assertEquals( -1, callableStatement.getUpdateCount() ); // Can't even use getObject( 1, ResultSet.class ) since the postgresql jdbc driver does not implement it... ResultSet results = (ResultSet) callableStatement.getObject( 1 ); So against postgresql, when a parameter is registered with mode of REF_CURSOR we need to: Know to append the `? =' Validate that the REF_CURSOR is registered positionally and that its position is 1 Disallow named parameters since the spec does not allow named/positional mixing and the REF_CURSOR parameter is implicitly positional Register the JDBC parameter as Types#OTHER
          Hide
          Steve Ebersole added a comment -

          Christian, I did some work on this today and got the basics working on PostgreSQL. I I have not tested out error handling, and I have not tested with Oracle. I will be pushing that work soon, if you wanted to try it out.

          I am going to leave this open for now because I want to unify some internal support contracts.

          This will be in the Beta5 release I do tomorrow too, hopefully with those unifications.

          Show
          Steve Ebersole added a comment - Christian, I did some work on this today and got the basics working on PostgreSQL. I I have not tested out error handling, and I have not tested with Oracle. I will be pushing that work soon, if you wanted to try it out. I am going to leave this open for now because I want to unify some internal support contracts. This will be in the Beta5 release I do tomorrow too, hopefully with those unifications.
          Hide
          Steve Ebersole added a comment -

          For my own reference the contracts I would like to unify are:

          1. org.hibernate.procedure.spi.CallableStatementSupport
          2. org.hibernate.engine.jdbc.cursor.spi.RefCursorSupport
          Show
          Steve Ebersole added a comment - For my own reference the contracts I would like to unify are: org.hibernate.procedure.spi.CallableStatementSupport org.hibernate.engine.jdbc.cursor.spi.RefCursorSupport
          Hide
          Christian Bauer added a comment -

          Steve, all my tests pass now and it looks like we have a very usable set of SP features. I haven't tested REF_CURSOR on Oracle though, only PostgreSQL.

          Show
          Christian Bauer added a comment - Steve, all my tests pass now and it looks like we have a very usable set of SP features. I haven't tested REF_CURSOR on Oracle though, only PostgreSQL.
          Hide
          Steve Ebersole added a comment -

          Awesome, thanks for trying it out Christian.

          Show
          Steve Ebersole added a comment - Awesome, thanks for trying it out Christian.

            People

            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - Not Specified
                Not Specified
                Logged:
                Time Spent - 10.75h
                10.75h

                  Development