#13 Error when using MpmTransaction with SQL Server database

closed
Bengt Gunne
None
5
2005-05-24
2005-04-19
Sean Flook
No

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

Discussion

<< < 1 2 (Page 2 of 2)
  • Sean Flook
    Sean Flook
    2005-04-21

     
    Attachments
  • Bengt Gunne
    Bengt Gunne
    2005-04-21

    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!

     
  • Sean Flook
    Sean Flook
    2005-04-21

    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.

     
  • Bengt Gunne
    Bengt Gunne
    2005-04-21

    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!

     
  • Sean Flook
    Sean Flook
    2005-04-21

     
    Attachments
  • Sean Flook
    Sean Flook
    2005-04-21

    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

     
  • Bengt Gunne
    Bengt Gunne
    2005-04-21

    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?

     
  • Sean Flook
    Sean Flook
    2005-04-21

    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.

     
  • Sean Flook
    Sean Flook
    2005-04-21

     
    Attachments
  • Bengt Gunne
    Bengt Gunne
    2005-04-22

    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.

     
  • Sean Flook
    Sean Flook
    2005-04-22

    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.

     
  • Bengt Gunne
    Bengt Gunne
    2005-04-22

    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).

     
  • Bengt Gunne
    Bengt Gunne
    2005-04-22

    Logged In: YES
    user_id=895705

    Cannot upload the install for some reason...

     
  • Sean Flook
    Sean Flook
    2005-04-22

    Logged In: YES
    user_id=684148

    Bengt

    Thanks very much, that has fixed all of the problems.

    Sean

     
  • Bengt Gunne
    Bengt Gunne
    2005-04-22

    • status: open --> closed
     
  • Bengt Gunne
    Bengt Gunne
    2005-04-22

    Logged In: YES
    user_id=895705

    That is good news!

    Bengt

     
  • Sean Flook
    Sean Flook
    2005-05-10

    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

     
  • Sean Flook
    Sean Flook
    2005-05-10

    • status: closed --> open
     
  • Sean Flook
    Sean Flook
    2005-05-24

    • status: open --> closed
     
<< < 1 2 (Page 2 of 2)