Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Posts
    107

    Question Unanswered: Accessing Global Cursor

    hi friends,

    Here is the stored procedures that I used.

    ------------------------------------------------------------------------------------
    create procedure globalCursor
    AS
    DECLARE abc CURSOR GLOBAL FOR
    select * from sales
    OPEN abc

    create procedure globalCursorTest
    AS
    DECLARE @sdate datetime
    DECLARE @sperson varchar(15)
    DECLARE @sregion varchar(15)
    DECLARE @sales int
    EXECUTE globalCursor
    FETCH NEXT FROM abc INTO @sdate, @sperson, @sregion, @sales
    print @sdate
    print @sperson
    print @sregion
    print @sales
    ------------------------------------------------------------------------------------

    When I execute globalCursorTest using SQL Query Analyser, it says

    ------------------------------------------------------------------------------------
    Server: Msg 16915, Level 16, State 1, Procedure globalCursor, Line 4
    A cursor with the name 'abc' already exists.
    Server: Msg 16905, Level 16, State 1, Procedure globalCursor, Line 5
    The cursor is already open.
    ------------------------------------------------------------------------------------

    how to solve this? or in other words, how to simply create the procedure in the database without executing it, as i can see the execution of the first procedure globalCursor causes this problem.

    Jake

  2. #2
    Join Date
    Feb 2004
    Posts
    492

    Re: Accessing Global Cursor

    Looks like the abc cursor is not closed/deallocated. Does either one of the procedures perform these actions?

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Ummm....

    Do you have an Oracle background?

    To my knowledge it doesn't work that way, though I'll go test it out...

    And yes, as Kaiowas points out you need to

    CLOSE ABC
    DEALLOCATE ABC

    But still, it looks like you're trying to mimic reference CURSORs like Oracle has....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Feb 2004
    Posts
    107
    hi brett,

    I'm new to database and doing DB2 to SQL server migration tool project.
    In DB2, one procedure can access the cursors opened by another procedure, after calling it. The called procedure will not return the cursor and it will not even have the cursor as the output parameter. But it will just open the cursor at the end of the procedure and the cursor is specially declared with the clause 'WITH RETURN TO CALLER/CLIENT'.

    The calling procedure just allocate cursors to the result sets opened by the called procedure, in the order.

    I thought I can achieve this using Global cursor in sql server, but i'm not sure. That's what I am trying.

    Yes, I agree that I missed to put CLOSE abc & DEALLOCATE abc at the end of the second procedure.
    but that will not solve my problem.
    I like to know how to just create the procedure in the sql server database without executing it, as i can guess the cause of the problem 'cursor already opened' is due to the execution of the first procedure while I try to create it in the database.

    Appreciate your he
    Jake

    Originally posted by Brett Kaiser
    Ummm....

    Do you have an Oracle background?

    To my knowledge it doesn't work that way, though I'll go test it out...

    And yes, as Kaiowas points out you need to

    CLOSE ABC
    DEALLOCATE ABC

    But still, it looks like you're trying to mimic reference CURSORs like Oracle has....

  5. #5
    Join Date
    Feb 2004
    Posts
    107
    anybody know about this.....

    Originally posted by Jake K
    hi brett,

    I'm new to database and doing DB2 to SQL server migration tool project.
    In DB2, one procedure can access the cursors opened by another procedure, after calling it. The called procedure will not return the cursor and it will not even have the cursor as the output parameter. But it will just open the cursor at the end of the procedure and the cursor is specially declared with the clause 'WITH RETURN TO CALLER/CLIENT'.

    The calling procedure just allocate cursors to the result sets opened by the called procedure, in the order.

    I thought I can achieve this using Global cursor in sql server, but i'm not sure. That's what I am trying.

    Yes, I agree that I missed to put CLOSE abc & DEALLOCATE abc at the end of the second procedure.
    but that will not solve my problem.
    I like to know how to just create the procedure in the sql server database without executing it, as i can guess the cause of the problem 'cursor already opened' is due to the execution of the first procedure while I try to create it in the database.

    Appreciate your he
    Jake

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "In DB2, one procedure can access the cursors opened by another procedure, after calling it."

    Sounds like a recipe for scope disaster to me. As if cursors weren't bad enougth to begin with.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I guess my best suggestion would be to rewrite your cursor procedure as a table function.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Feb 2004
    Posts
    107
    hi,

    it's definitely not scope disaster!!! By default, the cursors opened in a procedure could not be accessed from another procedure. If one wants this kind of feature, the cursor has to be specially declared with the option "WITH RETURN TO CALLER/CLIENT". It's like Sequel's local & global cursor concept. In global cursor, the cursor can be accessed from outside where it is declared.

    Jake

    Originally posted by blindman
    "In DB2, one procedure can access the cursors opened by another procedure, after calling it."

    Sounds like a recipe for scope disaster to me. As if cursors weren't bad enougth to begin with.

  9. #9
    Join Date
    Feb 2004
    Posts
    107
    thanks for your suggestion. as of now, i don't know about table function. I will try it out....
    but i have another way of achieving this. the procedure that i attached in the starting mail is working fine, of course after including close & disallocate stmts at the end of the second procedure, globalCursorTest.
    previously i used SQL Query Analyser GUI which will compile & execute the procedure at one shot. Thus the globalCursor procedure executed twice, which caused the 'cursor already opened' error.
    As I mentioned in my earlier mails, i search for a mechanism which will only compile & create the procedure into the db without executing it. I find isql command line tool creates the procedure into the db without executing it.
    After creating both the procedures, i executed second procedure, globalCursorTest. It works fine.

    friends, Thanks for your time.

    Jake

    Originally posted by blindman
    I guess my best suggestion would be to rewrite your cursor procedure as a table function.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •