Cursor Behaviour in 6.5/7.0

Cursor Behaviour in 6.5/7.0

Post by n.. » Thu, 30 Mar 2000 04:00:00



Hello All,
Greetings...
Please create the Stored Proc below in 6.5 and 7.0 in the PUBS Database
and see the Result Sets. I found the problem when was migrating one of
the stored procs in our 6.5 DB.
***********STORED PROC BEGIN **********************
drop proc nkcurTest
go
create proc nkcurTest
as




print "****SELECT VARIABLE assigned before DECALRE CURSOR****"

declare curtest cursor for
select au_id , au_lname from authors

open curtest


begin


end
close curtest
deallocate curtest

print ""
print "****SELECT VARIABLE assigned after DECALRE CURSOR before, OPEN
CURSOR****"
declare curtest1 cursor for
select au_id , au_lname from authors


open curtest1


begin


end
close curtest1
deallocate curtest1
go
***********STORED PROC END **********************
I would like to know if any of u have a similar problem and the
possible resolution to this. IS it a BUG or is this how works in SQL
7.0???
Any thoughts would be helpful.
Thanks
NK

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Cursor Behaviour in 6.5/7.0

Post by Dennis Slobodsk » Sat, 01 Apr 2000 04:00:00



> Please create the Stored Proc below in 6.5 and 7.0 in the PUBS Database
> and see the Result Sets. I found the problem when was migrating one of
> the stored procs in our 6.5 DB.
<Skipped>
> I would like to know if any of u have a similar problem and the
> possible resolution to this. IS it a BUG or is this how works in SQL
> 7.0???

Yes, I had discover the similar problem with SQL 7 (evalution version). It's
seems SQL 7 substitute variable with it value on creation time, and SQL 6.5
does on opening. So you must deallocate and re-create cursor every time when
you want to use it with another variable value, although in SQL 6.5 closing
and re-opening cursor was enough. I don't know is it bug or not, though in
migration guides no any mention of this problem.

 
 
 

Cursor Behaviour in 6.5/7.0

Post by Erland Sommarsk » Sat, 01 Apr 2000 04:00:00



>Yes, I had discover the similar problem with SQL 7 (evalution version).
>It's seems SQL 7 substitute variable with it value on creation time, and
>SQL 6.5 does on opening. So you must deallocate and re-create cursor every
>time when you want to use it with another variable value, although in SQL
>6.5 closing and re-opening cursor was enough. I don't know is it bug or
>not, though in migration guides no any mention of this problem.

Huh? If this is a bug, this must be in 6.5.

A few years ago, I wrote a fairly elaborate procedure, and part of
the tricks included closing the cursor, repopulating a help table,
and then start the cursor anew. And I found that CLOSE and OPEN
was not enough. I needed to DEALLOCATE and run the DECLARE statement
again.

But this was hardly in 6.5, but 6.0.

--

This is an incomplete mess.

 
 
 

1. different behaviour of string concatenation in SQL-server 7.0 com pared to 6.5

Hi,

I have a problem with the following stored procedure:

CREATE proc sp_enc_tin






begin


/* the original procedure contains
   some extra code here which
   performs arithmetics with the

   out because it should not be
   published */





end

The problem is this: when I compile this on SQL-Server 6.5 all works
well. The expected string is returned. When I compile it on SQL-Server
7.0 the returned string contains only the first character of the
expected string. First I thought it had something to do with the option
'concat null yields null'. Although it had some influence changing that
it didn't solve my problem. I solved the problem by declaring the output

My question(s): I couldn't find anything in the documentation of
knowledge base about different handling of chars, varchars or
concatenations of them apart from the discussion about 'concat null
yields null'. Does anyone know what causes this behaviour and if it's
possible to make SQL-Server 7.0 behave exactly the same as SQL-Server
6.5 at this point?

Thanx!

Theo

2. Is Number function?

3. Dynamic SQL and Cursors for 6.5 or 7.0

4. rebuilding master db error

5. Cursor Problems (SQL 6.5,7.0)

6. SUMMARY: Looking for a software kind of "report writer"

7. 6.5 to 7.0 Upgrade: 7.0 doesn't contail all objects from 6.5

8. Using an OR in a query?

9. Conversion from 6.5 to 7.0 - thinks 6.5 is 7.0

10. ADO to SQL 6.5 Strange Behaviour

11. SQL 6.5 - Incorrect divison results - Anomalous behaviour

12. Strange behaviour with SQL 6.5 SP5A install

13. Change in behaviour SQL Server 6.5