Firebird(3pm)

📦
version 1.39 · 9 versions
, in ubuntu stonking

💖 Firebird(3pm) 💖

okay bestie, Firebird(3pm) is a User Contributed Perl Documentation page, and we're about to make it make sense. no cap. 💅

✨ NAME ✨

DBD::Firebird — DBI driver for Firebird RDBMS server. that's it, that's the name. 🎀

🌸 SYNOPSIS 🌸

use DBI;

$dbh = DBI->connect("dbi:Firebird:db=$dbname", $user, $password);

# See the DBI module documentation for full details

💜 DESCRIPTION 💜

DBD::Firebird is a Perl module which works with the DBI module to provide access to Firebird databases. lowkey iconic. 🦋

🎀 MODULE DOCUMENTATION 🎀

This documentation describes driver specific behavior and restrictions. It is not supposed to be used as the only reference for the user. In any case consult the DBI documentation first ! (yes the space before the exclamation is canon, don't @ me). ✨

👑 THE DBI CLASS 👑

✨ DBI Class Methods ✨

💅 connect 💅

To connect to a database with a minimum of parameters, use the following syntax:

$dbh = DBI->connect("dbi:Firebird:dbname=$dbname", $user, $password);

If omitted, $user defaults to the ISC_USER environment variable (or, failing that, the DBI-standard DBI_USER environment variable). Similarly, $password defaults to ISC_PASSWORD (or DBI_PASS). If $dbname is blank, that is, "dbi:Firebird:dbname=", the environment variable ISC_DATABASE is substituted. slay. 🫶

The DSN may take several optional parameters, which may be split over multiple lines. Here is an example of connect statement which uses all possible parameters:

$dsn =<< "DSN";
dbi:Firebird:dbname=$dbname;
host=$host;
port=$port;
ib_dialect=$dialect;
ib_role=$role;
ib_charset=$charset;
ib_cache=$cache;
ib_db_triggers=0;
timeout=$timeout
DSN

$dbh =  DBI->connect($dsn, $username, $password);
bestie, notice the double space after the = in that last line? that's exactly how it is. copy it, don't "fix" it. 💀

The $dsn is prefixed by 'dbi:Firebird:', and consists of key-value parameters separated by semicolons. New line may be added after the semicolon. The following is the list of valid parameters and their respective meanings:

  • database — path to the database — required
  • dbname — path to the database
  • db — path to the database
  • hostname — hostname / IP address — optional
  • host — hostname / IP address
  • port — port number — optional
  • timeout — connect timeout in seconds — optional
  • ib_dialect — the SQL dialect to be used — optional
  • ib_role — the role of the user — optional
  • ib_charset — character set to be used — optional
  • ib_cache — number of database cache buffers — optional
  • ib_dbkey_scope — change default duration of RDB$DB_KEY — optional
  • ib_db_triggers — enable/disable database-level triggers — optional, defaults to 1 (enabled)

database could be used interchangeably with dbname and db. To connect to a remote host, use the host parameter. Here is an example of DSN to connect to a remote Windows host:

$dsn = "dbi:Firebird:db=C:/temp/test.gdb;host=example.com;ib_dialect=3";

Database file alias can be used too in connection string. In the following example, "billing" is defined in aliases.conf:

$dsn = 'dbi:Firebird:hostname=192.168.88.5;db=billing;ib_dialect=3';

Firebird as of version 1.0 listens on port specified within the services file. To connect to port other than the default 3050, add the port number at the end of host name, separated by a slash. Example:

$dsn = 'dbi:Firebird:db=/data/test.gdb;host=localhost/3060';

Firebird 1.0 introduces SQL dialect to provide backward compatibility with databases created by older versions of Firebird (pre 1.0). In short, SQL dialect controls how Firebird interprets:

  • double quotes
  • the DATE datatype
  • decimal and numeric datatypes
  • new 1.0 reserved keywords

Valid values for ib_dialect are 1 and 3 . The driver's default value is 3 (Currently it is possible to create databases in Dialect 1 and 3 only, however it is recommended that you use Dialect 3 exclusively, since Dialect 1 will eventually be deprecated. Dialect 2 cannot be used to create a database since it only serves to convert Dialect 1 to Dialect 3). periodt. 💅

http://www.firebirdsql.org/file/documentation/reference_manuals/user_manuals/html/isql-dialects.html

ib_role specifies the role of the connecting user. SQL role is implemented by Firebird to make database administration easier when dealing with lots of users. A detailed reading can be found at:

http://www.ibphoenix.com/resources/documents/general/doc_59

If ib_cache is not specified, the default database's cache size value will be used. The Firebird Operation Guide discusses in full length the importance of this parameter to gain the best performance. (ngl, read the guide if you're chasing performance 👑)

