FreeTDS Development Group

[Stephanie Lewis <slewis AT thinweb.com>]

Thanks alot Bob,
                    I'd already realized that the info was being sent back 
(about 5
min. after I'd posted the question as is so often the case), and had managed 
to
process the cursor's id for fetches, but started running into problems from 
there as
you would quess. I might play around with it a bit more. If I have any 
success I'll
keep you informed.

Thanks for all the insight!

Cheers,
       Steph

Bob Kline wrote:

> 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
>
> ---
> You are currently subscribed to freetds as: slewis AT thinweb.com
> To unsubscribe, forward this message to $subst('Email.Unsub')