(German-language version below)

A user-defined function (UDF) is utilized to perform tasks that Firebird can't. It is an external database function written entirely in another language, such as C++ or Pascal, to perform data manipulation tasks that not directly supported by Firebird/InterBase.

As a lot of Firebird-based software developers know, these functions help you to extend the SQL language as required. But on the downside, the binary implementation requires a compiler on the platform where you want to use the Firebird server. A UDF library available for Win64 can neither be used on Win32 nor on Linux.

Up to Firebird 2.5 the UDF was an extremely powerful implementation, but unfortunately also a critical point of failure. If any of your binary code was not as stable as it should be, especially in multi-user environments, it could crash the Firebird server completely. All connected users would be disconnected, and even though the database file itself should not contain a binary error after this happened, any user application had to be restarted. This might not have been such a big problem with the Firebird Classic version, but today, using a binary UDF is no longer recommended.

This was the reason for the Firebird Project to introduce the new and more stable UDR implementation. A UDR appears to be similar to a UDF, but it is loaded in a considerably more stable way on the server process, and an error in the implementation does not typically crash the server.

Since Firebird 3, it is recommended to use a UDR rather than a UDF and, starting with Firebird 4, the use of UDFs is deactivated by default in the firebird.conf file.

A further implementation for the functional extension of Firebird was also introduced with Firebird 3: stored functions. Similar to the UDR/UDF functions, a stored function can be implemented in the same way as it can be when using a stored procedure, but the concept of functions provides only one result based on the specified input params.

As you might know, there are widely-used UDF implementations, which are all too often completely outdated, such as the FreeAdhocUDF library, which has not been changed or improved during the last 12 years. Libraries like rFunc or FreeAdhocUDF often use very high know-how standards but, based on Firebird’s built-in functions such as substring, the parameter structure is not one-to-one compatible. If you want to use your existing Firebird 2.x-based software in a new non-UDF-based Firebird version 3 or 4, you will need a replacement.

For this reason we have developed a complete new set of functions called IBExpertFunctionLibrary.

IBExpertFunctionLibrary Enterprise Maintenance Subscription

Your company licenses the source code of the library as it is, but it is only permitted to use this with databases created by your company for internal or customer use.

This includes access to all new versions of the source code and up to 5 new functional implementations for 12 months.

These functional implementations are limited to the language features already possible in the Firebird language. External software required for the implementation, such as DLLs/SO or other libraries, are not covered by this, but on request, this can also be implemented.

Pricing

    IBExpertFunctionLibrary Enterprise Maintenance Subscription: EUR 990.00 

The current version 1.0 already basically covers almost all of the functions currently available in FreeAdhocUDF and rFunc. Some functions cannot be implemented or it no longer make any sense to implement them, but these will be available as empty non-functional prototypes, so that a call will not result in an error. 

IBExpertFunctionLibrary

Wie viele Firebird Softwareentwickler wissen, helfen Ihnen diese Funktionen dabei, die SQL-Sprache nach Bedarf zu erweitern. Der Nachteil ist jedoch, dass die binäre Implementierung einen Compiler auf der Plattform erfordert, auf der Sie den Firebird Server verwenden. Eine UDF-Bibliothek, die für Win64 verfügbar ist, kann weder unter Win32 noch unter Linux verwendet werden.

Bis zu Firebird 2.5 war die UDF eine extrem leistungsfähige Implementierung, aber leider auch ein kritischer Fehlerpunkt. Wenn einer Ihrer Binärcodes nicht so stabil war, wie er sein sollte, insbesondere in Mehrbenutzerumgebungen, konnte er den Firebird Server komplett zum Absturz bringen. Alle Client-Verbindungen wurden getrennt, und obwohl die Datenbankdatei selbst keinen Binärfehler enthalten sollte, musste jeder Client neu verbunden werden. Dies mag in der Firebird Classic Version kein so großes Problem gewesen sein, aber heute wird die Verwendung einer binären UDF nicht mehr empfohlen.

Dies war der Grund für das Firebird Projekt, die neue und stabilere UDR-Implementierung einzuführen. Eine UDR scheint einer UDF ähnlich zu sein, aber sie wird auf eine wesentlich stabilere Art und Weise in den Serverprozess geladen, und ein Fehler in der Implementierung führt normalerweise nicht zum Absturz des Servers.

Seit Firebird 3 wird empfohlen, eine UDR statt einer UDF zu verwenden und ab Firebird 4 ist die Verwendung von UDFs in der Datei firebird.conf standardmäßig deaktiviert.

