Skip to Content.
Sympa Menu

freetds - Re: JDBC-Gurus, please comment

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Bob Kline <bkline AT rksystems.com>
  • To: TDS Development Group <freetds AT franklin.oit.unc.edu>
  • Subject: Re: JDBC-Gurus, please comment
  • Date: Fri, 14 Sep 2001 11:51:49 -0400 (EDT)


On Fri, 14 Sep 2001, "Schörk, Andreas" wrote:

> Hello, could anybody put some effort into looking through the junit
> code below and tell me whether this is ok, I am also interested in
> experiences, how you think that other mssql-jdbc-drivers are
> handling this:

I haven't examined all of the code (except to notice that you haven't
compiled it yet -- an assumption I'm making based on some mismatched
names, such as sqlnocount2), but I'm willing to start nibbling at the
top part, to get the discussion going.

In general, I find Sun's documentation for JDBC to be less comprehensive
than other database API specifications. For example, how would one know
what the significance of the return value from CallableStatement's
execute() method signifies? The method is inherited from the base
interface PreparedStatement, but the documentation for the method in
that interface is silent on the return value. So the reader has to fall
back on the documentation for the execute() method in the grandparent
interface Statement, from which PreparedStatement is in turn derived.
That documentation tells us what Statement.execute()'s return value
means, but there is no guarantee that PreparedStatement, which exposes
its own version of execute(), uses the same semantics (if it did, there
wouldn't be any point in having a separately specified version of the
method).

Similarly, the JDBC documentation is not as forthcoming about the
meaning of the terms "result" and "SQL statement." Here's what the ODBC
documentation [1] has to say on this topic:

Result-Generating and Result-Free Statements
SQL statements can be loosely divided into the following five
categories:

* Result set--generating statements. These are SQL statements
that generate a result set. For example, a SELECT statement.

* Row count--generating statements. These are SQL statements
that generate a count of affected rows. For example, an
UPDATE or DELETE statement.

* Data Definition Language (DDL) statements. These are SQL
statements that modify the structure of the database. For
example, CREATE TABLE or DROP INDEX.

* Context-changing statements. These are SQL statements that
change the context of a database. For example, the USE and
SET statements in SQL Server.

* Administrative statements. These are SQL statements used for
administrative purposes in a database. For example, GRANT
and REVOKE.

SQL statements in the first two categories are collectively known
as result-generating statements. SQL statements in the latter
three categories are collectively known as result-free statements.
ODBC defines the semantics of batches that include only result-
generating statements."

Using this passage to fill in some of the JDBC documentation gaps, it
doesn't seem clear to me that all of the "statements" you're referring
to are going to show up on the getMoreResults() and getUpdateCount()
radar screen (for example, I take the "SET," "CREATE TABLE," "EXECUTE"
statements to be "result-free" statements).

So I would be tempted to replace code like this:

CallableStatement cstmt = conn.prepareCall("spTestExec");
assertTrue(!cstmt.execute());
assertTrue(cstmt.getUpdateCount() == 0); // set
assertTrue(!cstmt.getMoreResults());
assertTrue(cstmt.getUpdateCount() == 0); // create
assertTrue(!cstmt.getMoreResults());
assertTrue(cstmt.getUpdateCount() == 0); // execute
assertTrue(!cstmt.getMoreResults());
assertTrue(cstmt.getUpdateCount() == 1); // insert
assertTrue(cstmt.getMoreResults());
ResultSet rs = cstmt.getResultSet();
while (rs.next()) {
passed = true;
}
assertTrue(!cstmt.getMoreResults() && cstmt.getUpdateCount() == -1);

with something along these lines (making a somewhat unwarrented
assumption about the semantics of PreparedStatement.execute(), as
described above):

CallableStatement cstmt = conn.prepareCall("spTestExec");
assertTrue(!cstmt.execute());
assertTrue(cstmt.getUpdateCount() == 1); // insert
assertTrue(cstmt.getMoreResults());
ResultSet rs = cstmt.getResultSet(); // select
while (rs.next()) {
//passed = true;
}
assertTrue(!cstmt.getMoreResults() && cstmt.getUpdateCount() == -1);

However, I believe that the current version of the freetds_jdbc driver
will fail at the execute call, and further that it would miss the update
count for the INSERT statement. The driver has historically taken a
pretty cavalier approach to row count information.

This version would probably pass (though it shouldn't):

CallableStatement cstmt = conn.prepareCall("spTestExec");
assertTrue(cstmt.execute());
ResultSet rs = cstmt.getResultSet(); // select
while (rs.next()) {
//passed = true;
}
assertTrue(!cstmt.getMoreResults() && cstmt.getUpdateCount() == -1);

[1] ODBC Programmer's Reference, Volume 1, page 148.

--
Bob Kline
mailto:bkline AT rksystems.com
http://www.rksystems.com








Archive powered by MHonArc 2.6.24.

Top of Page