Devart Forums

Posted: **Tue 24 Feb 2009 17:24**

Hi,
I am evaluating latest version of the ODAC components. I am required to prepare a COM+ DLL using Delphi 6 that will be used from .net based application. Both applications need to connect to the same instance of Oracle 9i database using client version 9.2 and perform updates within a single transaction.

Delphi 6 DLL works perfectly fine if I use TMtsDataModule descendant and ADOConnection / ADOCommand components. Delphi provides good native support to manage it. This also proves that all other Oracle setup for MTS is proper (that took quite a bit of time to make it work right).

Delphi COM+ DLL using ODAC works except it does not participate in the same transaction that is initiated by .net application. In .net there is no way to specify OraSession.InternalName, OraTransaction.TransactionId / BranchQualifier / TransactionName etc. This is true with OracleConnection component available in System.Data.OracleClient (native MS ADO.net) and Oracle.DataAccess.Client (Oracle Data Access Provider v9.2 from Oracle). Checked even dotConnect for Oracle does not have such properties. In absence of such properties .net application cannot specify any specific TransactionId. While .net code does respond to exceptions raised within the scope of TransactionScope object but ODAC component within Delphi 6 COM+ DLL do not. In fact DB changes done within Delphi 6 are not commited at all and are rolled back after timeout interval occurs. ODAC documentation provies details of using ODAC & MTS when same Delphi application connects to more than one Oracle DB and performs update. Also there are examples of using same Oracle DB and performing updates within single transaction from multiple applications. This is achieved using TransactionId that can be set as long as application is in Delphi. There is no support for using MTS and sharing transaction with another application that is written using different development tool (Visual Studio in this case). Can this be done? Any suggestion would be greatly appreciated. We dont mind buying both VCL ODAC components and dotConnect for Oracle if it helps.

My Delphi 6 code is-

Code: Select all

    if (FOraSession = nil) then
    begin
      FOraSession := TOraSession.Create(nil);
      FOraSession.ConnectPrompt := false;
      FOraSession.Username := 'user';
      FOraSession.Password := 'pass';
      FOraSession.Server := 'mydb';
      FOraSession.InternalName := 'Something';
      FOraSession.Connect;
    end;
    
    if (FOraTransaction = nil) then
    begin
      FOraTransaction := TOraTransaction.Create(nil);
      //FOraTransaction.GlobalCoordinator := gcMTS;

      //FOraTransaction.AddSession(FOraSession);
    end;

    if (FOraQuery = nil) then
    begin
      FOraQuery := TOraQuery.Create(nil);
      FOraQuery.Session := FOraSession;
      FOraQuery.SQL.Clear;
      FOraQuery.SQL.Add('insert into ...');

      //SetLength(Id, 2);
      //id[0] := 7; id[1] := 3;
      //FOraTransaction.TransactionId := Id;

      //SetLength(Id, 1);
      //id[0] := 2;
      FOraTransaction.AddSession(FOraSession{, id});

      FOraTransaction.StartTransaction;
      FOraQuery.ExecSQL;
      FOraTransaction.Detach;
    end;

    EnableCommit; --from TMtsAutoObject

And my .net code is:

Code: Select all

            CoD6ComClass d6ComClass = new CoD6ComClass();

            TransactionOptions options = new TransactionOptions(); 
            options.IsolationLevel = IsolationLevel.ReadCommitted; 
            options.Timeout = new TimeSpan(0, 1, 0);

            using (TransactionScope scope = new TransactionScope(
                TransactionScopeOption.RequiresNew, options, EnterpriseServicesInteropOption.Automatic))
            {
                --update any table in same database used from D6
            OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);
            
            string sqlStr = "update ...";
            OracleCommand command = new OracleCommand(sqlStr, conn);
            command.CommandType = CommandType.Text;
            try
            {
                conn.Open();
                int affectedRow = command.ExecuteNonQuery();
                if (affectedRow == 0)
                    throw new Exception("Failed to update!");
                    //System.EnterpriseServices.ContextUtil.SetAbort();
            }
            finally
            {
                conn.Close();
            }

            --call function within Delphi 6 COM+ DLL as given above
            d6ComClass.MyD6Function(...);

            if (!CheckSomething)
              throw new Exception("Failed");


            else


              scope.Complete();
            }

            System.Runtime.InteropServices.Marshal.ReleaseComObject(d6ComClass);
            d6ComClass = null;

Posted: **Wed 25 Feb 2009 08:17**

ODAC does not support sharing MTS transaction with other applications.

Devart Forums

Delphi COM+ DLL using ODAC & .net appln sharing transaction

Delphi COM+ DLL using ODAC & .net appln sharing transaction