注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

中吴南顾惟一笑

成功法则就是那19个字

 
 
 

日志

 
 

About Cursor Mode - Row-Oriented Processing  

2010-07-16 14:22:07|  分类: dbms |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
A cursor keeps track of the position in the result set, and multiple operations can be performed row by row against a result set(position update/delete).

Cursor semantics are dependent on:
    1).Membership sensitivity
    2).Value sensitivity
    3).Scrollability (forward only or scrollable)
    4).Updatability (read-only or updateable)
    
    Membership sensitivity:
        Insensitive: result rows are fixed at open; no changes after result set is computed
        Repeatable:  result rows will not change once fetched
        Sensitive:   result rows will change with respect to concurrent inserts, deletes, and updates

    Value-sensitivity:
        Insensitive: data values will not change once the row has been fetched
        Sensitive:   data values will change with respect to concurrent updates

Cursors in ODBC
a).Forward-Only Cursor - serial cursor(the default cursor of ODBC)
    Can fetch only the next row in sequence(from beginning to end) from the result set
    Changes made by other cursors in this transaction or in other transactions will be visible only if they occur on rows ahead of the cursor.   
    To go to the previous row, close and then reopen to fetch to the position

b).Scrollable Cursor
    The cursor can be moved to any location
    Should scrollable cursor to a previously visited row reflect the changes that have occurred?
    Because only a set of rows that meet specific criteria are defined as result set.
    i).Static Cursor
      Changes made by this cursor are visible. Changes form other sources are not visible.
        - values and order usually do not reflect such a change occurred
        - whether or not reflect the own update, that value is determined by the SQL_STATIC_SENSITIVITY
        - If you reflect own update, and you can find out the row information SQL_ROW_UPDATED, SQL_ROW_DELETED, SQL_ROW_ADDED
        - other update does not reflect
         
    ii).Dynamic Cursor
        All inserts/updates/deletions, and changes in recordset order are visible. If the isolation level is dirty read, then uncommitted changes are visible. Otherwise, only committed changes are visible
        - Since the cursor is open, membership, order, value should reflect all the changes that occurred
        - Reflects own update/insert/delete and other update/insert/delete
        - Reflecting changes behaviour is determined by SQL_ATTR_TXN_ISOLATION attribute
        - The information on Row SQL_ROW_UPDATED, SQL_ROW_ADDED can be obtained, SQL_ROW_DELETED doesn't occur (not seen in result set)   

    iii).Keyset-Driven Cursor
    Keyset-driven cursors have the membership and order of rows in the result set fixed when the cursor is opened. Changes to nonkey columns are visible through the cursor.
        Updates from any source are visible. Inserts from sources outside this cursor are not visible(there is no key for them in the keyset).
        Inserts form this cursor appear at the bottom of the recordset. Deletions from any source are visible. Changes in row order are not visible.
        If the isolation level is dirty read, then uncommitted updates and deletions are visible; otherwise only committed updates and deletions are visible.


Call SQLSetStmtAttr() to set cursor characteristics, it implicitly decide the cursor type
    SQL_ATTR_CONCURRENCY
    SQL_ATTR_CURSOR_TYPE
    or
    SQL_ATTR_CURSOR_SCROLLABLE
    SQL_ATTR_CURSOR_SENSITIVITY
   
SQLGetInfo()
    SQL_CURSOR_SENSITIVITY
    SQL_SCROLL_OPTIONS



Cursor and Transaction
    Committing or rolling back a transaction, either by explicitly calling SQLEndTran or by operating in autocommit mode, causes some data sources to close all the cursors on all statements on a connection.
    For more information, see the SQL_CURSOR_COMMIT_BEHAVIOR and SQL_CURSOR_ROLLBACK_BEHAVIOR attributes in the SQLGetInfo function description.
  评论这张
 
阅读(111)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017