Why does JDBC return no results from a stored procedure?-CodePudding

I call a stored procedure using JDBC:

        Connection con = DriverManager.getConnection("jdbc:sqlserver://myhost;databaseName=mydb;encrypt=false","user", "pass");
        con.setAutoCommit(false);
        CallableStatement cs = con.prepareCall("{call abc(?,?,?,?,?,?,?)}");
        cs.setDate(1, java.sql.Date.valueOf(LocalDate.of(2023, 1, 10)));
        cs.setDate(2, java.sql.Date.valueOf(LocalDate.of(2023, 1, 9)));
        cs.setString(3, "PROD");
        cs.registerOutParameter(4, Types.NUMERIC);
        cs.registerOutParameter(5, Types.NUMERIC);
        cs.registerOutParameter(6, Types.NUMERIC);
        cs.registerOutParameter(7, Types.NUMERIC);
        cs.executeQuery();

I get a SQLServerException:

2023-01-13T11:22:48.975-06:00 DEBUG 21888 --- [restartedMain] c.m.s.jdbc.internals.SQLServerException : *** SQLException:SQLServerCallableStatement:5 com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set. The statement did not return a result set.

But I get no error calling it with Hibernate:

    StoredProcedureQuery query = em.createStoredProcedureQuery("abc")
            .registerStoredProcedureParameter(
                    "date1",
                    LocalDate.class,
                    ParameterMode.IN
                    )
            .registerStoredProcedureParameter(
                    "date2",
                    LocalDate.class,
                    ParameterMode.IN
                    )
            .registerStoredProcedureParameter(
                    "name",
                    String.class,
                    ParameterMode.IN
                    )
            .registerStoredProcedureParameter(
                    "value1",
                    BigDecimal.class,
                    ParameterMode.OUT
                    )
            .registerStoredProcedureParameter(
                    "value2",
                    BigDecimal.class,
                    ParameterMode.OUT
                    )
            .registerStoredProcedureParameter(
                    "value3",
                    BigDecimal.class,
                    ParameterMode.OUT
                    )
            .registerStoredProcedureParameter(
                    "value4",
                    BigDecimal.class,
                    ParameterMode.OUT
                    )
            .setParameter("date1", LocalDate.of(2023, 1, 10))
            .setParameter("date2", LocalDate.of(2023, 1, 9))
            .setParameter("name", "PROD");
    
    query.execute();
    log.info("value2 is {}", query.getOutputParameterValue("value2"));

What am I doing wrong with my JDBC call?

I looked at the TRACE log from the SQL Server driver. The only difference in bytes going going to SQL Server is ^ and H in the first line:

JDBC

03 01 02 26 00 5E 01 00 16 00 00 00 12 00 00 00   ...&.^..........
02 00 00 00 00 00 00 00 00 00 01 00 00 00 FF FF   ................
0A 00 00 00 00 00 E7 40 1F 09 04 D0 00 34 96 00   [email protected]..
45 00 58 00 45 00 43 00 20 00 74 00 65 00 6D 00   E.X.E.C. .a.b.c.

Hibernate

03 01 02 26 26 00 48 01 00 16 00 00 12 00 00 00   ...&.H..........
02 00 00 00 00 00 00 00 00 00 01 00 00 00 FF FF   ................
0A 00 00 00 00 00 E7 40 1F 09 04 D0 00 34 96 00   [email protected]..
45 00 58 00 45 00 43 00 20 00 74 00 65 00 6D 00   E.X.E.C. .a.b.c.

CodePudding user response：

The Hibernate code calls execute() which expects no result set. However, the JDBC code is calling executeQuery() and that expects a result set. Since the stored procedure doesn't return a result set, the SQL Server exception is thrown.

I changed the JDBC code to execute(), and it works the same as the Hibernate code.