www.mwasoftware.co.uk

IBX for Lazarus is derived from the Open Source edition of IBX published by Borland/Inprise in 2000 under the InterBase Public License. In 2011, the Open Source edition of IBX was brought up-to-date by MWA Software and focused on the Firebird Database API for both Linux and Windows platforms (32 and 64-bit), and has since been further developed. It is released under the InterBase Public License for the original code and under the compatible Initial Developers Public License for new software. The Firebird Relational Database Management System can be downloaded from http://www.firebirdsql.org. Version 2 (IBX2) provides full support for both the legacy Firebird API and the new Firebird 3 API.

The IBX source code can be viewed and downloaded using subversion (including the latest fixes), or downloaded using the following links:

Files:
ibx4lazarus2.0-2 2.0.2 HOT

Download

This is the .zip version for use on Windows. It includes the base IBX source code, the IBX controls and the Firebird Pascal Client Interface, plus many examples and documentation.



Created
Size
Downloads
2017-02-24
4.29 MB
1,236
ibx4lazarus2.0-2.tar 2.0.2 HOT

Download

This the tar.gz version for use on Unix type systems including Linux. It includes the base IBX source code, the IBX controls and the Firebird Pascal Client Interface, plus many examples and documentation.



Created
Size
Downloads
2017-02-24
4.12 MB
421

In addition to the original set of IBX Data Access Components, IBX2 provides:

  • Full support for both the Firebird 3 API and the legacy Firebird API.

  • Support for Firebird Arrays including a new Data Aware control derived from a TCustomStringGrid.

  • Support for embedded SQL statement execution.

  • A Powerful Script Engine and SQL Parser

  • A set of extended Data Aware Controls (IB Controls)

  • Local Database Support (Embedded Database Server)

IBX2 requires a minimum of FPC 3.0.0 and Lazarus 1.6.0. A blog on what went in to creating IBX2 is available here.

The original IBX code has been split into two packages: the main IBX package which provides the non-visual components, and a new fbintf package (see separate article). The fbintf package provides a common interface for the Firebird 3 API and the legacy Firebird API and is the means by which embedded SQL statement execution is supported. The fbintf package may also be used on its own and has the potential to provide a common Firebird API to all FPC database applications.

IBX2 includes much improved documentation. There is a 114 page user guide for IBX itself and a 91 page user guide to the Firebird Pascal API provide by the fbintf package. There are also 15 example programs included in the package.

IBX2 is intended to be backwards compatible and most user applications should re-compile with no issues. However, there are differences and the user guide includes a section on upgrading to IBX2.

MWA Software is pleased to announce the release of its Firebird Pascal API package (fbintf). This package has been derived from IBX for Lazarus and is part of the IBX2 development (see separate announcement). The intent is to provide common Pascal language bindings to the Firebird API that are independent of the Firebird API version. The IBX derived code is licensed under the Interbase Public License, with the remaining code licensed under the Initial Developer's Public License.

The fbintf package provides the Firebird API in a format where all data types for SQL data, interface parameters and results are native Pascal types, including all parameter blocks and database information. The Pascal API is pitched at a similar level and purpose to the IBPP Firebird API provided to the C++ world. The package is intended to be suitable for use on any platform supported by the Free Pascal Compile, and Delphi Win32. It is a required dependency for version 2 onwards of IBX for Lazarus, and is provided as both a standalone package and as part of IBX2. Latest fixes are available via subversion and as part of the IBX trunk.

It is available for download from:

fbintf1.0-2.tar 1.0.2 HOT

Download

This package contains the Firebird Pascal Client API. It is included in IBX2 and does not have to be downloaded separately.

This is the tar.gz version for Linux and other Unixes.



Created
Size
Downloads
2017-03-06
1011.06 KB
250
fbintf1.0-2 1.0.2 HOT

Download

This package contains the Firebird Pascal Client API. It is included in IBX2 and does not have to be downloaded separately.

This is the zip version for use with Windows and is compatible with both FPC (3.0.0 onwards) and Delphi (2010 onwards).



Created
Size
Downloads
2017-03-06
1.03 MB
353

Minimum requirements:

  • FPC version 3.0.0
  • Delphi 2010

