Learn working with SQL-databases

When you want to learn working with SQL-databases it is easy to get started with the open source Firebird SQL-databaseserver. Because it is open source it is completely free, it is a relatively small download and a quick and easy installation.

Firebird can be downloaded at the following site: http://www.firebirdsql.org/en/downloads/

Other free SQL-databases are Microsoft's SQL-server express edition and MySQL.

Firebird is easy to install and to learn interacting with it. Install Firebird after you've downloaded the latest version.

Once you run the setup, just leave language English selected and click 'Ok'.

firebird setup step 1

Next, the following window is displayed, just click 'Next'.

firebird setup step 2

Accept the license agreement and click 'Next'.

firebird setup step 3

Read the information if you want and click 'Next'.

firebird setup step 4

Install Firebird in the default folder. If you want, you can select a different folder. Click 'Next'.

firebird setup step 5

In the following screen, just leave the selection as it is and click 'Next'.

firebird setup step 6

In the following screen, leave the name for the start menu folder as it is and click 'Next'.

firebird setup step 7

In the next screen, also just leave the selections as it is and click 'Next'.

firebird setup step 8

In the next screen just click the 'Install' button.

firebird setup step 9

Wait for the installation to complete and the following screen is displayed. Click 'Next'.

firebird setup step 10

In the last setup-screen click the 'Finish' button and you're done installing the Firebird databaseserver.

firebird setup step 11

Once Firebird database server has been installed, you can see it is running by navigating to your Windows Control Panel - Adminstrative Tools - Services. The Firebird Guardian and the Firebird Server should be in the list of services. Whenever you want, you can stop the Firebird database server by stopping the Firebird Guardian service. But don't do this now, otherwise you won't be able to use your Firebird database server. Just know that there are services running on your computer that take care of your Firebird database server.

Firebird administration

In order to be able to interact with the Firebird databaseserver, you should download software that makes interacting with the databaseserver easier.

The following programs can be used to interact with the Firebird database:

Database Workbench
EMS SQL manager for Interbase/Firebird
Flamerobin
IBExpert
IBOConsole (homepage no longer online)

I use IBOConsole a lot. It is free, however the homepage is no longer available. Maybe you can find it still online at other sites. When you can't find it, just use one of the other tools I recommend. They offer free or lite editions.

Update: the last couple of months I am using IBExpert and I like this program more than IBOConsole.

Connect to the Firebird server

Once the Firebird administration software has been installed on your computer, you have to register your Firebird server first. Find the appropriate option in the software of choice to register your Firebird server.

When registering your Firebird server, commonly you specify the following options:

The alias of the server: localhost
The name of the server or host: localhost
The username: sysdba
The password: masterkey
Optionally the portnumber: 3050

Once you've specified these options, you're able to connect with your Firebird database-server. Once connected to your Firebird database-server, you can create your Firebird database. Note that the Firebird database-server and a Firebird database are different things. A Firebird database-server can host multiple Firebird databases. Please note that sometimes the Windows firewall is blocking database traffic. In case you can't connect to the database server, just disable the firewall temporary to see if the firewall is causing the problem or open port 3050 on the firewall. After installation Firebird uses this port. Also note that it is possible to change the portnumber Firebird is using by editing the firebird.conf configuration file under the Firebird installation directory.

Creating a database is commonly done by navigating to your registered localhost Firebird database-server and navigating to 'databases'. Right-click on 'databases' and select 'create new database' (when you use Database Workbench for example). In the screen that follows commonly you have to specify the alias you want to use for your database and the location of the file of the database on your computer, e.g. C:\Firebird\Customers.gdb. Commonly you use the file-extension .gdb for Firebird databasefiles. Leave the other options in the database creation screen as it is.

Now you're done creating your first database and you're able to execute SQL-statements against your Firebird database by opening a SQL-editor in the Firebird management tool of choice. Just create a table using the CREATE TABLE SQL-command, e.g.:

CREATE TABLE CUSTOMERS
(
ID INTEGER,
NAME VARCHAR(50)
)

Commit your SQL-statement in your Firebird management tool of choice and you've created your first SQL-table in your Firebird database. Now you can navigate to this table and fill it with data. In this example no primary key has been specified for the CUSTOMERS table, but for the meantime you have an idea of how to learn working with SQL-databases.

On this site you can learn more SQL-commands. And here's another site that is very helpful explaining about SQL.

Never forget committing your SQL-command or entered data, otherwise your command or data will not be executed against the database. Committing your work is usually done by clicking the 'commit' button in your SQL-management tool of choice.

IBExpert - Script Executive window

Recently, I got an error 'Script executed with errors!' - 'Cannot perform operation -- DB is not open.' when I ran a script in IBExperts' Script Executive window. I wondered why I got this error, because I was connected to my database of choice on the database-treeview on the left side of the screen.

The reason I got this error was that on top of the Script Executive window of IBExpert there is a checkbox 'Use current connect' and this checkbox was not checked. Once I checked this checkbox, my script was running fine.

Finetuning Firebird server performance

Some time ago, I had to finetune a Firebird servers' performance. I used FBTraceManager to find out which queries were causing a heavy load of the server. Whenever you want to finetune a Firebird server I recommend using this tool, because it gives much insight into what is happening on your server.

After watching the video's on their site, I adjusted my Firebird tracelogsize in Firebird's configuration file Firebird.conf (located in the Firebird installation directory).

I adjusted the setting MaxUserTraceLogSize as follows:

MaxUserTraceLogSize = 100

This setting causes 10 times 1Mb tracelogfiles in the folder C:\ProgramData\Firebird. The result of changing this setting is that FBTraceManager can log more. When you don't adjust this setting, during logging in FBTraceManager on the Statistic tabpage you'll see the TRACE_SESSION_SUSPENDED counter increase. This counter should not increase, so you'll see optimal results in FBTraceManager. In case in your situation the above setting in not enough, just increase it little by little.

In the project settings of FBTraceManager, you should disable visualization (check the checkbox), disable log to database (uncheck the checkbox) and event processing should be disabled (uncheck the checkbox).

fbtracemanager project wizard

I use the Hotspots tabpage of FBTraceManager to find out which queries take long to complete. Note: you'll have to press the 'Refresh' button regularly.

fbtracemanager

Debugging Firebird stored procedures and triggers

I use Upscene production's Hopper tool in order to be able to debug Firebird stored procedures and triggers. Without a good tool, debugging stored procedures is not easy. My experience is that Hopper is pleasant to use. Here you'll find some screenshots of the program.

Firebird date functions

On this site is information about Firebird date functions.

Firebird database conversion

FDBConvert utility provides you with a help in converting databases of Interbase 6.0YaffilFirebird 1.x, Firebird 2.0.x and Firebird 2.1.x formats into new Firebird 2.5 version. I've personally used this handy utility and it has been very useful for converting an old Firebird version 1.5 database into a Firebird version 2.5 database.

Off course you can also just do the following, which is more time consuming:

- uninstall your current Firebird version (e.g. 2.5.x)
- install the old version of Firebird matching the database that needs upgrading
- make a database backup (using the standard Firebird gbak commandline tool)
- uninstall the old Firebird version
- reinstall your current Firebird version (e.g. 2.5.x)
- restore the backed up database (again using gbak)

NoSql databases introduction by Martin Fowler

NoSql databases differ very much from relational databases like Firebird. In the following video Martin Fowler gives an introduction into NoSql databases.

 

 

 

Learn working with SQL-databases