FreeTDS Development Group

["ZIGLIO, Frediano, VF-IT" <Frediano.Ziglio AT vodafone.com>]

> 
> Hey Freddy,
> 
> I was thinking about this overnight - an annoying feature of 
> being part of this project - and I had the following thoughts.
> 
> 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.
> If we're to do this, it means that we HAVE to read the 
> DONEINPROC token and do something with the row count we get.
> 

Returning only the last valid count in this case seems the better
solution as triggers are executed before your UPDATE. I think this is
the reason of strange (and wrong) MS ODBC behaviour.

> 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 ?
> 

SQLRowCount is expected to return SQL_SUCCESS and -1 rows. SQLRowCount
return valid information after you retrived all rows.

> Isn't it therefore in the programmers arena to decide when 
> it's valid to call SQLRowcount ?
> 

Client should expect that row number is not available just after issuing
the query.

> 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...
> 
> Coming back to your "two statements in one prepare", 
> calling SQLRowcount and the equivalent-function-which-gives-the
> number-of-columns (SQLColcount ?) is bound to be ambiguous in 
> this context. We shouldn't worry that the results we pass 
> back are ambiguous - the programmer shouldn't expect anything 
> else, given what they've done.  
> 

This is why I suggest to return only the last update/delete/insert row
count.

> 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 have column count we have all column informations (names, lengths
and so on).

> 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. It might be worth trying out a simple statement 
> with a trigger attached to the update in your MS ODBC driver 
> to see how that behaves...bet I can guess...
> 
> Hope this helps
> 

I still think to return last row count from update/delete/insert so even
on prepare we can get last delete/insert/update row count. It's a
differect sligth behaviour but more correct. So "select ... delete ..."
return 2 recordset while "delete ... select ..." return just a
recordset.

freddy77