Problems with table variables in SQL Server called from Java

Question

I have a SQL statement which works when executed in MS Server Management Studio and works when submitted from C# but which does not work when submitted from Java (1.6, using sqljdbc4.jar).

The basic problem seems to be selecting into a table variable. Following up on the first comment I've completely re-written this question using simpler examples to show what works and does not work.

The following query:

DECLARE @IsLoadRaw as INT = ? 
DECLARE @PrimaryID as varchar(1000) = ?
--Declare temporary table to hold message IDs and fill according to Primary ID.
DECLARE @MessageIDs TABLE ( MessageID BIGINT )
SELECT MessageID FROM Messages WHERE PrimaryID = @PrimaryID

works both in SQL Management Studio and when submitted from Java. In both cases it returns a result set with two MessageIDs (correct for the given PrimaryID I'm using to test).

The following query:

DECLARE @IsLoadRaw as INT = ? 
DECLARE @PrimaryID as varchar(1000) = ?
--Declare temporary table to hold message IDs and fill according to Primary ID.
DECLARE @MessageIDs TABLE ( MessageID BIGINT );
INSERT @MessageIDs SELECT MessageID FROM Messages WHERE PrimaryID = @PrimaryID;
SELECT * FROM @MessageIDs;

works in SQL Management Studio where it returns a result set with the same two MessageIDs. When submitted from Java it does not return any result set.

The complete statement, which makes us of @MessageIDs, works when submitted from C# via ADO.NET. I assume the second sample here would work as well. The problem is isolated to Java and seems to relate to using a table variable. Since the code appears correct and runs under SQL Management Studio I'm perplexed as to how to debug this.

Any idea why this is not working from Java? What tools can I use to understand what the server is doing with this query WHEN SUBMITTED FROM Java?

Answer 1

I did some more excavation and found the answer:

INSERT @MessageIDs SELECT MessageID FROM Messages WHERE PrimaryID = @PrimaryID;

when submitted from Java returns an update count. When submitted from C# or from SQL Management Console it does not return an update count. I was not expecting this so it took some digging to find.

The java API for stepping through results of an execute() this is confusing, there are not may examples, and at least one that I found was not fully correct. I'll explain how I understand this to work.

Since most statements are simple, one change or one select, there are convenience execute methods on Statement, such as executeQuerry(), which returns a result set. Most cases use these and that is the end of the story.

If you have a more complex statement which does several things you call execute() and get back a list of things. INSERT, UPDATE, and DELETE (I believe) return a count of records modified. SELECT returns a result set. The result of executing a complex statement is a list of update counts and result sets, in the order they were executed. You then write code that steps through this list, processing each item.

The statement

DECLARE @MessageIDs TABLE ( MessageID BIGINT )
INSERT @MessageIDs SELECT MessageID FROM Messages WHERE PrimaryID = @PrimaryID;
SELECT * FROM Messages WHERE MessageID IN (SELECT MessageID FROM @MessageIDs) ORDER BY MessageID;
SELECT * FROM Attrs WHERE MessageID IN (SELECT MessageID FROM @MessageIDs) ORDER BY MessageID;

returns 2 result sets. In java, and only in java for reasons I don't know, the INSERT @MessageIDs... statement returns an update count, which is the first item in the list.

The java API for this is confusing. Statement.execute() and Statement.getMoreResults() return:

• true if the next result is a ResultSet
• false if the next result is an update count OR there are no more results

false has two meanings and can not be interpreted to be the end of results. You have to also check for a non-zero update count.

The final, functioning code ended up looking like this:

List<DtaMessage> msgList = new ArrayList<DtaMessage>();
boolean isResult = stmt.execute();

// Skip over update counts.  
while (!isResult) {
    if (stmt.getUpdateCount() == 0)
        // End of results.
        return msgList;
    isResult = stmt.getMoreResults();
}


// Process first result set.
ResultSet rs = stmt.getResultSet();
while (rs.next())
{
    DtaMessage msg = PopulateMessage(rs, isLoadRaw);
    msgList.add(msg);
}
rs.close();

// Skip over update counts.
isResult = stmt.getMoreResults();
while (!isResult) {
    if (stmt.getUpdateCount() == 0)
        // end of results.
        return msgList;
    isResult = stmt.getMoreResults();
}

// Process second result set.
rs = stmt.getResultSet();
while (rs.next())
{
    // process.
}
rs.close();
return msgList;

Though my sample SQL does nothing that would generate an update count between the two result sets this method will process results from several different SQL statements so I added the code to skip over up date counts which may show up in some cases.