IBExpert und Firebird Power Workshop

(German-language version below)

Firebird Administrator intensive training with emphasis on speed, security and interfaces.

In our Firebird Power Workshop, we focus on the tasks that an administrator needs to perform to effectively run a Firebird server. The aim is to achieve the optimum speed of the hardware and software used, to guarantee maximum operational reliability, and to be optimally prepared for a breakdown by means of a suitable worst-case scenario. We will also concentrate on the independent implementation of simple requirements in the areas of import, export and reporting.

Overview of topics

1. Basics

After an introduction to the basic Firebird technology, the most important files and installation versions for Windows and Linux with Firebird version 5 will be explained.

2. Hardware

• Using suitable tools, the Firebird performance with the hardware/software combination used is measured and compared with other systems
• Effects of Firebird versions and configurations for processors, memory, I/O load, and process priority
• Comparison of the different I/O subsystem techniques HDD, SSD, SATA, M.2, NVME, RAID, SAN, IOPS, Bandwidth, Reads, Writes, etc.
• Comparison of different CPU types, clock cycle, L1/L2/L3 cache, cores, Xeon E3/E5/E7
• Expedient use of memory, RAM disk, temporary memory, ECC, DDR, clock cycle
• Locate the bottleneck in your own server
• Supplementary hardware for performance optimization
• Recommendations for optimal hardware

3. Operating system

• What optimizations can I make in Windows and Linux operating systems?
• Comparison of a Firebird server running on a virtual machine or without virtualization
• Check relevant drivers and adjust configuration settings
• DLL version check and effects
• Effects and adjustments Antivirus, backup and image software
• Trojan-safe configuration
• Operating systems recommendations for Firebird and dependencies

4. Backup/Restore

• Data backup with gbak and other solutions
• The most important backup/restore parameters
• Partial backup of the database
• IBExpert tools for backup, restore, e-mail message in case of errors, database dump
• IBExpert tools for automating database comparisons, data and metadata using scripts
• Shadow copy as a turbo backup
• Suitable measures to prevent errors from the outset
• Concurrent incremental backup
• Basic replication knowledge

5. Troubleshooting

• Detect errors in the database
• Troubleshooting strategies and tools
• Correct errors using transaction counter limit
• Detect and correct incorrect metadata
• Recognize and correct faulty data
• Combine databases from different scripts
• Errors opening the database and gfix
• Errors after opening the database when accessing certain table data
• IBExpert repair tools, Database Inside, if nothing else helps
• IBExpert services and Hotline support for repairs

6. Interfaces

• Automatically generate reports with IBExpert Scripting and store them as PDF files or send them by e-mail
• Set up data interfaces for ODBC
• Data export to ODBC, CSV, Excel, HTML, etc. using IBExpert Scripting
• Data import from ODBC, CSV, Excel, HTML, etc. using IBExpert Scripting
• Read or write access to SMTP, POP3 and IMAP e-mail
• Automate data synchronization with script directly to MSSQL, Oracle, MySQL, etc.
• Convert data tables from or to other platforms
• Direct connection using additional tools to PayPal, Sharepoint, SAP, Salesforce, Magento, Dropbox, Google Calendar, Google Contacts, JSON, REST, XML etc.

7. Users and groups

• Create, change and delete users with password
• Create, change and delete roles
• Differences users, roles and permissions in Firebird 5
• Transfering the user database when updating Firebird

8. Database performance

• System tables and tools to detect long-running SQL queries
• Identify and eliminate the impact of long-running transactions
• Trace API and MON$ tables
• Detect and create missing indices
• Comprehending and using Expression Indices
• Understanding and updating index statistics
• Optimize index structures and avoid duplicates

9. Monitoring and Audit

• Log database changes with IBExpert log tools
• Log slow SQLs around the clock
• What's it doing now? External tools at operating system level for file system and process logging

10. Tips and tricks

---

IBExpert und Firebird Power Workshop

---

---

IBExpert Replicator Enterprise IBER

The new IBExpert Replicator Enterprise is now ready!
Add real-time replication to any Firebird database without any downtime!

The new IBER solution will be installed in a remote session by an IBExpert engineer on a Windows or Linux machine (virtual or physical) in your network while your employees are working on the database. 

What do you need for this? We recommend installing the IBER on a virtual machine, so that in the event of a unrecoverable failure of your existing production server, making it impossible to use the database on that machine, you or our engineer can activate the replicated database as a new production database by running a single batch file.

The existing replication master server can be any Linux or Windows machine in your local network. We recommend a server, which will execute the IBExpert Benchmark with a least 100% in each category. If faster servers are needed, please consider our IFS Server Hardware.