The Firebird Pascal API is intended to be simple to use and to place the minimum burden on the API user when it comes to managing the Firebird client library and the various Pascal objects that are created to provide the API. It is implemented as reference counted COM interfaces which, for the API user, are as easy to use as other managed types such as AnsiStrings and dynamic arrays. The user only needs to worry about accessing and using the interface; disposing of interfaces is performed automatically whenever an interface goes out of scope. It is intended to be a full implementation of the Firebird API, including events, services, blobs and arrays.

Two interface implementations are provided. One is for the new Firebird 3 Client API and the other for the legacy Firebird Client API used for Firebird 2.x and earlier. The Firebird 3 API implementation is used whenever possible and the older API only if the Firebird 3 API is not available.

The package is supported by a 91 page User Guide and a comprehensive test suite. The test suite includes 16 separate tests covering 104 individual test objectives. It is console mode and run from a batch script with automatic comparison against a reference results log. It uses the “heaptrc” unit to check for memory leaks. The objective of the test suite is to provide confidence in the correct operation of the software over a variety of platforms and with only expected variances between the two Firebird APIs. It is also intended to be the basis for regression testing. Documentation on the use of the test suite is provided with the package.

Given the level at which the package works and the power of Pascal “interfaces”, embedded SQL statements are possible, such as:

Attachment.ExecuteSQL([isc_tpb_write, isc_tpb_nowait,isc_tpb_concurrency],
   'Execute Procedure DELETE_EMPLOYEE ?', [8]);

and

writeln('Employee Count = ',
   MyAttachment.OpenCursorAtStart('Select count(*) from EMPLOYEE')[0].AsInteger);

The fbintf package may be used on its own or as part of IBX and has the potential to provide a common Firebird API to all FPC and Delphi database applications.

TIBLocalDBSupport is non-visual component supporting a TIBDatabase and intended to simplify the use of the embedded firebird server for Personal Database Applications, on both Linux and Windows platforms. The TIBLocalDBSupport component supports GUI programs, while the TIBCMLocalDBSupport provides the same support for console mode programs. Example applications are provided for both GUI and console mode in the ibx/examples/local-employeedb directory.

When enabled, TIBLocalDBSupport provides:

  • Verification that the embedded Firebird Server is in use.

  • Setup of the FIREBIRD environment variable for the embedded server.

  • DatabaseName, and login parameters management.

  • Use of the Firebird Services API to initialise an empty local database from a gbak format Firebird archive.

  • Use of the Firebird Services API to save the current local database to a gbak format Firebird archive.

  • Use of the Firebird Services API to replace the contents of the current local database from a gbak format Firebird archive.

  • Use of the TIBXScript Engine for automated field upgrade of the local database.

To use the component, simply drop it onto a form or data module and link it to the TIBDatabase.

Properties

Database

reference to the TIBDatabase component for the local database

DatabaseName

filename (no path) to use for the Firebird Database file.

EmptyDBArchive

filename (optional path) holding the database initialisation archive. May either be absolute path or relative to shared data directory.

Enabled

when false component does nothing

FirebirdDirectory

Full path to directory holding firebird.conf. May either be absolute path or relative to the shared data directory. If empty, defaults to shared data directory.

Name

Component Name

Options

  • iblAutoUpgrade: Automatically apply upgrade when database schema version is lower than required.

  • IblAllowDowngrade: Automatically apply downgrade when available to schema version compatible with the required version.

  • iblQuiet: true then no database overwrite warnings

RequiredVersionNo

The schema version number required by the application. TIBLocalDBSupport will normally try to upgrade/downgrade the schema to satisfy this requirement.

UpgradeConfFile

Path to upgrade configuration file. May either be absolute path or relative to the shared data directory.

VendorName

Used to construct path to Database Directory.

Note that at design time paths may use '/' or '\' as directory separator. At run time, they must be specified using the appropriate Directory Separator for the current platform.

Events:

OnGetDatabaseName

The database path name is normally computed automatically. However, this event allows an application to inspect and override the result.

OnNewDatabaseOpen

called after the successful initialisation of an empty local database.

OnGetDBVersionNo

