Oracle transaction rollback problem

[dnhelp]

I have problem with transaction. When I try to rollback transaction
commit happend and record is updated. Can anybody tell me, where I
made a mistake.

Thanx, S.


Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types

private sub mWriteOnOra()
Dim inFile As System.IO.FileStream
Dim binaryData() As Byte
inFile = New System.IO.FileStream(p_datoteka, _
System.IO.FileMode.Open, _
System.IO.FileAccess.Read)
ReDim binaryData(inFile.Length)
Dim bytesRead As Long = inFile.Read(binaryData, _
0, _
inFile.Length)
inFile.Close()
Dim cnStr As String = "Data Source=Oracle8i;User ID=" & gsUser
& ";Password=" & gsPwd & ";Data Source=" & gsDs & ";"
Dim sErr As String
Dim cn As OracleConnection
Dim myTrans As OracleTransaction

Dim cmd As New OracleCommand("SP_LOAD", cn)

cmd.CommandType = CommandType.StoredProcedure
With cmd.Parameters
.Add("BLOB_LOAD", OracleDbType.Blob, binaryData.Length,
ParameterDirection.Input).Value = binaryData
End With
Try
cmd.ExecuteNonQuery()
myTrans.Rollback()
' file shouldn't be in the table, but it is there

' myTrans.Commit()
Catch ex As Exception
myTrans.Rollback()
DoLog(1, ex.Message)
Finally
If cn.State <> ConnectionState.Closed Then cn.Close()
End Try
end sub

 

[David Browne]

I have problem with transaction. When I try to rollback transaction
commit happend and record is updated. Can anybody tell me, where I
made a mistake.

Thanx, S.

You must call Connection.BeginTransaction to start the transaction:

Dim cn As new OracleConnection(cnStr)
Dim myTrans As OracleTransaction = cn.BeginTransaction()

Also make sure your stored procedure doesn't contain a commit.

David