FreeTDS Development Group

[Bob Kline <bkline AT rksystems.com>]

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