none
Calling a procedure with JDBC does not return ResultSet RRS feed

  • Question

  • Hi! Calling a procedure with SQL Server Management Studio shows query result and returns value 0:

    However, calling the same procedure with JDBC returns also 0 but no query result. Why?

    Java implementation:

    System.out.println("java version: " + 
            System.getProperty("java.vendor") + " " +
            System.getProperty("java.version"));
    Class.forName(SQLServerDriver.class.getName());
    Connection connection = DriverManager.getConnection(
            "jdbc:sqlserver://localhost:1433;databaseName=MyDB",
            "MyUser", "MyPassword");        
    System.out.println("catalog: " + connection.getCatalog());
    System.out.println("schema: " + connection.getSchema());
    System.out.println("driver version: " + connection.getMetaData().getDriverVersion());
    // Call MyProc
    CallableStatement statement = connection.prepareCall("{ ? = call SP_WEB_Save_Asiakas(?, ?, ?, ?, ?, ?, ?)}");        
    statement.registerOutParameter(1, Types.INTEGER);
    statement.setString(2, "john.doe@example.com");
    statement.setString(3, "John Doe");
    statement.setString(4, "Test Street 1");
    statement.setString(5, "TEST CITY");
    statement.setString(6, "12340");
    statement.setString(7, "0123456789");
    statement.setString(8, "010170-001J");
    if (statement.execute()) {
        ResultSet result = statement.getResultSet();
        while(result.next()) {
            System.out.println("result: " + result.getString(1));
        }
    } else {
        System.out.println("no result");
    }
    System.out.println("return value: " + statement.getInt(1));
    statement.close();
    connection.close()

     
    Output:

    java version: Oracle Corporation 1.8.0_101
    catalog: MyDB
    schema: dbo
    driver version: 6.0.7507.100
    no result
    return value: 0





    • Edited by Haba7 Tuesday, September 6, 2016 8:54 PM
    Tuesday, September 6, 2016 8:50 PM

Answers

  • I don't know much about JDBC, but a common solution to this problem with ADO and several other APIs (but not needed with ADO .NET) is to add this line to the procedure:

    SET NOCOUNT ON

    Tuesday, September 6, 2016 9:09 PM
  • So the correct action is to rewrite the program to handle multiple result sets.
    I got it working now without "SET NOCOUNT ON" by calling Statement.getMoreResults().
    ResultSet = null;
    if (statement.execute()) {
        resultSet = statement.getResultSet();
    } else {
        while(statement.getUpdateCount() >= 0) {
            if (statement.getMoreResults(Statement.CLOSE_CURRENT_RESULT)) {
                resultSet = statement.getResultSet();
    break; } } }
    Thanks for helping!
    • Marked as answer by Haba7 Thursday, September 8, 2016 6:09 AM
    Thursday, September 8, 2016 6:08 AM

All replies

  • I don't know much about JDBC, but a common solution to this problem with ADO and several other APIs (but not needed with ADO .NET) is to add this line to the procedure:

    SET NOCOUNT ON

    Tuesday, September 6, 2016 9:09 PM
  • "SET NOCOUNT ON" seems to fix this issue. Thanks for helping!
    Wednesday, September 7, 2016 5:47 AM
  • Hi Haba7,

    Meanwhile, you could take a look at @@ROWCOUNT (Transact-SQL) to get a better understanding.


    Sam Zha
    TechNet Community Support

    Wednesday, September 7, 2016 5:57 AM
    Moderator
  • Meanwhile, you could take a look at @@ROWCOUNT (Transact-SQL) <https://msdn.microsoft.com/en-us/library/ms187316.aspx> to get a better understanding.

    In all honesty, Sam, I think that only serves to confuse.

    SET NOCOUNT ON is about this: by default when SQL Server perfoms an INSERT, UPDATE, DELETE or SELECT operation, it sends a message to SQL Server with the number of rows affected, and you see this message in the Message tab in SSMS. Poorly written client code gets confused by this, becaues they expect exactly one result set, and then this count comes and is taken as a result set.

    So the correct action is to rewrite the program to handle multiple result sets. However, this this count generates extra network traffic, it is good idea to turn it off anyway. And I can't say how the Java code should be fixed due to my lack of knowledge about Java and JDBC.

    The @@rowcount function is a function you can call inside T-SQL to see how many rows that were affected by the most recent statement. This function works no matter the setting of SET NOCOUNT ON.

    Wednesday, September 7, 2016 9:07 PM
  • So the correct action is to rewrite the program to handle multiple result sets.
    I got it working now without "SET NOCOUNT ON" by calling Statement.getMoreResults().
    ResultSet = null;
    if (statement.execute()) {
        resultSet = statement.getResultSet();
    } else {
        while(statement.getUpdateCount() >= 0) {
            if (statement.getMoreResults(Statement.CLOSE_CURRENT_RESULT)) {
                resultSet = statement.getResultSet();
    break; } } }
    Thanks for helping!
    • Marked as answer by Haba7 Thursday, September 8, 2016 6:09 AM
    Thursday, September 8, 2016 6:08 AM
  • Brilliant!

    Thursday, September 8, 2016 9:08 PM