FreeTDS Development Group

[Bob Kline <bkline AT rksystems.com>]

On Fri, 8 Sep 2000, Stephanie Lewis wrote:

> Just looking for some advice,
> 
>              The current JDBC FreeTds driver does not have any
> cursor support. Just to see what is returned, I've tried test
> queries like:
> 
> rs = stmt.executeQuery("DECLARE cursor1 CURSOR FOR SELECT FirstName
> FROM Employees OPEN cursor1 FETCH NEXT FROM cursor1");
> 
> expecting a result set containing the first row. When examining the
> return packet, all I get is a TDS_END_TOKEN and nothing more. 

I'm not sure what you mean by "all I get is a TDS_END_TOKEN and nothing
more." because that's not all that SQL Server is sending back (as you
can see by looking at the packet which is logged by the Logger class if
you turned on logging -- which is what "examining the return packet"
generally means in the context of this driver).

SQL Server is indeed sending back a TDS_END_TOKEN.  In fact, it's
sending back one for each of the first two statements it sees ("DECLARE
cursor1 CURSOR FOR ..." and "OPEN cursor1").  After that it sends the
meta-data for the results and the values for the first row.  The driver
doesn't know enough to keep going to get to the results, so you're
getting back a null from your executeQuery().

There's a more serious problem, though.  Even if you made the driver
smart enough to skip past the TDS_END_TOKENs, you wouldn't be able to do
much with the cursor, because the driver opens a separate connection for
each query.  This means that, for example, you wouldn't be able to
submit additional "FETCH" statements for the cursor, because you
wouldn't be able to get to it.

Questions about this "feature" of the driver have been raised here on
the list before (pointing out that it's not very efficient, and it loses
settings -- such as character set selection -- that the programmer
thinks (s)he has set for the duration of the program).  As far as I
know, Craig hasn't addressed any of these posts, at least not publicly.  
You've stumbled across one more drawback to this approach of opening a
separate connection for each query.

> I've also played with:
> 
> rs = stmt.executeQuery("declare @P1 int set @P1=NULL sp_cursoropen
> @P1 output, 'select FirstName from Employees', 8, 1, 1 Select @P1");
> 
> to see if I could get a handle on the cursor returned but again to
> no avail. I realize I should not be using sp_cursoropen directly
> from my app but have seen it used in other drivers.
> 

I don't think you should go down this road because:

  1. If the problems above were addressed, you wouldn't need to.
  2. Microsoft tells you it's not supported (but you knew that).

My advice, at least until we get some sort of dialog going with Craig
about solving the problems described above, is that you wrap your cursor
work inside stored procedures.  I realize that this may not address
every use case you might come up with, but that's the best we have for
right now.

-- 
Bob Kline
mailto:bkline AT rksystems.com
http://www.rksystems.com