🌈 available_drivers 🌈

@driver_names = DBI->available_drivers;

Implemented by DBI, no driver-specific impact. so not our problem bestie 💅

🦋 data_sources 🦋

This method is not yet implemented. 🙈

⭐ trace ⭐

DBI->trace($trace_level, $trace_file)

Implemented by DBI, no driver-specific impact. ✨

🧚 DBI Dynamic Attributes 🧚

See Common Methods. that's the whole vibe. 💖

💜 METHODS COMMON TO ALL DBI HANDLES 💜

🎀 err 🎀

$rv = $h->err;

Supported by the driver as proposed by DBI. ✅

🌸 errstr 🌸

$str = $h->errstr;

Supported by the driver as proposed by DBI. ✅

🦋 state 🦋

This method is not yet implemented. 😭

✨ trace ✨

$h->trace($trace_level, $trace_filename);

Implemented by DBI, no driver-specific impact. ✅

💫 trace_msg 💫

$h->trace_msg($message_text);

Implemented by DBI, no driver-specific impact. ✅

💅 func 💅

See Transactions section for information about invoking ib_set_tx_param() from func() method. iykyk. 🔥

👑 ATTRIBUTES COMMON TO ALL DBI HANDLES 👑

💖 Warn (boolean, inherited) 💖

Implemented by DBI, no driver-specific impact.

⭐ Active (boolean, read-only) ⭐

Supported by the driver as proposed by DBI. A database handle is active while it is connected and statement handle is active until it is finished.

🌈 Kids (integer, read-only) 🌈

Implemented by DBI, no driver-specific impact.

🎀 ActiveKids (integer, read-only) 🎀

Implemented by DBI, no driver-specific impact.

🦋 CachedKids (hash ref) 🦋

Implemented by DBI, no driver-specific impact.

💜 CompatMode (boolean, inherited) 💜

Not used by this driver.

✨ InactiveDestroy (boolean) ✨

Implemented by DBI, no driver-specific impact.

🌸 PrintError (boolean, inherited) 🌸

Implemented by DBI, no driver-specific impact.

💫 RaiseError (boolean, inherited) 💫

Implemented by DBI, no driver-specific impact.

🍓 ChopBlanks (boolean, inherited) 🍓

Supported by the driver as proposed by DBI.

👑 LongReadLen (integer, inherited) 👑

Supported by the driver as proposed by DBI. The default value is 80 bytes. (yeah, 80, not a typo bestie)

💅 LongTruncOk (boolean, inherited) 💅

Supported by the driver as proposed by DBI.

🧚 Taint (boolean, inherited) 🧚

Implemented by DBI, no driver-specific impact.

DATABASE HANDLE OBJECTS 💖

okay bestie, this is the $dbh era. the database handle. the main character. everything you do to a whole database lives here. let's get into it 💅

🌸 Database Handle Methods

✨ selectrow_array

@row_ary = $dbh->selectrow_array($statement, \%attr, @bind_values);

Done by DBI itself, no driver-specific impact. 🫶

✨ selectall_arrayref

$ary_ref = $dbh->selectall_arrayref($statement, \%attr, @bind_values);

Also DBI's own work, no driver-specific impact. 💜

✨ selectcol_arrayref

$ary_ref = $dbh->selectcol_arrayref($statement, \%attr, @bind_values);

Same deal, DBI handles it, no driver-specific impact. ✨

✨ prepare

$sth = $dbh->prepare($statement, \%attr);

The driver supports this exactly the way DBI proposed it. 👑

Now here's the important tea: when AutoCommit is On, calling this method implicitly starts a brand new transaction. That transaction gets committed automatically after the next execute() — or after the last fetch(), depending on what kind of statement it is. 😤

  • for select statements, the commit happens automatically after the last fetch(), or if there are rows still left over, when you explicitly call the finish() method.
  • for non-select statements, execute() will implicitly commit the transaction.

✨ prepare_cached

$sth = $dbh->prepare_cached($statement, \%attr);

Implemented by DBI, no driver-specific impact. 🎀

✨ do

$rv  = $dbh->do($statement, \%attr, @bind_values);

Driver supports it as DBI proposed. Use this one for non-select statements, because the driver skips the whole conservative prepare → execute two-step, and that makes execution faster. 🏎️