The replication target server can be any physical or virtual machine also running Windows or Linux. 

It should be preinstalled with the same Firebird installation that is already used on your existing master server. If our help is needed and you are not sure how to copy such a configuration, our engineers can evaluate your master server installation and transfer the Firebird server to a second machine following a very brief out time.

The IBER virtual machine can run on a separate machine or also on your replication target server as a virtual machine. If you do not have any preferred VM system, we will deploy the free VirtualBox software.

The IBER basic price includes up to one hour of remote session work for the setup. This requires that we get the above setup already preinstalled and the customer can give us the connection strings/SYSDBA or Owner and password of the Firebird databases used. Also, we expect to have the above-mentioned IBER VM and target server with a similar Firebird server already preinstalled. The basic price covers up to 2 databases to be replicated.  

If the customer is unable to carry out these preparations, we can perform additional configuration work based on our IBExpert Hotline package rates. This basically means: If you do not have your own IT manager, who is able to implement and document all above steps for us, and also your software manufacturer does not want to help you on this task, you can be confident that our IBExpert engineers will make it work. 

The IBER Product also includes 12-month emergency service in case of unrecoverable errors on our master server up to 4 times per year. If this happens more often, you should consider changing your server hardware. A IBExpert hotline package will still allow you to use our service, also when issues are to be solved that are not connected to the IBER replication. If such an emergency case is reported to us, but we see that a simple server restart solves the problem or the problem has nothing to do with Firebird, we will charge the time on the customer’s hotline account.

At the end of the initial 12-month period, we will charge you for a further 12 months with the IBER Subscription package, if our help and software updates are still required. Without the continued subscription, the software will not cease working, but in case of an emergency, our remote support then requires a prepaid hotline package.

What do you get?

After the basic installation carried out by our engineers remotely, which is typically done using our GoTo Meeting conference system, you will see a number of separate batch files on the IBER virtual machine which will perform the following tasks:

a) Create and init a new replication target for a specific master database
b) Drop any replication technology from a specific master database
c) Execute the replication based on Firebird events
d) Convert a target database to be a 100% replacement for the master database

If your software manufacturer does not want any alterations made in the database or does not directly support our technology, you can always execute script b) whenever the software manufacture applies, for example, his updates on the database, since this script will completely remove any IBER object from the database.  

After the updates have been applied, you only need to run the script a) again to recreate any necessary IBER objects and init the new replication target again. 

After init has been run, script c) will start and execute all operations (insert/update/deletes) performed in the master database, almost in real time, onto the replicated target database. If script c) cannot connect to master or target database, for example because of a temporary server reboot, it will ignore the errors for a few seconds and, after the reconnection is made, will process all records that were logged while the connection was not possible. 

If a backup and restore of the existing database is performed, a new init process using script a) is mandatory. A backup alone does not need such a new init.

The IBER replication can be interrupted at any time, due to a local power cut or whatever other reason. Once the systems are back online, there is no manual work required to reactivate the replication again and no new sync is necessary. Everything that is done on the master database will be immediately replicated to the target database right after the IBER VM and the target server can be reached again.

Important: If your software manufacturer claims that any manipulation of the database structure will result in a loss of warranty, you should request the manufacturer get in touch with us, since we can guarantee that our system has no negative impact on any Firebird 2.5-Firebird 5.0 databases except for a very low level of additional server performance which the IBER system will operate via one additionally connected database user. 

Zero maintenance and full real-time replication. 

Pricing

IBExpert Replicator Enterprise IBER Starter Package including init and 12 months support 1020 €.
IBExpert Replicator Enterprise IBERSub Subscription Package 12 months support 610 €.

IBExpert Developer Studio Edition

(German-language version below)

The IBExpert Developer Studio Edition contains a IBExpert Developer Studio Full Version with a 12 month software subscription valid for 12 months from date of purchase.

---

IBExpert Developer Studio Edition

IBExpert products, services & prices

Below you can find a summary of all IBExpert products and services.

Click on the corresponding  icon, you will automatically be forwarded to PayPro Global, responsible for the global sales of our software products.

Software

Services & Support

IBExpert Developer Studio Edition IBExpert Developer Studio Day Edition IBExpert Developer Studio Company Year Edition IBExpert Enterprise Server Year Edition IBExpert Server Tools IBExpert Distribution OEM Edition IBExpert Encryption OEM Edition IBExpert Replicator Enterprise IBER IBExpertFunctionLibrary IBExpert Benchmark Tool - Free special version IBExpert Developer Studio Personal Edition IBExpert Developer Studio Full Educational Edition

