Null Values in ProcedureCall

Question

I try the following code:

    ProcedureCall call = ss.createStoredProcedureCall("servicos.CLASSIFIER_MAP");

    call.registerParameter( 1, String.class, ParameterMode.IN).bindValue(classifierString);
    call.registerParameter( 2, Long.class, ParameterMode.IN ).bindValue(totalRows);

Sometimes the variable totalRows is null and an error appears

WARN:   SQL Error: 17041, SQLState: 99999
Error calling CallableStatement.getMoreResults

It is possible to send null values into a ProcedureCall in hibernate?

Answer 1

Currently there are 2 ways to achieve your objective :

1. Set the property param_null_passing to true in your hibernate configuration.
   <property name="hibernate.proc.param_null_passing">true</property>
   This is a global setting which will enable you to pass null values in to ProcedureCall throughout your application.

2. If you do not wish to apply the above setting globally, change your code to the following :

   ProcedureCall call = ss.createStoredProcedureCall("servicos.CLASSIFIER_MAP");
   
   call.registerParameter( 1, String.class, ParameterMode.IN).bindValue(classifierString); 
   call.registerParameter( 2, Long.class, ParameterMode.IN ).enablePassingNulls(true);
   
   call.setParameter(2,  totalRows);
   

From the Hibernate 5 documentation :

hibernate.proc.param_null_passing (default value is false) :

Global setting for whether null parameter bindings should be passed to database procedure/function calls as part of ProcedureCall handling. Implicitly Hibernate will not pass the null, the intention being to allow any default argument values to be applied.

This defines a global setting, which can then be controlled per parameter via org.hibernate.procedure.ParameterRegistration#enablePassingNulls(boolean)

Values are true (pass the NULLs) or false (do not pass the NULLs).