BUT — and this is the plot twist 😭 — if you use this method with bind values, that speed advantage basically evaporates, because the method calls prepare() internally to bind the placeholders anyway. So if you're sitting in a loop calling this repeatedly with bind values? Don't. Instead call prepare() once and execute() many times. fr fr.

Check the notes for the execute method elsewhere in this document. And heads up: unlike execute, this method currently doesn't return the number of affected rows. 🙈

✨ commit

$rc  = $dbh->commit;

Driver supports it as DBI proposed. Also go read the notes about Transactions elsewhere in this document. 🦋

✨ rollback

$rc  = $dbh->rollback;

Driver supports it as DBI proposed. And yeah, same — see the Transactions notes elsewhere in this document. 🌸

✨ disconnect

$rc  = $dbh->disconnect;

Driver supports it as DBI proposed. bye bestie 👋💜

✨ ping

$rc = $dbh->ping;

This driver supports the ping method! Use it to check whether a database handle is still valid. Apache::DBI especially needs this. 🔥

✨ primary_key_info

$sth = $dbh->primary_key_info('', '', $table_name);
@pks = $dbh->primary_key('', '', $table_name);

Driver supports it as DBI proposed. Quick note: catalog and schema are ignored. 💀

✨ table_info

$sth = $dbh->table_info;

Every Firebird version supports the basic DBI-specified columns (TABLE_NAME, TABLE_TYPE, etc.) plus IB_TABLE_OWNER. Some particular versions might hand you extra fields too, and those are prefixed with IB_. 🎀

Now, table searching might not behave the way you expect on older Interbase/Firebird engines that don't natively offer a TRIM() function. Here's why: some engines store TABLE_NAME in a blank-padded CHAR field, and a search for a table name runs as a SQL LIKE predicate — which is sensitive to blanks. So it's giving:

$dbh->table_info('', '', 'FOO');  # May not find table "FOO", depending on
                                  # FB version
$dbh->table_info('', '', 'FOO%'); # Will always find "FOO", but also tables
                                  # "FOOD", "FOOT", etc.

Future versions of DBD::Firebird may try to work around this irritating limitation, at the cost of efficiency. 😭

Also: Firebird implementations do not presently support the DBI concepts of 'catalog' and 'schema', so those parameters are effectively ignored. periodt.

✨ tables

@names = $dbh->tables;

Gives you a list of tables, and it excludes any 'SYSTEM TABLE' types. 💅

✨ type_info_all

$type_info_all = $dbh->type_info_all;

Driver supports it as DBI proposed. 🫶

For more details on the Firebird-specific data types, go read the Firebird Data Definition Guide:

http://www.firebirdsql.org/en/reference-manuals/

✨ type_info

@type_info = $dbh->type_info($data_type);

Implemented by DBI, no driver-specific impact. ✨

✨ quote

$sql = $dbh->quote($value, $data_type);

Implemented by DBI, no driver-specific impact. 💖

🌈 Database Handle Attributes

🎀 AutoCommit (boolean)

Driver supports it as DBI proposed. Per DBI's own classification, Firebird is a database where a transaction must be explicitly started. Without starting a transaction, every single change to the database becomes immediately permanent. 😳

AutoCommit defaults to on, which matches DBI's default. When you set AutoCommit to off, a transaction gets started, and every commit or rollback will automatically start a new transaction. For the full lore, see the notes about Transactions elsewhere in this document. 🦋

🎀 Driver (handle)

Implemented by DBI, no driver-specific impact. 💜

🎀 Name (string, read-only)

Not yet implemented. 🙈

🎀 RowCacheSize (integer)

Implemented by DBI, but not used by the driver. 💀

🎀 ib_softcommit (driver-specific, boolean)

Set this attribute to TRUE to use Firebird's soft commit feature — it defaults to FALSE. 🍓

What's the difference? Soft commit retains the internal transaction handle when committing a transaction. The default commit behavior always closes and invalidates the transaction handle.

Since the transaction handle is still open, you don't need to start a new transaction on every commit — so apps can pull a real performance improvement out of this. ✨ Using soft commit is also desirable when you're dealing with nested statement handles under AutoCommit on.

Flipping the attribute's value from TRUE to FALSE will force a hard commit, which closes the current transaction. 👑

🎀 ib_enable_utf8 (driver-specific, boolean)

Setting this attribute to TRUE makes any Perl Unicode strings you supply as statement parameters get downgraded to octet sequences before they're passed to Firebird. 🧚

And the other direction: any character data you pull out of the database — CHAR, VARCHAR, BLOB sub_type TEXT — will be upgraded to Perl Unicode strings.

