Page 1 of 1

File Lock Problems

Posted: 25.10.2007, 12:25
by Roaster
Hi,

I've coded these lines below to initialize a Firebird 2.x (embedded)
database for testing purposes.

The code does not make much sense but I it was my intention to show you
what happens in other procedures of my application before without copying
that code, too.
First I create a Firebird database and a query which fetches just a single
record. This works OK.

Afterwards I'm using the Zeos library to create a connection object with
exactly the same database (file). When connecting to the database I get an
error: "EZSQLException: SQL Error: operating system directive CreateFile
failed. The process cannot access the file since it is already used by
another process." (translated from German).

Why am I doing those steps you will ask? I'm using Context Database
Designer which can update an existing database using a given schema. This
component (from Contextsoft.com, btw.) uses TIBDatabase, too to access the
appropriate database.

So I've build a testing application which performs almost the same steps as
the component from Contextsoft, but I do get that mentioned error in both
cases.

Does sbdy have any clue what's happening here?

Code: Select all

function TWorkingDatabase.InitDatabase : boolean;
var
  o : TIBDatabase;
  q : TIBQuery;
begin
  FDBVersion := '';
  o := TIBDatabase.Create(Nil);
  o.DatabaseName := DBPath;
  o.Params.Add('password=12345678');
  o.Params.Add('user_name=sysdba');
  o.LoginPrompt := False;
  o.Open;

  q := TIBQuery.Create(nil);
  q.Database := o;
  q.SQL.Text := 'SELECT VERINFO FROM SysTable';
  q.Transaction := o.DefaultTransaction;
  if q.Transaction = nil then
      q.Transaction := o.InternalTransaction;
  q.Active := True;
  q.Active := False;

  o.ForceClose;
  o.Connected := False;

  dmMainDatabase.dbMainConnection.Database := DBPath;
  dmMainDatabase.dbMainConnection.Protocol := DBProtocol;
  dmMainDatabase.dbMainConnection.Password := 'abcdefghi';
  dmMainDatabase.dbMainconnection.User := 'sysdba';
  try
    dmMainDatabase.dbMainConnection.Connected := True;
    // Fetch db version
    FDBVersion := dmMainDatabase.DatabaseSchema.VersionLabel;
    Result := True;
  finally

  end;
end;
Thanks, Michael

Posted: 25.10.2007, 19:26
by mdaems
Have a look at this bug report in our tracker http://zeosbugs.firmos.at/view.php?id=58
(Sorry, you'll have to create an account there)
I attached a copy.

Mark

Posted: 27.10.2007, 20:20
by Roaster
Thanks Mark for your reply.

I guess the Mantis report describes a similar problem. But the source of this issue is a DLL holding a lock to the database file.

I try to describe what I've found out and how I solved this problem: If using TIBDatabase prior to TZConnection/TZQuery, the IB component loads gds32.dll. This DLL holds a lock even after those components have been destroyed. (Exiting the application destroys the lock file, though).

To solve this I'm searching for the loaded library gds32.dll, fetch the handle to it and unload this library - that's all!

Right after this I can use TZConnection and any subsequent components of Zeos w/o any further problems.

Simply huh? Yes, of course, but hard to find. :shock: and I even guess not to solve anyway because of the different used library files.

I should mention that I've renamed the fbclient.dll to gds32.dll and copied it to the appropriate PATH to use the IB controls and Zeos parallel.

Oh, wait, a solution would be to add a property to the TZConnection component which DLL file to use, e.g. fbclient.dll, gds32.dll and so on, this way i would be guaranteed that both, Zeos and IB, use the same DLL.

Posted: 28.10.2007, 09:01
by mdaems
Thanks for your explanation Roaster.
Conclusion : the IB controls should release the file when the components are destroyed or connections are closed. Maybe you could file them a bug report?

Concerning the feature request to add a 'library' option : this one is already asked for a few times. Have a look at the feature requests forum. I can't recall it's already in there. If not, please add it. I already received a patch from Tohenk for mysql, but I don't want to add it before we get 6.6 stable. The more, we'll have to check if the implementation is general and flexible enough.

Now I'm thinking about it... I'll have to check if we can't implement a new component, a 'TZNonDefaultDatabaseDriver'. This one could be connected to a TZConnection. Main properties of this new component : Protocol, library filename, library path (and a 'wastebasket' called Properties, of course). I think I already know 3 users by (nick)name who woud be very happy about this option as they are struggling with this dll confusion problem.
Thinking deeper : would it be usefull to be able to add a library handle as a property of this new component? So you would even be able to reuse the dll handle used by other components. (I must admit : that's really spooky)

Mark