[3.0.3] StoredProcedureCall exception using mssql jdbc driver

[alexr-ligolab]

eclipselink 3.0.3 mssql-jdbc:11.2.1.jre17 or mssql-jdbc:8.4.1.jre8

CODE:

StoredProcedureCall spc = new StoredProcedureCall();
spc.setProcedureName("LabOrderReport_Update");
spc.addNamedArgumentValue("LAB_ORDER_REPORT_ID", 504258);
spc.addNamedArgumentValue("REQUEST_RELEASE", 0);
uow.executeNonSelectingCall(spc);

EXCEPTION: Exception [EclipseLink-4002] (Eclipse Persistence Services - 3.0.3.v202208191135): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: com.microsoft.sqlserver.jdbc.SQLServerException: The method executeUpdate() cannot take arguments on a PreparedStatement or CallableStatement.

EXPLANATION: I tracked down this bug to changes in StoredProcedureCall:prepareStatement:857. The introduction of the override forces StoredProcedureCall to return instances of SQLServerCallableStatement instead of SQLServerStatement. This in itself is probably not wrong but when actually executing the statement in DatabaseAccessor:executeDirectNoSelect:895 we have the following logic

if ((call != null) && call.isDynamicCall(session)) {
    rowCount = statement.executeUpdate(call.getSQLString());
} else {
    rowCount = ((PreparedStatement)statement).executeUpdate();
}

StoredProcedureCall returns true for the isDynamicCall() check and executes statement.executeUpdate(call.getSQLString()) which produces the exception since the statement now is an instance of PreparedStatement instead of Statement. As far as I can tell the right solution would be to make StoredProcedureCall always return false for the isDynamicCall() check

[rfelcman]

Sorry but what about for:

MS-SQL Procedure

CREATE PROCEDURE bug_test_1730
    @Parameter1 int,
    @Parameter2 int OUTPUT
AS
    SET @Parameter2 = @Parameter1 * 10;
GO

Java code:

import org.eclipse.persistence.jpa.JpaEntityManager;
import org.eclipse.persistence.queries.DataReadQuery;
import org.eclipse.persistence.queries.StoredProcedureCall;
import org.eclipse.persistence.sessions.DatabaseRecord;
import org.eclipse.persistence.sessions.Session;
....
    @Test
    public void testStoredProcedureCall() {
        EntityManagerFactory entityManagerFactory = Persistence.createEntityManagerFactory("test-jpa-pu");
        EntityManager em = entityManagerFactory.createEntityManager();
        Session session = ((JpaEntityManager)em).getDatabaseSession();
        try {
            StoredProcedureCall spc = new StoredProcedureCall();
            spc.setProcedureName("bug_test_1730");
            spc.addNamedArgumentValue("Parameter1", 11);
            spc.addNamedOutputArgument("Parameter2", "Parameter2", Integer.class);

            DataReadQuery query = new DataReadQuery();
            query.setShouldBindAllParameters(true);
            query.setCall(spc);
            DatabaseRecord row = (DatabaseRecord)((Vector)session.executeQuery(query)).firstElement();
            System.out.println(row);
            assertEquals(110, row.get("Parameter2"));
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException();
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            em.close();
        }
    }