FreeTDS Development Group

["Thompson, Bill D (London)" <bill_d_thompson AT ml.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.

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 ?

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

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.  

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

> -----Original Message-----
> From: ZIGLIO, Frediano, VF-IT [SMTP:Frediano.Ziglio AT vodafone.com]
> Sent: Monday, February 16, 2004 2:45 PM
> To:   FreeTDS Development Group
> Subject:      RE: [freetds] SQLRowCount failing after prepare and execute
> 
> > 
> > Thanks Freddy - most interesting.
> > 
> > 1st interesting thing is the packet we get back from the 
> > prepare. Its like we get the full results back even thogh the 
> > stateemnts havent been executed yet. It seems to me we should 
> > only be ienterested in the DONEPROC and PARAM tokens from a 
> > sp_prepare...
> > 
> > 2nd interesting thing. These are the DONEINPROC tokens coming 
> > back from sp_execute () when the prepared statement was 
> > 
> > select * from #tmp where i = 2 
> > delete from #tmp
> > 
> > ff 11 00 c1 00 00 00 00 00
> > ff 11 00 c4 00 01 00 00 00
> > 
> > We have two things that may help us with the DONEINPROC tokens
> > 
> > 1) both have the "done_count_valid" bit set.
> > 2) the short token value. I can tell you that 
> > 
> >     c1 = SELECT
> >     c4 = DELETE
> > 
> > also, FYI...
> > 
> >     c3 = INSERT
> >     c5 = UPDATE
> > 
> > Not sure if this helps.
> > 
> > My feeling on this is that after a statement (or batched 
> > multiple statements
> > ) have been executed by the server,
> > the API functions should behave exactly the same to the 
> > caller whether the statement was previously "prepared" or not.
> > 
> > I don't know if this how the MS ODBC driver behaves, and I 
> > don't really care to be honest.
> > 
> > In our example the user should be able to identify, using the 
> > API, that there were two separate statements in the prepared 
> > batch, and be able to access the rowcounts and column counts 
> > for each! 
> > 
> > Good Luck,
> > 
> > Bill
> > 
> 
> It would be very nice... however it's impossible. A simple select can
> return a bunch of DONE/DONEINPROC... simply a trigger can execute a
> INSERT/UPDATE/DELETE and this statement returns DONEINPROC like your
> prepared INSERT/DELETE/UPDATE... there is no way to distinguish the two
> cases so it's better to ignore every DONEINPROC returned (this seemed
> the way MS ODBC works...). I don't like the way MS ODBC handle this case
> either however I understand the request... be able to read row count
> even using prepared insert/delete/update.
> 
> freddy77
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds

==============================================================================

If you are not an intended recipient of this e-mail, please notify
the sender, delete it and do not read, act upon, print, disclose,
copy, retain or redistribute it.

Click here for important additional terms relating to this e-mail.
     <http://www.ml.com/email_terms/>

==============================================================================