called to get the current database schema version number. If this event is not handled then schema upgrade/downgrade is never performed.

OnGetSharedDataDir

The shared data directory is normally computed automatically. However, this event allows an application to inspect and override the result.

Shared Data Directory

The shared data directory is the base directory for all static data files used by TIBLocalDBSupport. This is determined as follows:

  • Windows: the application executable's location.

  • Unix: the application executable's location unless this is /usr/bin, /usr/local/bin, /usr/sbin or /usr/local/sbin, when the shared data directory is set to /usr/share/<application name> or /usr/local/share/<application name> depending on whether the application is in /usr or /usr/local.

Note: that the <application name> is taken from sysutils.ApplicationName and defaults to the filename of the application executable less any extension.

DatabaseName, and login parameters management

When TIBLocalDBSupport is in use, the TIBDatabase.DatabaseName property is ignored and instead, it is generated algorithmically as:

  • Windows: "User Application Directory"\VendorName\DatabaseName

  • Unix: "User Home Directory"/."VendorName"/DatabaseName

The “DatabaseName” comes from the TIBLocalDBSupport.DatabaseName property.

The “VendorName” comes from the TIBLocalDBSupport.VendorName property. If the TIBLocalDBSupport .VendorName property is left empty then Sysutils.VendorName is used. If this is empty then no VendorName component is present in the path.

Note the use of a hidden directory under Unix.

If the generated DatabaseName is not appropriate then the TIBLocalDB.OnGetDatabaseName event handler gives a chance to inspect it and change it to something different.

The Database Params are copied from the TIBDatabase component except that the “user_name” and “password” parameters are removed if present. When running under Windows, the “user_name” is then set to “SYSDBA” and the “password” to “masterkey”. Under Unix, these parameters are omitted.

Database Initialisation

When the linked TIBDatabase connected property is set to “true”, TIBLocalDB generates the DatabaseName (as described above) and then if it does not correspond to an existing file, TIBLocalDBSupport uses the Firebird Services API to create the database file from an “empty database” archive in gbak format. In practice, the archive can contain both the database metadata and initial table data.

The “empty database” archive is given by the TIBLocalDBSupport, EmptyDBArchive property. This should be a filename (usually with the .gbk extension) and may include an optional path. Relative paths areinterpreted as relative to the shared data directory.

The Services API is then used to create the initial database from this archive. An error is raised if the archive is not present.

The local database can be re-initialised at any time by calling the TIBLocalDBSupport.NewDatabase method.

Saving the Current Database

The current database contents can be saved at any time by a call to TIBLocalDBSupport.SaveDatabase. The filename for the archive can be provided in the method call. If empty, then the user is prompted to enter a filename (default extension .gbk).

The Services API is then called to archive the database to the specified file in gbak format.

Restoring the Database from an Archive

The local database can be overwritten (restored) from any archive in gbak format (including those saved using the SaveDatabase method) by calling the TIBLocalDBSupport.RestoreDatabase method. The filename for the source archive can be provide in the method call. If empty, then the user is prompted to locate the file.

The Services API is then called to restore the local database from the archive.

Database Schema Upgrade

A Software Application Update can also require a corresponding update to the database schema. With embedded Firebird server applications where the user may not even be aware that a database server is in use, it is important to have a means to field upgrade the database schema in as seamless and automatic a manner as possible. TIBLocalDBSupport supports a suitable mechanism using the TIBXScript engine.

The underlying idea is that the database schema comes with a version number given as a single integer. The first version to be released is version 1, the second is version 2 and so on. The current schema version number must be saved as data somewhere in the database. As this is database schema dependent, TIBLocalDBSupport does not know how to determine the current database schema number and instead relies upon the application responding to the OnGetDBVersionNo event.

Each version of an application will have a maximum and minimum version of the database schema that it can support, and it is expected to check that the schema version is acceptable in its TIBDatabase OnConnect handler. However, before this handler is called, TIBLocalDBSupport will itself check the current schema version against its RequiredVersionNo property (which should be set to the maximum supported schema version no).

  • If iblAutoUpgrade is given in the Options property and the current schema is less than the Required Version no., then TIBLocalDBSupport will attempt to apply the upgrade rules to raise the version number to that required.

  • If iblAllowDowngrade is given in the Options property and the current schema is greater than the Required Version no., then TIBLocalDBSupport will attempt to locate a suitable backup archive and restore this as the current database. This case is usually only found in the unlikely event of a failed upgrade and the user has installed an older version of the software in order to recover from the problem.