Caveat: right now this only works if the ib_charset DSN parameter is "UTF8". In the future, encoding and decoding to/from arbitrary character sets may get implemented. lowkey fingers crossed. 🤞

Example:

$dbh = DBI->connect( 'dbi:Firebird:db=database.fdb;ib_charset=UTF8',
    { ib_enable_utf8 => 1 } );

STATEMENT HANDLE OBJECTS 🦋

and now... the $sth. the statement handle. the one that actually does the thing. 🌸

💜 Statement Handle Methods

✨ bind_param

Driver supports it as DBI proposed. The SQL data type you pass as the third argument is ignored. 💅

✨ bind_param_array

Driver supports it as DBI proposed. The attributes you supply in the optional third parameter are ignored. 💀

✨ bind_param_inout

Not supported by this driver. that's it, that's the whole vibe 🙈

✨ execute

$rv = $sth->execute(@bind_values);

Driver supports it as DBI proposed. 🔥

✨ fetchrow_arrayref

$ary_ref = $sth->fetchrow_arrayref;

Driver supports it as DBI proposed. ✨

✨ fetchrow_array

@ary = $sth->fetchrow_array;

Driver supports it as DBI proposed. 🫶

✨ fetchrow_hashref

$hash_ref = $sth->fetchrow_hashref;

Driver supports it as DBI proposed. 💜

✨ fetchall_arrayref

$tbl_ary_ref = $sth->fetchall_arrayref;

Implemented by DBI, no driver-specific impact. 🎀

✨ finish

$rc = $sth->finish;

Driver supports it as DBI proposed. 🌸

✨ rows

$rv = $sth->rows;

Driver supports it as DBI proposed. For select statements it returns the number of fetched rows. Otherwise? It returns -1, which means the number of affected rows is unknown. 😭

✨ bind_col

$rc = $sth->bind_col($column_number, \$var_to_bind, \%attr);

Driver supports it as DBI proposed. ✨

✨ bind_columns

$rc = $sth->bind_columns(\%attr, @list_of_refs_to_vars_to_bind);

Driver supports it as DBI proposed. 💖

✨ dump_results

$rows = $sth->dump_results($maxlen, $lsep, $fsep, $fh);

Implemented by DBI, no driver-specific impact. 🦋

⭐ Statement Handle Attributes

🎀 NUM_OF_FIELDS (integer, read-only)

Implemented by DBI, no driver-specific impact. 💅

🎀 NUM_OF_PARAMS (integer, read-only)

Implemented by DBI, no driver-specific impact. 💜

🎀 NAME (array-ref, read-only)

Driver supports it as DBI proposed. ✨

🎀 NAME_lc (array-ref, read-only)

Implemented by DBI, no driver-specific impact. 🎀

🎀 NAME_uc (array-ref, read-only)

Implemented by DBI, no driver-specific impact. 🌸

🎀 TYPE (array-ref, read-only)

Driver supports it as DBI proposed — with one restriction though: the types are Firebird-specific data types, which do not correspond to international standards. so don't expect them to match up with anything else. 🙈

🎀 PRECISION (array-ref, read-only)

Driver supports it as DBI proposed. ✨

🎀 SCALE (array-ref, read-only)

Driver supports it as DBI proposed. 💖

🎀 NULLABLE (array-ref, read-only)

Driver supports it as DBI proposed. 🫶

🎀 CursorName (string, read-only)

Driver supports it as DBI proposed. 💜

🎀 Statement (string, read-only)

Driver supports it as DBI proposed. 🦋

🎀 RowCache (integer, read-only)

Not supported by the driver. 💀

🎀 ParamValues (hashref, read-only)

Driver supports it as DBI proposed. 🌈

🌸 TRANSACTION SUPPORT 🌸

okay bestie, this is where it gets real. 💅 The transaction behavior is controlled with the attribute AutoCommit. For the full, complete definition of AutoCommit, pls go check the DBI documentation — that's the authority, not me.

Per the DBI specification, the default for AutoCommit is TRUE. In this mode, literally any change to the database becomes valid immediately — no waiting, no take-backs. Any commit() or rollback() will be rejected. 😤

If AutoCommit is switched off, a transaction starts immediately, like right away. A rollback() will rollback and close the active transaction, and then implicitly start a new transaction. And a disconnect will issue a rollback. Yep. That's the vibe.

Firebird gives you fine control over transaction behavior — you can specify the access mode, the isolation level, the lock resolution, and the table reservation (for a specified table). For this purpose, the ib_set_tx_param() database handle method is available. 👑

Upon a successful connect(), these default parameter values will be used for every SQL operation:

Access mode:        read_write
Isolation level:    snapshot
Lock resolution:    wait

