Firebird: Deal with Unavailable Database

“unavailble database” error prompt connecting to Firebird service is annoying and difficult to trace.  Sometime it work and sometime it doesn’t.  We don’t know when it work and when it doesn’t work.

“unavailable database” happen in TCP/IP connection

Use TCP/IP connection string to connect to a Firebird database is easy to trace.  The connection string looks like

• 192.168.5.1:/data/db/test.fdb
• localhost:/data/db/test.fdb

Please note that localhost is not using local protocol connection but it is a TCP/IP connection using loopback address.

If you encounter “unavailable database” error for TCP/IP connection, please make sure:

1. You network connection on both end are working and the TCP/IP configuration is correct.  Try to run “PING” to the Firebird server will confirm the connection is working.
2. Make sure Firebird Service is running
3. Make sure Firewall doesn’t block Firebird listening port.  Default port number is 3050
4. Make sure the connection string point to valid Firebird database file

Once you got above verified, the “unavailable database” error prompt should gone.

“unavailable database” happen in Local Protocol

The data transmission throughput for local protocol is few times better than TCP/IP connection.  However, it is easy to encounter “unavailable database” error while using Local Protocol when Firebird is running as service.  The connection string is simple:

• c:\db\test1.fdb
• c:\db\test2.fdb

The connection string is the database file path itself without any prefix.

Using Local Protocol with Firebird 1.5

	Run as Application	Run as Service
Windows XP	Yes	Yes. Only work for first logon user
Windows Vista	Yes	No

Using Local Protocol with Firebird 2.X

	Run as Application	Run as Service
Windows XP	Yes	Yes.  Work for any number of logon users
Windows Vista	Yes	No

Please note that Local Protocol only works for:

1. x86 OS and x86 Firebird
2. x64 OS and x64 Firebird

Local Protocol doesn’t work for x64 OS and x86 Firebird.

The above tables explains why local protocol sometime work and sometime doesn’t work.

dbExpress Driver for Firebird

dbExpress is data driver architecture developed by Embarcadero
Delphi / C++ Builder (Formerly known as Borland or CodeGear Delphi / C+
+ Builder).

Firebird is a relational database offering many ANSI SQL standard
features that runs on Linux, Windows, and a variety of Unix platforms.

dbExpress Driver for Firebird is a high quality driver providing
optimum performance for Firebird service data accessing operation.

dbExpress support both Delphi 2007 and Delphi 2009.

URL: http://sites.google.com/site/dbxfirebird/

Firebird: Deal with Unavailable Database

“unavailble database” error prompt connecting to Firebird service is annoying and difficult to trace.  Sometime it work and sometime it doesn’t.  We don’t know when it work and when it doesn’t work.

“unavailable database” happen in TCP/IP connection

Use TCP/IP connection string to connect to a Firebird database is easy to trace.  The connection string looks like

• 192.168.5.1:/data/db/test.fdb
• localhost:/data/db/test.fdb

Please note that localhost is not using local protocol connection but it is a TCP/IP connection using loopback address.

If you encounter “unavailable database” error for TCP/IP connection, please make sure:

1. You network connection on both end are working and the TCP/IP configuration is correct.  Try to run “PING” to the Firebird server will confirm the connection is working.
2. Make sure Firebird Service is running
3. Make sure Firewall doesn’t block Firebird listening port.  Default port number is 3050
4. Make sure the connection string point to valid Firebird database file

Once you got above verified, the “unavailable database” error prompt should gone.

“unavailable database” happen in Local Protocol

The data transmission throughput for local protocol is few times better than TCP/IP connection.  However, it is easy to encounter “unavailable database” error while using Local Protocol when Firebird is running as service.  The connection string is simple:

• c:\db\test1.fdb
• c:\db\test2.fdb

The connection string is the database file path itself without any prefix.

Using Local Protocol with Firebird 1.5

	Run as Application	Run as Service
Windows XP	Yes	Yes. Only work for first logon user
Windows Vista	Yes	No

Using Local Protocol with Firebird 2.X

	Run as Application	Run as Service
Windows XP	Yes	Yes.  Work for any number of logon users
Windows Vista	Yes	No

The above tables explains why local protocol sometime work and sometime doesn’t work.

Firebird Installation

Firebird support running as service or application mode.  It always confuse me and I don't know what is the best working mode in my daily development and deployment environment.

Running Firebird as application

Running Firebird as application provide developer greater control during development process.  It allow user to start and stop the application anytime.  It also allow user to run various version of Firebird easily without performing any installation in privilege account.