IBExpert services & support IBExpert & Firebird Prepaid Hotline Database analysis IBExpert Firebird SQL Performance Monitoring Firebird Server Health Check & Backup Monitoring IBExpert Firebird Replication Remote Firebird 4/5 Update Workshop IBExpert and Firebird Training and Workshops Firebird IFSLR Server Firebird IFSMR Server

---

IBExpert Developer Studio Edition

IBExpert Developer Studio is the most comprehensive GUI tool for Firebird and InterBase^® on the market today. It includes the following components: IBExpert IDE, IBEScript.exe, and IBExpertDemoDB. Read the full software product details here.

IBExpert Developer Studio Packages	No. of activations	Subscription for the first 12 months Online price in Euros (EUR) net of sales tax/VAT		Subscription for the next 12 months Online price in Euros (EUR) net of sales tax/VAT	Subscription expiry more than 12 months Online price in Euros (EUR) net of sales tax/VAT	Subscription expiry more than 4 years Online price in Euros (EUR) net of sales tax/VAT
IBExpert Developer Studio Single	1	289.00		137.00	215.00	240.00
IBExpert Developer Studio multiple 2-4	2-4	259.00 each		119.00 each	189.00 each	220.00
IBExpert Developer Studio multiple 5-9	5-9	219.00 each		102.00 each	159.00 each	190.00
IBExpert Developer Studio multiple 10-24	10-24	169.00 each		78.00 each	122.00 each	140.00
IBExpert Developer Studio multiple 25-49	25-49	149.00 each		67.00 each	103.00 each	120.00
IBExpert Developer Studio multiple 50+	50+	129.00 each		62.00 each	95.00 each	110.00

Prices are quoted in Euros and net of any statutory VAT/sales tax and any costs for postage and packaging (where applicable).

All software subscriptions are valid for 12 months from the date of purchase.

---

IBExpert Developer Studio Day Edition

The IBExpert Day Edition is the ideal supplement to all IBExpert full versions, ready for use on all computers at any time. Read the full software product details here.

IBExpert Day Edition Packages	Online price in Euros (EUR) net of sales tax/VAT
Day Edition 25 activations, valid for 12 months from the date of purchase IBExpert IDE 25 individual activations.	45.00
Day Edition 100 activations, valid for 12 months from the date of purchase IBExpert IDE 100 individual activations.	160.00
Day Edition 250 activations, valid for 12 months from the date of purchase IBExpert IDE 250 individual activations.	245.00
Day Edition 500 activations, valid for 12 months from the date of purchase IBExpert IDE 500 individual activations.	310.00
Day Edition 1000 activations, valid for 12 months from the date of purchase IBExpert IDE 1,000 individual activations.	520.00
Day Edition 2500 activations, valid for 12 months from the date of purchase IBExpert IDE 2,500 individual activations.	1,150.00
Day Edition 5000 activations, valid for 12 months from the date of purchase IBExpert IDE 5,000 individual activations.	2100.00

Prices are quoted in Euros and net of any statutory VAT/sales tax and any costs for postage and packaging (where applicable).

All Day Edition activations are available for 12 months from the date of purchase. Each activation, once started, remains valid for 24 hours.

---

IBExpert Developer Studio Company Year Edition

Use the IBExpert IDE, IBEScript.dll, IBEScript.exe, and the BackupRestore Scheduler modules on multiple computers, also in virtual environments and/or in the cloud, without hardware-related unlock codes. Read the full software product details here.

In a virtual environment, which does not always run on the same hardware, the use of an IBExpert Company Year, Enterprise Server Year, Distribution OEM is necessary to avoid problems such as machine locks.

Description	Months	Online price in Euros (EUR) net of sales tax/VAT
IBExpert Company Year Edition S The IBExpert Company Year Edition S version is reserved exclusively for single programmers/developers. For further information and purchase please contact register@ibexpert.net.	12	569.00	upon request
IBExpert Company Year Edition M: up to 25 employees	12	1,095.00
IBExpert Company Year Edition L: up to 100 employees	12	2,269.00
IBExpert Company Year Edition SL: up to 250 employees	12	3,490.00
IBExpert Company Year Edition XL: up to 500 employees	12	6,250.00

Prices are quoted in Euros and net of any statutory VAT/sales tax and any costs for postage and packaging (where applicable).

All Company Year Edition licenses are available for 12 months from the date of purchase.

---

IBExpert Enterprise Server Year Edition

In a virtual environment, which does not always run on the same hardware, the use of an IBExpert Company Year, Enterprise Server Year, Distribution OEM is necessary to avoid problems such as machine locks.

Work with IBExpert software in a virtual environment
The ESYE contains the following components: IBExpert IDE, IBEScript.dll, IBEScript.exe and the BackupRestore Scheduler. ... Read the full software product details here.

