Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Unanswered: sqlstatus when fetching from cursor

    I'm new to Sybase - have been on it for only 4 hours .. So, excuse my ignorance ;-)

    Here's the scenario: I want to update records in a table by declaring a cursor .. Things work as I expect if I the table already exisits ... If I create and populate the table within the procedure and then declare the cursor, only one record is fetched(and updated) ..

    I would appreciate any guidance ..

    TIA for your response

    Sathyaram

    Here's the sample of what I do :

    Procedure Creation:

    create procedure n006104t2_2
    as
    declare @t1 int
    declare @t2 int

    create table #t1(i int,j int)
    insert into #t1 values(1,0)
    insert into #t1 values(2,0)
    insert into #t1 values(3,0)
    declare c1 cursor for select i,j from #t1
    open c1
    fetch c1 into @t1,@t2
    while (@@sqlstatus !=2 )
    begin
    select 't1,t2',@t1,@t2
    update #t1 set j=@t1 where current of c1
    fetch c1 into @t1,@t2
    end
    select 'sqlstatus',@@sqlstatus
    close c1
    select * from #t1

    Executing using :

    exec n006104t2_2

    In this proc, only one record is updated ... In other words, during the second fetch , the sqlstatus returned is 2 ...


    whereas if I create the tables beforehand, everything seems to work fine, ie, all records are updated

    Creating table :

    create table #t1(i int,j int)
    insert into #t1 values(1,0)
    insert into #t1 values(2,0)
    insert into #t1 values(3,0)

    Stored Proc defn:

    create procedure n006104t2_1
    as
    declare @t1 int
    declare @t2 int
    declare c1 cursor for select i,j from #t1
    open c1
    fetch c1 into @t1,@t2
    while (@@sqlstatus !=2 )
    begin
    select 't1,t2',@t1,@t2
    update #t1 set j=@t1 where current of c1
    fetch c1 into @t1,@t2
    end
    select 'sqlstatus',@@sqlstatus
    close c1
    select * from #t1

    Executing using :
    exec n006104t2_1
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Are you sure? The posted code produce the same results for me
    3 rows updated in both cases
    What version of Sybase are you running?

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Thanks for your response

    Yeh, sure .. I have the problem

    Here's the version info :

    select @@version


    column1
    -----------------------------------------------------------------------------------------------------------------------
    Adaptive Server Enterprise/12.5.0.3/EBF 11442 ESD#4/P/Sun_svr4/OS 5.8/rel12503/1939/64-bit/FBO/Sun Sep 21 05:21:37 2003


    ---

    I tried different things and the following is a 'expanded' procedure ..

    Here, with the 'create unique index' commented out, the procedure doesn't pass through "'I''m here' " ... If I create the index, it works as expected ..

    Creating the unique index is a work around, but not happy to do it ..

    Any clues ?

    Thanks

    Sathyaram
    Code:
    create procedure n006104t2_3
    as
    declare @t1 int
    declare @t2 int
    create table #t1(i int,j int)
    insert into #t1 values(1,0)
    insert into #t1 values(2,0) 
    insert into #t1 values(3,0) 
    --create unique index ind1 on #t1(i)
    declare c1 cursor for select i,j from #t1  
    open c1
    fetch c1 into @t1,@t2 
    if (@@sqlstatus != 2) 
    begin 
    select 'sqlstatus',@@sqlstatus
    select 't1,t2',@t1,@t2
    update #t1 set j=@t1 where current of c1 
    select 'update sqlstatus',@@sqlstatus
    fetch c1 into @t1,@t2 
    if (@@sqlstatus != 2 ) 
    begin 
    select 'I''m here' 
    select 'sqlstatus',@@sqlstatus
    select 't1,t2',@t1,@t2
    update #t1 set j=@t1 where current of c1 
    fetch c1 into @t1,@t2 
    select 'sqlstatus',@@sqlstatus
    select 't1,t2',@t1,@t2
    update #t1 set j=@t1 where current of c1 
    end 
    end
    select * from #t1
    close c1 
    drop table #t1
    The output is like

    Code:
     Warnings: ---> 
       W (1): 010P4: An output parameter was received and ignored.
              <--- 
     1 record(s) affected 
     
     1 record(s) affected 
     
     1 record(s) affected 
     
     1 record(s) affected 
     column1     column2    
     ----------  ---------- 
     sqlstatus   0          
     1 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms] 
     column1     column2     column3    
     ----------  ----------  ---------- 
     t1,t2       1           0          
     1 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms] 
     
     1 record(s) affected 
     column1           column2    
     ----------------  ---------- 
     update sqlstatus  0          
     1 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms] 
     
     0 record(s) affected 
     i     j    
     ----  ---- 
     2     0    
     3     0    
     1     1    
     3 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms] 
     [Executed: 01/08/06 15:16:26 BST ] [Execution: 20/ms]
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Looks like a bug to me. Got the same problem as you on an old ASE 12.0
    No problem however on ASE 12.5.3 or ASE 15.0
    Do you really need to use a cursor? You should really try and avoid it even if it makes the query more complex.

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Thanks for your time pdreyer ..

    Found the bug documented in

    http://search.sybase.com/kbx/solvedc...umber=10969113

    The workaround recommended is to define the cursor explicitly as 'for update' and create an unique index for the underlying table.

    And, Thanks for the suggestion on cursors. I am with you on the usage of cursors. But for the business logic of this SP, it cannot be done in a single statement. A good thing is the number of records to be processed in the cursor is only a few hundred.

    Thanks again for helping with this one.


    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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