Re: updateable cursors & visibility

So INSENSITIVE => READONLY, but READONLY can not deduce INSENSITIVE, right?
But in ODBC spec. there's something about cursor like this:
------------------------------------------------------------
If set SQL_ATTR_CURSOR_SENSITIVITY to SQL_SENSITIVE,
then SQL_ATTR_CONCURRENCY must be SQL_CONCUR_LOCK, SQL_CONCUR_ROWVER, or SQL_CONCUR_VALUES, as specified by the driver. It is never set to SQL_CONCUR_READ_ONLY.
and SQL_ATTR_CURSOR_TYPE must be SQL_CURSOR_FORWARD_ONLY, SQL_CURSOR_STATIC, SQL_CURSOR_KEYSET_DRIVEN, or SQL_CURSOR_DYNAMIC, as specified by the driver.
----------------------------------------------------------------
And I can not understand why SENSITIVE cursor can not be READONLY. ??? Is this a error made by microsoft?

>Peter Eisentraut wrote:
>> Neil Conway writes:
>>
>> > 11) If an <updatability clause> of FOR UPDATE with or without a <column
>> > name list> is specified, then INSENSITIVE shall not be specified and QE
>> > shall be updateable.
>> >
>> > I'm not really sure I see the point of this restriction, though...
>>
>> If you allow updatable insensitive cursors, then you are really saying,
>> whatever happens in the rest of the world does not affect my cursor, but
>> what I do in my cursor affects the rest of the world. You can easily
>> construct some cases where this would have bizarre results. For example,
>> someone inserts a primary key into the underlying table. You don't see it
>> in the cursor, so you happily insert the same primary key. How can you
>> and when should you detect this error? Also, since the snapshot of
>> insensitive cursors is frozen when the cursor is created, would you insert
>> new rows "in the past"? It's not really workable when you think it
>> through.
>
>You bring up a very good point here I didn't realize --- that when you
>have a cursor, then do an UPDATE using the cursor information, your
>UPDATE must have current visibility, not visibility at the time of
>cursor creation, and I agree with you.
>
>I talked to Neil Conway on the phone and we discussed various options.
>One idea is to require FOR UPDATE on the cursor --- while that prevents
>other transactions from changing the cursor, it doesn't deal with the
>current transaction modifying the table outside the cursor. One idea is
>to have UPDATE/DELETE WHERE CURRENT OF behave like UPDATE/DELETE do now
>when they find a row that is locked by another transaction --- they wait
>to see if the transaction commits or aborts, then if committed they
>follow the tid to the newly updated row, check the WHERE clause to see
>if it still is satisfied, then perform the update. (Is this correct?)
>I think WHERE CURRENT OF could do the same thing --- take the tid of the
>cursor row, find the newest version of the row, wait for any active
>transaction, re-test the cursor WHERE clause, and update the row. Seems
>this would make WHERE CURRENT OF behave just like UPDATE, except it is
>getting its rows from the cursor.
>
>As far as someone inserting into the table at the same time, I don't
>know how to show that row in the cursor, but referential integrity
>constraints are going to be checked by the UPDATE, and that UPDATE has
>current visibility, so it should see any inserts that are valid. It
>doesn't seem much worse than what we have now.
>

Regards!

　　　　　　　　Han
　　　　　　　　zhouhanok(at)vip(dot)sina(dot)com
　　　　　　　　　　2003-03-26