Promotic
WikipediaLinkedInYoutubeTwitterFacebook

FireBird

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 licence allowing 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 sourceforge.net/projects/firebird/. The FireBird and InterBase database topics are covered by the www.ibphoenix.com/ webpages, 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 disc. 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 included in 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 www.mengoni.it/Downloads/IBOConsoleSetup.exe. A list and description of the SQL commands is also included in the IBOConsole help.

 
The database can be filled:
- by the PmTrend object, if the Storage type configurator is set in this object to Database FireBird cyclic
- by the PmAdo object
- by the PmDatabase (obsolete) object via named ODBC source

FireBird features

The advantages of using the FireBird with 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 disc.
6) The installation is easy, supporting majority of operation systems (MS 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 a system service (running, even if no user is logged in), or as application (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 user. 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 fro 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 a 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 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 PmDatabase object and 'pm_trend' for saving the trends by the PmTrend object. Create 3 user accounts, where 'pm_admin' has the administrator rights, 'pm_writer' has the rights for read and write and can create, delete and modify the table structure, and 'pm_reader' has rights only for data reading.

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 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 PmAdo and PmDatabase objects.

In PmDatabase object it is necessary to configure following items:

1) 'Technology' to 'Access through ODBC'.
2) 'Database type' to 'ODBC; Data source'.
3) 'Database (file, folder, data source)' to the name of registered ODBC source, for example to 'pm_data'.
4) 'Table (file)' to the table name that will work with (in case of the possibility of SELECT SQL command will not be used when opening the table).
5) 'User' to the name of the SQL Server user, for example to 'pm_reader'.
6) 'Password' to the password of corresponding SQL Server user for authentization.
 
The 'pm_trend' database is designed for trend data of the PROMOTIC application, this database is used by the trend component and the user is not supposed to make any modifications of this database. For trends the PmTrend object is used.

In this object the following items must be configured:

1) 'Storage type' to Database MS SQL server backups (obsolete) or Database MS SQL server cyclic.
2) 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, see:
© MICROSYS, spol. s r. o.Tavičská 845/21 703 00 Ostrava-Vítkovice