Using FireBird RDBMS

FireBird is an open-source RDBMS (such as MySQL), which runs on Windows, Linux and UNIX platforms. FireBird scores better than MySQL since it supports triggers, procedures, UDFs (User Defined Functions), roles, exceptions and database shadows. Its functionality is, in fact, similar to RDBMSs such as MySQL and Oracle, finding use in production systems. As another example, if you want to shift the database for your accounting package to an RDBMS and want to do that for free, you can try FireBird. It comes with various programming interfaces, such as ODBC, ADO.NET and JDBC. We will see how to install and configure this RDBMS on Windows, and connect it to a sample VB 6.0 application.

Configuration



FireBird can be deployed on all Windows platforms. This month's PCQ Essential CD carries the Windows installer (FireBird-1.5.1.4481-Win32.exe). Copy this to your desktop and double click on it to run. This will install FireBird to 'C:\program files\FireBird' by default. After installation, once you reboot your machine, FireBird will be up and running in the background.

To confirm, click on Start>Control Panel and double click on 'FireBird 1.5 Server Manager'. You will get a window showing the status of your FireBird database. Here check on the 'Use the Guardian' check box, which automatically restarts Firebird, if the server crashes.

The FireBird administrative account is called 'sysdba' and its default password is 'masterkey'. In a production environment, you should change the password. For this, open a command window and do as follows.

C:\Program Files\Firebird\Firebird_1_5\bin> gsec -user 'sysdba' -password 'masterkey'



GSEC>modify sysdba -pw

Here is the new password you want for this (sysdba) account. You can use the same command with a different username as the first parameter of 'modify' to change the password for that user.

Creating a database



It's time to create your first FireBird database. These are created with a '.fdb' extension. Open a Windows command prompt and execute the following.

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



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


SQL>create database 'c:\data.fdb' user 'SYSDBA' password 'masterkey';


SQL>exit;

These will create a FireBird database called data.fdb in C:\ using the default database administrator account and its password.

Creating users



The next step would be to create users for the database created above, to provide user-level security to the database. The SYSDBA can access any database. To create a new user, run the following commands.

C:\Program Files\Firebird\Firebird_1_5\bin> gsec -user 'sysdba' -password 'masterkey'



GSEC>add testuser -pw test

This will create a user 'testuser' with password 'test'. You do this using the sysdba account.

Configuring ODBC



FireBird provides connectors for ODBC, ADO.NET and JDBC. To configure ODBC, first install FireBird ODBC driver (on this month's CD). Click on Start>Settings>Control Panel>Administrative Tools, and double click on 'Data Source (ODBC)'. This will open a 'Data Source (ODBC) administrator' window, where you select the 'System DSN' tab and click on Add. Now you will get a list of ODBC drivers from various vendors. Select 'FireBird/Intebase driver' and click on Finish.

Next, you will get a 'FireBird ODBC Setup' window. Give a name to your database connection in the 'DSN (Data Source Name)' text box. Then under 'Database' give the full path of your database file (.fdb). Lastly, give the user name and password for this database in 'Database Account' and 'Password', respectively. To test your settings, click on 'Test Connection'. Finally, click Finish to exit.

Creating a VB front



We have used VB 6.0 to create our test application. To start, open a New Project in VB and select the Standard EXE project type. You will get a designer showing "Form1". Now from the toolbox double click both the ADO Data Control (created as 'adodc1') and the DBGrid (created as 'dbgrid1') to draw them both on Form1.

On Form1, select 'Data Control' and press F4 to show its properties sheet. On this, select the 'Connection String' property. Now you will be asked for details of your connection. Check the 'Use Connection String' option and the 'Build' button. On the 'Data Link Properties' window that you get next, select 'Microsoft OLE DB Provider for ODBC' from the list and click on Next. In the same window, under the Connection tab, select the DSN (created above) from the 'Use data source name' drop down. Again, click on the 'Test Connection' button to check your settings. Click on OK to finish.

On the same Properties box, select the 'Record Source' property. From the command type drop down, select 'adCmdTable' and then select the 'FireBird table' you want to connect to from the 'Tables and Stored Procedure Name' drop down. Now you need to bind the datagrid to the ADO control on the form so that you can view/manipulate the data. For this, go to the datagrid's Properties and select the 'data source' property, select 'adodc1' to bind.

The demo application is ready, which will show the data from the selected table from the database and even allow you to

make changes in it. You can use any ODBC/JDBC/ ADO compliant system to connect including languages like PHP and

ASP.NET.

Sanjay Majumder