Search code examples
javasqlsql-serverjdbccursor

Using a database API cursor with JDBC and SQLServer to select batch results

SOLVED (See answer below.)

I did not understand my problem within the proper context. The real issue was that my query was returning multiple ResultSet objects, and I had never come across that before. I have posted code below that solves the problem.

PROBLEM

I have an SQL Server database table with many thousand rows. My goal is to pull the data back from the source database and write it to a second database. Because of application memory constraints, I will not be able to pull the data back all at once. Also, because of this particular table's schema (over which I have no control) there is no good way for me to tick off the rows using some sort of ID column.

A gentleman over at the Database Administrators StackExchange helped me out by putting together something called a database API cursor, and basically wrote this complicated query that I only need to drop my statement into. When I run the query in SQL Management Studio (SSMS) it works great. I get all the data back, a thousand rows at a time.

Unfortunately, when I try to translate this into JDBC code, I get back the first thousand rows only.

QUESTION

Is it possible using JDBC to retrieve a database API cursor, pull the first set of rows from it, allow the cursor to advance, and then pull the subsequent sets one at a time? (In this case, a thousand rows at a time.)

SQL CODE

This gets complicated, so I'm going to break it up.

The actual query can be simple or complicated. It doesn't matter. I've tried several different queries during my experimentation and they all work. You just basically drop it into the the SQL code in the appropriate place. So, let's take this simple statement as our query:

SELECT MyColumn FROM MyTable;

The actual SQL database API cursor is far more complicated. I will print it out below. You can see the above query buried in it:

-- http://dba.stackexchange.com/a/82806
DECLARE @cur INTEGER
    ,
    -- FAST_FORWARD | AUTO_FETCH | AUTO_CLOSE
    @scrollopt INTEGER = 16 | 8192 | 16384
    ,
    -- READ_ONLY, CHECK_ACCEPTED_OPTS, READ_ONLY_ACCEPTABLE
    @ccopt INTEGER = 1 | 32768 | 65536
    ,@rowcount INTEGER = 1000
    ,@rc INTEGER;

-- Open the cursor and return the first 1,000 rows
EXECUTE @rc = sys.sp_cursoropen @cur OUTPUT
    ,'SELECT MyColumn FROM MyTable'
    ,@scrollopt OUTPUT
    ,@ccopt OUTPUT
    ,@rowcount OUTPUT;

IF @rc <> 16 -- FastForward cursor automatically closed
BEGIN
    -- Name the cursor so we can use CURSOR_STATUS
    EXECUTE sys.sp_cursoroption @cur
        ,2
        ,'MyCursorName';

    -- Until the cursor auto-closes
    WHILE CURSOR_STATUS('global', 'MyCursorName') = 1
    BEGIN
        EXECUTE sys.sp_cursorfetch @cur
            ,2
            ,0
            ,1000;
    END;
END;

As I've said, the above creates a cursor in the database and asks the database to execute the statement, keep track (internally) of the data it's returning, and return the data a thousand rows at a time. It works great.

JDBC CODE

Here's where I'm having the problem. I have no compilation problems or run-time problems with my Java code. The problem I am having is that it returns only the first thousand rows. I don't understand how to utilize the database cursor properly. I have tried variations on the Java basics:

// Hoping to get all of the data, but I only get the first thousand.
ResultSet rs = stmt.executeQuery(fq.getQuery());
while (rs.next()) {
    System.out.println(rs.getString("MyColumn"));
}

I'm not surprised by the results, but all of the variations I've tried produce the same results.

From my research it seems like the JDBC does something with database cursors when the database is Oracle, but you have to set the data type returned in the result set as an Oracle cursor object. I'm guessing there is something similar with SQL Server, but I have been unable to find anything yet.

Does anyone know of a way?

I'm including example Java code in full (as ugly as that gets).

// FancyQuery.java

import java.sql.*;

public class FancyQuery {

    // Adapted from http://dba.stackexchange.com/a/82806
    String query = "DECLARE @cur INTEGER\n"
                 + "    ,\n"
                 + "    -- FAST_FORWARD | AUTO_FETCH | AUTO_CLOSE\n"
                 + "    @scrollopt INTEGER = 16 | 8192 | 16384\n"
                 + "    ,\n"
                 + "    -- READ_ONLY, CHECK_ACCEPTED_OPTS, READ_ONLY_ACCEPTABLE\n"
                 + "    @ccopt INTEGER = 1 | 32768 | 65536\n"
                 + "    ,@rowcount INTEGER = 1000\n"
                 + "    ,@rc INTEGER;\n"
                 + "\n"
                 + "-- Open the cursor and return the first 1,000 rows\n"
                 + "EXECUTE @rc = sys.sp_cursoropen @cur OUTPUT\n"
                 + "    ,'SELECT MyColumn FROM MyTable;'\n"
                 + "    ,@scrollopt OUTPUT\n"
                 + "    ,@ccopt OUTPUT\n"
                 + "    ,@rowcount OUTPUT;\n"
                 + "    \n"
                 + "IF @rc <> 16 -- FastForward cursor automatically closed\n"
                 + "BEGIN\n"
                 + "    -- Name the cursor so we can use CURSOR_STATUS\n"
                 + "    EXECUTE sys.sp_cursoroption @cur\n"
                 + "        ,2\n"
                 + "        ,'MyCursorName';\n"
                 + "\n"
                 + "    -- Until the cursor auto-closes\n"
                 + "    WHILE CURSOR_STATUS('global', 'MyCursorName') = 1\n"
                 + "    BEGIN\n"
                 + "        EXECUTE sys.sp_cursorfetch @cur\n"
                 + "            ,2\n"
                 + "            ,0\n"
                 + "            ,1000;\n"
                 + "    END;\n"
                 + "END;\n";

    public String getQuery() {
        return this.query;
    }

    public static void main(String[ ] args) throws Exception {

        String dbUrl = "jdbc:sqlserver://tc-sqlserver:1433;database=MyBigDatabase";
        String user = "mario";
        String password = "p@ssw0rd";
        String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";

        FancyQuery fq = new FancyQuery();

        Class.forName(driver);

        Connection conn = DriverManager.getConnection(dbUrl, user, password);
        Statement stmt = conn.createStatement();

        // We expect to get 1,000 rows at a time.
        ResultSet rs = stmt.executeQuery(fq.getQuery());
        while (rs.next()) {
            System.out.println(rs.getString("MyColumn"));
        }

        // Alas, we've only gotten 1,000 rows, total.

        rs.close();
        stmt.close();
        conn.close();
    }
}
Solution

  • I figured it out.

    stmt.execute(fq.getQuery());

ResultSet rs = null;

for (;;) {
    rs = stmt.getResultSet();
    while (rs.next()) {
        System.out.println(rs.getString("MyColumn"));
    }
    if ((stmt.getMoreResults() == false) && (stmt.getUpdateCount() == -1)) {
        break;
    }
}

if (rs != null) {
    rs.close();
}

    After some additional googling, I found a bit of code posted back in 2004:

    http://www.coderanch.com/t/300865/JDBC/databases/SQL-Server-JDBC-Registering-cursor

    The gentleman who posted the snippet that I found helpful (Julian Kennedy) suggested: "Read the Javadoc for getUpdateCount() and getMoreResults() for a clear understanding." I was able to piece it together from that.

    Basically, I don't think I understood my problem well enough at the outset in order to phrase it correctly. What it comes down to is that my query will be returning the data in multiple ResultSet instances. What I needed was a way to not merely iterate through each row in a ResultSet but, rather, iterate through the entire set of ResultSets. That's what the code above does.