Problem: JDBC'S Stored Procedure Empty ResultSet When Creating Tables

Prerequisites:

this is a quick issue/solution blog and not an introduction to JDBC or Java.

The Issue

Whilst working on calling a Stored Procedure from our SQLServer, the stored procedure refused to return any data. When called from a database viewer (we use DBVisualizer) the SP works fine.

The issue is that the SP was creating a temporary tables and inserting/updating and whatnot. Thus a row-count was returned which then was turned off by default on our servers. Hence, no actual ResultSets were returned at all.

Solution

This solution was only tested on SQLServer. Let's imagine the following:

A stored procedure that creates/updates/deletes/inserts rows and tables... etc.

Called: sp_child()

if it's called like this it won't work:

public static void main(String... args) throws ClassNotFoundException, SQLException {  
        Connection conn = null;
        CallableStatement stmt = null;

        try {

            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            conn = DriverManager.getConnection("jdbc:sqlserver://XXXX:XXXX;database=XXXX", "XXX", "XXX");
            stmt = conn.prepareCall("{call sp_child() }");

            boolean results = stmt.execute();

            System.out.println(results);
            ResultSet rs = null;
            if (results)
                rs = stmt.getResultSet();

            while (results) {
                while (rs.next()) {
                    // Get whatever columns you'd like
                }
                results = stmt.getMoreResults();
            }
        } catch (SQLException se) {
            //Handle errors for JDBC
            se.printStackTrace();
        } finally {
            //finally block used to close resources
                if (stmt != null)
                    stmt.close();
                if (conn != null)
                    conn.close();
        }//end try
    }

Sadly the above will return an empty ResultSet and the reason behind it is because it's actually being confused with the ROWCOUNT that usually is produced after creating new tables (Temp or Perm).

I have actually tried to SET NOCOUNT ON of the SQLServer in the same SP before calling it, still wouldn't work. The work around is to create a parent SP, set the no count of rows on before executing the child stored procedure. As follows:

IF OBJECT_ID ( 'sp_parent', 'P' ) IS NOT NULL DROP PROCEDURE sp_parent;  
GO  
CREATE PROCEDURE [DBO].[sp_parent]  
AS  
BEGIN  
        SET NOCOUNT ON
        EXEC('sp_child ')
END  
GO  
finally

if you have any Parameters -- which I assume you do -- simply give your parent SP the same parameter and pass them down-wards to the child.

The solution might not be completely perfect but it works.
EXEC('sp_child @param1=' + @input1)

Happy Coding. And if you have a different solution, please share!

AbdulRehman Faraj

Electronic and Computer Engineer graduate and currently working as Development and Integration Analyst at Baring Asset Management. Love Programming, and game development....

London, United Kingdom http://github.com/AbdulR3hman