Description	Months	Online price in Euros (EUR) net of sales tax/VAT
1 * IBExpert activation for 1 Server, location-independent	12	669.00

Prices are quoted in Euros and net of any statutory VAT/sales tax and any costs for postage and packaging (where applicable).

All Enterprise Server Year Edition licenses are available for 12 months from the date of purchase.

---

IBExpert Server Tools

The IBExpert Server Tools include IBEScript exe and dll, hkSCC.exe, the IBExpert Backup/Restore Scheduler and the IBExpert Encryption Plugin for Firebird 3 on Win32/Win64. The IBExpert Server Tools can also be deployed on virtual machines or in a cloud infrastructure. Read the full software product details here.

Description	No. of users	Online price (EUR) net of sales tax/VAT
IBExpert Server Tools single edition for one activation	1	175.00
IBExpert Server Tools multiple activations		upon request

Prices are quoted in Euros and net of any statutory VAT/sales tax and any costs for postage and packaging (where applicable).

The IBExpert Server Tools with one-time download access, available for 12 months from the date of purchase.

---

IBExpert Distribution OEM Edition

Our optimal solution for all businesses for flexible and independent working with IBExpert on all physical or virtual computers without activation codes and without an active internet connection. Read the full software product details here.

Description	Price in Euros (EUR) net of sales tax/VAT
OEM Distribution Edition: IBExpert.exe	13,050.00
OEM Distribution Edition: IBEScript.exe	13,050.00
OEM Distribution Edition: IBEScript.dll	13,050.00

Prices are quoted in Euros and net of any statutory VAT/sales tax and any costs for postage and packaging (where applicable).
By ordering you agree to the OEM Distribution Edition terms and conditions.

Please mail register@ibexpert.net if you wish to order this software product.

---

IBExpert Encryption OEM Edition

The IBExpert Encryption Plugin for Firebird enables you to securely encrypt your Firebird 3 - 5 databases (also embedded). ... continue reading.

Description	Price in Euros (EUR) net of sales tax/VAT
IBExpert Encryption OEM incl. source code for Windows	4,990.00
IBExpert Encryption OEM incl. source code for Linux	4,990.00

Prices are quoted in Euros and net of any statutory VAT/sales tax and any costs for postage and packaging (where applicable).

Please mail register@ibexpert.net if you wish to order this software product.

---

IBExpert Replicator Enterprise IBER

Add real-time replication to any Firebird database without any downtime. ... continue reading.

Description	Price in Euros (EUR) net of sales tax/VAT
IBExpert Replicator Enterprise IBER Starter Package including init and 12 months support	1,020.00
IBExpert Replicator Enterprise IBERSub Subscription Package 12 months support	610.00

Prices are quoted in Euros and net of any statutory VAT/sales tax and any costs for postage and packaging (where applicable).

---

IBExpertFunctionLibrary

The IBExpertFunctionLibrary is a comprehensive set of functions, suitable for all Firebird versions. ... continue reading.

Description	Price in Euros (EUR) net of sales tax/VAT
IBExpertFunctionLibrary Enterprise Maintenance Subscription	1,020.00

Prices are quoted in Euros and net of any statutory VAT/sales tax and any costs for postage and packaging (where applicable).

---

IBExpert Benchmark Tool - Free special version

Compare your computer performance and test the speed of your Firebird Windows server with the free IBExpert Benchmark Tool. ... continue reading.

Description	Price in Euros (EUR) net of sales tax/VAT
IBExpert Benchmark Tool: Download here	free of charge

---

IBExpert Developer Studio Personal Edition

The IBExpert Personal Edition for private users includes the IBExpert IDE. Read the full software product details here.

Description	No. of users	Price in Euros (EUR) net of sales tax/VAT
IBExpert Personal Edition	1 workstation	free of charge

---

IBExpert Developer Studio Full Educational Edition

The IBExpert Developer Studio Full Educational Edition is a full IBExpert Developer Studio version. Read the full software product details here.

Description	No. of users	Online price in Euros (EUR) net of sales tax/VAT
IBExpert Developer Studio Full Educational Edition incl. 12 month software subscription	1 workstation	upon request

Prices are quoted in Euros and net of any statutory VAT/sales tax and any costs for postage and packaging (where applicable).

The software subscription is valid for 12 months from the date of purchase.

Furthermore, IBExpert is also available as a free IBExpert Developer Studio Educational Edition.

---

IBExpert services & support

We offer a wide range of training and support services for Firebird, InterBase^®, Lazarus, Delphi^®, FastReport^® and of course, IBExpert. All support and training services are available worldwide; languages spoken: German and English. View the full details and conditions of these services here.