The following example show deployment strategy of running few instances of Firebird as application.  First, obtain the following zip archive files from Firebird installation repositories:

1. Firebird-1.5.6.5026-0_win32.zip
2. Firebird-2.1.3.18185-0_Win32.zip / Firebird-2.1.3.18185-0_x64.zip
3. Firebird-2.5.0.26074-0_Win32.zip / Firebird-2.5.0.26074-0_x64.zip

Extract the zip archive files in your account profile: e.g: %USERPROFILE% (c:\Users\my-account)

Open Windows Registry Editor, and locate the key:[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Run]

Enter the following entries:

"FB156"="C:\Users\my-account\Firebird-1.5.6.5026-0_win32\bin\fbserver -a"
"FB213"="C:\Users\my-account\Firebird-2.1.3.18185-0_x64\bin\fbserver -a -p 3213"
"FB250"="C:\Users\my-account\Firebird-2.5.0.26074-0_x64\bin\fbserver -a -p 3250"

Parameter –a indicates the application should run as application.  Parameter –p specify the port number the application should listen to.  Please note that port number is should be used by application exclusively.

You should logoff from your account and logon again to activate the changes.

The above example deploy 3 instance of Firebird:

1. Firebird-1.5.6.5026-0_win32 listen on default port 3050
2. Firebird-2.1.3.18185-0_x64 listen on port 3213
3. Firebird-2.5.0.26074-0_x64 listen on port 3250

Running Firebird as service

If you run Firebird 1.5 or Firebird 2.0/2.1 binary setup file, you may specify if you want to run Firebird as application or service.  For Windows 2000, XP, Vista and above, you should choose to run Firebird as service.  You may also configure the running mode after installation in Firebird Control Panel Applet.

It is not necessary to run Firebird Guardian for Firebird running in service mode.

Install Firebird service with command line tool

Apart from the common GUI configuration we familiar for Firebird startup configuration, we may also use the command line tool to perform the configuration.  The tool is call "instsvc.exe" that is usually located in "%ProgramFiles\Firebird\Firebird_X_X\bin".  We may use the tool to configure the running mode, and start or stop the Firebird Service:

C:\Program Files\Firebird\Firebird_1_5\bin>instsvc

Usage:
  instsvc i[nstall] [ -s[uperserver]* | -c[lassic] ]
                    [ -a[uto]* | -d[emand] ]
                    [ -g[uardian] ]
                    [ -l[ogin] username [password] ]

          sta[rt]   [ -b[oostpriority] ]
          sto[p]
          q[uery]
          r[emove]

  This utility should be located and run from the 'bin' directory
  of your Firebird installation.

  '*' denotes the default values
  '-z' can be used with any other option, prints version
  'username' refers by default to a local account on this machine.
  Use the format 'domain\username' or 'server\username' if appropriate.

For example:

1. Start cmd In Administrator Account
2. Run
     "C:\Program Files\Firebird\Firebird_1_5\bin\instsvc" -i -s
   to configure Firebird running as service
3. Run
     "C:\Program Files\Firebird\Firebird_1_5\bin\instsvc" start
   to start the service

Running both Firebird 1.5 and 2.1 service in same machine

It is possible to run more than one instance or one version of Firebird service in single operating system.  This is particularly useful in development environment where we want to make sure our application works fine for various versions of Firebird service.

Port Conflict

Firebird service listen to TCP port 3050.  All Firebird versions listen to same port 3050 by default.  One TCP port may listened by one instance of Firebird service only.

To get the job done, we have to choose different port for another instance of Firebird service.  For example, we may configure Firebird 1.5 listen to port 3050 and Firebird 2.1 listen to port 3052.

Windows Service Name

Another obstacle is both Firebird 1.5 and Firebird 2.1's instsvc tool will use the same service name ("DefaultInstance").  Window service name must be unique at all time.  Fortunately, Firebird 2.1's instsvc supports installing service with custom service name.

Here is an example to show you how to install both Firebird 1.5 and 2.1 in single machine:

1. Run the following in privilege account.
2. Install Firebird 1.5 running in service mode.  Start the service after installation
3. Install Firebird 2.1, the setup should prompt you that another instance of Firebird is running and you have to configure Firebird 2.1 service manually.
4. Start cmd in privilege account and run "notepad %ProgramFiles%\Firebird\Firebird_2_1\firebird.conf" to configure the Firebird Service listen to port 3052:
       RemoteServicePort = 3052
   
5. Configure the Firebird 2.1 service to run as difference instance name ("Firebird 2.1"):
       "%ProgramFiles%\Firebird\Firebird_2_1\bin\instsvc" i -name "Firebird 2.1"
   
