Issues with having two connections

[Lass.Mint]

Hi,
I have been adding a feature to our software that reads data from one schema and writes it to another one. For this, the tool creates two TOraSessions. The writing operations are done in transactions.

This is working fine in most cases but at some point an exception is raised with the message:

One of the connections in the transaction is not active

After debugging for some time I found that the new, second session somehow gets disconnected. So I added several

Code: Select all

if not Session.Connected then
  Session.Connect;

which seemed to fix the issue for most cases, but now I'm stuck in a situation where this does not help.

I'm using ODAC 8.1.4 Pro for Delphi 2006. The call stack shows that this is somehow related to starting a transaction:

Code: Select all

:7573c42d KERNELBASE.RaiseException + 0x58
:00849fd5 TDATransaction.PrepareTransaction + $8D
:0084a268 TDATransaction.StartTransaction + $98
:008a7469 TOraTransaction.StartTransaction + $95
:008a73d1 TOraTransaction.StartTransaction + $11
:008aed01 TOraSession.StartTransaction + $5D
:008aeca1 TOraSession.StartTransaction + $15
MintCourseMigration.TMintCourseMigration.DeleteCtypeVersions
MintCourseMigration.TMintCourseMigration.Execute
MintFormCourseMigration.TFormCourseMigration.btnStartClick($2F74410)
Controls.TControl.Click
StdCtrls.TButton.Click
StdCtrls.TButton.CNCommand((48401, 3196, 0, 396412, 0))
Controls.TControl.WndProc((48401, 3196, 396412, 0, 3196, 0, 3196, 6, 0, 0))
Controls.TWinControl.WndProc((48401, 3196, 396412, 0, 3196, 0, 3196, 6, 0, 0))
StdCtrls.TButtonControl.WndProc((48401, 3196, 396412, 0, 3196, 0, 3196, 6, 0, 0))
Controls.TControl.Perform(48401,3196,396412)
Controls.DoControlMsg(396412,(kein Wert))
Controls.TWinControl.WMCommand((273, 3196, 0, 396412, 0))
Forms.TCustomForm.WMCommand((273, 3196, 0, 396412, 0))
Controls.TControl.WndProc((273, 3196, 396412, 0, 3196, 0, 3196, 6, 0, 0))
Controls.TWinControl.WndProc((273, 3196, 396412, 0, 3196, 0, 3196, 6, 0, 0))
Forms.TCustomForm.WndProc((273, 3196, 396412, 0, 3196, 0, 3196, 6, 0, 0))
Controls.TWinControl.MainWndProc((273, 3196, 396412, 0, 3196, 0, 3196, 6, 0, 0))
Classes.StdWndProc(461880,273,3196,396412)
:76fb62fa ; C:\Windows\syswow64\USER32.dll
:76fb6d3a USER32.GetThreadDesktop + 0xd7
:76fb965e ; C:\Windows\syswow64\USER32.dll
:76fb96c5 USER32.SendMessageW + 0x4c
:76ff5fbb ; C:\Windows\syswow64\USER32.dll
:76ff60fc ; C:\Windows\syswow64\USER32.dll
:76fe312e ; C:\Windows\syswow64\USER32.dll
:76fdaa3f ; C:\Windows\syswow64\USER32.dll
:76fb62fa ; C:\Windows\syswow64\USER32.dll
:76fb6d3a USER32.GetThreadDesktop + 0xd7
:76fc0d27 USER32.GetClientRect + 0xc5
:76fc794a USER32.CallWindowProcA + 0x1b
Controls.TWinControl.DefaultHandler((kein Wert))
Controls.TControl.WMLButtonUp((514, 0, 29, 3, (29, 3), 0))
Controls.TControl.WndProc((514, 0, 196637, 0, 0, 0, 29, 3, 0, 0))
Controls.TWinControl.WndProc((514, 0, 196637, 0, 0, 0, 29, 3, 0, 0))
StdCtrls.TButtonControl.WndProc((514, 0, 196637, 0, 0, 0, 29, 3, 0, 0))
Controls.TWinControl.MainWndProc((514, 0, 196637, 0, 0, 0, 29, 3, 0, 0))
Classes.StdWndProc(396412,514,0,196637)
:76fb62fa ; C:\Windows\syswow64\USER32.dll
:76fb6d3a USER32.GetThreadDesktop + 0xd7
:76fb77c4 ; C:\Windows\syswow64\USER32.dll
:76fb7bca USER32.DispatchMessageA + 0xf
Forms.TApplication.ProcessMessage((396412, 514, 0, 196637, 4523436, (1076, 737)))
Forms.TApplication.HandleMessage
Forms.TCustomForm.ShowModal
MintFormScheduler.TFormScheduler.btnDesignMigrationClick($6142970)
Controls.TControl.Click
BegaButton.TBegaButton.Click
Controls.TControl.WMLButtonUp((514, 0, 39, 23, (39, 23), 0))
Controls.TControl.WndProc((514, 0, 1507367, 0, 0, 0, 39, 23, 0, 0))
Controls.TControl.Perform(514,0,1507367)
Controls.TWinControl.IsControlMouseMsg((514, 0, 492, 23, (492, 23), 0))
Controls.TWinControl.WndProc((514, 0, 1507820, 0, 0, 0, 492, 23, 0, 0))
Controls.TWinControl.MainWndProc((514, 0, 1507820, 0, 0, 0, 492, 23, 0, 0))
Classes.StdWndProc(527372,514,0,1507820)
:76fb62fa ; C:\Windows\syswow64\USER32.dll
:76fb6d3a USER32.GetThreadDesktop + 0xd7
:76fb77c4 ; C:\Windows\syswow64\USER32.dll
:76fb7bca USER32.DispatchMessageA + 0xf
Forms.TApplication.ProcessMessage((527372, 514, 0, 1507820, 4516884, (494, 67)))
Forms.TApplication.HandleMessage
Forms.TApplication.Run
Scheduler.Scheduler
:757d336a kernel32.BaseThreadInitThunk + 0x12
:779992b2 ntdll.RtlInitializeExceptionChain + 0x63
:77999285 ntdll.RtlInitializeExceptionChain + 0x36