If you wish to order any of our support services, please mail register@ibexpert.net and you will be sent our prepayment invoice.

Hotline

IBExpert und Firebird hotline includes training and support services for Firebird, InterBase®, Lazarus, Delphi, FastReport and of course, IBExpert. Details

Fees in Euros (EUR):

IBExpert Prepaid Hotline Account 60 minutes         135.00
IBExpert Prepaid Hotline Account 120 minutes       260.00
IBExpert Prepaid Hotline Account 240 minutes       500.00
IBExpert Prepaid Hotline Account 480 minutes       970.00
IBExpert Prepaid Hotline Account 960 minutes    1,880.00

Database Repair & Analysis

If you are confronted with a damaged or corrupt database we can often offer a rapid solution without even having to come out and analyze the corrupt database on site. Before we can provide you with our database repair estimate, we must first analyse your database. Details

Fees in Euros (EUR):

Database analysis: evaluation fee                            150.00
Database analysis express: evaluation fee               300.00

Monitoring

If you are experiencing poor performance with your Firebird application, we can help. We set up a live monitoring on your database server. After approximately 7 days you will receive a detailed written report. Details

Fees in Euros (EUR):

IBExpert Firebird SQL Performance Monitoring    1,200.00

Firebird Server Health Check & Backup Monitoring

Are you running Firebird on your own or existing server hardware? With our 3-year service package, we check and monitor the key elements required for stable operation.

Fees in Euros (EUR):

Firebird Server Health Check & Backup Monitoring   1,750.00 (1,990.00 USD) für 3 Jahre

Prices are quoted in Euros and net of any statutory VAT/sales tax.

---

IBExpert and Firebird Training and Workshops

Our training sessions using online video transmission combine personal training with physical independence: promptly and flexibly and tailored to your requirements

Perhaps your team is also located at different sites?
All employees can take part in our training courses, even from their home office.
The number of participants does not affect the cost.

The coaching days can be scheduled at relatively short notice and you and your team will not be absent from productive work for too long during the course.
The course can take place in a much shorter period of time. We train in blocks of 2-4 hours at a time.

We tailor all topics individually to your company and the requirements of your team, taking into account the level of knowledge of the participants and also look at tackling current projects.
The training courses can thus be structured in blocks and/or key topics can be scheduled for different employees, and can of course also be adjusted during an ongoing training session.
Our workshops are complemented by video recordings, which we capture during the workshops and make them available to you in digital form.

Workshops are much more effective when you structure them interactively. We offer our courses as interactive workshops and not in a classic lecture format.

You can send your training enquiry to register@ibexpert.net

We coordinate the agenda, the dates and the technical implementation together with yourselves.

Topics

Upgrading from Firebird 2.5 to 3.0/4.0
Setting up a permanent SQL monitoring on your own database
Applying the Trace API and results for optimization
Setting up and understanding basic master-slave replication
Working with multiple databases and transparent external blob storage

IBExpert is the ideal partner for training and workshops in your company

Years of experience allow you to gain access to tried and tested strategies and techniques for software development. The training sessions take place over a period of 1-5 days, depending upon the participants’ previous knowledge and the depth and range of the topics covered.

We provide expert workshops at your company, tailored to fit your needs. Send us your detailed list of requirements, and we will send you a written offer, recommending the number of days necessary to ensure your team can learn the necessary skills to fulfil your requirements.

Our standard courses:

IBExpert und Firebird Power Workshop
IBExpert Firebird 3 Bootcamp
Bootcamp database web applications using Firebird
Firebird Developer Days
Lazarus training for Delphi programmers

---

IBExpertFunctionLibrary Documentation

(German-language version below)

What is IBExpertFunctionLibrary?

IBExpertFunctionLibrary is a collection of Firebird database functions developed by IBExpert as a modern replacement for older UDF libraries (FreeAdhocUDF and rFunc).

Traditional Firebird UDFs are external binary libraries, usually written in languages such as C/C++ or Pascal. They were commonly used with Firebird 2.x to add functionality that was not available directly in SQL.

The problem is that binary UDFs are platform-dependent and can also affect server stability. A faulty UDF can potentially crash the Firebird server process. For this reason, Firebird has gradually moved away from traditional UDFs.

Why was IBExpertFunctionLibrary created?

Starting with Firebird 3, Firebird recommends newer mechanisms such as:

• Stored Functions
• UDRs - User Defined Routines

instead of traditional binary UDFs.

From Firebird 4 onward, traditional UDF support is disabled by default in firebird.conf.

This creates a migration problem for older applications that contain SQL such as:

   SELECT some_old_udf(field1)
   FROM customer;

