Description:
hello,
i have an error: nested transaction
i read some bugs reports but not fix
in VB.NET
i create an object MysqlConnection with constructor, methods and property
in this object, constructor new create object and open connection.
many methods like select,update,insert,multiselect....
so i use this object during the entire session. close connection when i quit the application
in this application, i use select to read tables to extract data to fill a form
and i use button validation to save data into tables of mysql. During this phase, i use transaction (default config):
Public Sub MySQL_update(ByVal myQuery As String, ByVal WaitingReturn As Boolean, ByVal ParamArray myParameters() As String)
_datatable = New System.Data.DataTable
myQuery = "SET AUTOCOMMIT=0;" & myQuery
_MySQL_command = New Global.MySql.Data.MySqlClient.MySqlCommand(myQuery, _mySQL_Connection)
_MySQL_adapter.UpdateCommand = _MySQL_command
_MySQL_transaction = _MySQL_command.Connection.BeginTransaction()
_MySQL_adapter.UpdateCommand.Transaction = _MySQL_transaction
If myParameters.Length > 0 Then
For i As Integer = 0 To myParameters.Length - 1 Step 2
If myParameters(i + 1).ToString = "Null" Then
_MySQL_adapter.UpdateCommand.Parameters.AddWith(myParameters(i), DBNull.Value)
Else
_MySQL_adapter.UpdateCommand.Parameters.AddWith(myParameters(i), myParameters(i + 1))
End If
Next
End If
If WaitingReturn Then
Try
_MySQL_adapter.Fill(_datatable)
_MySQL_transaction.Commit()
Catch e As Global.MySql.Data.MySqlClient.MySqlException
_MySQL_transaction.Rollback()
Nexus.erreurs.message_erreur_mysql(e.Source, e.Number, e.Message, myQuery, myParameters)
_datatable = Nothing
End Try
Else
Try
_MySQL_adapter.UpdateCommand.ExecuteNonQuery()
_MySQL_transaction.Commit()
Catch e As Global.MySql.Data.MySqlClient.MySqlException
_MySQL_transaction.Rollback()
Nexus.erreurs.message_erreur_mysql(e.Source, e.Number, e.Message, myQuery, myParameters)
End Try
_datatable = Nothing
End If
End Sub
my problem is that: during the first select and first update or insert, no problem but if i do a new select and update after, i have an error at the line:
_MySQL_transaction = _MySQL_command.Connection.BeginTransaction()
with message: nested transaction....
but i use commit and rollback option, why the system tell me that my transaction earlier is not close ?
How to repeat:
this is my update methode using with my object
Public Sub MySQL_update(ByVal myQuery As String, ByVal WaitingReturn As Boolean, ByVal ParamArray myParameters() As String)
_datatable = New System.Data.DataTable
myQuery = "SET AUTOCOMMIT=0;" & myQuery
_MySQL_command = New Global.MySql.Data.MySqlClient.MySqlCommand(myQuery, _mySQL_Connection)
_MySQL_adapter.UpdateCommand = _MySQL_command
_MySQL_transaction = _MySQL_command.Connection.BeginTransaction()
_MySQL_adapter.UpdateCommand.Transaction = _MySQL_transaction
If myParameters.Length > 0 Then
For i As Integer = 0 To myParameters.Length - 1 Step 2
If myParameters(i + 1).ToString = "Null" Then
_MySQL_adapter.UpdateCommand.Parameters.AddWith(myParameters(i), DBNull.Value)
Else
_MySQL_adapter.UpdateCommand.Parameters.AddWith(myParameters(i), myParameters(i + 1))
End If
Next
End If
If WaitingReturn Then
Try
_MySQL_adapter.Fill(_datatable)
_MySQL_transaction.Commit()
Catch e As Global.MySql.Data.MySqlClient.MySqlException
_MySQL_transaction.Rollback()
Nexus.erreurs.message_erreur_mysql(e.Source, e.Number, e.Message, myQuery, myParameters)
_datatable = Nothing
End Try
Else
Try
_MySQL_adapter.UpdateCommand.ExecuteNonQuery()
_MySQL_transaction.Commit()
Catch e As Global.MySql.Data.MySqlClient.MySqlException
_MySQL_transaction.Rollback()
Nexus.erreurs.message_erreur_mysql(e.Source, e.Number, e.Message, myQuery, myParameters)
End Try
_datatable = Nothing
End If
End Sub
example:
(creation and open connection)
myconnection = new mysqlobject(ipadress,port)
myconnection.select
myconnection.update
and after again
myconnection.select
and myconnection.update => error message: nested connection
Suggested fix:
i use an alternative;
i don t open the connection during the creation of object but during the launch of methode
i add _connection.open and _connection.close in update methode
but i m afraid that means many and many open/close operations in mysql server
Description: hello, i have an error: nested transaction i read some bugs reports but not fix in VB.NET i create an object MysqlConnection with constructor, methods and property in this object, constructor new create object and open connection. many methods like select,update,insert,multiselect.... so i use this object during the entire session. close connection when i quit the application in this application, i use select to read tables to extract data to fill a form and i use button validation to save data into tables of mysql. During this phase, i use transaction (default config): Public Sub MySQL_update(ByVal myQuery As String, ByVal WaitingReturn As Boolean, ByVal ParamArray myParameters() As String) _datatable = New System.Data.DataTable myQuery = "SET AUTOCOMMIT=0;" & myQuery _MySQL_command = New Global.MySql.Data.MySqlClient.MySqlCommand(myQuery, _mySQL_Connection) _MySQL_adapter.UpdateCommand = _MySQL_command _MySQL_transaction = _MySQL_command.Connection.BeginTransaction() _MySQL_adapter.UpdateCommand.Transaction = _MySQL_transaction If myParameters.Length > 0 Then For i As Integer = 0 To myParameters.Length - 1 Step 2 If myParameters(i + 1).ToString = "Null" Then _MySQL_adapter.UpdateCommand.Parameters.AddWith(myParameters(i), DBNull.Value) Else _MySQL_adapter.UpdateCommand.Parameters.AddWith(myParameters(i), myParameters(i + 1)) End If Next End If If WaitingReturn Then Try _MySQL_adapter.Fill(_datatable) _MySQL_transaction.Commit() Catch e As Global.MySql.Data.MySqlClient.MySqlException _MySQL_transaction.Rollback() Nexus.erreurs.message_erreur_mysql(e.Source, e.Number, e.Message, myQuery, myParameters) _datatable = Nothing End Try Else Try _MySQL_adapter.UpdateCommand.ExecuteNonQuery() _MySQL_transaction.Commit() Catch e As Global.MySql.Data.MySqlClient.MySqlException _MySQL_transaction.Rollback() Nexus.erreurs.message_erreur_mysql(e.Source, e.Number, e.Message, myQuery, myParameters) End Try _datatable = Nothing End If End Sub my problem is that: during the first select and first update or insert, no problem but if i do a new select and update after, i have an error at the line: _MySQL_transaction = _MySQL_command.Connection.BeginTransaction() with message: nested transaction.... but i use commit and rollback option, why the system tell me that my transaction earlier is not close ? How to repeat: this is my update methode using with my object Public Sub MySQL_update(ByVal myQuery As String, ByVal WaitingReturn As Boolean, ByVal ParamArray myParameters() As String) _datatable = New System.Data.DataTable myQuery = "SET AUTOCOMMIT=0;" & myQuery _MySQL_command = New Global.MySql.Data.MySqlClient.MySqlCommand(myQuery, _mySQL_Connection) _MySQL_adapter.UpdateCommand = _MySQL_command _MySQL_transaction = _MySQL_command.Connection.BeginTransaction() _MySQL_adapter.UpdateCommand.Transaction = _MySQL_transaction If myParameters.Length > 0 Then For i As Integer = 0 To myParameters.Length - 1 Step 2 If myParameters(i + 1).ToString = "Null" Then _MySQL_adapter.UpdateCommand.Parameters.AddWith(myParameters(i), DBNull.Value) Else _MySQL_adapter.UpdateCommand.Parameters.AddWith(myParameters(i), myParameters(i + 1)) End If Next End If If WaitingReturn Then Try _MySQL_adapter.Fill(_datatable) _MySQL_transaction.Commit() Catch e As Global.MySql.Data.MySqlClient.MySqlException _MySQL_transaction.Rollback() Nexus.erreurs.message_erreur_mysql(e.Source, e.Number, e.Message, myQuery, myParameters) _datatable = Nothing End Try Else Try _MySQL_adapter.UpdateCommand.ExecuteNonQuery() _MySQL_transaction.Commit() Catch e As Global.MySql.Data.MySqlClient.MySqlException _MySQL_transaction.Rollback() Nexus.erreurs.message_erreur_mysql(e.Source, e.Number, e.Message, myQuery, myParameters) End Try _datatable = Nothing End If End Sub example: (creation and open connection) myconnection = new mysqlobject(ipadress,port) myconnection.select myconnection.update and after again myconnection.select and myconnection.update => error message: nested connection Suggested fix: i use an alternative; i don t open the connection during the creation of object but during the launch of methode i add _connection.open and _connection.close in update methode but i m afraid that means many and many open/close operations in mysql server