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
of the Borland
company. It is an open-source product under a very liberal license allowing to deliver also the SQL server installation package with client tools together with the application. The license 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 prompt, allowing to administer the system from the prompt and 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 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 OS Windows.
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
FireBird featuresAdvantages 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 (OS Windows 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) In OS Windows 2000/XP/Vista it is running either as 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 producer support. Possible problems must be solved individually by the designer. Related to this fact, there is some minor portion of slightly obsolete documentation.
Databse SQL Serveru FireBird
installation. During the installation it is necessary to go through multiple windows.
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
enables basic configuration.
a) Use Guardian to control the server.
b) Run FireBird server as service or as application. On the target application it is more common to run the server as sevice. 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.
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 MSSQL
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:
for user data accessible by the PmaDatabase
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 PROMOTIC to FireBirdThe '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
In the PmaDatabase object it is necessary to set the following configuration items:
' to the table name that will work with (if the possibility of SELECT
SQL statement will not be used when opening the table).
' 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 configuration items:
' to Database MS SQL server backups (obsolete)
or Database MS SQL server cyclic
and in the window Database FireBird cyclic
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