Eine weitere Implementierung für die funktionale Erweiterung von Firebird wurde ebenfalls mit Firebird 3 eingeführt: Stored Functions. Ähnlich wie die UDR/UDF-Funktionen kann eine Stored Function auf die gleiche Weise wie eine Stored Procedure implementiert werden, aber das Konzept der Funktionen liefert nur ein Ergebnis basierend auf den angegebenen Eingabeparametern.

Wie Sie vielleicht wissen, gibt es weit verbreitete UDF-Implementierungen, die allzu oft völlig veraltet sind, wie z. B. die FreeAdhocUDF-Bibliothek, die in den letzten 12 Jahren weder geändert noch verbessert wurde. Bibliotheken wie rFunc oder FreeAdhocUDF verwenden oft sehr hohe Know-How-Standards, aber, basierend auf Firebirds eingebauten Funktionen wie z.B. substring, ist die Parameterstruktur nicht eins-zu-eins kompatibel. Wenn Sie Ihre bestehende Firebird 2.x-basierte Software in einer neuen, nicht UDF-basierten Firebird Version 3 oder 4 verwenden möchten, benötigen Sie einen Ersatz.

Aus diesem Grund haben wir einen komplett neuen Satz von Funktionen mit dem Namen IBExpertFunctionLibrary entwickelt.

IBExpertFunctionLibrary Enterprise Maintenance Subscription

Ihr Unternehmen lizenziert den Quellcode der Bibliothek, wie er ist, aber es ist nur erlaubt, diesen mit Datenbanken zu verwenden, die von Ihrem Unternehmen für den internen oder Kundengebrauch erstellt wurden.

Dies beinhaltet den Zugriff auf alle neuen Versionen des Quellcodes und bis zu 5 neue Funktionsimplementierungen für 12 Monate.

Diese funktionalen Implementierungen sind auf die Sprachfeatures beschränkt, die bereits in der Firebird-Sprache möglich sind. Externe Software, die für die Implementierung benötigt wird, wie z.B. DLLs/SO oder andere Bibliotheken, sind davon nicht abgedeckt, können aber auf Anfrage ebenfalls implementiert werden.

Preisgestaltung

IBExpertFunctionLibrary Enterprise Maintenance Subscription: EUR 990,00

Die aktuelle Version 1.0 deckt im Grunde bereits fast alle Funktionen ab, die derzeit in FreeAdhocUDF und rFunc verfügbar sind. Einige Funktionen können nicht implementiert werden oder es macht keinen Sinn mehr, sie zu implementieren, aber diese werden als leere nicht-funktionale Prototypen verfügbar sein, so dass ein Aufruf nicht zu einem Fehler führt. 

IBExpert 2024.07.04

and bugfixes 2024.07.11, 2024.07.23, 2024.10.08

1. PSQL parser; Database Comparer

Improved parsing of parenthesized SELECT's (Firebird 5)

2. Script Executive

Support for parenthesized SELECT's added (Firebird 5)

3. PSQL Debugger

Implemented debugging of PSQL objects that contain subroutines that read/writes variables defined at the main level (Firebird 5)

4. Minor bugfixes and small improvements

IBExpert 2024.04.07

1. Database Registration

"Show server version mismatch warning" option added (Additional tab). 

If this option is ON (default) IBExpert will compare the specified and the actual version of the database and warn in case of a mismatch with a choice of possible actions.

2. PSQL Debugger

Support for time zones implemented (Firebird 4, 5). 

3. Data Grids

The possibility to dump blobs to file without preloading them into the IBExpert GUI has been implemented; it is available from the grid context menu.

Fixed the issue with incorrect representation of RDB$DB_KEY for tables with >2G records. 

4. Code Completion

Fixed the incorrect behaviour when selecting/unselecting items in the list using the mouse. 

5. Database Inside

Support for Firebird 5 databases. 

6. SQL Editor, Statements History List

Added counters for page operations. 

7. Database Comparer

Support for parenthesized SELECTs (Firebird 5) implemented. 

8. Other minor bugfixes and small improvements

IBExpert 2024.01.22

Mainly bug fixes.

IBExpert 2023.11.22

Mainly bug fixes.

IBExpert 2023.10.27

1. Code Editor, Code Completion

• Improved performance especially on databases with lot of objects.
• Suggesting possible joins based on existing relationships by foreign keys - call the Code Completion after JOIN keyword.
• There was a bug on some configurations in the previous version which caused IBExpert to freeze when the Code Completion is fires. Fixed in this version.

2. Database Inside

• Support for Firebird 4 databases.

3. Partial support for Firebird 5 implemented

• Support for all known syntax changes/additions in the PSQL parser:

     SKIP LOCKED, WHEN NOT MATCHED BY SOURCE, parenthesized SELECTs etc.

     However, there is NO support yet for parenthesized SELECTs in the Database Comparer.
     Also the PSQL debugger doesn't allow to debug roitines with subroutines which reference variables in a main routine.

