Subject Re: [firebird-support] Embedded Fails on Program Restart,
Author Helen Borrie
At 06:50 AM 12/03/2009, you wrote:
>Hi Folks:
> Developing on XP Home, VS2008 Pro, C++, Firebird
>SuperServer, ibpp-2-5-3-1.
> I think this is more of a Firebird question than an IBPP question.
> My application always uses embedded Firebird, as it's primary database or for configuration information if it's connecting to a multi-user database on a server.
> If the user selects a multi-user database, the application disconnects from the the multi-user databse if it's using one, disconnects from the database used by the embedded firebird, starts up a new version of itself with CreateProcess(), and calls PostQuitMessage(0).
> This, of course, leads to the possibility that a new instance of the application will start up before the old instance shuts down.

Understand that a client program "disconnecting" means that the client interface sends a detach *request* to the server. Very well-behaved applications take care of shutting down everything that might possibly be active before they send the detach request. Less well-behaved ones blast out by calling detach arbitrarily. That does swim...but it leaves the server to do the cleanup in its own time.

> I'm getting the following error message:
>SQL Message : -902
>Unsuccessful execution caused by a system error that precludes
>successful execution of subsequent statements
>Engine Code : 335544344
>Engine Message :
>I/O error for file "<path and name of file used by embedded Firebird>"
>Error while trying to open file
>The process cannot access the file because it is being used by another process.

That's because the detach process is still going. Beginning-middle-end. The detach call is the "beginning". "Middle" is still going. There's an exclusive lock on the local database until after "end" so the request for a new connection encounters the i/o error.

> If I single step the first instance of the app to it's termination, with the debugger, and then attend to a messagebox I've added before attempting to open the database, for the second instance of the app, things work fine.
> So it's a race condition.

Not exactly. If the database lock has gone, the new connection request will succeed. If you must do it the way you're doing it then some delaying tactic is apparently enough.

> But the first instance did close the database before the second instance started.

See above.

> Should a second instance embedded Firebird be able to open a database that the first instance opened, if the first instance disconnected from it but not yet terminated?

> Suggestions?

There's no such thing as a "single user database" as opposed to a "multi-user database". Why do all this chopping about with new instances of the application? While it's true that you can shut down the embedded connection by terminating your application, it's only one way to shut down the embedded connection. According to your experience, it's the wrong way.

In the embedded library you have an embedded client that is perfectly capable of connecting to remote servers. There's no need to shut down your application and restart it in order to connect to a remote server. The same client can be connected to the local database via the embedded server and to other servers (via TCP/IP) simultaneously. You can detach from the local database any time you want to: the server doesn't go away. When you want to reconnect, it will be there, ready to roll. Same goes for connecting - disconnecting - reconnecting to any of the dbs on remote servers.