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
