Re: Bug with Multiple Result Sets?



Wes Clark wrote:

I am rying to retrieve the data from the DBCC SHOW_STATISTICS command. I am getting different behavior between 2000 and 2005. I run the following program, and you can see the differences. I am getting three results sets using the 2000 driver against the 2000 database. I am only getting one result set using the 2005 driver against the 2005 database.


Hi Wess. I would like to ask you to alter the processing
of the statement to the code below, which is the ideal to
handle all possible results from any procedure, and let me
know if you get everything you need in both cases:

    boolean aResultSetIsNext = stmt.execute("dbcc show_statistics([Person.Address], PK_Address_AddressID)");
    int updateCount = -1;

    while (true) { // handle all in-line results from any procedure
      if (aResultSetIsNext) {
        ResultSet r = ps.getResultSet();
        System.out.println("We got a valid result set...");
        while (r.next()) {
          // process result set
        }
        r.close();
      } else {
        updateCount = ps.getUpdateCount();
        if (updateCount  != -1) {
          System.out.println("Got a valid update count = " + updateCount);
        }
      }
      if ((!aResultSetIsNext) && (updateCount == -1)) break; // done with loop
      aResultSetIsNext = ps.getMoreResults();
    }
    System.out.println("Done with inline returns");
    // For CallableStatements, get output parameters now...

Joe Weinstein at BEA Systems


/**
 * Created by IntelliJ IDEA.
 * User: wclark
 * Date: Jan 29, 2006
 * Time: 3:39:41 PM
 * To change this template use File | Settings | File Templates.
 */

import java.sql.Connection;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.ResultSet;

/**
 * Class description...
 *
 * @author wclark
 */
public class MultipleResultSets {
  public MultipleResultSets() {
  }

public static void main(String[] args) {
try {
System.out.println("Class being loaded com.microsoft.sqlserver.jdbc.SQLServerDriver");
java.lang.Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String url = "jdbc:sqlserver://ALKOR:1434;selectMethod=direct;databaseName=AdventureWorks;user=sa;password=456";
System.out.println("URL being used: " + url);
Connection conn = java.sql.DriverManager.getConnection(url);
Statement stmt = conn.createStatement();
stmt.execute("dbcc show_statistics([Person.Address], PK_Address_AddressID)");
int updateCount = stmt.getUpdateCount();
System.out.println("Update count is " + updateCount);
ResultSet rs = stmt.getResultSet();
System.out.println("Is first result set null? " + (rs == null ? "Yes, null" : "No, not null"));
boolean moreResults = stmt.getMoreResults();
System.out.println("stmt.getMoreResults() returns " + moreResults);
if (moreResults) {
rs = stmt.getResultSet();
System.out.println("Is second result set null? " + (rs == null ? "Yes, null" : "No, not null"));
moreResults = stmt.getMoreResults();
System.out.println("stmt.getMoreResults() returns " + moreResults);
if (moreResults) {
rs = stmt.getResultSet();
System.out.println("Is third result set null? " + (rs == null ? "Yes, null" : "No, not null"));
moreResults = stmt.getMoreResults();
System.out.println("stmt.getMoreResults() returns " + moreResults);
}
}
conn.close();
} catch (ClassNotFoundException e) {
e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
} catch (SQLException e) {
e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
}
try {
System.out.println("Class being loaded com.microsoft.jdbc.sqlserver.SQLServerDriver");
java.lang.Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
String url = "jdbc:microsoft:sqlserver://ITH:1433;databaseName=pubs;user=sa;password=123;";
System.out.println("URL being used: " + url);
Connection conn = java.sql.DriverManager.getConnection(url);
Statement stmt = conn.createStatement();
stmt.execute("dbcc show_statistics(authors, aunmind)");
int updateCount = stmt.getUpdateCount();
System.out.println("Update count is " + updateCount);
ResultSet rs = stmt.getResultSet();
System.out.println("Is first result set null? " + (rs == null ? "Yes, null" : "No, not null"));
boolean moreResults = stmt.getMoreResults();
System.out.println("stmt.getMoreResults() returns " + moreResults);
if (moreResults) {
rs = stmt.getResultSet();
System.out.println("Is second result set null? " + (rs == null ? "Yes, null" : "No, not null"));
moreResults = stmt.getMoreResults();
System.out.println("stmt.getMoreResults() returns " + moreResults);
if (moreResults) {
rs = stmt.getResultSet();
System.out.println("Is third result set null? " + (rs == null ? "Yes, null" : "No, not null"));
moreResults = stmt.getMoreResults();
System.out.println("stmt.getMoreResults() returns " + moreResults);
}
}
} catch (ClassNotFoundException e) {
e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
} catch (SQLException e) {
e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
}

  }
}

Results:

C:\j2sdk1.4.2_05\bin\java -Didea.launcher.port=7537 "-Didea.launcher.bin.path=C:\Program Files\JetBrains\IntelliJ IDEA 5.0\bin" -Dfile.encoding=windows-1252 -classpath "C:\j2sdk1.4.2_05\jre\lib\charsets.jar;C:\j2sdk1.4.2_05\jre\lib\jce.jar;C:\j2sdk1.4.2_05\jre\lib\jsse.jar;C:\j2sdk1.4.2_05\jre\lib\plugin.jar;C:\j2sdk1.4.2_05\jre\lib\rt.jar;C:\j2sdk1.4.2_05\jre\lib\sunrsasign.jar;C:\j2sdk1.4.2_05\jre\lib\ext\dnsns.jar;C:\j2sdk1.4.2_05\jre\lib\ext\ldapsec.jar;C:\j2sdk1.4.2_05\jre\lib\ext\localedata.jar;C:\j2sdk1.4.2_05\jre\lib\ext\sunjce_provider.jar;C:\j2sdk1.4.2_05\lib\tools.jar;C:\Documents and Settings\wclark\IdeaProjects\WesAdHoc\classes;C:\dev\lib\sqljdbc.jar;C:\dev\lib\msbase.jar;C:\dev\lib\msutil.jar;C:\dev\lib\mssqlserver.jar;C:\Program Files\New Atlanta\JTurbo30\lib\JTurbo.jar;C:\Java\jTDS1.2\jtds-1.2.jar;C:\Program Files\JetBrains\IntelliJ IDEA 5.0\lib\idea_rt.jar" com.intellij.rt.execution.application.AppMain MultipleResultSets
Class being loaded com.microsoft.sqlserver.jdbc.SQLServerDriver
URL being used: jdbc:sqlserver://ALKOR:1434;selectMethod=direct;databaseName=AdventureWorks;user=sa;password=456
Update count is -1
Is first result set null? No, not null
stmt.getMoreResults() returns false
Class being loaded com.microsoft.jdbc.sqlserver.SQLServerDriver
URL being used: jdbc:microsoft:sqlserver://ITH:1433;databaseName=pubs;user=sa;password=123;
Update count is -1
Is first result set null? No, not null
stmt.getMoreResults() returns true
Is second result set null? No, not null
stmt.getMoreResults() returns true
Is third result set null? No, not null
stmt.getMoreResults() returns false

Process finished with exit code 0


.

Relevant Pages

  • Bug with Multiple Result Sets?
    ... boolean moreResults = stmt.getMoreResults; ... //To change body of catch statement use File | ... Settings | File Templates. ...
    (microsoft.public.sqlserver.jdbcdriver)
  • RE: Using Unicode data Through a DataSource
    ... resultsEndOrUpdateCount =!stmt.getMoreResults) ... //To change body of catch statement use File | Settings | File Templates. ...
    (microsoft.public.sqlserver.jdbcdriver)
Loading