Any of the above values can be changed using ib_set_tx_param(). 🦋

✨ ib_set_tx_param ✨

$dbh->func(
   -access_mode     => 'read_write',
   -isolation_level => 'read_committed',
   -lock_resolution => 'wait',
   'ib_set_tx_param'
);

Valid value for -access_mode is read_write, or read_only.

Valid value for -lock_resolution is wait, or no_wait. In Firebird 2.0, a timeout value for wait is introduced. This can be specified using a hash ref as the lock_resolution value:

$dbh->func(
   -lock_resolution => { wait => 5 }, # wait for 5 seconds
   'ib_set_tx_param'
);

-isolation_level may be: read_committed, snapshot, snapshot_table_stability.

If read_committed is to be used with record_version or no_record_version, then they should be inside an anonymous array:

$dbh->func(
   -isolation_level => ['read_committed', 'record_version'],
   'ib_set_tx_param'
);

Table reservation is supported since DBD::Firebird 0.30. Names of the tables to reserve, as well as their reservation params/values, are specified inside a hashref, which is then passed as the value of -reserving. 🎀

The following example reserves foo_table with read lock and bar_table with read lock and protected access:

$dbh->func(
   -access_mode     => 'read_write',
   -isolation_level => 'read_committed',
   -lock_resolution => 'wait',
   -reserving       =>
       {
           foo_table => {
               lock    => 'read',
           },
           bar_table => {
               lock    => 'read',
               access  => 'protected',
           },
       },
   'ib_set_tx_param'
);

Possible table reservation parameters are:

  • access (optional) — valid values are shared or protected.
  • lock (required) — valid values are read or write.

Under AutoCommit mode, invoking this method doesn't only change the transaction parameters (like it does with AutoCommit off) — it also commits the current transaction. The new transaction parameters will be used in any newly started transaction. 🔥

ib_set_tx_param() can also be invoked with no parameter, in which case it resets the transaction parameters to the default value. Slay.

💖 DATE, TIME, and TIMESTAMP FORMATTING SUPPORT 💖

DBD::Firebird supports various formats for query results of DATE, TIME, and TIMESTAMP types.

By default, it uses %c for TIMESTAMP, %x for DATE, and %X for TIME, and passes them to ANSI C's strftime() function to format your query results. These values are respectively stored in the ib_timestampformat, ib_dateformat, and ib_timeformat attributes, and may be changed in two ways:

  • At $dbh level — this replaces the default values. Example:
$dbh->{ib_timestampformat} = '%m-%d-%Y %H:%M';
$dbh->{ib_dateformat} = '%m-%d-%Y';
$dbh->{ib_timeformat} = '%H:%M';
  • At $sth level — this overrides the default values only for the currently prepared statement. Example:
$attr = {
   ib_timestampformat => '%m-%d-%Y %H:%M',
   ib_dateformat => '%m-%d-%Y',
   ib_timeformat => '%H:%M',
};
# then, pass it to prepare() method.
$sth = $dbh->prepare($sql, $attr);

Since locale settings affect the result of strftime(), if your application is designed to be portable across different locales, you may want to consider using these two special formats: 'TM' and 'ISO'. TM returns a 9-element list, much like Perl's localtime(). The ISO format applies sprintf()'s pattern %04d-%02d-%02d %02d:%02d:%02d.%04d for TIMESTAMP, %04d-%02d-%02d for DATE, and %02d:%02d:%02d.%04d for TIME. 🧚

$dbh->{ib_time_all} can be used to specify all of the three formats at once. Example:

$dbh->{ib_time_all} = 'TM';

🦋 EVENT ALERT SUPPORT 🦋

An event alerter is used to notify client applications whenever something has happened on the database. For this to work, a trigger should be created, which then calls POST_EVENT to post the event notification to the interested client. A client could behave in two ways: wait for the event synchronously, or register a callback which will be invoked asynchronously each time a posted event is received. ✨

ib_init_event

$evh = $dbh->func(@event_names, 'ib_init_event');

Creates an event handle from a list of event names.

ib_wait_event

$dbh->func($evh, 'ib_wait_event');

Wait synchronously for particular events registered via the event handle $evh. Returns a hashref containing pair(s) of the posted event's name and its corresponding count, or undef on failure.

ib_register_callback

my $cb = sub { my $posted_events = $_[0]; ++$::COUNT < 6 };
$dbh->func($evh, $cb, 'ib_register_callback');

sub inc_count { my $posted_events = shift; ++$::COUNT < 6 };
$dbh->func($evh, \&inc_count, 'ib_register_callback');

