Zero results when calling a Sybase stored procedure via JDBC

Question

I call the Sybase stored procedure, which returns multiple result sets via JDBC. I need to get a specific result set that has a column called "Result". This is my code:

CallableStatement cs = conn.prepareCall(sqlCall); cs.registerOutParameter(1, Types.VARCHAR); cs.execute(); ResultSet rs=null; int count = 1; boolean flag = true; while (count < 20000 && flag == true) { cs.getMoreResults(); rs = cs.getResultSet(); if (rs != null) { ResultSetMetaData resultSetMetaData = rs.getMetaData(); int columnsCount = resultSetMetaData.getColumnCount(); if (resultSetMetaData.getColumnName(1).equals("Result")) { // action code resultset found flag = false; // loop on the resultset and add the elements returned to an array list while (rs.next()) { int x = 1; while (x <= columnsCount) { result.add(rs.getString(x)); x++; } } result.add(0, cs.getString(1)); } } count++; } 

What happens is that cs.getMoreResults returns a lot of null result sets until it reaches the target. I cannot use cs.getMoreResults as a loop condition because it returns false for null result sets.

I set a fixed number to end the loop under the condition that the desired result set was not returned so that it would not go into an infinite loop. It worked fine, but I don’t think it is right.

I think null result objects returned from destination in Sybase select @variable = value

Has anyone come across this before?

Answer 1

You are misinterpreting the return value of getMoreResults() . You also ignore the return value of execute() , this method returns a boolean indicating the type of the first result:

• true : result: ResultSet
• false : result - the number of updates

If the result is true , you use getResultSet() to retrieve the ResultSet , otherwise getUpdateCount() to get the number of updates. If the number of updates is -1 , then there are no more results. Note that the number of updates will also be -1 if the current result is ResultSet . It is also useful to know that getResultSet() should return null if there are no more results or the result is equal to the number of updates (this last condition is why you get so many null values).

Now, if you want more results, you call getMoreResults() (or his brother accepts an int parameter). The return value of boolean has the same value as the value of execute() , so false does not mean that there are no more results!

Results if getMoreResults() returns false and getUpdateCount() returns -1 (as also described in Javadoc)

Essentially this means that if you want to process all the results correctly, you need to do something like below:

 boolean result = stmt.execute(...); while(true) { if (result) { ResultSet rs = stmt.getResultSet(); // Do something with resultset ... } else { int updateCount = stmt.getUpdateCount(); if (updateCount == -1) { // no more results break; } // Do something with update count ... } result = stmt.getMoreResults(); } 

I assume you get a lot of updates before you get the actual ResultSet .

I'm not very good at Sybase, but his cousin SQL Server has an "annoying" function to return update counts from stored procedures unless you explicitly put SET NOCOUNT ON; at the beginning of the stored procedure.

NOTE. Part of this answer is based on my answer to Running "sp_msforeachdb" in a Java Application