or:

   SELECT rFuncFunction(...)
   FROM orders;

Rewriting a large application and all its database code can be expensive.

IBExpertFunctionLibrary was created primarily to provide replacements for these legacy functions while allowing applications to run on modern Firebird versions.

Main use case

The most important scenario is:

Old Firebird 2.x application -> uses FreeAdhocUDF / rFunc -> database upgraded to Firebird 3 / 4 / 5 -> IBExpertFunctionLibrary provides compatible replacements

This can significantly reduce the amount of application SQL that must be changed during a Firebird migration.

How does it differ from an old UDF library?

With an old UDF, the database declaration typically references an external binary library:

   DECLARE EXTERNAL FUNCTION MY_FUNCTION
       VARCHAR(255)
   RETURNS VARCHAR(255)
   ENTRY_POINT 'MyFunction'
   MODULE_NAME 'myudf';

That means Firebird has to load something such as:

   myudf.dll

on Windows or:

   myudf.so

on Linux.

The binary also needs to match the operating system and architecture. A Windows 64-bit UDF cannot simply be copied to a Linux Firebird server. 

IBExpertFunctionLibrary instead targets the newer Firebird function mechanisms, avoiding reliance on the old UDF architecture where possible.

Compatibility

The current library covers almost all functions available in FreeAdhocUDF and rFunc. 

Some legacy functions cannot reasonably be reproduced using modern Firebird functionality. In those cases, IBExpert may provide a non-functional prototype so that existing SQL does not immediately fail because the function name is missing. 

This distinction is important when migrating an application:

Function exists      ≠      Function necessarily produces the old result

Functions used by business-critical SQL should therefore be tested after migration.

Typical migration workflow

A practical migration would normally look like this:

1. Identify existing external functions

Inspect the old database for UDF declarations and determine which libraries they belong to.

Typical examples may come from:

FreeAdhocUDF, rFunc and custom application UDFs.

2. Determine where the functions are used

Search:

stored procedures, triggers, views, stored functions, application SQL.

3. Compare them with IBExpertFunctionLibrary

Determine whether an equivalent implementation exists.

4. Install/create the replacement functions

The exact deployment scripts are supplied with the licensed IBExpertFunctionLibrary source/package rather than being documented on the public product page.

5. Test the database

Particularly check functions involving:

Strings, dates, numeric conversion, NULL handling, character sets, BLOBs.

6. Remove the old binary UDF dependency

Once all calls have been migrated and verified, the old DLL/SO library should no longer be necessary.

Stored Functions vs UDFs

A Firebird stored function behaves much more like normal SQL database code:

   CREATE FUNCTION CALCULATE_VALUE (
       A INTEGER,
       B INTEGER
   )
   RETURNS INTEGER
   AS
   BEGIN
       RETURN A + B;
   END

It can then be called directly:

   SELECT CALCULATE_VALUE(10, 20)
   FROM RDB$DATABASE;

Result: 30

This approach has several advantages over traditional UDFs:

• No external DLL/SO
• No architecture-specific binary
• Much easier deployment
• Better server isolation
• Database code can be backed up with database metadata
• Firebird introduced stored functions in Firebird 3.

UDR vs Stored Function

The two modern approaches serve slightly different purposes.

Feature	Stored Function	UDR
Written in SQL/PSQL	Yes	Usually no
External compiled code	No	Usually yes
Suitable for simple logic	Excellent	Yes
Complex external processing	Limited	Excellent
Deployment complexity	Low	Higher
Replacement for many classic UDFs	Yes	Yes

For functions that can be implemented using Firebird SQL/PSQL, a stored function is usually the simpler option.

A UDR is useful when functionality genuinely requires external compiled code.

Important migration consideration

Functions from different historical libraries are not always parameter-compatible, even if they perform similar operations.

For example, a legacy function equivalent to Firebird's:

   SUBSTRING(...)

may use a different argument order or indexing convention.

Therefore, migration should not simply be:

old function name -> new function name

without checking parameters and return values.

A safer approach is:

Old function -> Check parameter types -> Check NULL behaviour -> Check result type ->
Compare sample results -> Replace

In short

IBExpertFunctionLibrary is most useful when you have:

an older Firebird application + FreeAdhocUDF / rFunc / legacy UDF dependencies + a planned migration to Firebird 3, 4 or 5.

Instead of continuing to depend on old binary UDF libraries, it provides a migration path toward modern Firebird functions.

For a new Firebird 5 application, however, it generally makes more sense to use Firebird built-in functions, stored functions and UDRs directly, rather than introduce compatibility functions unless they are actually needed.

---

IBExpertFunctionLibrary Dokumentation