The schema upgrade rules are read from the upgrade configuration file. This is a text file in “ini” file format with the following sections:

[status]

This should have a single named value “current” giving the current database schema number as in integer e.g.

current = 2

This should normally be set to the same value as the RequiredVersionNo property and acts as a check to ensure that both are in sync.

[Version.nnn]

Where nnn is an integer with leaving zeroes. For example, “Version.002” is the section read to upgrade the database schema from version 1 to version 2. This section can contain the following named values:

Name

Type

Use

Upgrade

string

Name and optional path to the SQL script used to perform the upgrade. May either be absolute path or relative to the upgrade configuration file. Either forwards or back slashes may be used as the path delimiter.

Msg

string

Text message displayed in progress dialog while script is active. Defaults to “Upgrading Database Schema to Version nnn”.

BackupDatabase

yes/no

If present and set to “yes” then a database backup in gbak format is made before the upgrade is performed. The backup file is located in the same directory as the database file and is given the same name as the database file with the extension replaced with “.nnn.gbak”. Where “nnn” is the current schema version number (i.e. prior to running the upgrade script).

<Parameter Name>

string

Name and optional path to binary data file. May either be absolute path or relative to the upgrade configuration file. Either forwards or back slashes may be used as the path delimiter.

For example:

[Version.002]

Msg = Upgrading to Version 2

BackupDatabase = yes

Upgrade = patches/02-patch.sql

mugshot = images/man.png.gz

Note that in the above, “mugshot” is intended to be used to resolve an Update, Insert or Delete query parameter in the 02-patch.sql file. E.g.

Update EMPLOYEE Set Photo =:MUGSHOT Where Emp_no = 2;

This is only applicable to BLOB columns and the above is interpreted as update the EMPLOYEE table where the Emp_no is “2” and set the value of the Photo column to the binary data contained in the file “images/man.png.gz”. The “.gz” extension is recognised as a gzip compressed file and decompressed before updating the table.

When the current database schema is more than one version number less than that required, the upgrade rules are applied iteratively to upgrade the database to the required schema version.

 

TIBXScript script engine runs an SQL script from a file or stream. The text is parsed into SQL statements which are executed in turn. The intention is to be compatible with Firebird's ISQL command line utility, but with extensions:

  • All DML and DDL Statements are supported.

  • CREATE DATABASE, DROP DATABASE, CONNECT and COMMIT are supported.

  • The following SET statements are supported:

    • SET SQL DIALECT

    • SET TERM

    • SET AUTODDL

    • SET BAIL

    • SET ECHO

    • SET COUNT

    • SET STATS

    • SET NAMES <character set>

  • New Command: RECONNECT. Performs a commit followed by disconnecting and reconnecting to the database.

  • Procedure Bodies (BEGIN .. END blocks) are self-delimiting and do not need an extra terminator. If a terminator is present, this is treated as an empty statement. The result is ISQL compatible, but does not require the use of SET TERM.

  • DML statements may have arguments in IBX format (e.g UPDATE MYTABLE Set data = :mydata). Arguments are valid only for BLOB columns and are resolved using the GetParamValue event. This returns the blobid to be used. A typical use of the event is to read binary data from a file, save it in a blob stream and return the blob id.

  • The simple XML formats for binary blob data (see 7.6.1) and array data (see 7.6.2) as export by TIBExtract (see 7.6) are supported.

  • C++ style comment lines.

Select SQL statements are not directly supported but can be handled by an external handler (OnSelectSQL event). If the handler is not present then an exception is raised if a Select SQL statement is found.

Properties:

Database

Link to a TIBDatabase component

Transaction

Link to a TIBTransaction. Defaults to internal transaction (concurrency, wait)

AutoDDL

When true, DDL statements are always committed after execution

Echo

