OLE DB recordset - pessimistic locking

[Michal Svoboda]

Hello!

I need help with this stuff.
My consumer uses SQLOLEDB provider for access to database (SQL sever 2000)
and uses OLE DB for reading a data (server side cursors, IRowset and next
interfaces).
I need implement pessimistic locking, record by record. This is same
functionality like implements ADO (see example) - when current row is
editing, for others users (sessions) is current row "read only". But I need
implement it through OLE DB, no ADO.

This example is in Visual Basic (copy from MSDN), but I use Visual C++ 6.0.

Sub ADOUpdateRecord()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
' Open the connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=.\NorthWind.mdb;"
' Open the recordset
rst.Open "SELECT * FROM Customers WHERE CustomerId = 'LAZYK'", cnn,
adOpenKeyset, adLockPessimistic
' Update the Contact name of the
' first record
' Now is current row lock until calling Update
rst.Fields("ContactName").Value = "New Name"
' Save the changes you made to the
' current record in the Recordset
rst.Update
' Close the recordset
rst.Close
End Sub

Thank you.

Michal Svoboda

[relaxin]

I don't think it's possible to allow a record to read as read only once
someone has started modifying, unless the other users are doing "dirty
reads".

But to somewhat get you some help with your question, you would set the
isolation level thru ITransactionLocal :: ITransaction and set the isolation
level to one of the following:
Value Description
ISOLATIONLEVEL_UNSPECIFIED Applicable only to
ITransactionJoin::JoinTransaction. Invalid for ITransactionLocal or for
setting isolation level while in auto-commit mode.
ISOLATIONLEVEL_CHAOS Cannot overwrite the dirty data of other
transactions at higher isolation levels.
ISOLATIONLEVEL_READUNCOMMITTED Read Uncommitted.
ISOLATIONLEVEL_BROWSE Synonym for ISOLATIONLEVEL_READUNCOMMITTED.
ISOLATIONLEVEL_READCOMMITTED Read Committed.
ISOLATIONLEVEL_CURSORSTABILITY Synonym for
ISOLATIONLEVEL_READCOMMITTED.
ISOLATIONLEVEL_REPEATABLEREAD Repeatable Read.
ISOLATIONLEVEL_SERIALIZABLE Serializable.
ISOLATIONLEVEL_ISOLATED Synonym for ISOLATIONLEVEL_SERIALIZABLE

Hope this helps some.

Thanks

"Michal Svoboda" <svo...@stormware.cz> wrote in message
news:eY00lutmCHA.2188@TK2MSFTNGP09...

[Michal Svoboda]

Thanks.

But transactions are not a good solution.

Sample:

1. Open rowset 1

2. Fetch current row from rowset 1

3. Start transaction // OK. Current row form
rowset 1 is locked

4. Open rowset 2

5. Fetch row from rowset 2 // Bad - row from rowset 2 is locked
too

6. rowset 1 Set data

7. Commit transaction

I don't want to lock other data from other rowsets - I need lock only
current row from rowset 1.

Michal Svoboda.

"relaxin" <m...@yourhouse.com> píąe v diskusním příspěvku
news:#qbjM2vmCHA.1356@tkmsftngp04...

[relaxin]

But isn't that what pessimistic locking is all about?

"Michal Svoboda" <svo...@stormware.cz> wrote in message

news:#pgueY7mCHA.952@TK2MSFTNGP12...

[Michal Svoboda]

No.

I want to lock edited data only.

In sample - row from rowset 1 is edited; row from rowset 2 is fetched only.

No transaction please.

ADO implements pessimistic locking. But I cannot use ADO. I must use OLE DB.

Michal Svoboda.

"relaxin" <m...@yourhouse.com> píąe v diskusním příspěvku

news:OqwdWk8mCHA.2400@TK2MSFTNGP11...

[Songnian Qian]

According to MSDN document "Mapping ADO Methods to OLE DB
Interfaces", pessimistic or optimistic locking are
implemented by set properties DBPROP_IRowsetChange,
DBPROP_UPDATABILITY,
DBPROP_IrowsetUpdate.
However property values are not documented in any MSDN
article. I believe ADO does more then set those properties.

The way I tried is use ADO to open rowset with pessimistic
locking, then use ADORecordsetConstruction interface to
attach ADO recordset to OLE DB Consumer CRowset. You may
need to move record in ADO as well.

Good luck!

Songnian

>.
>