samuel
10/23/2002 10:21:00 PM
I'm using ODBC.NET to access an INFORMIX 7.31 Database.
The server has the MDAC 2.7 and the Informix Driver is
3.81 (that comes with IBM/Informix Client SDK 2.8)
The system performs all data access tasks without any
problem, BUT when executing transactions I have the
following scenario:
1. When 2 different activities are performed (for
instance: 1 INSERT and 1 UPDATE) there is no problem.
2. When 2 or more activites of the same type (for
instance UPDATE) are executed, the systems hangs.
The same code performs without any problem when using
OLEDB.NET (but for other reasons we needed to "downgrade"
to ODBC.NET).
Can anybody help me with this issue?
Samuel
-----------------------------------------
CODE EXECUTES WITHOUT PROBLEM
-----------------------------------------
TRANSACTION:
1. UPDATE existing data in a table
2. INSERT new data into a 2nd table
-----------------------------------------
dim _ConE21 As OdbcConnection = New OdbcConnection()
Dim trn_req As OdbcTransaction
Dim cmd_req As New OdbcCommand()
' Open the Connection
_ConE21.ConnectionString = _str_connection
_ConE21.Open()
' Start a local transaction
trn_req = _ConE21.BeginTransaction
(IsolationLevel.ReadCommitted)
' Assign transaction object for a pending local
transaction
cmd_req.Connection = _ConE21
cmd_req.Transaction = trn_req
Try
'
' update the REQUISITION_HEADER record
'
str_query_UPD_ReqHdr = "UPDATE .... "
cmd_req.CommandText = str_query_UPD_ReqHdr
cmd_req.Transaction = trn_req
cmd_req.ExecuteNonQuery()
cmd_req.Parameters.Clear()
'
' create an ENTRY on the REQUISITION_APPROVAL table
'
str_query_UPD_ReqHdr = "INSERT ....."
cmd_req.CommandText = str_query_UPD_ReqHdr
cmd_req.Transaction = trn_req
cmd_req.ExecuteNonQuery()
cmd_req.Parameters.Clear()
'
' commit the transaction
'
trn_req.Commit()
Catch err As System.Data.OleDb.OleDbException
'
' Rollback the transaction
'
trn_req.Rollback()
Finally
'
' close the connection
'
trn_req = Nothing
_ConE21.close()
End Try
-----------------------------------------
CODE EXECUTES WITH PROBLEM
-----------------------------------------
TRANSACTION:
1. UPDATE data in a table (REQUISITION_ITEM)
2. UPDATE data in a table (REQUISITION_HEADER)
-----------------------------------------
dim _ConE21 As OdbcConnection = New OdbcConnection()
Dim trn_req As OdbcTransaction
Dim cmd_req As New OdbcCommand()
' Open the Connection
_ConE21.ConnectionString = _str_connection
_ConE21.Open()
' Start a local transaction
trn_req = _ConE21.BeginTransaction
(IsolationLevel.ReadCommitted)
' Assign transaction object for a pending local
transaction
cmd_req.Connection = _ConE21
cmd_req.Transaction = trn_req
Try
'
' update the REQUISITION_ITEM record
'
str_query_UPD_ReqItem = "UPDATE .... "
cmd_req.CommandText = str_query_UPD_ReqItem
cmd_req.Transaction = trn_req
cmd_req.ExecuteNonQuery()
cmd_req.Parameters.Clear()
'
' update the REQUISITION_HEADER record
'
str_query_UPD_ReqHdr = "UPDATE ....."
cmd_req.CommandText = str_query_UPD_ReqHdr
cmd_req.Transaction = trn_req
cmd_req.ExecuteNonQuery()
cmd_req.Parameters.Clear()
'
' commit the transaction
'
trn_req.Commit()
Catch err As System.Data.OleDb.OleDbException
'
' Rollback the transaction
'
trn_req.Rollback()
Finally
'
' close the connection
'
trn_req = Nothing
_ConE21.close()
End Try