# or anonyomus subroutine
$dbh->func(
  $evh,
  sub { my ($pe) = @_; ++$::COUNT < 6 },
  'ib_register_callback'
);

Associates an event handle with an asynchronous callback. A callback will be passed a hashref as its argument, and this hashref contains pair(s) of the posted event's name and its corresponding count.

It is safe to call ib_register_callback multiple times for the same event handle. In this case, the previously registered callback will be automatically cancelled.

If the callback returns FALSE, the registered callback will no longer be invoked — but internally it's still there until the event handle goes out of scope (or gets undef-ed), or you call ib_cancel_callback to actually disassociate it from the event handle. 👀

ib_cancel_callback

$dbh->func($evh, 'ib_cancel_callback');

Unregister a callback from an event handle. This function has a limitation, however: it can't be called from inside a callback. In many cases you won't need this function at all, since when an event handle goes out of scope, its associated callback(s) will be automatically cancelled before it's cleaned up. 🫶

👑 RETRIEVING FIREBIRD / INTERBASE SPECIFIC INFORMATION 👑

ib_tx_info

$hash_ref = $dbh->func('ib_tx_info');

Retrieve information about the current active transaction.

ib_database_info

$hash_ref = $dbh->func(@info, 'ib_database_info');
$hash_ref = $dbh->func([@info], 'ib_database_info');

Retrieve database information from the current connection.

ib_plan

$plan = $sth->func('ib_plan');

Retrieve the query plan from a prepared SQL statement.

my $sth = $dbh->prepare('SELECT * FROM foo');
print $sth->func('ib_plan'); # PLAN (FOO NATURAL)

ib_drop_database

$result = $dbh->func('ib_drop_database');

Drops the database associated with the connection. The database handle is no longer valid after calling this function.

⚠️ Caution is advised, bestie — the drop is irrevocable. Like, gone-gone. 💀

🌈 UNSUPPORTED SQL STATEMENTS 🌈

Here's a list of SQL statements which can't be used. But this shouldn't be a problem, because their functionality is already provided by the DBI methods. periodt.

  • SET TRANSACTION — use $dbh->func(..., 'set_tx_param') instead.
  • DESCRIBE — provides information about columns that are retrieved by a DSQL statement, or about placeholders in a statement. This functionality is supported by the driver, and is transparent for users. Column names are available via the $sth->{NAME} attributes.
  • EXECUTE IMMEDIATE — calling do() method without bind value(s) will do the same.
  • CLOSE, OPEN, DECLARE CURSOR — $sth->{CursorName} is automagically available upon executing a SELECT .. FOR UPDATE statement. A cursor is closed after the last fetch(), or by calling $sth->finish().
  • PREPARE, EXECUTE, FETCH — similar functionalities are obtained by using the prepare(), execute(), and fetch() methods.

💜 COMPATIBILITY WITH DBIx::* MODULES 💜

DBD::Firebird is known to work with DBIx::Recordset 0.21, and Apache::DBI 0.87. Yuri Vasiliev <yuri.vasiliev@targuscom.com> reported successful usage with Apache::AuthDBI (part of the Apache::DBI 0.87 distribution). 🫶

The driver is untested with Apache::Session::DBI. Doesn't work with Tie::DBI. 😭 Tie::DBI calls $dbh->prepare("LISTFIELDS $table_name"), on which Firebird fails to parse. I think that the call should be made within an eval block.

🛠️ DBD::Firebird->create_database( { params... } ) 🛠️

A class method, and it makes you empty databases. Iconic behavior: it croaks on error, so if it's mad, you'll know. 😤 The params you can hand it are:

  • db_path (string, required) — where the database lives, host name included if you need one. Like, the whole path, bestie.

Examples:

server:/path/to/db.fdb
/srv/db/base.fdb
  • user (string, optional) — the user name you want the request to go out as.
  • password (string, optional) — the password for that request.
  • page_size (integer, optional) — the page size of the freshly born database. Should be something the server actually supports. Firebird 2.5 supports these page sizes: 1024, 2048, 4096, 8192 and 16384, and it defaults to 4096. Periodt.
  • character_set (string, optional) — the database's default character set. Firebird 2.5 defaults to "NONE".
  • dialect (integer, optional) — the database's dialect. Defaults to 3.

Once it's created, go connect to it the normal way with DBI->connect(...) and you're in. 💖

🩹 DBD::Firebird->gfix( { params } ) 🩹

Another class method — this one simulates a subset of what Firebird's gfix(1) utility does. Not the whole thing, just a slice. 🍰

