|
Home > Archive > SQL Server JDBC > March 2006 > Bug with Multiple Result Sets?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Bug with Multiple Result Sets?
|
|
| Wes Clark 2006-01-29, 8:23 pm |
| 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.
/**
* 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;selectMet
hod=direct;databaseN
ame=AdventureWorks;u
ser=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 (ClassNotFoundExcept
ion 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:sqlse
rver:// ITH:1433;databaseNam
e=pubs;user=sa;passw
ord=123;";
System.out.println("URL being used: " + url);
Connection conn = java.sql.DriverManager.getConnection(url);
Statement stmt = conn.createStatement();
stmt.execute("dbcc show_statistics(auth
ors, 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 (ClassNotFoundExcept
ion 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\Inte
lliJ IDEA 5.0\bin"
-Dfile.encoding=windows-1252 -classpath
"C:\j2sdk1.4. 2_05\jre\lib\charset
s.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\sunrsas
ign.jar;C:\j2sdk1.4. 2_05\jre\lib\ext\dns
ns.jar;C:
\j2sdk1.4. 2_05\jre\lib\ext\lda
psec.jar;C:\j2sdk1.4. 2_05\jre\lib\ext\loc
aledata.jar;C:\j2sdk1.4. 2_05\jre\lib\ext\sun
jce_provider.jar;C:\j2sdk1.4.2_05\lib\tools.jar;C:\Documents
and
Settings\wclark\Idea
Projects\WesAdHoc\cl
asses;C:\dev\lib\sql
jdbc. jar;C:\dev\lib\msbas
e. jar;C:\dev\lib\msuti
l. jar;C:\dev\lib\mssql
server.jar;C:\Program
Files\New
Atlanta\JTurbo30\lib
\JTurbo.jar;C:\Java\jTDS1.2\jtds-1.2.jar;C:\Program
Files\JetBrains\Inte
lliJ 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;selectMet
hod=direct;databaseN
ame=AdventureWorks;u
ser=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:sqlse
rver:// ITH:1433;databaseNam
e=pubs;user=sa;passw
ord=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
| |
| Wes Clark 2006-01-29, 8:23 pm |
|
I tried the 2005 driver against the 2000 database, and got the same
incorrect results, so it's the driver and not the new DBCC SHOW_STATISTICS
behavior in 2005.
| |
| Joe Weinstein 2006-01-29, 8:23 pm |
|
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;selectMet
hod=direct;databaseN
ame=AdventureWorks;u
ser=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 (ClassNotFoundExcept
ion 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:sqlse
rver:// ITH:1433;databaseNam
e=pubs;user=sa;passw
ord=123;";
> System.out.println("URL being used: " + url);
> Connection conn = java.sql.DriverManager.getConnection(url);
> Statement stmt = conn.createStatement();
> stmt.execute("dbcc show_statistics(auth
ors, 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 (ClassNotFoundExcept
ion 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\Inte
lliJ IDEA 5.0\bin"
> -Dfile.encoding=windows-1252 -classpath
> "C:\j2sdk1.4. 2_05\jre\lib\charset
s.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\sunrsas
ign.jar;C:\j2sdk1.4. 2_05\jre\lib\ext\dns
ns.jar;
C:\j2sdk1.4. 2_05\jre\lib\ext\lda
psec.jar;C:\j2sdk1.4. 2_05\jre\lib\ext\loc
aledata.jar;C:\j2sdk1.4. 2_05\jre\lib\ext\sun
jce_provider.jar;C:\j2sdk1.4.2_05\lib\tools. jar;C:\Documents[col
or=darkred]
> and
> Settings\wclark\Idea
Projects\WesAdHoc\cl
asses;C:\dev\lib\sql
jdbc. jar;C:\dev\lib\msbas
e. jar;C:\dev\lib\msuti
l. jar;C:\dev\lib\mssql
server.jar;C:\Program
> Files\New
> Atlanta\JTurbo30\lib
\JTurbo.jar;C:\Java\jTDS1.2\jtds-1.2.jar;C:\Program
> Files\JetBrains\Inte
lliJ 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;selectMet
hod=direct;databaseN
ame=AdventureWorks;u
ser=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:sqlse
rver:// ITH:1433;databaseNam
e=pubs;user=sa;passw
ord=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
>[/color]
| |
| Wes Clark 2006-01-30, 3:23 am |
| 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;selectMet
hod=direct;databaseN
ame=AdventureWorks;u
ser=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 ==");
processMultipleResul
tSetsFirstWay(conn, sql);
System.out.println("== Second way ==");
processMultipleResul
tSetsSecondWay(conn,
sql);
conn.close();
} catch (ClassNotFoundExcept
ion 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;selectMet
hod=direct;databaseN
ame=Pubs;user=sa;pas
sword=123";
System.out.println("URL being used: " + url);
Connection conn = java.sql.DriverManager.getConnection(url);
String sql = "dbcc show_statistics(auth
ors, aunmind)";
Statement stmt = conn.createStatement();
System.out.println("== First way ==");
processMultipleResul
tSetsFirstWay(conn, sql);
System.out.println("== Second way ==");
processMultipleResul
tSetsSecondWay(conn,
sql);
conn.close();
} catch (ClassNotFoundExcept
ion 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:sqlse
rver:// ALKOR:1433;databaseN
ame=pubs;user=sa;pas
sword=123;";
System.out.println("URL being used: " + url);
Connection conn = java.sql.DriverManager.getConnection(url);
String sql = "dbcc show_statistics(auth
ors, aunmind)";
System.out.println("== First way ==");
processMultipleResul
tSetsFirstWay(conn, sql);
System.out.println("== Second way ==");
processMultipleResul
tSetsSecondWay(conn,
sql);
conn.close();
} catch (ClassNotFoundExcept
ion 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:sqlse
rver:// ALKOR:1434;databaseN
ame=AdventureWorks;u
ser=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 ==");
processMultipleResul
tSetsFirstWay(conn, sql);
System.out.println("== Second way ==");
processMultipleResul
tSetsSecondWay(conn,
sql);
conn.close();
} catch (ClassNotFoundExcept
ion e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void processMultipleResul
tSetsFirstWay(Connec
tion 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 processMultipleResul
tSetsSecondWay(Conne
ction 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;selectMet
hod=direct;databaseN
ame=AdventureWorks;u
ser=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;selectMet
hod=direct;databaseN
ame=Pubs;user=sa;pas
sword=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:sqlse
rver:// ALKOR:1433;databaseN
ame=pubs;user=sa;pas
sword=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:sqlse
rver:// ALKOR:1434;databaseN
ame=AdventureWorks;u
ser=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
| |
| joeNOSPAM@bea.com 2006-01-30, 11:23 am |
| Hi. An execute() call may cause the DBMS to return any number
of update counts and/or result sets in any order. The return from
execute() says whether the first thing returned is a result set.
GetMoreResults() moves the processing to the next output to
process, and returns true if the next output is a result set.
The same SQL may return a different series of outputs for
different DBMSes, based on procedure definitions, triggers etc,
however, if one driver returns more valid info than another,
that may be a driver bug.
Joe Weinstein at BEA Systems
| |
| Wes Clark 2006-01-30, 1:23 pm |
| I tested with the jTDS 1.2 and the JTurbo driver. Both behaved as the older
MS driver behaved. They did not return row counts as update counts
interspersed between the result sets.
| |
| Evan T. Basalik 2006-01-30, 1:23 pm |
| Joe,
Let me set up your test code and I will let you know what I find.
Evan
--------------------
>From: "joeNOSPAM@bea.com" <joe.weinstein@gmail.com>
>Newsgroups: microsoft.public.sqlserver.jdbcdriver
>Subject: Re: Bug with Multiple Result Sets?
>Date: 30 Jan 2006 08:52:09 -0800
>Message-ID: <1138639929.383911.36070@g49g2000cwa.googlegroups.com>
>References: <BFF86425-775F-4123-AE58- 3C84FE25425C@microso
ft.com>
> <43DD5D6F.3060806@bea.com>
> <461F7166-63ED-4C52-85CE- C08A52F2E576@microso
ft.com>
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-online.de!border2.nntp.dca.giganews.com!nntp.giganews.com!
news-out.readnews.com!news-out.readnews.com!news-pusher.readnews.com!postnews.google.com!g49g2000cwa.googlegroups.com!not-for-mail
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.jdbcdriver:7427
>X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
>
>Hi. An execute() call may cause the DBMS to return any number
>of update counts and/or result sets in any order. The return from
>execute() says whether the first thing returned is a result set.
>GetMoreResults() moves the processing to the next output to
>process, and returns true if the next output is a result set.
>The same SQL may return a different series of outputs for
>different DBMSes, based on procedure definitions, triggers etc,
>however, if one driver returns more valid info than another,
>that may be a driver bug.
>Joe Weinstein at BEA Systems
>
>
--
This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
Note: For the benefit of the community-at-large, all responses to this message are best directed to the newsgroup/thread from which they originated.
| |
| Angel Saenz-Badillos[MS] 2006-02-01, 8:23 pm |
| 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@discussions
.microsoft.com> wrote in message
news:461F7166-63ED-4C52-85CE- C08A52F2E576@microso
ft.com...
> 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;selectMet
hod=direct;databaseN
ame=AdventureWorks;u
ser=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 ==");
> processMultipleResul
tSetsFirstWay(conn, sql);
> System.out.println("== Second way ==");
> processMultipleResul
tSetsSecondWay(conn,
sql);
> conn.close();
> } catch (ClassNotFoundExcept
ion 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;selectMet
hod=direct;databaseN
ame=Pubs;user=sa;pas
sword=123";
> System.out.println("URL being used: " + url);
> Connection conn = java.sql.DriverManager.getConnection(url);
> String sql = "dbcc show_statistics(auth
ors, aunmind)";
> Statement stmt = conn.createStatement();
> System.out.println("== First way ==");
> processMultipleResul
tSetsFirstWay(conn, sql);
> System.out.println("== Second way ==");
> processMultipleResul
tSetsSecondWay(conn,
sql);
> conn.close();
> } catch (ClassNotFoundExcept
ion 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:sqlse
rver:// ALKOR:1433;databaseN
ame=pubs;user=sa;pas
sword=123;";
> System.out.println("URL being used: " + url);
> Connection conn = java.sql.DriverManager.getConnection(url);
> String sql = "dbcc show_statistics(auth
ors, aunmind)";
> System.out.println("== First way ==");
> processMultipleResul
tSetsFirstWay(conn, sql);
> System.out.println("== Second way ==");
> processMultipleResul
tSetsSecondWay(conn,
sql);
> conn.close();
> } catch (ClassNotFoundExcept
ion 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:sqlse
rver:// ALKOR:1434;databaseN
ame=AdventureWorks;u
ser=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 ==");
> processMultipleResul
tSetsFirstWay(conn, sql);
> System.out.println("== Second way ==");
> processMultipleResul
tSetsSecondWay(conn,
sql);
> conn.close();
> } catch (ClassNotFoundExcept
ion e) {
> e.printStackTrace();
> } catch (SQLException e) {
> e.printStackTrace();
> }
>
> }
>
> public static void processMultipleResul
tSetsFirstWay(Connec
tion 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 processMultipleResul
tSetsSecondWay(Conne
ction 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;selectMet
hod=direct;databaseN
ame=AdventureWorks;u
ser=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;selectMet
hod=direct;databaseN
ame=Pubs;user=sa;pas
sword=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:sqlse
rver:// ALKOR:1433;databaseN
ame=pubs;user=sa;pas
sword=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:sqlse
rver:// ALKOR:1434;databaseN
ame=AdventureWorks;u
ser=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
| |
| Wes Clark 2006-02-06, 8:23 pm |
| Has this officially been accepted as a bug? If so, will there be a patch or
a scheduled new version to address it?
| |
| Evan T. Basalik 2006-02-07, 11:23 am |
| Wes,
Sorry for the delay - I am seeing the exact same results that you do and it definitely looks like something odd is going on here. I will continue to keep digging
and I will keep you posted.
Evan
--------------------
>Thread-Topic: Bug with Multiple Result Sets?
>thread-index: AcYlxEI6IkwmnKWZQ7Wl
g+O4T4/iDw==
>X-WBNR-Posting-Host: 209.78.47.227
>From: =?Utf-8?B?V2VzIENsYXJr?= < WesClark@discussions
.microsoft.com>
>References: <BFF86425-775F-4123-AE58- 3C84FE25425C@microso
ft.com> <43DD5D6F.3060806@bea.com> <461F7166-63ED-4C52-85CE-C08A52F2E576
@microsoft.com> <1138639929.383911.36070@g49g2000cwa.googlegroups.com>
>
>I tested with the jTDS 1.2 and the JTurbo driver. Both behaved as the older
>MS driver behaved. They did not return row counts as update counts
>interspersed between the result sets.
>
--
This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
Note: For the benefit of the community-at-large, all responses to this message are best directed to the newsgroup/thread from which they originated.
| |
| Evan T. Basalik 2006-02-07, 8:23 pm |
| Wes,
After some more internal discussions, I have decided to file a bug about this issue.
Evan
--------------------
>From: "Angel Saenz-Badillos[MS]" <angelsa@online.microsoft.com>
>References: <BFF86425-775F-4123-AE58- 3C84FE25425C@microso
ft.com> <43DD5D6F.3060806@bea.com> <461F7166-63ED-4C52-85CE-C08A52F2E576
@microsoft.com>
>Subject: Re: Bug with Multiple Result Sets?
>Date: Wed, 1 Feb 2006 13:06:30 -0800
--
This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
Note: For the benefit of the community-at-large, all responses to this message are best directed to the newsgroup/thread from which they originated.
| |
| Wes Clark 2006-02-07, 8:23 pm |
| Okay. How can be notified when and if the bug is scheduled to be included in
a patch or release?
| |
| Evan T. Basalik 2006-02-20, 11:23 am |
| I just got some feedback from DEV and it looks like this is not a bug. Instead, what you are seeing is by-design. The problem is that dbcc returns an update
count after each result set. Therefore, when you check getMoreResults, it properly shows false b/c the next item is an update count, not a result set. In other
words, Joe was right! :)
The code below shows a way to retrieve the results I think for which you are looking:
import java.sql.Connection;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.ResultSet;
public class MultipleResultSets20
05 {
public MultipleResultSets20
05() {
}
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:// evanbagx620\\sql2005
;selectMethod=direct
;databaseName=Advent
ureWorks;user=sa;pas
sword=sW62292";
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 resultSetNum = 0;
for (boolean resultsEndOrUpdateCo
unt = false;
;
resultsEndOrUpdateCo
unt = !stmt.getMoreResults())
{
if (resultsEndOrUpdateC
ount)
{
int updateCount = stmt.getUpdateCount();
if (-1 == updateCount) // end of results
break;
System.out.println("Update count is: " + updateCount);
}
else // result is ResultSet
{
ResultSet rs = stmt.getResultSet();
switch (++resultSetNum)
{
case 1:
System.out.println("STAT_HEADER Results");
while (rs.next())
{
System.out.println("Name: " + rs.getString(1));
}
break;
case 2:
System.out.println("DENSITY_VECTOR Results");
while (rs.next())
{
System.out.println("Columns: " + rs.getString(3));
}
break;
case 3:
System.out.println("HISTOGRAM Results");
while (rs.next())
{
System.out.println("RANGE_ROWS: " + rs.getInt(2));
}
break;
}
rs.close();
}
}
stmt.close();
conn.close();
} catch (ClassNotFoundExcept
ion 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.
}
}
}
--------------------
>Thread-Topic: Bug with Multiple Result Sets?
>thread-index: AcYsMKeg8LpwjWKkRYK6
JutU+9qQAg==
>From: =?Utf-8?B?V2VzIENsYXJr?= < WesClark@discussions
.microsoft.com>
>References: <BFF86425-775F-4123-AE58- 3C84FE25425C@microso
ft.com> <43DD5D6F.3060806@bea.com> <461F7166-63ED-4C52-85CE-
C08A52F2E576@microso
ft.com> <On#bgN3JGHA.2992@tk2msftngp13.phx.gbl> <v0UZ$NCLGHA.1240@TK2MSFTNGXA02.phx.gbl>
>Date: Tue, 7 Feb 2006 13:51:30 -0800
>Message-ID: <12CE9C6C-9E6F-4ACA-9463- FE9799075C52@microso
ft.com>
>Newsgroups: microsoft.public.sqlserver.jdbcdriver
>Okay. How can be notified when and if the bug is scheduled to be included in
>a patch or release?
>
--
This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
Note: For the benefit of the community-at-large, all responses to this message are best directed to the newsgroup/thread from which they originated.
| |
| Joe Weinstein 2006-02-20, 1:23 pm |
|
Evan T. Basalik (MSFT) wrote:
> I just got some feedback from DEV and it looks like this is not a bug. Instead, what you are seeing is by-design. The problem is that dbcc returns an update
> count after each result set. Therefore, when you check getMoreResults, it properly shows false b/c the next item is an update count, not a result set. In other
> words, Joe was right! :)
Now how the heck did *that* happen??
;)
Joe
>
> The code below shows a way to retrieve the results I think for which you are looking:
>
> import java.sql.Connection;
> import java.sql.Statement;
> import java.sql.SQLException;
> import java.sql.ResultSet;
>
> public class MultipleResultSets20
05 {
> public MultipleResultSets20
05() {
> }
>
> 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:// evanbagx620\\sql2005
;selectMethod=direct
;databaseName=Advent
ureWorks;user=sa;pas
sword=sW62292";
> 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 resultSetNum = 0;
> for (boolean resultsEndOrUpdateCo
unt = false;
> ;
> resultsEndOrUpdateCo
unt = !stmt.getMoreResults())
> {
> if (resultsEndOrUpdateC
ount)
> {
> int updateCount = stmt.getUpdateCount();
> if (-1 == updateCount) // end of results
> break;
>
> System.out.println("Update count is: " + updateCount);
> }
> else // result is ResultSet
> {
> ResultSet rs = stmt.getResultSet();
>
> switch (++resultSetNum)
> {
> case 1:
> System.out.println("STAT_HEADER Results");
> while (rs.next())
> {
> System.out.println("Name: " + rs.getString(1));
> }
> break;
>
> case 2:
> System.out.println("DENSITY_VECTOR Results");
> while (rs.next())
> {
> System.out.println("Columns: " + rs.getString(3));
> }
> break;
>
> case 3:
> System.out.println("HISTOGRAM Results");
> while (rs.next())
> {
> System.out.println("RANGE_ROWS: " + rs.getInt(2));
> }
> break;
> }
>
> rs.close();
> }
> }
>
> stmt.close();
> conn.close();
> } catch (ClassNotFoundExcept
ion 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.
> }
> }
> }
>
> --------------------
>
>
> C08A52F2E576@microso
ft.com> <On#bgN3JGHA.2992@tk2msftngp13.phx.gbl> <v0UZ$NCLGHA.1240@TK2MSFTNGXA02.phx.gbl>
>
>
>
>
| |
| Wes Clark 2006-02-20, 8:23 pm |
| If you look at my posting from before, I already recoded my test program to
properly detect update counts as well as result sets. If DBCC is returning
update counts, then why do the JTurbo driver, the jTDS 1.0 driver, and the MS
2000 JDBC driver, when running the same commands against the same database,
not return them? I would need to define and execute a stored procedure
which actually does return an update count to demonstrate the other drivers
can handle update counts. The difference in behavior is not the 2005 SQL
Server engine, but the driver. Here are my results running your program with
all the drivers:
Class being loaded com.microsoft.sqlserver.jdbc.SQLServerDriver
URL being used:
jdbc:sqlserver:// localhost:2005;selec
tMethod=direct;datab
aseName=AdventureWor
ks;user=sa;password=
***
STAT_HEADER Results
Name: PK_Address_AddressID
Update count is: 1
DENSITY_VECTOR Results
Columns: AddressID
Update count is: 1
HISTOGRAM Results
RANGE_ROWS: 0
RANGE_ROWS: 1062
RANGE_ROWS: 95
RANGE_ROWS: 18431
RANGE_ROWS: 21
Update count is: 5
Update count is: 0
Class being loaded com.microsoft.jdbc.sqlserver.SQLServerDriver
URL being used:
jdbc:microsoft:sqlse
rver:// localhost:2005;selec
tMethod=direct;datab
aseName=AdventureWor
ks;user=sa;password=
***;
STAT_HEADER Results
Name: PK_Address_AddressID
DENSITY_VECTOR Results
Columns: AddressID
HISTOGRAM Results
RANGE_ROWS: 0
RANGE_ROWS: 1062
RANGE_ROWS: 95
RANGE_ROWS: 18431
RANGE_ROWS: 21
Class being loaded com.newatlanta.jturbo.driver.Driver
URL being used:
jdbc:JTurbo://localhost:2005/AdventureWorks/user=sa/password=***
STAT_HEADER Results
Name: PK_Address_AddressID
DENSITY_VECTOR Results
Columns: AddressID
HISTOGRAM Results
RANGE_ROWS: 0
RANGE_ROWS: 1062
RANGE_ROWS: 95
RANGE_ROWS: 18431
RANGE_ROWS: 21
Update count is: 0
Class being loaded net.sourceforge.jtds.jdbc.Driver
URL being used:
jdbc:jtds:sqlserver:
//localhost:2005/ AdventureWorks;user=
sa;password=***
STAT_HEADER Results
Name: PK_Address_AddressID
DENSITY_VECTOR Results
Columns: AddressID
HISTOGRAM Results
RANGE_ROWS: 0
RANGE_ROWS: 1062
RANGE_ROWS: 95
RANGE_ROWS: 18431
RANGE_ROWS: 21
Notice there are no update counts reported from the other drivers.
"Evan T. Basalik (MSFT)" wrote:
> I just got some feedback from DEV and it looks like this is not a bug. Instead, what you are seeing is by-design. The problem is that dbcc returns an update
> count after each result set. Therefore, when you check getMoreResults, it properly shows false b/c the next item is an update count, not a result set.
| |
| Wes Clark 2006-02-20, 8:23 pm |
| Here are some very interesting results. I created a stored procedure that
returned result set interspersed with update counts:
CREATE PROCEDURE testProcReturns
AS
BEGIN
SELECT VacationHours from HumanResources.Employee where employeeID < 3;
update HumanResources.Employee set VacationHours = VacationHours + 1 where
employeeID < 3;
SELECT VacationHours from HumanResources.Employee where employeeID < 3;
update HumanResources.Employee set VacationHours = VacationHours - 1 where
employeeID < 3;
SELECT VacationHours from HumanResources.Employee where employeeID < 3;
END;
When I ran the procedure using all four drivers, all except the new driver
correctly returned the update counts:
Class being loaded com.microsoft.sqlserver.jdbc.SQLServerDriver
URL being used:
jdbc:sqlserver:// localhost:2005;selec
tMethod=direct;datab
aseName=AdventureWor
ks;user=sa;password=
123
Vacation hours is 21
Vacation hours is 42
Vacation hours is 22
Vacation hours is 43
Vacation hours is 21
Vacation hours is 42
Class being loaded com.microsoft.jdbc.sqlserver.SQLServerDriver
URL being used:
jdbc:microsoft:sqlse
rver:// localhost:2005;selec
tMethod=direct;datab
aseName=AdventureWor
ks;user=sa;password=
123;
Vacation hours is 21
Vacation hours is 42
Update count is: 2
Vacation hours is 22
Vacation hours is 43
Update count is: 2
Vacation hours is 21
Vacation hours is 42
Class being loaded com.newatlanta.jturbo.driver.Driver
URL being used:
jdbc:JTurbo://localhost:2005/AdventureWorks/user=sa/password=123
Vacation hours is 21
Vacation hours is 42
Update count is: 2
Vacation hours is 22
Vacation hours is 43
Update count is: 2
Vacation hours is 21
Vacation hours is 42
Class being loaded net.sourceforge.jtds.jdbc.Driver
URL being used:
jdbc:jtds:sqlserver:
//localhost:2005/ AdventureWorks;user=
sa;password=123
Vacation hours is 21
Vacation hours is 42
Update count is: 2
Vacation hours is 22
Vacation hours is 43
Update count is: 2
Vacation hours is 21
Vacation hours is 42
I am not clear what is going on here, but these results strongly suggest the
new driver is not behaving correctly.
| |
| Wes Clark 2006-02-25, 9:28 am |
| So, Evan (and Joe), from my last post you can deduce I contend there is still
a bug. Do you agree at this point?
| |
| Joe Weinstein 2006-02-25, 9:28 am |
|
Wes Clark wrote:
> So, Evan (and Joe), from my last post you can deduce I contend there is still
> a bug. Do you agree at this point?
Yes.
Joe
| |
| Evan T. Basalik 2006-02-25, 9:28 am |
| Wes,
With your code and stored proc, I am able to reproduce the results. Let me do some more testing and I will let you know what I find.
Evan
--------------------
>Thread-Topic: Bug with Multiple Result Sets?
>Subject: Re: Bug with Multiple Result Sets?
>Date: Mon, 20 Feb 2006 16:56:29 -0800
>
>Here are some very interesting results. I created a stored procedure that
>returned result set interspersed with update counts:
>CREATE PROCEDURE testProcReturns
>AS
>BEGIN
> SELECT VacationHours from HumanResources.Employee where employeeID < 3;
> update HumanResources.Employee set VacationHours = VacationHours + 1 where
>employeeID < 3;
> SELECT VacationHours from HumanResources.Employee where employeeID < 3;
> update HumanResources.Employee set VacationHours = VacationHours - 1 where
>employeeID < 3;
> SELECT VacationHours from HumanResources.Employee where employeeID < 3;
>END;
>
>When I ran the procedure using all four drivers, all except the new driver
>correctly returned the update counts:
>
>Class being loaded com.microsoft.sqlserver.jdbc.SQLServerDriver
>URL being used:
>jdbc:sqlserver:// localhost:2005;selec
tMethod=direct;datab
aseName=AdventureWor
ks;user=sa;password=
123
>Vacation hours is 21
>Vacation hours is 42
>Vacation hours is 22
>Vacation hours is 43
>Vacation hours is 21
>Vacation hours is 42
>Class being loaded com.microsoft.jdbc.sqlserver.SQLServerDriver
>URL being used:
> jdbc:microsoft:sqlse
rver:// localhost:2005;selec
tMethod=direct;datab
aseName=AdventureWor
ks;user=sa;password=
123;
>Vacation hours is 21
>Vacation hours is 42
>Update count is: 2
>Vacation hours is 22
>Vacation hours is 43
>Update count is: 2
>Vacation hours is 21
>Vacation hours is 42
>Class being loaded com.newatlanta.jturbo.driver.Driver
>URL being used:
>jdbc:JTurbo://localhost:2005/AdventureWorks/user=sa/password=123
>Vacation hours is 21
>Vacation hours is 42
>Update count is: 2
>Vacation hours is 22
>Vacation hours is 43
>Update count is: 2
>Vacation hours is 21
>Vacation hours is 42
>Class being loaded net.sourceforge.jtds.jdbc.Driver
>URL being used:
> jdbc:jtds:sqlserver:
//localhost:2005/ AdventureWorks;user=
sa;password=123
>Vacation hours is 21
>Vacation hours is 42
>Update count is: 2
>Vacation hours is 22
>Vacation hours is 43
>Update count is: 2
>Vacation hours is 21
>Vacation hours is 42
>
>I am not clear what is going on here, but these results strongly suggest the
>new driver is not behaving correctly.
>
--
This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
Note: For the benefit of the community-at-large, all responses to this message are best directed to the newsgroup/thread from which they originated.
| |
| Evan T. Basalik 2006-03-05, 8:24 pm |
| Wes,
I have refiled the original bug with the additional information.
Evan
--------------------
>Thread-Topic: Bug with Multiple Result Sets?
>From: =?Utf-8?B?V2VzIENsYXJr?= < WesClark@discussions
.microsoft.com>
>Subject: Re: Bug with Multiple Result Sets?
>Date: Mon, 20 Feb 2006 16:56:29 -0800
>Here are some very interesting results. I created a stored procedure that
>returned result set interspersed with update counts:
>CREATE PROCEDURE testProcReturns
>AS
>BEGIN
> SELECT VacationHours from HumanResources.Employee where employeeID < 3;
> update HumanResources.Employee set VacationHours = VacationHours + 1 where
>employeeID < 3;
> SELECT VacationHours from HumanResources.Employee where employeeID < 3;
> update HumanResources.Employee set VacationHours = VacationHours - 1 where
>employeeID < 3;
> SELECT VacationHours from HumanResources.Employee where employeeID < 3;
>END;
>
>When I ran the procedure using all four drivers, all except the new driver
>correctly returned the update counts:
>
>Class being loaded com.microsoft.sqlserver.jdbc.SQLServerDriver
>URL being used:
>jdbc:sqlserver:// localhost:2005;selec
tMethod=direct;datab
aseName=AdventureWor
ks;user=sa;password=
123
>Vacation hours is 21
>Vacation hours is 42
>Vacation hours is 22
>Vacation hours is 43
>Vacation hours is 21
>Vacation hours is 42
>Class being loaded com.microsoft.jdbc.sqlserver.SQLServerDriver
>URL being used:
> jdbc:microsoft:sqlse
rver:// localhost:2005;selec
tMethod=direct;datab
aseName=AdventureWor
ks;user=sa;password=
123;
>Vacation hours is 21
>Vacation hours is 42
>Update count is: 2
>Vacation hours is 22
>Vacation hours is 43
>Update count is: 2
>Vacation hours is 21
>Vacation hours is 42
>Class being loaded com.newatlanta.jturbo.driver.Driver
>URL being used:
>jdbc:JTurbo://localhost:2005/AdventureWorks/user=sa/password=123
>Vacation hours is 21
>Vacation hours is 42
>Update count is: 2
>Vacation hours is 22
>Vacation hours is 43
>Update count is: 2
>Vacation hours is 21
>Vacation hours is 42
>Class being loaded net.sourceforge.jtds.jdbc.Driver
>URL being used:
> jdbc:jtds:sqlserver:
//localhost:2005/ AdventureWorks;user=
sa;password=123
>Vacation hours is 21
>Vacation hours is 42
>Update count is: 2
>Vacation hours is 22
>Vacation hours is 43
>Update count is: 2
>Vacation hours is 21
>Vacation hours is 42
>
>I am not clear what is going on here, but these results strongly suggest the
>new driver is not behaving correctly.
>
--
This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
Note: For the benefit of the community-at-large, all responses to this message are best directed to the newsgroup/thread from which they originated.
| |
| Wes Clark 2006-03-05, 8:24 pm |
| Good. Since this is basic behavior of processing procedure calls,
I wonder if the unit test suite is complete. Please keep me informed
of when this will be scheduled for fix.
"Evan T. Basalik (MSFT)" wrote:
> Wes,
>
> I have refiled the original bug with the additional information.
>
> Evan
>
| |
| Wes Clark 2006-03-08, 8:23 pm |
| Is there a way I can track the progress in fixing this bug? Is there an
updated status?
"Evan T. Basalik (MSFT)" wrote:
> Wes,
>
> I have refiled the original bug with the additional information.
>
> Evan
>
| |
| Wes Clark 2006-03-13, 1:23 pm |
| Could you please provide me with a status on this bug? Is there another
channel I should be using?
| |
| Evan T. Basalik 2006-03-13, 8:23 pm |
| Wes,
I did some more playing around and it looks like I can only repro the problem if I use Statement.Execute. If I use PreparedStatement or CallableStatement,
the update counts are picked up correctly.
We are still researching...
Evan
--------------------
>X-Tomcat-ID: 563255050
>References: <BFF86425-775F-4123-AE58- 3C84FE25425C@microso
ft.com> <43DD5D6F.3060806@bea.com> <461F7166-63ED-4C52-85CE-
C08A52F2E576@microso
ft.com> <On#bgN3JGHA.2992@tk2msftngp13.phx.gbl> <v0UZ$NCLGHA.1240@TK2MSFTNGXA02.phx.gbl> <12CE9C6C-9E6F-
4ACA-9463- FE9799075C52@microso
ft.com> <09HCtnjNGHA.3504@TK2MSFTNGXA01.phx.gbl> <ED7C8EFF-FDFF-46C4-8280-16162AB415F7
@microsoft.com>
>From: evanba@online.microsoft.com (Evan T. Basalik (MSFT))
>Organization: Microsoft
>Date: Fri, 24 Feb 2006 22:44:21 GMT
>Subject: Re: Bug with Multiple Result Sets?
>
>Wes,
>
>With your code and stored proc, I am able to reproduce the results. Let me do some more testing and I will let you know what I find.
>
>Evan
>
>--------------------
>
>
>--
>
>This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at
>http://www.microsoft.com/info/cpyright.htm
>
>Note: For the benefit of the community-at-large, all responses to this message are best directed to the newsgroup/thread from which they originated.
>
>
--
This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
Note: For the benefit of the community-at-large, all responses to this message are best directed to the newsgroup/thread from which they originated.
| |
| Wes Clark 2006-03-13, 8:23 pm |
| Thanks for the update. I'll look into why we are using Statement.execute()
instead of CallableStatement.execute().
"Evan T. Basalik (MSFT)" wrote:
> Wes,
>
> I did some more playing around and it looks like I can only repro the problem if I use Statement.Execute. If I use PreparedStatement or CallableStatement,
> the update counts are picked up correctly.
>
> We are still researching...
>
> Evan
> --------------------
> C08A52F2E576@microso
ft.com> <On#bgN3JGHA.2992@tk2msftngp13.phx.gbl> <v0UZ$NCLGHA.1240@TK2MSFTNGXA02.phx.gbl> <12CE9C6C-9E6F-
> 4ACA-9463- FE9799075C52@microso
ft.com> <09HCtnjNGHA.3504@TK2MSFTNGXA01.phx.gbl> <ED7C8EFF-FDFF-46C4-8280-16162AB415F7
> @microsoft.com>
>
>
> --
>
> This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
>
> Note: For the benefit of the community-at-large, all responses to this message are best directed to the newsgroup/thread from which they originated.
>
>
| |
| Evan T. Basalik 2006-03-14, 9:23 am |
| Wes,
I just got confirmation from DEV that we can reproduce the problem, but only when using Statement.Execute. If you can switch to PreparedStatement or
CallableStatement, you should get the proper results for the update counts.
We have fixed this issue in SP1 of our 2005 JDBC driver (currently targeted for somewhere around June or July). If you need to get this fixed sooner, go
ahead and open a case with us and we can see about filing a request for a fix. As always, since it is a known bug, the case will be non-decrement.
Evan
--------------------
>Thread-Topic: Bug with Multiple Result Sets?
>thread-index: AcZG68ZADsnto+BvRAyR
O9+m8Q4j4w==
>X-WBNR-Posting-Host: 209.78.47.227
>From: =?Utf-8?B?V2VzIENsYXJr?= < WesClark@discussions
.microsoft.com>
>References: <BFF86425-775F-4123-AE58- 3C84FE25425C@microso
ft.com> <43DD5D6F.3060806@bea.com> <461F7166-63ED-4C52-85CE-
C08A52F2E576@microso
ft.com> <On#bgN3JGHA.2992@tk2msftngp13.phx.gbl> <v0UZ$NCLGHA.1240@TK2MSFTNGXA02.phx.gbl> <12CE9C6C-9E6F-
4ACA-9463- FE9799075C52@microso
ft.com> <09HCtnjNGHA.3504@TK2MSFTNGXA01.phx.gbl> <ED7C8EFF-FDFF-46C4-8280-16162AB415F7
@microsoft.com> <t9X0cPZOGHA.3052@TK2MSFTNGXA01.phx.gbl> <pAZoYouRGHA.4824@TK2MSFTNGXA03.phx.gbl>
>Subject: Re: Bug with Multiple Result Sets?
>Date: Mon, 13 Mar 2006 14:16:28 -0800
>
>Thanks for the update. I'll look into why we are using Statement.execute()
>instead of CallableStatement.execute().
>"Evan T. Basalik (MSFT)" wrote:
>
CallableStatement,[c
olor=darkred]
9E6F-[color=darkred]
>
--
This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
Note: For the benefit of the community-at-large, all responses to this message are best directed to the newsgroup/thread from which they originated.
|
|
|
|
|