When true, all SQL statements are echoed to log

StopOnFirstError

When true the script engine terminates on the first SQL Error.

IgnoreGrants

When true, grant statements are silently discarded. This can be useful when applying a script using the Embedded Server.

ShowAffectedRows

When true, the number of affected rows is written to the log after a DML statement is executed.

ShowPerformanceStats

When true, performance statistics (in ISQL format) are written to the log after a DML statement is executed.

DataOutputFormatter

Identifies a Data Output Formatter component used to format the results of executing a Select Statement.

 

Events:

GetParamValue

called when an SQL parameter is found (in PSQL :name format). This is only called for blob fields. Handler should return the BlobID to be used as the parameter value. If not present an exception is raised when a parameter is found.

Hint: use TIBBlobStream to create and read the blob from a file.

OnOutputLog

Called to write SQL Statements to the log (stdout)

OnErrorLog

Called to write all other messages to the log (stderr)

OnProgressEvent

Progress bar support. If Reset is true the value is maximum value of progress bar. Otherwise called to step progress bar.

OnSelectSQL

handler for select SQL statements. If not present, then the DataOutputFormatter is used to process select SQL statements. If neither an OnSelect Handler or a DataOutputFormatter is defined then select statements. result in an exception.

An OnSelectSQL handler may either process the select statement itself or call TIBXScript.DefaultSelectSQLHandler to invoke default processing as described above.

OnSetStatement

Handler for unrecognised SET Statements.

 

Usage:

The following TIBXScript functions may be used to execute an SQL statement or script:

function RunScript(SQLFile: string): boolean; overload;

function RunScript(SQLStream: TStream): boolean; overload;

function RunScript(SQLLines: TStrings): boolean; overload;

function ExecSQLScript(sql: string): boolean;

An SQL script may be passed as a File, a stream, a TStrings or as a single string. The above functions differ only in the way the script is provided. Otherwise, they are identical. The script is parsed into statements and executed one statement at a time in the order given in the script. The function returns true if all statements have been successfully executed and false otherwise.

Examples

Two example programs are provided in the ibx/examples directory that illustrate the use of TIBXScript in both GUI and console mode. These are:

  1. ibx/examples/scriptengine

  2. ibx/examples/fbsql

The Script Engine Example

This example application illustrates use of the TIBXScript SQL script engine. It works with the example employee database and comes with various test scripts to illustrate how it works. These are all located in the "tests" directory.

Compile and run the application after first ensuring that the example employee database is available on the local server. If it is on a remote server, then you will have to adjust the IBDatabase1.DatabaseName property accordingly.

You can just type SQL queries into the left hand text box and click on "Execute" to run them. The results appear in the right hand text box. Select queries are supported by opening a new dynamically created window with a grid containing the query results. This window is non-modal and multiple query results can be shown simultaneously. The grid is a TIBDynamicGrid and clicking on the column header will resort the grid using the selected column.

The test scripts are loaded in the left hand text box by clicking on the "Load Script" button. The scripts are:

1. CreateCountriesTable.sql

This adds a new table "COUNTRIES" to the employee database and then populates it with country data including the country name and ISO2 and 3 character shortnames. At the end of the script, the contents of the new table are displayed.

2. CreateCountriesTablewithError.sql

This does the same as the above, except that the first insert statement contains a syntax error. It may be used to experiment with the "Stop on First Error" checkbox, and shows how the script engine can recover and continue from (some) syntax errors.

3. DeptListView.sql

This script adds a complex View to the database and tests the script engine in complex scenarios, such as recursive queries.

4. createproc.sql

This script adds three simple stored procedures. It demonstrates the different ways that procedure bodies can be declared (ISQL compatible, standard terminator and no terminator). Use of comments is also demonstrated.

5. ParameterisedQueries.sql

This script demonstrates the use of PSQL style query parameters for BLOB columns. In this case a new column "Image" is added to the COUNTRY Table and an image in png format (the flag of St George) is added to the entry for England. The value of the Image column is given by a parameter ":MyImage". This is resolved by the application which asks for the file containing the image to be placed in the field.

You should locate and return the "flag_en.png" file.

