App losing db connection

I'm having a weird issue with an old Delphi app losing it's database connection. Actually, I think it's losing something else that then makes the connection either drop or be unusable. The app is written in Delphi 6 and uses the Direct Oracle Access component (v4.0.7.1) to connect to an Oracle 9i database. The app runs as a service and periodically queries the db using a TOracleQuery object (qryAlarmList). The method that is called to do this looks like this:

procedure TdmMain.RefreshAlarmList;
begin
  try
    qryAlarmList.Execute;
  except
    on E: Exception do
    begin
      FStatus := ssError;
      EventLog.LogError(-1, 'TdmMain.RefreshAlarmList', 'Message: ' + E.Message);
    end;
  end;
end;

It had been running fine for y开发者_JS百科ears, until a couple of Perl scripts were added to this machine. These scripts run every 15 minutes and look for datafiles to import into the db, and then they do a some calculations and a bunch of reads/writes to/from the db. For some reason, when they are processing large amounts of data, and then the Delphi app tries to query the db, the Delphi app throws an exception at the "qryAlarmList.Execute" line in the above code listing. The exception is always:

Access violation at address 00000000. read of address 00000000

HOW can something that the Perl scripts are doing cause this?? There are other Perl scripts on this machine that load data using the same modules and method calls and we didn't have problems. To make it even weirder, there are two other apps that will also suddenly lose their ability to talk to the database at the same time as the Perl stuff is running. Neither of those apps run on this machine, but both are Delphi 6 apps that use the same DOA component to connect to the same database. We have other apps that connect to the same db, written in Java or C# and they don't seem to have any problems.

I've tried adding code before the '.Execute' method is called to:

• check the session's connection (session.CheckConnection(true); always comes back as 'ccOK').

• see whether I can access a field of the qryAlarmList object to see if maybe it's become null; can access it fine.

• check the state of the qryAlarmList; always says it's qsIdle.

Does anyone have any suggestions of something to try? This is driving me nuts!

Dave

If other apps on other machines do also lose their connection to the DB, I would investigate on the DB side and look there (pull out perf stats, logs,...).
Maybe the Perl scripts are causing some resource clogging on the DB server, blocking other trying to access.
And it might be related to the way the D6 apps are connecting, leaving the other C#, java... able to work?

My reasoning is that I only see the DB as the common link in MachineA/D6 losing connection and MachineB/D6 losing connection...

Hope it helps

It sounds like something is resetting the listener. Have the dba check various logs to see if the listener bounces when these perl jobs run. Or check to see if the PID (process ID) of the listener stays the same all day long, or if it jumps when these perl jobs run.

"Access violation at address 00000000. Read of address 00000000" has a very specific meaning. It almost certainly means that something is trying to call a virtual method on a nil object reference. If it's not something obvious, then try rebuilding with Debug DCUs on and run under the debugger. It should break and show you exactly where the problem is.

Also, you mentioned you're in Delphi 6 and this only happens with large data sets. In that case, you might want to look at FastMM4, a replacement memory manager. The old BorlandMM memory manager had some issues that could give access violations when working with large amounts of data, and FastMM fixes them.

Dave, I have the same problem, but not have perl or another process running. I saw that my oracle db was with SGA_MAX_SIZE below necessary, but I can't shutdown in this moment because is production db and have that warning very users.

Please, looks/change your database parameters and give us a feedback

Good Luck. Allisson

Ps: sorry my poor English

DOA v 4.0.7.1 is 5 years old. Why not just try to upgrade your DOA to the latest version ?