Error when using MpmTransaction with SQL Server database
Brought to you by:
fral
Menu
▾
▴
#13 Error when using MpmTransaction with SQL Server database
I am trying to use a MpmTransaction with a group of
update stored procedures. This code worked for SQL
Server before converting the code to use Mimer.
Now when I call the update on the dataadapter I get the
following message:
"The transaction objectis not associated with the
connection object".
The same code works fine with Oracle
Logged In: YES
user_id=895705
Still works for me.
If you debug your application you can get hold of the
SqlDataAdapters for the various commands:
Use the watch window in the debugger and enter daStreet
right before you want to perform the daStreet.Update().
Expand daStreet and view the tree of properties:
daStreet + _internalDataAdapter + NativeDataAdapter +
xxxCommand
Here you can see the properties assigned to the underlying
plugins command objects. Maybe you can see if it looks
consistent or not!
Logged In: YES
user_id=684148
I think I may have found something. If I look at
daStreet.InsertCommand.Connection.State it is Open, but If I
look at
daStreet._internalDataAdapter.NativeDataAdapter.InsertCom
mand.Connection.State it is Closed.
daStreet._internalDataAdapter.NativeDataAdapter.InsertCom
mand.Transaction.Connection.State is showing as Open.
Logged In: YES
user_id=895705
I need to understand in what order you are doing things. Of
particular importance is the setting of DataSoure in the
ConnectionString.
Can you tell me the order of operations? I.e. when do you
perform new MpmConnection, when to you set the
connectionstring, when do set the various properties on the
insert MpmCommand, when do you Open then connection.
It looks like it is something with this sequence that does not
set the internal properties correctly. But I do not see this as
your wrapper class is not included. I would really like to set
up this sequence in my test!
Logged In: YES
user_id=684148
Here is the order:
Create Streets class
Create DataSet
Create Connection object with connection string
Create StreetData object
Create Select Command
Set Command Type
Set command connection
Create Update command
Set Command Type
Set Command Text (StoredProcedure)
Set command connection
Create Insert command
Set Command Type
Set Command Text (StoredProcedure)
Set command connection
Create Delete command
Set Command Type
Set Command Text (StoredProcedure)
Set command connection
Create DataAdapter
Set select command of dataadapter to select command
Set update command of dataadapter to update command
Set insert command of dataadapter to insert command
Set delete command of dataadapter to delete command
Call Create on Streets class
Open connection
Set transaction to conn.BeginTransaction
Call StreetData Create method
Set DataAdapter Insert Command Connection to conn
Set DataAdapter Insert Command Transaction to trans
Clear Insert Commands paramters
Add new parameters
Call DataAdapter Update method. -- This then causes the
exception
I have included the source file for the base class to help
Logged In: YES
user_id=895705
I am sorry, but I can simply not reproduce your problem...
Would it be possible for you to put together a simple
reproducer project together with a few database definitions for
me?
Logged In: YES
user_id=684148
I have attached a zip file that contains a simple solution that
demonstrats a similiar problem, where the native connection
is not opened. I have included a SQL script to create the
table and stored procedure as well. All you need to do is
click on the button and you should get an error. I am seeing
this error with both SQL Server and Oracle.
Logged In: YES
user_id=895705
Thank you for the reproducer. Finally I understand what is
going on.
When you set a ConnectionString in an MpmConnection the
system determines what underlying plugin to use. So
whenever a connection string is set, the system allocates a
new underlying plugin connection.
In your code this occurs when you want to change the
timeout before you open the connection (with logic involving
szOldConnectionString).
In order to solve this properly in Mpm I would need to keep
track of all objects in the system. The reason is that when
the connection string is changed it is also possible that the
underlying plugin changes. This involves a fair amount of
bookkeeping and involves a fair amount of programming.
An intermediate solution might be to check if it is the same
plugin as last time, and avoid creating a new one in this
case. That would solve your problem, but not the case with
different plugins. I need to consider the consequences of this,
but if you are interested in this (i.e. the timeout are vital to
change) I will look into it.
Logged In: YES
user_id=684148
Unfortunately I do need to alter the connection timeout for
some of the stored procedures I use as the default timeout is
too short for some of the stored procedures, but for the reset
of the stored procedures the default is fine.
The only problem is that for my original issue I do not change
the connection string! So I do not think this is the whole
problem. I will try and produce a reproducer for the first issue
as well and see if that helps.
Logged In: YES
user_id=895705
I have made a correction where I detect if the same plugin is
used. An installation file is among the attached files. The
installation will install in parallel with your current installation
with version number 1.2.4 (this is not an official release).
Logged In: YES
user_id=895705
Cannot upload the install for some reason...
Logged In: YES
user_id=684148
Bengt
Thanks very much, that has fixed all of the problems.
Sean
Logged In: YES
user_id=895705
That is good news!
Bengt
Logged In: YES
user_id=684148
Bengt
Sorry to say that this issue has raised its head again when I
am using the transaction object with the delete commands.
Looking at the properties of the MpmDataAdapter delete
command I noticed that
MpmConnetion.State = Open,
MpmTransaction.Connection.State = Open,
MpmTransaction.NativeTransaction.Connection.State =
Open,
NativeCommand.Transaction.Connection.State = Open
but
NativeCommand.Connection.State = Closed
Hope this helps.
This is happening for SQL Server.
Sean