FireBird - database SQL server
For the needs of smaller database applications the freeware SQL database
FireBird can be used with the PROMOTIC system. This community project has demerged from the commercial product
InterBase by the
Borland company. It is an open-source product under a very liberal licence that allows to deliver also the SQL server installation package with client tools together with the application. The licence also allows to use this product in commercial sphere (contrary to the
MySQL database). The important fact is that
FireBird has no artificial functionality limitation (that is commonly used for example in the MS SQL Express Edition - limited database size, memory and CPU utilization, etc.)
The product is downloadable from the project Web
http://sourceforge.net/projects/firebird. The
FireBird and
InterBase database topics are covered by the
http://www.ibphoenix.com Web pages, including the links (in the
Downloads section) to
FireBird installation packages together with multiple additional utilities (freeware and commercial).
A part of the
FireBird program is always an administrative text console
ISQL.EXE with the command line, allowing to administer the system from the command line and allows to run the SQL scripts from the disk. Another text console
GSEC.EXE can be used for managing the users of the SQL database. The description of the
FireBird database installation and basic configuration including the table data access is published in the
Quick Start Guide, that is a part of the installation package and accessible from the
Start menu in
Windows OS.
However for the configuration of the SQL server is faster, advantageous and easier to use one of the graphic administration tools. The most commonly used tool for this purpose is probably the
IBOConsole, that is also available for free. The installation package of the graphic administration tool
IBOConsole can also be downloaded from previously mentioned Web, or directly from its author´s pages at
http://www.mengoni.it/Downloads/IBOConsoleSetup.exe. A list and description of the SQL statements is also included in the
IBOConsole help.
The database can be filled:
- by the
PmaDatabase object (obsolete) via named
ODBC source
FireBird features
Advantages of using the FireBird in the PROMOTIC system:
1) It can be used free of charge.
2) The product functionality is not artificialy limited.
3) It is a powerful, stable and technologically developed product.
4) The configuration and functionality is more simple than for example the MS SQL Server.
5) Each database is located in one independent file, that can be located anywhere on the disk.
6) The installation is easy, supporting majority of operation systems (Windows OS and Linux).
7) Easy backup, access rights management, transaction evaluation, replication, etc.
8) Network access without the need of file sharing.
9) Using the internal SQL language for programming data checking, recounts and filters directly into the SQL server.
10) It is running either as Windows service (running, even if no user is logged-in), or as application program (tray icon).
11) It is possible to set some PROMOTIC components to save data into FireBird (e.g. trends).
Disadvantages and limitations of FireBird:
1) It is a community project, without the official manufacturer support. Possible problems must be solved individually by the designer. Related to this fact, there is some minor portion of slightly obsolete documentation.
FireBird installation
Databse SQL Serveru
FireBird installation. During the installation it is necessary to go through multiple windows.
1) Component selection box (
Select componets) allows to select the installation type.
a) Full installation of Server and development tools. Installation of the SQL server FireBird (including the client tools and ODBC driver).
b) Minimum client install. Installation of the ODBC driver for accessing the FireBird SQL server on another computer.
2) Select Additional tasks allows basic configuration.
a) Use Guardian to control the server.
b) Run FireBird server as Windows service or as application. On the target application it is more common to run the server as Windows service. On the development computer it is handy to run he server as application.
c) Start FireBird automatically everytime you boot up.
d) Copy Firebird client library to system Windows folder. Caution: This option must be checked in order to make the IBOConsole work properly.
FireBird configuration
In order to use the installed
FireBird with the PROMOTIC application, it is necessary to complete its basic configuration (create the databases and users with preconfigured access rights). The easiest way to do so is to use the
IBOConsole client tools. For the PROMOTIC system, we recommend to create the databases and database users similarly as for the
MS SQL Server (see:
MS SQL Server - description, installation and management).
Caution: The administrator account in
FireBird is named
SYSDBA and after the installation the predefined password is
masterkey. Using the graphic administration tool
IBOConsole or text console
GSEC.EXE it is necessary to change this predefined password.
Create two databases:
-
'pm_data' for user data accessible by the
PmaAdo object
Create 3 user accounts:
- 'pm_admin' has the administrator rights
- 'pm_writer' has the rights for read and write and can create, delete and modify the table structure
- 'pm_reader' has rights read only
Then create ODBC source
'pm_data' for accessing the database
'pm_data' via ODBC, using the ODBC data source administrator (
ODBCAD32.EXE program).
Access from the PROMOTIC application to FireBird
The 'pm_data' database is designed for PROMOTIC application user data and it is not used by any PROMOTIC system component. This database can contain only user data. The application can work with this data via the ODBC interface (registered data ODBC source
'pm_data') using the
PmaAdo and
PmaDatabase objects.
In the
PmaDatabase object it is necessary to set the following configurators:
4) '
Table (file)' to the table name that will work with (if the Possibility of
SELECT SQL statement will not be used when opening the table).
5) '
User' to the name of the SQL Server user, for example to
'pm_reader'.
The 'pm_trend' database is designed for trend data of the PROMOTIC application, this database is used by the trend component and the designer is not supposed to make any modifications of this database. For trends the
PmaTrendGroup object is used.
In this object, it is necessary to set the following configurators:
1) the "
Storage type" configurator to
Database MS SQL server backups (obsolete) or
Database MS SQL server cyclic.
2) and in the "
Database FireBird cyclic" window it is necessary to configure the items referring the database, used for data writing.
That are preconfigured for example as:
server=localhost, uid=pm_writer, pwd=writer, database=pm_trend
(server name
localhost can be different).
For next descriptions to the
Firebird, see: