Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2006
    Location
    Czech Republic
    Posts
    82

    Unanswered: Did you experienced any problems with cursors

    Hello,

    can I secure use cursor in Sybase? Are they some problems I can met... I heard that I must not update table that I have cursor - otherwise I can experience unexpected behaviour... Is it true? Are there any other failures as this?

    Thanks for hints

    David

  2. #2
    Join Date
    Mar 2007
    Posts
    25
    Hi Musil David,

    It is possible to update the table that we have in cursor provided the cursor is declared as an updatable cursor.

    declare cursor_name cursor
    for select_statement
    [for {read only | update [of column_name_list]}]

    Make sure the table being updated has a unique index. If it does not, Adaptive Server rejects the declare cursor statement.

    Also in the update statement we need to use the "where current of <cursor name>" clause.

    Regards,
    Poornima.
    Last edited by J.Poornima; 10-15-07 at 04:15.

  3. #3
    Join Date
    Feb 2007
    Location
    Chennai, TN
    Posts
    68
    Hi,
    There are some disadvantages while updating through cursors

    1) If there are more number of datas to be updated, performance will be an issue to be considered.
    2) There is a chance for hanging of server if more datas are to be updated.
    3) Before updating the data through cursors, the table has to be locked. There is a chance that some one else can change the data while the data is updated through cursors (as it is time consuming).
    4) Dirty read or update should not happen.

  4. #4
    Join Date
    Sep 2006
    Location
    Czech Republic
    Posts
    82
    According to your answers, let s have:

    declare myCursor cursor
    for select id from TABLE where ....
    for read only

    declare @id numeric(9,0)

    open myCursor

    fetch myCursor into @id

    while @@sqlstatus = 0
    begin
    update TABLE set some_column = some_data where id = @id

    fetch biz_book into @id
    end
    close myCursor
    deallocate myCursoer


    So questions:
    1) is this use of cursor O.K, or should I declare cursor for update? Or is there better solution (temporary/permanent tables walking row by row?)
    2) is close cursor and deallocate cursor mandatory or Sybase manage this itself?

  5. #5
    Join Date
    Mar 2007
    Posts
    25
    Hi,

    To perform a row by row processing we can also use temporary tables.
    Create a temporary table with a identity column and the columns in the select list of the cursor.

    INSERT INTO #temp (Column list)
    <Select query of the cursor>

    SELECT@num_rows = Count(*) FROM #temp

    SELECT @row_id = 1

    WHILE (@row_id <= @num_rows)
    BEGIN

    Select from the #temp table where <identity column> = @row_id

    All our logic goes here

    SELECT @row_id = @row_id + 1

    END

    Hope the above helps.

    Regards,
    Poornima.

  6. #6
    Join Date
    Mar 2007
    Posts
    25
    Hi,

    If in case you want to use only a cursor for row by row processing and also you want to perform updates on the cursor table then you need to declare the cursor like this

    declare cursor_name cursor
    for select_statement
    for update [of column_name_list]

    Regards,
    Poornima.

  7. #7
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    You should not do row by row processing, it is slow
    Rather change your update to be a join with your query

    This is false:
    Quote Originally Posted by J.Poornima
    Make sure the table being updated has a unique index. If it does not, Adaptive Server rejects the declare cursor statement.

  8. #8
    Join Date
    Sep 2006
    Location
    Czech Republic
    Posts
    82
    Quote Originally Posted by pdreyer
    You should not do row by row processing, it is slow
    Rather change your update to be a join with your query.
    Yes, I know. But sometimes you cannot avoid row processing e.g. if data rows from table are input params for another stored procedure to call...

    As I experienced cursor run a bit faster then row by row processing through temporary table...

Posting Permissions

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