''SELECT ... FOR UPDATE'' Example

This example demonstrates the use of SELECT ... FOR UPDATE to lock all the rows of a dynaset while it is being updated.

To run this code, copy this code into the definition section of a form and then press F5.

Sub Form_Load ()

'Declare variables

Dim OraSession As OraSession

Dim OraDatabase As OraDatabase

Dim OraDynaset As OraDynaset

Dim fld As OraField

'Create the OraSession Object.

Set OraSession = CreateObject("OracleInProcServer.XOraSession")

'Create the OraDatabase Object by opening a connection to Oracle.

Set OraDatabase = OraSession.OpenDatabase("ExampleDb",

"scott/tiger", 0&)

'Start Transaction processing before creating the dynaset

'with FOR UPDATE, or an error will occur.

OraSession.BeginTrans

'Create the OraDynaset Object

Set OraDynaset = OraDatabase.CreateDynaset("select * from emp FOR UPDATE", 0&)

' Create a field object for faster access.

' This will cause a reference to the Dynaset to be held.

Set fld = OraDynaset.Fields("sal")

'Traverse until EOF is reached, setting

'each employee's salary to zero.

Do Until OraDynaset.EOF

OraDynaset.Edit

fld.value = 0

OraDynaset.Update

OraDynaset.MoveNext

Loop

MsgBox "All salaries set to ZERO."

'When using FOR UPDATE, you must close (reduce the

' reference count to zero) the dynaset by setting it to

' Nothing, or an error will occur.

Set OraDynaset = Nothing

'You must also set fld to Nothing since it contains a

' reference to the dynaset.

Set fld = Nothing

'End Transaction processing

OraSession.CommitTrans

End Sub