A T-SQL CURSOR issue, need help

Hi there,
I have a very strange problem when using a t-sql cursor, appritiated if someone could explain why.

When executing the SQL string to fill the cursor, everything works fine, nothin wrong so far

But when executing the whole query (traversing the sql string result) I experience the following:

First the code for the cursor

USE pubs
GO

-- Declare the variables to store the values returned by FETCH.
DECLARE @.au_lname varchar(40), @.au_fname varchar(20)
DECLARE @.iCounter smallint

DECLARE authors_cursor CURSOR FOR
SELECT au_lname, au_fname FROM authors
WHERE au_lname LIKE "B%"
ORDER BY au_lname, au_fname

SET @.iCounter = 1

OPEN authors_cursor

-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.

FETCH NEXT FROM authors_cursor
INTO @.au_lname, @.au_fname

-- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
WHILE @.@.FETCH_STATUS = 0
BEGIN

-- Concatenate and display the current values in the variables.
PRINT "No: " + CAST(@.iCounter AS varchar(3)) + " Author: " + @.au_fname + " " + @.au_lname

SET @.iCounter = iCounter + 1

-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM authors_cursor
INTO @.au_lname, @.au_fname
END

CLOSE authors_cursor
DEALLOCATE authors_cursor
GO

As I said the SQL query to fill the cursor works fine, but the result from the whole query above is this!?

No: 1 Author: Abraham Bennet
No: 2 Author: Reginald Blotchet-Halls
No: 3 Author: Abraham Bennet
No: 4 Author: Reginald Blotchet-Halls

No: 6 Author: Reginald Blotchet-Halls
No: 7 Author: Abraham Bennet
No: 8 Author: Reginald Blotchet-Halls

No: 10 Author: Abraham Bennet
No: 11 Author: Reginald Blotchet-Halls
No: 12 Author: Abraham Bennet
No: 13 Author: Reginald Blotchet-Halls

It seams that the query omits som fetches, does anybody know why?

In the BOL it says that WHILE loop only executes if the latest fetches succeeds, but why shouldnt it, the SQL query is ok!?

appritiate some help,
thanx in advance.I ran your code. Could not re-produce the error. Send me the data too. I assume you have fixed some sytax errors in your code.|||It was easier than I thougt, the error I mean.

Yes, I to found some syntax error after submitting the post, but the error was that some fields in my rowset containded a null value, and when the code tried to print that value it failed, which meant that the specific row never showed up.

I fixed by setting the vaule to 0

A fix for "SelectMethod= cursor"?

I haven't seen this mentioned anywhere, is this covered under the
"Update Performance" and/or "Updateable recordsets" headings? Having
to include that in the connection string greatly reduced our
performance in previous attempts to use SQL Server from our Java
application. If so I will start down the path of trying to convince
the powers that be of the need to switch from Oracle to SQL Server.
The main reason I got shot down before was performance of the free
drivers, and cost of the not-free drivers with good performance.
Thanks,
Ed
EdGmail wrote:
> I haven't seen this mentioned anywhere, is this covered under the
> "Update Performance" and/or "Updateable recordsets" headings? Having
> to include that in the connection string greatly reduced our
> performance in previous attempts to use SQL Server from our Java
> application. If so I will start down the path of trying to convince
> the powers that be of the need to switch from Oracle to SQL Server.
> The main reason I got shot down before was performance of the free
> drivers, and cost of the not-free drivers with good performance.
> Thanks,
> Ed
>
There is no way around the selectMethod=cursor setting if you want
transactional behavior, for example. Also, even with selectMethod=direct
the old MS driver will cache pretty much everything to memory.
Your choices are the new MS driver for SQL Server 2005 (a copy of the
JNetDirect driver, which is currently in beta and still works --
although undocumented -- with older versions of SQL Server) or jTDS (you
would probably be surprised that the performance and stability of jTDS
is at least comparable to that of the commercial drivers).
Disclaimer: I am a jTDS developer.
Alin,
The jTDS Project.
|||I remember now why the "SelectMethod=Cursor" was causing a problem. It
wasn't so much performance as it was causing exceptions when we tried
to do our initial system load with Ant and Torque. I never researched
it thoroughly, but apparently Torque (or ant) spawns threads which
attempt to clone the connection, which the original MS driver choked
on. We tried the JNetDirect driver and that worked OK w/o
"SelectMethod", but we (ok, managment) just didn't want to cough up the
dough for it. Is that particular problem addressed by jTDS or the new
MS driver?
Thanks,
Ed
|||EdGmail wrote:
> Is that particular problem addressed by jTDS or the new MS driver?
Actually that's only a problem of the DataDirect driver and its
derivatives, including the old MS driver. It's not a limitation of SQL
Server, so the other drivers don't have this issue.
Alin,
The jTDS Project.