Unfortunately, the exception message does not really help me in finding the cause of the issue. Can you give me hint in which situations this exception in raised? What could be the cause for the connection not being active? And how can I make it active or keep it from being inactive?

This is how the first connection is created:

Code: Select all

FODACSession := TOraSession.Create(NIL);
if ASessionName <> '' then
  FODACSession.Name := ASessionName;
FODACSession.Options.Direct := CMintConfigValueDirectOracleConnection;
FODACSession.AutoCommit := true;
FODACSession.Server := LServerName;
FODACSession.Username := AUserName;
FODACSession.Password := APassword;
FODACSession.ThreadSafety := TRUE;
FODACSession.Connect;

This is how the second session is created:

Code: Select all

FTargetDBConnection := TOraSession.Create(nil);
FTargetDBConnection.Username := FDBParametes.SchemaName;
FTargetDBConnection.Password := FDBParametes.Password;
FTargetDBConnection.AutoCommit := false;
FTargetDBConnection.ThreadSafety := true;

FTargetDBConnection.Options.Direct := FDBParametes.DirectMode;

if FDBParametes.DirectMode then
  FTargetDBConnection.Server := FDBParametes.GetDirectModeConnectionString
else
  FTargetDBConnection.Server := FDBParametes.TNSName;

FTargetDBConnection.Connect;

And this is code where the exception is currently raised:

Code: Select all

for i := 0 to High(FSuccessfullCourses) do
begin
  TargetDBConnection.StartTransaction;
  try
    ExecuteSqlOnTargetDB(
      'delete '#13#10 +
      '  from request'#13#10 +
      ' where request_key in (select request_key from course where course_key = ' + keyToOracleStr(FSuccessfullCourses[i]) + ')');
    TargetDBConnection.Commit;
  except
    on E: Exception do
    begin
      DoLogMessage('ERROR: Could not delete course with key #' + IntToStr(FSuccessfullCourses[i]) + '! Cause: ' + E.Message);
      TargetDBConnection.Rollback;
      exit;
    end;
  end;
  DoLogMessage('REQ' + StringOfChar('.', i + 1));
end;

for i := 0 to High(FRevisionKeys) do
begin
  TargetDBConnection.StartTransaction;
  try
    ExecuteSqlOnTargetDB(
      'delete '#13#10 +
      '  from course_type_version ctv '#13#10 +
      ' where ctype_version_key = ' + keyToOracleStr(FRevisionKeys[i])
      );
    TargetDBConnection.Commit;
  except
    on E: Exception do
    begin
      DoLogMessage('ERROR: Could not delete revision with key #' + IntToStr(FRevisionKeys[i]) + '! Cause: ' + E.Message);
      TargetDBConnection.Rollback;
      exit;
    end;
  end;
  DoLogMessage('REV' + StringOfChar('.', i + 1));
end;

The excpetion is raised in the second " TargetDBConnection.StartTransaction;".

Thanks a lot in advance for any help.

[AlexP]

Hello,

You are using an obsolete ODAC version, please try to reproduce the problem on the latest trial ODAC version 9.5.15 http://www.devart.com/odac/download.html , and if the problem repeats, contact us again.

[Lass.Mint]

Thanks for the reply. I have downloaded the update and will check if it fixes the issue.

[Lass.Mint]

Hi,
I have installed the update and that showed a different error message. I got

ORA-12520: TNS:listener could not find available handler for requested type of server

After googling that for a while I realized, that there was a bug in the way created and used the TOraSession. I created a new connection for each step of the data transfer and then, I also did not close these connections ever . So, at some point, Oracle was out of connections.

Anyways, thank you for your support. The actual problem was sitting in front of the PC here.

[AlexP]

If you have any further questions, feel free to contact us.