Was ist die IBExpertFunctionLibrary?

IBExpertFunctionLibrary ist eine Sammlung von Firebird-Datenbankfunktionen, die von IBExpert als moderner Ersatz für ältere UDF-Bibliotheken (FreeAdhocUDF und rFunc) entwickelt wurde.

Herkömmliche Firebird-UDFs sind externe Binärbibliotheken, die in der Regel in Sprachen wie C/C++ oder Pascal geschrieben sind. Sie wurden häufig in Verbindung mit Firebird 2.x verwendet, um Funktionen hinzuzufügen, die in SQL nicht direkt verfügbar waren.

Das Problem besteht darin, dass binäre UDFs plattformabhängig sind und zudem die Stabilität des Servers beeinträchtigen können. Eine fehlerhafte UDF kann unter Umständen zum Absturz des Firebird-Serverprozesses führen. Aus diesem Grund hat sich Firebird nach und nach von herkömmlichen UDFs verabschiedet.

Warum wurde die IBExpertFunctionLibrary entwickelt?

Ab Firebird 3 empfiehlt Firebird neuere Mechanismen wie:

• Gespeicherte Funktionen
• UDRs – benutzerdefinierte Routinen

anstelle herkömmlicher binärer UDFs.

Ab Firebird 4 ist die Unterstützung für herkömmliche UDFs in der Datei firebird.conf standardmäßig deaktiviert.

Dies führt zu einem Migrationsproblem für ältere Anwendungen, die SQL-Anweisungen wie die folgenden enthalten:

   SELECT some_old_udf(field1)
   FROM customer;

oder:

   SELECT rFuncFunction(...)
   FROM orders;

Das Neuprogrammieren einer großen Anwendung und ihres gesamten Datenbankcodes kann kostspielig sein.

Die IBExpertFunctionLibrary wurde in erster Linie entwickelt, um Ersatz für diese Legacy-Funktionen zu bieten und gleichzeitig die Ausführung von Anwendungen auf modernen Firebird-Versionen zu ermöglichen.

Haupteinsatzbereich

Das wichtigste Szenario ist:

Alte Firebird 2.x-Anwendung -> verwendet FreeAdhocUDF / rFunc -> Datenbank auf Firebird 3 / 4 / 5 aktualisiert -> IBExpertFunctionLibrary bietet kompatible Ersatzfunktionen.

Dadurch lässt sich der Umfang der SQL-Anweisungen in der Anwendung, die bei einer Firebird-Migration geändert werden müssen, erheblich reduzieren.

Wie unterscheidet sie sich von einer alten UDF-Bibliothek?

Bei einer alten UDF verweist die Datenbankdeklaration in der Regel auf eine externe Binärbibliothek:

   DECLARE EXTERNAL FUNCTION MY_FUNCTION
       VARCHAR(255)
   RETURNS VARCHAR(255)
   ENTRY_POINT 'MyFunction'
   MODULE_NAME 'myudf';

Das bedeutet, dass Firebird eine Datei wie beispielsweise

   myudf.dll

unter Windows oder

   myudf.so

unter Linux laden muss.

Die Binärdatei muss zudem mit dem Betriebssystem und der Architektur übereinstimmen. Eine 64-Bit-UDF für Windows kann nicht einfach auf einen Linux-Firebird-Server kopiert werden.

Die IBExpertFunctionLibrary zielt stattdessen auf die neueren Firebird-Funktionsmechanismen ab und vermeidet, sofern möglich, die Abhängigkeit von der alten UDF-Architektur.

Kompatibilität

Die aktuelle Bibliothek deckt nahezu alle in FreeAdhocUDF und rFunc verfügbaren Funktionen ab.

Einige ältere Funktionen lassen sich mit den modernen Funktionen von Firebird nicht sinnvoll nachbilden. In solchen Fällen stellt IBExpert möglicherweise einen nicht funktionsfähigen Prototyp bereit, damit bestehende SQL-Anweisungen nicht sofort fehlschlagen, weil der Funktionsname fehlt.

Diese Unterscheidung ist bei der Migration einer Anwendung wichtig:

Die Funktion existiert      ≠      Die Funktion liefert zwangsläufig das alte Ergebnis

Funktionen, die in geschäftskritischen SQL-Anweisungen verwendet werden, sollten daher nach der Migration getestet werden.

Typischer Migrationsablauf

Eine praktische Migration würde normalerweise wie folgt ablaufen:

1.    Vorhandene externe Funktionen identifizieren

Überprüfen Sie die alte Datenbank auf UDF-Deklarationen und ermitteln Sie, zu welchen Bibliotheken diese gehören.

Typische Beispiele hierfür sind:

