FreeTDS Development Group

["Matthew A. Davis" <azami AT speakeasy.net>]

"Thompson, Bill D (London)" wrote:
> Lets put aside your recent findings with the "two statements in one prepare"
> for the moment, and get back to the original problem.
> Matthew reports that SQLRowCount doesn't return the value when called as
> follows:
> 
> SQLPrepare(hstmt, (SQLCHAR *)("UPDATE <table> SET <column> = <value>" ),
> SQL_NTS);
> SQLExecute(hstmt);
> SQLRowCount(hstmt, &rows);
> 
> This is clearly bad news. We should give him the rowcount.

Thanks.  I've been following the discussion as best I can, but have to admit 
much of it has been "over my head" - I'm just not familiar enough with the 
inner workings of ODBC drivers.  As a user, what I know most about is the 
(expected) external behavior...

> Some questions. If the prepared statement is a SELECT... what does the
> execute do ? presumably it doesn't fetch the rows...
> Is it valid to call SQLRowcount directly after the SQLExecute in this
> circumstance. If so, what is it supposed to return ?
> it can't return a rowcount as we haven't fetched the rows or read the
> following DONE token , have we ?

According to my ODBC documents, the row count for a select statement is 
driver-defined.  Some drivers may return the correct number, but the 
documentation specifically cautions against relying on this information.

> If the programmer knows they have prepared and executed an UPDATE statement,
> then calling SQLRowcount afterwards is valid, and it should return a valid
> value.
> If the programmer knows they have prepared and executed a SELECT statement
> then presumably calling SQLRowcount afterwards is meaningless, and is not
> going to tell them anything, so they shouldn't call it or rely on the
> results if they do...

I agree 100%, with the caveat that if you happen to have a driver whose 
driver-defined behavior is that it returns the correct row count after 
executing a select, and you have reason to expect you won't ever need to use 
a different driver, then you might reasonably take advantage of that
driver-defined behavior.

Luckily, that's not what I'm trying to do. :-)

> coming back to solving Matthews problem - we SHOULD read the DONEINPROC
> token and save any valid "done count" value. and we should return that value
> when asked. If we were to maintain a "running total" of "done counts" from
> the DONEINPROC tokens we read, and do the same for the column counts
> (however they are arrived at) then we would have valid values to return in
> Matthews case. We would also have something to return in your "two
> statements in one prepare" case - although it would be (necessarily)
> ambiguous. I THINK this method might approximate the results you are seeing
> from the MS ODBC driver...
> 
> If we end up adding in rowcounts generated by triggered
> updates/inserts/deletes - so what ? we would be returning a valid total of
> the number of records affected by the original statement.

For my purposes, I think this would work.  For a general concept of 
"correctness" - from my perspective as a user - SQLRowCount() should give the 
same answer for the same command, regardless of whether it was prepared and 
executed, or just execdirected.  In the case of triggered activity, does
SQLRowCount() include rows affected by the triggers when you use 
SQLExecDirect()?  I'll try to find a moment to test this under Windows later 
today.

Big thanks to everyone who is looking into this for me!

-Matthew
-- 
Matthew A. Davis  ( mailto:azami AT speakeasy.net )
http://www.speakeasy.net/~azami

 "It's not always popular upholding the Constitution,
  but it's always the right thing to do."
                     -Senator Patrick J. Leahy (D-Vt)