6. Start Firebird 2.1:     "%ProgramFiles%\Firebird\Firebird_2_1\bin\instsvc" start
7. Remember to configure your Firewall for port 3052
8. The hostname setting for client connection is something like "host-name/3052"

To check both service is installed properly, you may open Windows services (Start | Control Panel | Administrative Tools | Services):

You may now enjoy both Firebird 1.5 and 2.1 in same machine.  The same steps may apply to future version of Firebird and you may install as many version of Firebird you want provided you choose the port number and service name carefully.

Running more than one instance of Firebird

Using the same technique as "Running both Firebird 1.5 and 2.1 in one machine", you may also running more than one instance of Firebird service.  For example, running 3 instances of Firebird 1.5 in same machine.  To get the job done, you may obtain the Firebird Zip installation file and extract the zip files into 3 difference folders.  Just follow the above steps to configure the Firebird services.

Maximize GBAK performance

GBAK is a command line tool that allow us to perform Firebird database backup and restore operations.  We may use the tool with degraded performance if we didn't use it properly.  GBAK allow us to perform backup or restore remotely.

Restore database from Host A to Host B

In this scenario, Host A has the backup file and Host B is the machine we want the database to restore to.  We may issue the command from Host A:

gbak -c -v backup.fbk HostB:c:\...\db.fdb

This operation will degrade GBAK as it has to travel through a TCP/IP layer to transport the data packet.

To maximize performance of the operation, we copy the backup file to Host B and execute GBAK with service_mgr.  Both backup and database file are located in same Host B machine.  You will be amazed by the performance of the GBAK operations:

gbak -c -v -se HostB:service_mgr c:\..\backup.fbk c:\...\db.fdb

Reference:

1. http://www.destructor.de/firebird/instsvc.htm

Setup Visual Studio 2005 to build Firebird source

Due to I/O redirector problem in GBAK.EXE (http://tech.groups.yahoo.com/group/firebird-support/message/90995), I download Firebird source to attempt compile it under Visual Studio 2005. Here are the steps:

1. Install Visual Studio 2005
2. Read the instruction in firebird source folder: doc/README.build.msvc
3. Install Firebird server
4. Setup these environment variables:
1. FIREBIRD=C:\Program Files\Firebird\Firebird_1_5
2. ISC_USER=SYSDBA
3. ISC_PASSWORD=masterkey
4. LIB=C:\Program Files\Microsoft Visual Studio 8\VC\lib
5. PATH=C:\Program Files\Microsoft Visual Studio 8\Common7\IDE;C:\Program Files\Microsoft Visual Studio 8\VC\bin
5. Open file "builds\win32\blrtable.BAT", add an include path: -I"C:\Program Files\Microsoft Visual Studio 8\VC\include" to the cl.exe clause
6. Open folder "builds\win32\msvc7", convert 2 solution files: Firebird2.sln and Firebird2Boot.sln to Visual Studio 2005 solution format.
7. Execute cmd.exe, and run Prepare.BAT, make_boot.BAT and make_all.BAT

Connect to Firebird 1.5 database on Windows Vista using Local Protocol

Using Local Protocol to connect to Firebird database no longer works in Windows Vista when we install firebird server using setup file's default configuration. Using local connection is convenient and fast if we perform GBAK, GFIX, QLI operation compare to TCP/IP connection. To configure Firebird 1.5 works with windows vista for local connection, install the firebird server to run as application instead of service: I haven't try using local connection for Firebird 2.x install on windows vista. Some suggest that changing IPCName in firebird.conf works even running firebird as service.

How to install Firebird ODBC Drivers

The Firebird ODBC drivers may obtain from http://www.firebirdsql.org/ I haven't try the Windows Full Install .exe file yet. The following are steps for manual installation. The following files are needed:

1. IscDbc.dll
2. OdbcJdbc.dll
3. OdbcJdbcSetup.dll
4. OdbcJdbc.chm

Try your best to download these files and keep in a folder. For example: c:\temp Then go to cmd prompt to perform Manual Install

cd c:\temp %windir%\system32\RegSvr32.EXE .\OdbcJdbcSetup.dll

Manual Uninstall

cd c:\temp %windir%\system32\RegSvr32.EXE /u .\OdbcJdbcSetup.dll

Always set full path for RegSvr32.EXE and always set .\ before OdbcJdbcSetup.dll After finish install, you may go to Control Panel | Administrative Tools | Data Sources (ODBC) | Drivers to make sure the Firebird ODBC Driver has installed.