FreeTDS Development Group

["James K. Lowden" <jklowden AT schemamania.org>]

"A result is something returned by the data source after a statement is
executed. ODBC has two types of results: result sets and row counts. Row
counts are the number of rows affected by an update, delete, or insert
statement."

This idea of row-count-only resultsets is a snakepit.  It's one of those
areas where the "standard" is whatever the driver says it is.  

http://www.easysoft.com/developer/languages/perl/sql_server_unix_tutorial.html#procedures_multiple


do {
        while (my (@row) = $sth1->fetchrow_array) {
            print "\t", join (", ", @row) , "\n";
        }
} while ($sth1->{odbc_more_results}); 

Hmph!  I've been working with $sth->{Active}, which the DBI doc even says
is poorly defined:

        'The "Active" attribute is true if the handle object is "active". 
This is
rarely used in applications. The exact meaning of active is somewhat vague
at the moment.'

But then I see $sth->{odbc_more_results}, above.  

        "To determine whether to return true or false, odbc_more_results calls
SQLMoreResults. There are some situations where DBD::ODBC will
automatically call SQLMoreResults without the need for odbc_more_results.
If the previous SQL statement that executed was a non result-set
generating statement, such as an INSERT statement, DBD::ODBC calls
SQLMoreResults for you. This triggers the execution of the next statement
in the procedure. If the previous statement generated a result set, your
script needs to call SQLMoreResults explicitly by using
odbc_more_results."

(Everyone except the ODBC documentation understands "resultset" to mean
"rows of data from the server".)  

Sounds like odbc_more_results exists to process DONEINPROC packets, so
Perl scripts can bypass them.  

For comparison, look at what DB2 says:

http://publib.boulder.ibm.com/infocenter/db2v7luw/index.jsp?topic=/com.ibm.db2v7.doc/db2l0/sqll1338.htm

        "The ODBC specification of SQLMoreResults() also allow counts 
associated
with the execution of parameterized INSERT, UPDATE, and DELETE statements
with arrays of input parameter values to be returned. However, DB2 CLI
does not support the return of such count information."

I particularly like this:

http://www.vieka.com/esqldoc/esqlref/htm/odbcdetermining_the_number_of_affected_rows.htm

        "If a batch of SQL statements is executed, the count of affected rows
might be a total count for all statements in the batch or individual
counts for each statement in the batch."

I wasn't able to find online documentation for Oracle or Sybase.  

I suppose, as best we can, we should emulate Microsoft's implementation:
SQLMoreResults should SUCCEED for DONE packets, so applications can get
rowcounts.  But a standards lawyer could defend almost anything.  

--jkl