FreeAdhocUDF, rFunc und benutzerdefinierte Anwendungs-UDFs.

2. Ermitteln Sie, wo die Funktionen verwendet werden

Suche:

Stored Procedures, Trigger, Views, Stored Functions, Anwendungs-SQL.

3. Vergleichen Sie diese mit der IBExpertFunctionLibrary

Stellen Sie fest, ob eine entsprechende Implementierung vorhanden ist.

4. Die Ersatzfunktionen installieren/erstellen

Die genauen Deployment-Skripte sind im lizenzierten IBExpertFunctionLibrary-Quellcode bzw. -Paket enthalten und nicht auf der öffentlichen Produktseite dokumentiert.

5. Testen Sie die Datenbank

Überprüfen Sie insbesondere Funktionen in folgenden Bereichen:

Zeichenketten, Datumsangaben, numerische Konvertierung, Umgang mit NULL-Werten, Zeichensätze, BLOBs.

6. Entfernen Sie die alte binäre UDF-Abhängigkeit

Sobald alle Aufrufe migriert und überprüft wurden, sollte die alte DLL-/SO-Bibliothek nicht mehr benötigt werden.

Gespeicherte Funktionen vs. UDFs

Eine gespeicherte Firebird-Funktion verhält sich viel eher wie normaler SQL-Datenbankcode:

   CREATE FUNCTION CALCULATE_VALUE (
       A INTEGER,
       B INTEGER
   )
   RETURNS INTEGER
   AS
   BEGIN
       RETURN A + B;
   END

Es kann dann direkt aufgerufen werden:

   SELECT CALCULATE_VALUE(10, 20)
   FROM RDB$DATABASE;

Ergebnis: 30

Dieser Ansatz bietet gegenüber herkömmlichen UDFs mehrere Vorteile:

• Keine externen DLLs/SO-Dateien
• Keine architekturabhängigen Binärdateien
• Deutlich einfachere Bereitstellung
• Bessere Serverisolierung
• Der Datenbankcode kann zusammen mit den Datenbank-Metadaten gesichert werden
• Firebird hat in Firebird 3 gespeicherte Funktionen eingeführt.

UDR vs. Stored Function

Die beiden modernen Ansätze dienen leicht unterschiedlichen Zwecken.

Merkmal	Stored Function	UDR
In SQL/PSQL geschrieben	Ja	In der Regel nein
Externer kompilierter Code	Nein	In der Regel ja
Geeignet für einfache Logik	Hervorragend	Ja
Komplexe externe Verarbeitung	Eingeschränkt	Hervorragend
Komplexität bei der Bereitstellung	Gering	Höher
Ersatz für viele klassische UDFs	Ja	Ja

Bei Funktionen, die mit Firebird SQL/PSQL implementiert werden können, ist eine gespeicherte Funktion in der Regel die einfachere Option.

Eine UDR ist dann sinnvoll, wenn die Funktionalität tatsächlich externen kompilierten Code erfordert.

Wichtige Migrationshinweise

Funktionen aus verschiedenen älteren Bibliotheken sind nicht immer parameterkompatibel, auch wenn sie ähnliche Operationen ausführen.

Beispielsweise kann eine ältere Funktion, die der Firebird-Funktion

   SUBSTRING(...)

entspricht, eine andere Argumentreihenfolge oder Indizierungskonvention verwenden.

Daher sollte die Migration nicht einfach wie folgt erfolgen:

alter Funktionsname -> neuer Funktionsname

ohne die Parameter und Rückgabewerte zu überprüfen.

Eine sichere Vorgehensweise ist:

Alte Funktion -> Parametertypen prüfen -> Verhalten bei NULL prüfen -> Ergebnistyp prüfen ->
Beispielergebnisse vergleichen -> Ersetzen

Zusammenfassung

IBExpertFunctionLibrary ist in folgende Situationen besonders nützlich:

eine ältere Firebird-Anwendung + Abhängigkeiten von FreeAdhocUDF, rFunc oder älteren UDFs + eine geplante Migration auf Firebird 3, 4 oder 5.

Anstatt weiterhin auf alte binäre UDF-Bibliotheken angewiesen zu sein, bietet sie einen Migrationspfad hin zu modernen Firebird-Funktionen.

Bei einer neuen Firebird-5-Anwendung ist es jedoch in der Regel sinnvoller, die integrierten Funktionen, Stored Functions und UDRs von Firebird direkt zu verwenden, anstatt Kompatibilitätsfunktionen einzuführen, sofern diese nicht tatsächlich benötigt werden.

Firebird Developer Days

(German-language version below)

---

---

Firebird Entwickler Tage

---

---