Note that the interactive resolution of the parameter is an example. The parameter resolution is carried out by an event handler that could, for example, have looked for a file which might conventionally have been called "MyImage.bin" to correspond to the query parameter.

6. Reverseall.sql

Reverses out the above.

7. SelectQuery.sql

Illustrates handling of select queries.

The fbsql Console Mode Application

fbsql is more than just a simple example and is an ISQL replacement console mode program for both interactive and non-interactive use. fbsql uses TIBXScript as its SQL Script Engine and TIBExtract to extract metadata from the database. Select queries are handled by by outputing the query results to stdout in CSV format suitable for loading into a spreadsheet, as insert statements, or in a block format. It also includes an interactive version of TIBXScript.

Usage: fbsql <options> <database name>

Options:

-a write database metadata to stdout
-A write database metadata and table data to stdout
-b stop on first error
-e echo sql statements to stdout
-i <filename> execute SQL script from file
-h show this information
-o <filename> output to this file instead of stdout
-p <password> provide password on command line (insecure)
-r <rolename> open database with this rolename
-s <sql> Execute SQL text
-t specify output format for SQL Statements
      BLK (default) for block format
      CSV (default) for CSV format
      INS (default) for Insert Statement format
-u <username> open database with this username (defaults to SYSDBA)

Environment Variables:

ISC_USER Login user Name

ISC_PASSWORD Login password

Saving the username and/or password as environment variables avoids having to enter them on the command line and is a more secure means of provding the password.

If no password is provided on the command line or through the environment, then the user is prompted for a password to be entered securely.

If neither an "-s" or a "-i" option is provided on the command line, then fbsql runs interactively.

fbsql uses IBX in console mode. Before opening this project you should tell the Lazarus IDE about the ibexpressconsolemode package. All you need to do in the IDE is to select "Packages->Open Package File" and open ibexpressconsolemode.lpk which you can find in the ibx root directory. You should then close it again immediately afterwards. There is no need to install or compile it. Opening the package is sufficient for Lazarus to remember it.

SQL Statements Supported

  • All DML and DDL Statements are supported.

  • CREATE DATABASE, DROP DATABASE, CONNECT and COMMIT are supported.

  • Additionally, RECONNECT is interpreted as dropping the connection and reconnecting.

ISQL Command Support

  • SET SQL DIALECT

  • SET TERM

  • SET AUTODDL

  • SET BAIL

  • SET ECHO

  • SET COUNT

  • SET STATS

  • SET NAMES <character set>

  • SET HEADING

  • SET ROWCOUNT

  • SET PLAN

  • SET PLAN ONLY

  • QUIT

  • EXIT

To use, compile the program in the Lazarus IDE and run it from the command line. See above for the command line parameters. For example:

fbsql -a -u SYSDBA -p masterkey employee

will write out the metadata for the local employee database to stdout (assuming default password).

fbsql -A -u SYSDBA -p masterkey -o employeedump.sql employee

will dump the employee database, include data, to a text file (employeedump.sql).

fbsql -u SYSDBA -p masterkey -i employeedump.sql

will recreate the database dumped in the file "employeedump.sql". Note that the "CREATE DATABASE" statement is at the start of this file and should be edited to identify the database file that is to be created. Alternatively,

fbsql -u SYSDBA -p masterkey -i employeedump.sql new-employee.fdb

will restore the database to the database file 'new-employee.fdb' provided that it has already been created as an empty database. Note that in this case, the "CREATE DATABASE" statement should remain commented out.

fbsql -s "Select * From EMPLOYEE" -u SYSDBA -p masterkey employee

will write out the contents of the EMPLOYEE table in the local employee database to stdout (assuming default password).

fbsql -b -e ../scriptengine/tests/CreateCountriesTable.sql -u SYSDBA -p masterkey employee

will run the script CreateCountriesTable.sql from the script engine test suite and apply it to the local employee database. Each statement will be echoed to stdout and processing will stop on the first error.

Note that on Linux, to run a program from the command line that is not on the PATH, you need to:

cd to the example directory "ibx/examples/fbsql"

run the program as "./fbsql" e.g.

./fbsql -a -u SYSDBA -p masterkey employee