@Procedure returning also ResultSet no longer returns Map for OUT params

[Parawata]

Hi, a change was made in 2.6.0 in JpaQueryExecution.ProcedureExecution.doExecute that has introduced a condition on the doExecute that in the event of true extracts the query results via Hibernate instead of through StoredProcedureJpaQuery.extractOutputValue and returns a different result.

This change means it is no longer possible to execute stored procedures that contain cursors as well as other OUT params in Spring JPA.

i.e.
Release 2.5.7

@Override
protected Object doExecute(AbstractJpaQuery jpaQuery, JpaParametersParameterAccessor accessor)     
{
    Assert.isInstanceOf(StoredProcedureJpaQuery.class, jpaQuery);
    StoredProcedureJpaQuery storedProcedureJpaQuery = (StoredProcedureJpaQuery) jpaQuery;
    StoredProcedureQuery storedProcedure = storedProcedureJpaQuery.createQuery(accessor);
    storedProcedure.execute();

    return storedProcedureJpaQuery.extractOutputValue(storedProcedure);
}

The object returned is a Map<String,Object>. This can contain multiple OUT params including REF_CURSOR's.
i.e.

result = {HashMap@19130}  size = 5
   "out_param1" -> out_value1
   "out_param2" -> out_value2
   "out_param3" -> out_value3
   "cursor_list" -> {ForwardOnlyResultSet@19217}
   key = "cursor_list"
   value = {ForwardOnlyResultSet@19217}
   "out_param4" -> out_value4

Release 2.6.0

@Override
protected Object doExecute(AbstractJpaQuery jpaQuery, JpaParametersParameterAccessor accessor) {

    Assert.isInstanceOf(StoredProcedureJpaQuery.class, jpaQuery);

    StoredProcedureJpaQuery storedProcedureJpaQuery = (StoredProcedureJpaQuery) jpaQuery;
    StoredProcedureQuery storedProcedure = storedProcedureJpaQuery.createQuery(accessor);

    boolean returnsResultSet = storedProcedure.execute();

    if (returnsResultSet) { //evaluates to true for ref_cursor

        if (!SurroundingTransactionDetectorMethodInterceptor.INSTANCE.isSurroundingTransactionActive()) {
            throw new InvalidDataAccessApiUsageException(NO_SURROUNDING_TRANSACTION);
        }

        if (storedProcedureJpaQuery.getQueryMethod().isCollectionQuery()) {
            return storedProcedure.getResultList();
        } else {
            return storedProcedure.getSingleResult();
        }
    }

    return storedProcedureJpaQuery.extractOutputValue(storedProcedure);
}

In this case for the same query as that run in 2.5.7 the object returned is List<Object[]>. This list will ONLY contain the REF_CURSOR results, for the return structure used it is not possible to include the other OUT params and later identify them. The same would probably apply if more than one REF_CURSOR is in the procedure although I haven't tested this yet.

i.e. Returned object is the forward resultset evaluated where each cursor row is contained in an Object[]

results = {ArrayList@19242}  size = 100
    0 = {Object[10]@19247}
        0 = {BigDecimal@19347} value1
        1 = "value2"
        2 = null
        3 = "value4"
        ...
        9 = "value10"
    1 = {Object[10]@19248}
    2 = {Object[10]@19249}
    3 = {Object[10]@19250}
    4 = {Object[10]@19251}
    5 = {Object[10]@19252} 

I performed one test where I changed the boolean to false to ensure my query results were extracted through storedProcedureJpaQuery.extractOutputValue(storedProcedure) and found the Map returned contained all results in the same format as previous with 2.5.7.

Was this change in behaviour intentional? I can't find any reference to it in the release notes so not sure why it was done. At the moment on 2.6.0 it doesn't seem to be possible to use Spring Data JPA for procedures with cursors and other OUT params.

Is there a way to change behaviour to use storedProcedureJpaQuery.extractOutputValue(storedProcedure) instead? Our application contains over 500 tables and approx 1000 procedures all of which use a standard pattern where each contains a default of 3 out params containing additional data. It's not possible for us to break this up so support for cursors and out params in the same procedures is essential.
A secondary concern is the cursor rows coming back with no identifying metadata. This removes the ability to use custom convertors to map the forward resultsets to objects as well as introducing vunerabilities when the array position alone is the only means to map column values to object attributes.

More details on this can be found at https://stackoverflow.com/questions/70173380/spring-boot-data-jpa-2-6-0-behavioural-change-on-procedures-with-ref-cursor

Thanks

[Parawata]

Has anyone had time to look at this yet? Or if this is not considered an issue could you give an example of how it supports multiple out params that include cursors?

Thanks

[schauder]

I guess this might be caused by 554bd3d. @gregturn could you take a look?