How to do Row Locking
Posted: Wed 26 Nov 2008 21:12
Ok,
To summarize, I am trying to Lock a record so I can get the value of a column and then edit that value without other users grabbing the same value. To make this happen I would use the “FOR UPDATE clause in my Select Statement to lock the record (Using System.Data.OracleClient namespace).
Now I have converted the project to use your dotConnect for Oracle. How can I do the same with your product? I tried to use the “FOR UPDATE” clause and I get and error “ORA-01002: fetch out of sequence”.
Steps I Need:
1. Get the Value of the column in a table and lock it so no one else can obtain the same record or value.
2. Update the value.
3. Basically done. Release the lock and go on about our merry way.
Code I would like to Run: (VB.NET)
Dim sqlText As String = _
"Select VALUETOCHANGE, PRIMARYCOLUMN " & _
"From MYTABLE " & _
"Where PRIMARYCOLUMN = 'SomeValue' FOR UPDATE "
Dim updateText As String = _
"Update MYTABLE Set " & _
"VALUETOCHANGE= (VALUETOCHANGE +1) " & _
"Where PRIMARYCOLUMN = 'SomeValue'"
Dim value As Integer
Dim dt As New DataTable
Try
Using cn As New OracleConnection(myConnectionString)
cn.Open()
Using cm As New OracleCommand(sqlText, cn)
Using da As New OracleDataAdapter(cm)
da.Fill(dt)
value = CInt(dt.Rows(0)(2))
cm.CommandText = updateText
cm.ExecuteNonQuery()
End Using
End Using
End Using
Catch ex As Exception
Throw
End Try
Return value
To summarize, I am trying to Lock a record so I can get the value of a column and then edit that value without other users grabbing the same value. To make this happen I would use the “FOR UPDATE clause in my Select Statement to lock the record (Using System.Data.OracleClient namespace).
Now I have converted the project to use your dotConnect for Oracle. How can I do the same with your product? I tried to use the “FOR UPDATE” clause and I get and error “ORA-01002: fetch out of sequence”.
Steps I Need:
1. Get the Value of the column in a table and lock it so no one else can obtain the same record or value.
2. Update the value.
3. Basically done. Release the lock and go on about our merry way.
Code I would like to Run: (VB.NET)
Dim sqlText As String = _
"Select VALUETOCHANGE, PRIMARYCOLUMN " & _
"From MYTABLE " & _
"Where PRIMARYCOLUMN = 'SomeValue' FOR UPDATE "
Dim updateText As String = _
"Update MYTABLE Set " & _
"VALUETOCHANGE= (VALUETOCHANGE +1) " & _
"Where PRIMARYCOLUMN = 'SomeValue'"
Dim value As Integer
Dim dt As New DataTable
Try
Using cn As New OracleConnection(myConnectionString)
cn.Open()
Using cm As New OracleCommand(sqlText, cn)
Using da As New OracleDataAdapter(cm)
da.Fill(dt)
value = CInt(dt.Rows(0)(2))
cm.CommandText = updateText
cm.ExecuteNonQuery()
End Using
End Using
End Using
Catch ex As Exception
Throw
End Try
Return value