• Support for new built-in functions in the Code Parameters hint.
• Support for partial indices in the index editors and metadata extract.
• Support for parallel workers in following IBEBlock functions:

    ibec_BackupDatabase, ibec_RestoreDatabase, @Services.RepairDatabase.

      There is a new option available - ParallelWorkers (or just Parallel) - which allows to specify number of parallel workers (e.g. ParallelWorkers=4)

4. Other minor bugfixes and small improvements

IBExpert 2023.09.13

Mainly bug fixes.

IBExpert 2023.07.09

1. DB Explorer

•   Activating/deactivating database objects from the Project View tree.

2. Search in Metadata

•   Possibility to search in scripts/blocks (IBE$SCRIPTS table) has been implemented.

3. Code Editors

•  Hyperlinks behaviour for package routines implemented.
• "Before load from file" event block implemented. This block allows you to perform some actions before the appearance of the "Open file" dialog; for example, change a source directory. See block template for more details.
• "Init/perform user actions" event block implemented. This block allows you to define user actions with code editor content which will be executed via a shortcut and/or from the code editor context menu (the very first item in the menu, if the block exists and is active). See block template for more details.

4. Dependencies trees

•   Solved some issues with the incorrect display of package dependencies.

5. Copy Database Object

• Support of SQL role in copy blocks added.

6. IBEBlock

• @Services.RepairDatabase and @Services.SetDBProperties functions implemented. 

    Example:

    execute ibeblock

    as
    begin
      -- Both functions return nothing (NULL) if there were no errors, 
      -- otherwise they will return an error message 

      sDBName = 'dbserver/3053:D:\databases\db.fdb';
      sConnectParams = 'ClientLib="D:\ClientLibs\fbclient_30.dll"; User=SYSDBA; Password=masterkey;';

      -- Supported repair params: Limbo, CheckDB, IgnoreChecksum, 
      -- KillShadows, Mend, Sweep, Validate, ValidateFull

      Res = @Services.RepairDatabase(:sDBName, :sConnectParams || 'Mend');

      if (Res is not null) then
        ibec_ShowMessage(Res); 

      -- All supported params are listed in the example

      Res = @Services.SetDBProperties(:sDBName, :sConnectParams || 'PageBuffers=2048; SQLDialect=3;
            SweepInterval=20000; ForcedWrites=TRUE; ReadOnly=FALSE');

      if (Res is not null) then
        ibec_ShowMessage(Res);

    end;

• ibec_ExtractMetadata function:
  ExtractPrimaryKeys and ExtractUniques options added to prevent extraction of primary keys and unique constraints. Together with the existing ExtractReferences and ExtractChecks these options allow you to suppress the extraction of all constraints.
  
  By default all of these options are ON (TRUE). To disable the extraction of primary keys, for example, set the corresponding option to FALSE: ExtractPrimaryKeys=FALSE.
  
  
• Code Editor namespace with set of functions implemented. See example of usage in the "Init/perform user actions" event block template.

7. Minor bugfixes and small improvements

IBExpert 2023.04.14

Minor bugfixes and small improvements.

IBExpert 2023.03.06

 1. Data Grid

• Possibility to edit UUID/OCTETS values in the filter criteria tree has been implemented.
• The mouse wheel can now be used to perform Page Up and Page Down actions.
• By default the Ctrl key should be held to perform PageUp/PageDown.
• To change the default modifier put the following piece of code into the After IBExpert starts event block:

    ibec_SetGlobalVar('IBE$DATA_GRID_PAGE_SCROLL_MODIFIER', <value>);

        where <value> is one of following: empty string, null, 'CTRL', 'ALT', 'SHIFT'.

        An empty string or null as a new modifier value makes PageDown/PageUp a default                behaviour of the mouse wheel in the Data Grid.

2. Code Editor

• Support of keyboard templates in the Code Completion list.
  There is a new option Show keyboard templates in Code Completion list in the Options | Editor Options | Code Insight.

3. Automatic restore of a dead connection

• By default if the database connection is lost IBExpert shows a dialog window and offers to restore a dead connection. To attempt to restore the connection automatically without user confirmation put following line of code into the After IBExpert starts event block:

    ibec_SetGlobalVar('IBE$RESTORE_DEAD_CONNECTION', TRUE);

4. Database Object Editors

• Support of object names longer than 31 chars (Firebird 4) where they were not yet supported.

5. Export Data

• Export to clipboard in JSON format implemented.

6. Code Formatter

• Support of space after opening and before closing bracket options.

7. Minor bugfixes and small improvements