Terence,
I could be wrong but from what little I remember from TDS I think
that unless you enable a server-side cursor, once you start retrieving
data from the server you have no choice but to keep reading it until
it is exhausted - this is why SQL Server ODBC driver only supports
1 active statement (by default). When you enable server-side cursors
the whole ball game changes. The code previously posted works when
you do this.
Not that I'm saying your statement contradicts this - certainly,
fetching all the data on one statement satisfies the continue reading
until nothing left. The problem comes when you start fetching on one
statement (not complete) and then attempt to create a new active
statement. To my knowledge, nothing other than server-side cursors
or closing one cursor before opening another gets around this. The
posted code attempts to get two open/active statements. If you start
fetching on one statement then close (in DBI terms finish) it the
ODBC driver still has to read the remaining data - you can demonstrate
this quite easily by selecting a long image/text column from a table,
reading a few bytes then closing the statement - the close takes
a long time (as even though you didn't need it the ODBC driver has
to read the rest of the text/image data) before it can start a new
request.
Martin
Terence J. Young, D.C. wrote:
Hi,
You should only have to clear the statement if you are fetching a single
row or fetching into a set variables. This will happen even if the
result set on the backend contains only one record. If you are fetching
row by row in a loop, fetching one more time beyond the last record
should clear out the result table. If you execute a command that
fetches all the rows, I would think you shouldn't see the problem. (the
code should take care of this)
The fact that the older connection didn't exhibit this problem may be
that it calls a diffent set of lib calls in TDS that cleans up after
itself, or, the module cleans up after itself. I have seen this dual
behavior in another product that I use to connect to sybase, Studio. If
I use their V2 dams, I don't have to worry about cleared result sets. If
use their newer, object oriented V3 dams, I have to execute a
StatementObj.$clear after any $fetch into a row or fetchinto a set of
variables, even if the query is expected to find only one row. When I
fetch into a list, the dam clears out the result set (presumably, the
internal code executes row fetches until it receives no return)
terry