Params is a hash reference, and the keys are:

  • db_path (string, required) — the path to the database you're connecting to. Include host name if necessary.
  • user (string, optional) — user name to connect as. Has to be SYSDBA or the database owner. Like, it's not optional-optional, the value matters, ok?
  • password (string, optional) — password for the connection.
Heads up: user and password are not needed for embedded connections. 🫶
  • forced_writes (boolean, optional) — if you pass it, it sets the database's forced writes flag, which makes Firebird use synchronous writes while working with that database. Slow but safe vibes.
  • buffers (integer, optional) — if you pass it, it sets the default number of buffers for the database. Can be overridden at connect time. Important detail: buffers are measured in database pages, not bytes. Do not get it twisted. 💅

❓ FAQ ❓

😭 Why do some operations performing positioned update and delete fail when AutoCommit is on?

For example, this little snippet here? Fails.

 $sth = $dbh->prepare(
 "SELECT * FROM ORDERS WHERE user_id < 5 FOR UPDATE OF comment");
 $sth->execute;
 while (@res = $sth->fetchrow_array) {
     $dbh->do("UPDATE ORDERS SET comment = 'Wonderful' WHERE
     CURRENT OF $sth->{CursorName}");
 }

Okay so here's the tea. When AutoCommit is on, a transaction gets started inside prepare(), and it's committed automatically after the last fetch(), or inside finish(). Meanwhile, inside do(), a transaction is started right before the statement runs and gets committed right after the statement runs. The transaction handle is stored within the database handle. The driver is smart enough not to override an active transaction handle with a new one. So look at the snippet above — after the first fetchrow_array(), the do() is still using the same transaction context, but the literal second it finishes executing the statement it commits the transaction… while the next fetchrow_array() still needs that transaction context. 💀 Rude.

So the secret to making this work is to keep the transaction open. Two ways to do it:

  • Using AutoCommit = 0

If yours defaults to AutoCommit on, you can put the snippet within a block:

 {
     $dbh->{AutoCommit} = 0;
     # same actions like above ....
     $dbh->commit;
 }
  • Using $dbh->{ib_softcommit} = 1

This is a driver-specific attribute,You may want to look at t/70-nested-sth.t to see it in action.

🪆 Why do nested statement handles break under AutoCommit mode?

Same explanation as above applies, bestie. And the workaround is also much alike:

 {
     $dbh->{AutoCommit} = 0;
     $sth1 = $dbh->prepare("SELECT * FROM $table");
     $sth2 = $dbh->prepare("SELECT * FROM $table WHERE id = ?");
     $sth1->execute;

     while ($row = $sth1->fetchrow_arrayref) {
        $sth2->execute($row->[0]);
        $res = $sth2->fetchall_arrayref;
     }
     $dbh->commit;
 }

You may also use $dbh->{ib_softcommit} please check t/70nested-sth.t for an example on how to use it.

🤔 Why do placeholders fail to bind, generating unknown datatype error message?

Because you can't bind a field name. That's the whole thing. This example will fail:

 $sth = $dbh->prepare("SELECT (?) FROM $table");
 $sth->execute('user_id');

There are also cases where placeholders can't be used in conjunction with a COLLATE clause, such as this:

 SELECT * FROM $table WHERE UPPER(author) LIKE UPPER(? COLLATE FR_CA);

This is a Firebird's SQL parser thing, not a "DBD::Firebird" thing. The driver just passes SQL statements through the engine. Not its fault, not its problem. 🤷

🔢 How to do automatic increment for a specific field?

Create a sequence and a trigger to associate it with the field. The following example creates a sequence named PROD_ID_SEQ, and a trigger for table ORDERS which uses the generator to perform auto increment on field PRODUCE_ID with increment size of 1.

 $dbh->do("create sequence PROD_ID_SEQ");
 $dbh->do(
 "CREATE TRIGGER INC_PROD_ID FOR ORDERS
 BEFORE INSERT POSITION 0
 AS BEGIN
   NEW.PRODUCE_ID = NEXT VALUE FOR PROD_ID_SEQ;
 END");

From Firebird 3.0 there is Identity support ✨

📄 How can I perform LIMIT clause as I usually do in MySQL?

The "LIMIT" clause lets users fetch only a portion rather than the whole records as the result of a query. This is particularly efficient and useful for paging feature on web pages, where users can navigate back and forth between pages. Very main character of it.

Using Firebird 2.5.x this can be implemented by using "ROWS" .

http://www.firebirdsql.org/refdocs/langrefupd21-select.html#langrefupd21-select-rows

For example, to display a portion of table employee within your application:

 # fetch record 1 - 5:
 $res = $dbh->selectall_arrayref("SELECT * FROM employee rows 1 to 5)");

 # fetch record 6 - 10:
 $res = $dbh->selectall_arrayref("SELECT * FROM employee rows 6 to 10)");

🗓️ How can I use the date/time formatting attributes?

Those attributes take the same format as the C function strftime()'s. Examples:

 $attr = {
    ib_timestampformat => '%m-%d-%Y %H:%M',
    ib_dateformat => '%m-%d-%Y',
    ib_timeformat => '%H:%M',
 };

Then, pass it to the prepare() method.

 $sth = $dbh->prepare($stmt, $attr);
 # followed by execute() and fetch(), or:

 $res = $dbh->selectall_arrayref($stmt, $attr);

⏳ Can I set the date/time formatting attributes between prepare and fetch?

No. "ib_dateformat", "ib_timeformat", and "ib_timestampformat" can only be set during $sth->prepare. If this is a problem to you, let me know, and probably I'll add this capability for the next release. (Said with love. 🫶)

🚫 Can I change ib_dialect after DBI->connect ?

No. If this is a problem to you, let me know, and probably I'll add this capability for the next release.

🦕 OBSOLETE FEATURES 🦕

  • Private Method — set_tx_param() is obsoleted by ib_set_tx_param(). It's giving legacy. Retire her. 💀

🧪 TESTED PLATFORMS 🧪

💻 Clients

  • Linux
  • FreeBSD
  • Solaris
  • Win32

🖥️ Servers

  • Firebird 2.5.x SS , SC and Classic for Linux (32 bits and 64)
  • Firebird 2.5.x for Windows, FreeBSD, Solaris

👑 AUTHORS 👑

  • DBI by Tim Bunce <Tim.Bunce@pobox.com>
  • DBD::Firebird by Edwin Pratomo <edpratomo@cpan.org>, Daniel Ritz <daniel.ritz@gmx.ch> and many others. See "COPYRIGHT & LICENSE".

This module is originally based on the work of Bill Karwin's IBPerl. Respect the ancestry. 🫡

🐛 BUGS/LIMITATIONS 🐛

Please report bugs and feature suggestions using http://rt.cpan.org/Public/Dist/Display.html?Name=DBD-Firebird .

This module doesn't work with MSWin32 ActivePerl iThreads, and its emulated fork. Tested with MSWin32 ActivePerl build 809 (Perl 5.8.3). The whole process will block in unpredictable manner. So like, don't. 😭

Under Linux, this module has been tested with several different iThreads enabled Perl releases.

No problem occurred so far.. until you try to share a DBI handle ;-)

But if you plan to use thread, you'd better use the latest stable version of Perl

On FreeBSD you need a Perl compiled with thread support.

Limitations:

  • Arrays are not (yet) supported
  • Read/Write BLOB fields block by block not (yet) supported. The maximum size of a BLOB read/write is hardcoded to about 1 MB.
  • service manager API is not supported.
Not "yet" supported ≠ never, bestie. They said yet. There's hope. 🦋

🌈 SEE ALSO 🌈

DBI(3).

Copyright (c) 2010- 2012  Popa Adrian Marius <mapopa@gmail.com>
Copyright (c) 2011- 2012  Stefan Suciu <stefbv70@gmail.com>
Copyright (c) 2011-2015, 2017, 2024, 2025  Damyan Ivanov
<dmn@debian.org>
Copyright (c) 2011  Alexandr Ciornii <alexchorny@gmail.com>
Copyright (c) 2010, 2011  Mike Pomraning <mjp@pilcrow.madison.wi.us>
Copyright (c) 1999-2008  Edwin Pratomo
Portions Copyright (c) 2001-2005  Daniel Ritz

The DBD::Firebird module is free software. You may distribute under the terms of either the GNU General Public License or the Artistic License, as specified in the Perl README file. ✨

💖 ACKNOWLEDGEMENTS 💖

An attempt to enumerate all who have contributed patches (may misses some): Michael Moehle, Igor Klingen, Sergey Skvortsov, Ilya Verlinsky, Pavel Zheltouhov, Peter Wilkinson, Mark D. Anderson, Michael Samanov, Michael Arnett, Flemming Frandsen, Mike Shoyher, Christiaan Lademann.

Shoutout to every single one of them, no cap. 🫶

aaaand that's the whole page, bestie!! 🎀 we did it!! this was perl v5.42.3, dated 2026-08-20, filed under Firebird(3pm) — go forth and query responsibly, you absolute icon 💅👑

📦 where this page comes from