Re: Bug with Multiple Result Sets?

Just a quick note, please do not post connection string information in the 
newsgroups. I will let Evan handle this issue, it looks interesting.

Thank you,
-- 
Angel Saenz-Badillos [MS] DataWorks
This posting is provided "AS IS", with no warranties, and confers no 
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging: http://weblogs.asp.net/angelsb/




"Wes Clark" <WesClark@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message 
news:461F7166-63ED-4C52-85CE-C08A52F2E576@xxxxxxxxxxxxxxxx
> New program and results below.  The new driver is getting "results"
> interleaved with result sets.  The doc for "getMoreResults" is " Moves to
> this Statement
>
> import java.sql.*;
>
> public class MultipleResultSets {
>  public MultipleResultSets() {
>  }
>
>  public static void main(String[] args) {
>    try {
>      System.out.println("Trying the 2005 driver against the 2005 
> database");
>      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);
>      String sql = "dbcc show_statistics([Person.Address],
> PK_Address_AddressID)";
>      System.out.println("== First way ==");
>      processMultipleResultSetsFirstWay(conn, sql);
>      System.out.println("== Second way ==");
>      processMultipleResultSetsSecondWay(conn, sql);
>      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("Trying the 2005 driver against the 2000 
> database");
>      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:1433;selectMethod=direct;databaseName=Pubs;user=sa;password=123";
>      System.out.println("URL being used: " + url);
>      Connection conn = java.sql.DriverManager.getConnection(url);
>      String sql = "dbcc show_statistics(authors, aunmind)";
>      Statement stmt = conn.createStatement();
>      System.out.println("== First way ==");
>      processMultipleResultSetsFirstWay(conn, sql);
>      System.out.println("== Second way ==");
>      processMultipleResultSetsSecondWay(conn, sql);
>      conn.close();
>    } catch (ClassNotFoundException e) {
>      e.printStackTrace();
>    } catch (SQLException e) {
>      e.printStackTrace();
>    }
>    try {
>       System.out.println("Trying the 2000 driver against the 2000 
> database");
>       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://ALKOR:1433;databaseName=pubs;user=sa;password=123;";
>       System.out.println("URL being used: " + url);
>       Connection conn = java.sql.DriverManager.getConnection(url);
>       String sql = "dbcc show_statistics(authors, aunmind)";
>       System.out.println("== First way ==");
>       processMultipleResultSetsFirstWay(conn, sql);
>       System.out.println("== Second way ==");
>       processMultipleResultSetsSecondWay(conn, sql);
>       conn.close();
>     } catch (ClassNotFoundException e) {
>       e.printStackTrace();
>     } catch (SQLException e) {
>       e.printStackTrace();
>     }
>    try {
>       System.out.println("Trying the 2000 driver against the 2005 
> database");
>       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://ALKOR:1434;databaseName=AdventureWorks;user=sa;password=456;";
>       System.out.println("URL being used: " + url);
>       Connection conn = java.sql.DriverManager.getConnection(url);
>       String sql = "dbcc show_statistics([Person.Address],
> PK_Address_AddressID)";
>       System.out.println("== First way ==");
>       processMultipleResultSetsFirstWay(conn, sql);
>       System.out.println("== Second way ==");
>       processMultipleResultSetsSecondWay(conn, sql);
>       conn.close();
>     } catch (ClassNotFoundException e) {
>       e.printStackTrace();
>     } catch (SQLException e) {
>       e.printStackTrace();
>     }
>
>  }
>
>  public static void processMultipleResultSetsFirstWay(Connection conn,
> String sql) {
>    try {
>      Statement stmt = conn.createStatement();
>      stmt.execute(sql);
>      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 (SQLException e) {
>      e.printStackTrace();
>    }
>  }
>
>  public static void processMultipleResultSetsSecondWay(Connection conn,
> String sql) {
>    try {
>      Statement stmt = conn.createStatement();
>      boolean aResultSetIsNext = stmt.execute(sql);
>      int updateCount = -1;
>      while (true) { // handle all in-line results from any procedure
>        if (aResultSetIsNext) {
>          ResultSet r = stmt.getResultSet();
>          System.out.println("We got a valid result set...");
>          while (r.next()) {
>            // process result set
>          }
>          r.close();
>        } else {
>          updateCount = stmt.getUpdateCount();
>          if (updateCount != -1) {
>            System.out.println("Got a valid update count = " + 
> updateCount);
>          }
>        }
>        if ((!aResultSetIsNext) && (updateCount == -1)) break; // done with
> loop
>        aResultSetIsNext = stmt.getMoreResults();
>      }
>      System.out.println("Done with inline returns");
>      // For CallableStatements, get output parameters now...
>      stmt.close();
>    } catch (SQLException e) {
>      e.printStackTrace();  //To change body of catch statement use File |
> Settings | File Templates.
>    }
>  }
> }
>
> Results:
> Trying the 2005 driver against the 2005 database
> Class being loaded com.microsoft.sqlserver.jdbc.SQLServerDriver
> URL being used:
> jdbc:sqlserver://ALKOR:1434;selectMethod=direct;databaseName=AdventureWorks;user=sa;password=456
> == First way ==
> Update count is -1
> Is first result set null? No, not null
> stmt.getMoreResults() returns false
> == Second way ==
> We got a valid result set...
> Got a valid update count = 1
> We got a valid result set...
> Got a valid update count = 1
> We got a valid result set...
> Got a valid update count = 5
> Got a valid update count = 0
> Done with inline returns
> Trying the 2005 driver against the 2000 database
> Class being loaded com.microsoft.sqlserver.jdbc.SQLServerDriver
> URL being used:
> jdbc:sqlserver://ALKOR:1433;selectMethod=direct;databaseName=Pubs;user=sa;password=123
> == First way ==
> Update count is -1
> Is first result set null? No, not null
> stmt.getMoreResults() returns false
> == Second way ==
> We got a valid result set...
> Got a valid update count = 1
> We got a valid result set...
> Got a valid update count = 3
> We got a valid result set...
> Got a valid update count = 22
> Got a valid update count = 0
> Done with inline returns
> Trying the 2000 driver against the 2000 database
> Class being loaded com.microsoft.jdbc.sqlserver.SQLServerDriver
> URL being used:
> jdbc:microsoft:sqlserver://ALKOR:1433;databaseName=pubs;user=sa;password=123;
> == First way ==
> 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
> == Second way ==
> We got a valid result set...
> We got a valid result set...
> We got a valid result set...
> Done with inline returns
> Trying the 2000 driver against the 2005 database
> Class being loaded com.microsoft.jdbc.sqlserver.SQLServerDriver
> URL being used:
> jdbc:microsoft:sqlserver://ALKOR:1434;databaseName=AdventureWorks;user=sa;password=456;
> == First way ==
> 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
> == Second way ==
> We got a valid result set...
> We got a valid result set...
> We got a valid result set...
> Done with inline returns 


.

Relevant Pages

  • Re: Syntax
    ... tablename above except that it is an mdb rather than a table in SQL server. ... Thus my connection table is now the mdb file name minus the ".mdb" extension ... the Access mdb connection string should be the variable name as ...
    (microsoft.public.sqlserver.dts)
  • Re: Unable to Locate Server
    ... I am using the copy of SQL Express that came with Visual Studio. ... > Boolean& failoverDemandDone, String host, String failoverPartner, String ... > owningObject, SqlConnectionString connectionOptions, String newPassword, ... >> trying to make a remote database connection while you think your database is ...
    (microsoft.public.sqlserver.setup)
  • Re: Setting a combo Rowsource from SQL server
    ... Since you set the RowSource to the SQL String, ... the Connection you established is not used. ...
    (microsoft.public.access.forms)
  • Re: Im wondering about have two connections, one inside the other.
    ... consider using parameterized SQL instead of adding values to the SQL statement with string concatenation. ... Dim sds As New SqlDataSource(cts.ConnectionString, "SELECT * FROM Photos, ... The same code (new connection string) works OK with Sql 2008 Server. ...
    (microsoft.public.sqlserver.programming)
  • Re: Why wont this simple SQL statement INSERT a value?
    ... use the parameter in your SQL statements preceeded by a ?. ... problem was to do with the VS2005 built connection string DataSource=| ... Dim num, value As String ...
    (microsoft.public.dotnet.languages.vb)
Loading