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

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 );
     }
}

Linked issues

required for

HHH-10697

Backport HHH-9548 to globally enable passing null as stored procedure parameter to Hibernate 5.0

Web links

http://stackoverflow.com/questions/22045641/is-it-possible-to-pass-a-null-parameter-to-a-stored-procedure-in-java-jpa-2-1

Activity

Show:

Steve Ebersole February 9, 2016 at 10:07 PM

As far as the JPA contracts it was decided to rely on casting rather than these hint hacks. So to control this per-parameter, you'd cast the parameter as obtained from the javax.persistence.StoredProcedureQuery to org.hibernate.jpa.spi.StoredProcedureQueryParameterRegistration:

StoredProcedureQuery query = ...;
( (StoredProcedureQueryParameterRegistration) query.getParameter( "firstArg" ) ).enablePassingNulls( true );
...

Steve Ebersole January 25, 2016 at 5:44 PM

To be clear what I am talking about is properly handling the following 2 situations:

StoredProcedureQuery spq = ...;
spq.setHint( "hibernate.proc.param_null_passing.firstArg", "true" );
...
spq.registerStoredProcedureParameter( "firstArg", ... );

and

StoredProcedureQuery spq = ...;
spq.registerStoredProcedureParameter( "firstArg", ... );
...
spq.setHint( "hibernate.proc.param_null_passing.firstArg", "true" );

The first case illustrates that every time a parameter is registered, we'd have to iterate over hints to see if there is a hint for it. The second case illustrates the reverse: every time a hint is added if it starts with hibernate.proc.param_null_passing we'd have to see if there is already a parameter registered for it.

Partially this is a result of the decision to model this boolean on the parameter registration object itself. But that is, imo, the most natural modeling for that.

Steve Ebersole January 19, 2016 at 9:49 PM

I have pushed my work so far, the question of "direct support through javax.persistence.StoredProcedureQuery" is still outstanding. I'll leave this open in the meantime to gather feedback if anyone has any, otherwise I'll just release as-is for 5.1

Steve Ebersole January 19, 2016 at 9:46 PM

How to support this through javax.persistence.StoredProcedureQuery directly is still an open item. I have implemented it on the JPA side just for NamedStoredProcedureQuery via hints. But for JPA StoredProcedureQuery there is a bit of chicken/egg wrt mixed calls to #setHint and #registerStoredProcedureParameter. Meaning to properly support setting this through hints, we'd have to look through hints whenever a parameter is registered and we'd have to look at registered parameter whenever a hint is set. Not a fan of that.

Steve Ebersole January 8, 2016 at 3:44 PM

Currently the native query interfaces do not expose any componentized form of parameter information (e.g., query.getParameter("foo")). JPA does. We know that ultimately we want to consolidate (at least conceptually) native and JPA. To that end maybe it makes sense to introduce a "parameter binding" construct in native like we have in our JPA impl. The reason that is important is because then the native contracts could be something like query.getParameter( "foo" ).passNulls( true )

Again, this all assumes we go for per-param.

Steve Ebersole January 6, 2016 at 3:49 PM
Edited

I completely agree that we need the SessionFactory-level option.

I also agree that we should allow it at a more granular level. The question really is "which level? per-query or per-param?". Per-param is the most natural in terms of fit, but it's also the least straightforward in terms of API. Also, we have to consider the JPA and native splits here. For native we can of course introduce our own API methods as we see fit. For JPA this has to be done via hints:

query.setHint( "hibernate.proc.param.nulls.foo", "pass" )

(assuming values like "pass" or "ignore"). Personally I am not a fan of handling options that are comprised of "[stem-name].[some-specific-part]" because it forces us to touch each and every entry in the Map (or conversely forces us to do a Map look up within an iteration over each and every known parameter).

We also have to consider named queries and how to allow specifying this parameter nullness handling in the named definition. And again that is a very different proposition between native and JPA.

Emmanuel Bernard January 6, 2016 at 9:10 AM

I'm intrigued by per param, would it be something like

query.ignoreParam("foo")

or something defined in the query metadata (JPA annotations will make it unintuitive to add such metadata, you will need some ad-hoc annotation on the side)

About the global level, history tends to tell us that in these 50/50 cases, people like the global Session Facotry setting to change the default.

Fixed

Details
Assignee
Steve Ebersole
Reporter
Marcial Atiénzar Navarro
Labels
Time tracking
4.22h logged
Components
query-hql
Fix versions
5.1.0
Priority
Major

Created December 18, 2014 at 11:39 AM

Updated April 19, 2016 at 3:22 PM

Resolved February 9, 2016 at 10:08 PM

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

Description

Original Description

Linked issues

required for

Web links

Activity

Steve Ebersole February 9, 2016 at 10:07 PM

Steve Ebersole January 25, 2016 at 5:44 PM

Steve Ebersole January 19, 2016 at 9:49 PM

Steve Ebersole January 19, 2016 at 9:46 PM

Steve Ebersole January 8, 2016 at 3:44 PM

Steve Ebersole January 6, 2016 at 3:49 PM
Edited

Emmanuel Bernard January 6, 2016 at 9:10 AM

Details
Assignee
Steve Ebersole
Reporter
Marcial Atiénzar Navarro
Labels
Time tracking
4.22h logged
Components
query-hql
Fix versions
5.1.0
Priority
Major

Details

Assignee

Reporter

Labels

Time tracking

Components

Fix versions

Priority

Flag notifications

Something's gone wrong

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

Description

Original Description

Linked issues

required for

Web links

Activity

Steve Ebersole February 9, 2016 at 10:07 PM

Steve Ebersole January 25, 2016 at 5:44 PM

Steve Ebersole January 19, 2016 at 9:49 PM

Steve Ebersole January 19, 2016 at 9:46 PM

Steve Ebersole January 8, 2016 at 3:44 PM

Steve Ebersole January 6, 2016 at 3:49 PMEdited

Emmanuel Bernard January 6, 2016 at 9:10 AM

DetailsAssigneeSteve EbersoleSteve EbersoleReporterMarcial Atiénzar NavarroMarcial Atiénzar NavarroLabelsverify-affects-5.0Time tracking4.22h loggedComponentsquery-hqlFix versions5.1.0PriorityMajor

Details

Assignee

Reporter

Labels

Time tracking

Components

Fix versions

Priority

Flag notifications

Something's gone wrong

Steve Ebersole January 6, 2016 at 3:49 PM
Edited

Details
Assignee
Steve Ebersole
Reporter
Marcial Atiénzar Navarro
Labels
Time tracking
4.22h logged
Components
query-hql
Fix versions
5.1.0
Priority
Major