Uploaded image for project: 'Hibernate ORM'
  1. HHH-9548

Allow propagation of NULL for stored-procedure argument parameters to database

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 5.1.0
    • Component/s: query-hql
    • Last commented by a user?:
      true
    • Sprint:

      Description

      This is allowed at 2 levels...

      First, this can be controlled at a "global" level (via the new hibernate.proc.param_null_passing setting, see org.hibernate.cfg.AvailableSettings#PROCEDURE_NULL_PARAM_PASSING) when building the SessionFactory.

      Additionally this can be adjusted per-parameter, per-execution. In the native Hibernate API this is done through org.hibernate.procedure.ParameterRegistration#enablePassingNulls. The default for this per-parameter is defined by the "global" setting. In JPA, this is done via hints; in the initial implementation this is only supported through named queries.


      Original Description

      I've this Exception when pass a null as an In Paramter to a storedprocedure:

      Caused by: java.sql.SQLException: Falta el parámetro IN o OUT en el índice:: 11
      	at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:2244) [ojdbc7.jar:12.1.0.2.0]
      	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4790) [ojdbc7.jar:12.1.0.2.0]
      	at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:4901) [ojdbc7.jar:12.1.0.2.0]
      	at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:5631) [ojdbc7.jar:12.1.0.2.0]
      	at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1385) [ojdbc7.jar:12.1.0.2.0]
      	at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.execute(WrappedPreparedStatement.java:404)
      	at org.hibernate.result.internal.OutputsImpl.<init>(OutputsImpl.java:69) [hibernate-core-4.3.7.Final.jar:4.3.7.Final]
      

      I see this is this file

      org.hibernate.procedure.internal.AbstraAbstractParameterRegistrationImpl::prepare (línea 276-293 hibernate-core-4.3.7.Final)
      
      if ( mode == ParameterMode.INOUT || mode == ParameterMode.IN ) {
      	if ( bind == null || bind.getValue() == null ) {
      		// the user did not bind a value to the parameter being processed.  That might be ok *if* the
      		// procedure as defined in the database defines a default value for that parameter.
      		// Unfortunately there is not a way to reliably know through JDBC metadata whether a procedure
      		// parameter defines a default value.  So we simply allow the procedure execution to happen
      		// assuming that the database will complain appropriately if not setting the given parameter
      		// bind value is an error.
      		log.debugf(
      				"Stored procedure [%s] IN/INOUT parameter [%s] not bound; assuming procedure defines default value",
      				procedureCall.getProcedureName(),
      				this
      		);
      	}
      	else {
      		typeToUse.nullSafeSet( statement, bind.getValue(), startIndex, session() );
      	}
      }
      

      I've try to set a default value but not luck. I think that It can be remove the if and use

      typeToUse.nullSafeSet( statement, bind.getValue(), startIndex, session() );
      

      Because in

      org.hibernate.type.descriptor.sql.BasicBinder::bind (línea 66 hibernate-core-4.3.7.Final)
      

      it has this null protection:

      @Override
      public final void bind(PreparedStatement st, J value, int index, WrapperOptions options) throws SQLException {
           final boolean traceEnabled = log.isTraceEnabled();
           if ( value == null ) {
                  if ( traceEnabled ) {
                      log.trace(
      			String.format(
      					NULL_BIND_MSG_TEMPLATE,
      					index,
      					JdbcTypeNameMapper.getTypeName( getSqlDescriptor().getSqlType() )
      			)
      		);
              }
              st.setNull( index, sqlDescriptor.getSqlType() );
           }
           else {
              if ( traceEnabled ) {
                  log.trace(
      			String.format(
      					BIND_MSG_TEMPLATE,
      					index,
      					JdbcTypeNameMapper.getTypeName( sqlDescriptor.getSqlType() ),
      					getJavaDescriptor().extractLoggableRepresentation( value )
      			)
      		);
               }
               doBind( st, value, index, options );
           }
      }
      

        Attachments

          Issue links

            Activity

              People

              • Votes:
                3 Vote for this issue
                Watchers:
                9 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 - 4h 13m
                  4h 13m