1Firebird(3) User Contributed Perl Documentation Firebird(3)
2
6 DBD::Firebird - DBI driver for Firebird RDBMS server
7
9 use DBI;
10 $dbh = DBI->connect("dbi:Firebird:db=$dbname", $user, $password);
12 # See the DBI module documentation for full details
14
16 DBD::Firebird is a Perl module which works with the DBI module to
17 provide access to Firebird databases.
18
20 This documentation describes driver specific behavior and restrictions.
21 It is not supposed to be used as the only reference for the user. In
22 any case consult the DBI documentation first !
23
25 DBI Class Methods
26 connect
27 To connect to a database with a minimum of parameters, use the
28 following syntax:
29 $dbh = DBI->connect("dbi:Firebird:dbname=$dbname", $user, $password);
31 If omitted, $user defaults to the ISC_USER environment variable
33 (or, failing that, the DBI-standard DBI_USER environment variable).
34 Similarly, $password defaults to ISC_PASSWORD (or DBI_PASS). If
35 $dbname is blank, that is, "dbi:Firebird:dbname=", the environment
36 variable ISC_DATABASE is substituted.
37 The DSN may take several optional parameters, which may be split
39 over multiple lines. Here is an example of connect statement which
40 uses all possible parameters:
41 $dsn =<< "DSN";
43 dbi:Firebird:dbname=$dbname;
44 host=$host;
45 port=$port;
46 ib_dialect=$dialect;
47 ib_role=$role;
48 ib_charset=$charset;
49 ib_cache=$cache;
50 timeout=$timeout
51 DSN
52 $dbh = DBI->connect($dsn, $username, $password);
54 The $dsn is prefixed by 'dbi:Firebird:', and consists of key-value
56 parameters separated by semicolons. New line may be added after the
57 semicolon. The following is the list of valid parameters and their
58 respective meanings:
59 parameter meaning optional?
61 -----------------------------------------------------------------
62 database path to the database required
63 dbname path to the database
64 db path to the database
65 hostname hostname / IP address optional
66 host hostname / IP address
67 port port number optional
68 timeout connect timeout in seconds optional
69 ib_dialect the SQL dialect to be used optional
70 ib_role the role of the user optional
71 ib_charset character set to be used optional
72 ib_cache number of database cache buffers optional
73 ib_dbkey_scope change default duration of RDB$DB_KEY optional
74 database could be used interchangebly with dbname and db. To
76 connect to a remote host, use the host parameter. Here is an
77 example of DSN to connect to a remote Windows host:
78 $dsn = "dbi:Firebird:db=C:/temp/test.gdb;host=example.com;ib_dialect=3";
80 Database file alias can be used too in connection string. In the
82 following example, "billing" is defined in aliases.conf:
83 $dsn = 'dbi:Firebird:hostname=192.168.88.5;db=billing;ib_dialect=3';
85 Firebird as of version 1.0 listens on port specified within the
87 services file. To connect to port other than the default 3050, add
88 the port number at the end of host name, separated by a slash.
89 Example:
90 $dsn = 'dbi:Firebird:db=/data/test.gdb;host=localhost/3060';
92 Firebird 1.0 introduces SQL dialect to provide backward
94 compatibility with databases created by older versions of Firebird
95 (pre 1.0). In short, SQL dialect controls how Firebird interprets:
96 - double quotes
98 - the DATE datatype
99 - decimal and numeric datatypes
100 - new 1.0 reserved keywords
101 Valid values for ib_dialect are 1 and 3 .The driver's default value
103 is 3 (Currently it is possible to create databases in Dialect 1 and
104 3 only, however it is recommended that you use Dialect 3
105 exclusively, since Dialect 1 will eventually be deprecated. Dialect
106 2 cannot be used to create a database since it only serves to
107 convert Dialect 1 to Dialect 3).
108 http://www.firebirdsql.org/file/documentation/reference_manuals/user_manuals/html/isql-dialects.html
110 ib_role specifies the role of the connecting user. SQL role is
112 implemented by Firebird to make database administration easier when
113 dealing with lots of users. A detailed reading can be found at:
114 http://www.ibphoenix.com/resources/documents/general/doc_59
116 If ib_cache is not specified, the default database's cache size
118 value will be used. The Firebird Operation Guide discusses in full
119 length the importance of this parameter to gain the best
120 performance.
121 available_drivers
123 @driver_names = DBI->available_drivers;
124 Implemented by DBI, no driver-specific impact.
126 data_sources
128 This method is not yet implemented.
129 trace
131 DBI->trace($trace_level, $trace_file)
132 Implemented by DBI, no driver-specific impact.
134 DBI Dynamic Attributes
136 See Common Methods.
137
139 err
140 $rv = $h->err;
141 Supported by the driver as proposed by DBI.
143 errstr
145 $str = $h->errstr;
146 Supported by the driver as proposed by DBI.
148 state
150 This method is not yet implemented.
151 trace
153 $h->trace($trace_level, $trace_filename);
154 Implemented by DBI, no driver-specific impact.
156 trace_msg
158 $h->trace_msg($message_text);
159 Implemented by DBI, no driver-specific impact.
161 func
163 See Transactions section for information about invoking
164 "ib_set_tx_param()" from func() method.
165
167 Warn (boolean, inherited)
168 Implemented by DBI, no driver-specific impact.
169 Active (boolean, read-only)
171 Supported by the driver as proposed by DBI. A database handle is
172 active while it is connected and statement handle is active until
173 it is finished.
174 Kids (integer, read-only)
176 Implemented by DBI, no driver-specific impact.
177 ActiveKids (integer, read-only)
179 Implemented by DBI, no driver-specific impact.
180 CachedKids (hash ref)
182 Implemented by DBI, no driver-specific impact.
183 CompatMode (boolean, inherited)
185 Not used by this driver.
186 InactiveDestroy (boolean)
188 Implemented by DBI, no driver-specific impact.
189 PrintError (boolean, inherited)
191 Implemented by DBI, no driver-specific impact.
192 RaiseError (boolean, inherited)
194 Implemented by DBI, no driver-specific impact.
195 ChopBlanks (boolean, inherited)
197 Supported by the driver as proposed by DBI.
198 LongReadLen (integer, inherited)
200 Supported by the driver as proposed by DBI.The default value is 80
201 bytes.
202 LongTruncOk (boolean, inherited)
204 Supported by the driver as proposed by DBI.
205 Taint (boolean, inherited)
207 Implemented by DBI, no driver-specific impact.
208
210 Database Handle Methods
211 selectrow_array
212 @row_ary = $dbh->selectrow_array($statement, \%attr, @bind_values);
213 Implemented by DBI, no driver-specific impact.
215 selectall_arrayref
217 $ary_ref = $dbh->selectall_arrayref($statement, \%attr, @bind_values);
218 Implemented by DBI, no driver-specific impact.
220 selectcol_arrayref
222 $ary_ref = $dbh->selectcol_arrayref($statement, \%attr, @bind_values);
223 Implemented by DBI, no driver-specific impact.
225 prepare
227 $sth = $dbh->prepare($statement, \%attr);
228 Supported by the driver as proposed by DBI. When AutoCommit is On,
230 this method implicitly starts a new transaction, which will be
231 automatically committed after the following execute() or the last
232 fetch(), depending on the statement type. For select statements,
233 commit automatically takes place after the last fetch(), or by
234 explicitly calling finish() method if there are any rows remaining.
235 For non-select statements, execute() will implicitly commits the
236 transaction.
237 prepare_cached
239 $sth = $dbh->prepare_cached($statement, \%attr);
240 Implemented by DBI, no driver-specific impact.
242 do
244 $rv = $dbh->do($statement, \%attr, @bind_values);
245 Supported by the driver as proposed by DBI. This should be used
247 for non-select statements, where the driver doesn't take the
248 conservative prepare - execute steps, thereby speeding up the
249 execution time. But if this method is used with bind values, the
250 speed advantage diminishes as this method calls prepare() for
251 binding the placeholders. Instead of calling this method
252 repeatedly with bind values, it would be better to call prepare()
253 once, and execute() many times.
254 See the notes for the execute method elsewhere in this document.
256 Unlike the execute method, currently this method doesn't return the
257 number of affected rows.
258 commit
260 $rc = $dbh->commit;
261 Supported by the driver as proposed by DBI. See also the notes
263 about Transactions elsewhere in this document.
264 rollback
266 $rc = $dbh->rollback;
267 Supported by the driver as proposed by DBI. See also the notes
269 about Transactions elsewhere in this document.
270 disconnect
272 $rc = $dbh->disconnect;
273 Supported by the driver as proposed by DBI.
275 ping
277 $rc = $dbh->ping;
278 This driver supports the ping-method, which can be used to check
280 the validity of a database-handle. This is especially required by
281 "Apache::DBI".
282 primary_key_info
284 $sth = $dbh->primary_key_info('', '', $table_name);
285 @pks = $dbh->primary_key('', '', $table_name);
286 Supported by the driver as proposed by DBI. Note that catalog and
288 schema are ignored.
289 table_info
291 $sth = $dbh->table_info;
292 All Firebird versions support the basic DBI-specified columns
294 (TABLE_NAME, TABLE_TYPE, etc.) as well as "IB_TABLE_OWNER".
295 Peculiar versions may return additional fields, prefixed by "IB_".
296 Table searching may not work as expected on older
298 Interbase/Firebird engines which do not natively offer a TRIM()
299 function. Some engines store TABLE_NAME in a blank-padded CHAR
300 field, and a search for table name is performed via a SQL "LIKE"
301 predicate, which is sensitive to blanks. That is:
302 $dbh->table_info('', '', 'FOO'); # May not find table "FOO", depending on
304 # FB version
305 $dbh->table_info('', '', 'FOO%'); # Will always find "FOO", but also tables
306 # "FOOD", "FOOT", etc.
307 Future versions of DBD::Firebird may attempt to work around this
309 irritating limitation, at the expense of efficiency.
310 Note that Firebird implementations do not presently support the DBI
312 concepts of 'catalog' and 'schema', so these parameters are
313 effectively ignored.
314 tables
316 @names = $dbh->tables;
317 Returns a list of tables, excluding any 'SYSTEM TABLE' types.
319 type_info_all
321 $type_info_all = $dbh->type_info_all;
322 Supported by the driver as proposed by DBI.
324 For further details concerning the Firebird specific data-types
326 please read the Firebird Data Definition Guide
327 http://www.firebirdsql.org/en/reference-manuals/
329 type_info
331 @type_info = $dbh->type_info($data_type);
332 Implemented by DBI, no driver-specific impact.
334 quote
336 $sql = $dbh->quote($value, $data_type);
337 Implemented by DBI, no driver-specific impact.
339 Database Handle Attributes
341 AutoCommit (boolean)
342 Supported by the driver as proposed by DBI. According to the
343 classification of DBI, Firebird is a database, in which a
344 transaction must be explicitly started. Without starting a
345 transaction, every change to the database becomes immediately
346 permanent. The default of AutoCommit is on, which corresponds to
347 the DBI's default. When setting AutoCommit to off, a transaction
348 will be started and every commit or rollback will automatically
349 start a new transaction. For details see the notes about
350 Transactions elsewhere in this document.
351 Driver (handle)
353 Implemented by DBI, no driver-specific impact.
354 Name (string, read-only)
356 Not yet implemented.
357 RowCacheSize (integer)
359 Implemented by DBI, not used by the driver.
360 ib_softcommit (driver-specific, boolean)
362 Set this attribute to TRUE to use Firebird's soft commit feature
363 (default to FALSE). Soft commit retains the internal transaction
364 handle when committing a transaction, while the default commit
365 behavior always closes and invalidates the transaction handle.
366 Since the transaction handle is still open, there is no need to
368 start a new transaction upon every commit, so applications can gain
369 performance improvement. Using soft commit is also desirable when
370 dealing with nested statement handles under AutoCommit on.
371 Switching the attribute's value from TRUE to FALSE will force hard
373 commit thus closing the current transaction.
374 ib_enable_utf8 (driver-specific, boolean)
376 Setting this attribute to TRUE will cause any Perl Unicode strings
377 supplied as statement parameters to be downgraded to octet
378 sequences before passing them to Firebird.
379 Also, any character data retrieved from the database (CHAR,
381 VARCHAR, BLOB sub_type TEXT) will be upgraded to Perl Unicode
382 strings.
383 Caveat: Currently this is supported only if the ib_charset DSN
385 parameter is "UTF8". In the future, encoding and decoding to/from
386 arbitrary character set may be implemented.
387 Example:
389 $dbh = DBI->connect( 'dbi:Firebird:db=database.fdb;ib_charset=UTF8',
391 { ib_enable_utf8 => 1 } );
392
394 Statement Handle Methods
395 bind_param
396 Supported by the driver as proposed by DBI. The SQL data type
397 passed as the third argument is ignored.
398 bind_param_array
400 Supported by the driver as proposed by DBI. The attributes,
401 supplied in the optional third parameter are ignored.
402 bind_param_inout
404 Not supported by this driver.
405 execute
407 $rv = $sth->execute(@bind_values);
408 Supported by the driver as proposed by DBI.
410 fetchrow_arrayref
412 $ary_ref = $sth->fetchrow_arrayref;
413 Supported by the driver as proposed by DBI.
415 fetchrow_array
417 @ary = $sth->fetchrow_array;
418 Supported by the driver as proposed by DBI.
420 fetchrow_hashref
422 $hash_ref = $sth->fetchrow_hashref;
423 Supported by the driver as proposed by DBI.
425 fetchall_arrayref
427 $tbl_ary_ref = $sth->fetchall_arrayref;
428 Implemented by DBI, no driver-specific impact.
430 finish
432 $rc = $sth->finish;
433 Supported by the driver as proposed by DBI.
435 rows
437 $rv = $sth->rows;
438 Supported by the driver as proposed by DBI. It returns the number
440 of fetched rows for select statements, otherwise it returns -1
441 (unknown number of affected rows).
442 bind_col
444 $rc = $sth->bind_col($column_number, \$var_to_bind, \%attr);
445 Supported by the driver as proposed by DBI.
447 bind_columns
449 $rc = $sth->bind_columns(\%attr, @list_of_refs_to_vars_to_bind);
450 Supported by the driver as proposed by DBI.
452 dump_results
454 $rows = $sth->dump_results($maxlen, $lsep, $fsep, $fh);
455 Implemented by DBI, no driver-specific impact.
457 Statement Handle Attributes
459 NUM_OF_FIELDS (integer, read-only)
460 Implemented by DBI, no driver-specific impact.
461 NUM_OF_PARAMS (integer, read-only)
463 Implemented by DBI, no driver-specific impact.
464 NAME (array-ref, read-only)
466 Supported by the driver as proposed by DBI.
467 NAME_lc (array-ref, read-only)
469 Implemented by DBI, no driver-specific impact.
470 NAME_uc (array-ref, read-only)
472 Implemented by DBI, no driver-specific impact.
473 TYPE (array-ref, read-only)
475 Supported by the driver as proposed by DBI, with the restriction,
476 that the types are Firebird specific data-types which do not
477 correspond to international standards.
478 PRECISION (array-ref, read-only)
480 Supported by the driver as proposed by DBI.
481 SCALE (array-ref, read-only)
483 Supported by the driver as proposed by DBI.
484 NULLABLE (array-ref, read-only)
486 Supported by the driver as proposed by DBI.
487 CursorName (string, read-only)
489 Supported by the driver as proposed by DBI.
490 Statement (string, read-only)
492 Supported by the driver as proposed by DBI.
493 RowCache (integer, read-only)
495 Not supported by the driver.
496 ParamValues (hashref, read-only)
498 Supported by the driver as proposed by DBI.
499
501 The transaction behavior is controlled with the attribute AutoCommit.
502 For a complete definition of AutoCommit please refer to the DBI
503 documentation.
504 According to the DBI specification the default for AutoCommit is TRUE.
506 In this mode, any change to the database becomes valid immediately. Any
507 commit() or rollback() will be rejected.
508 If AutoCommit is switched-off, immediately a transaction will be
510 started. A rollback() will rollback and close the active transaction,
511 then implicitly start a new transaction. A disconnect will issue a
512 rollback.
513 Firebird provides fine control over transaction behavior, where users
515 can specify the access mode, the isolation level, the lock resolution,
516 and the table reservation (for a specified table). For this purpose,
517 "ib_set_tx_param()" database handle method is available.
518 Upon a successful "connect()", these default parameter values will be
520 used for every SQL operation:
521 Access mode: read_write
523 Isolation level: snapshot
524 Lock resolution: wait
525 Any of the above value can be changed using "ib_set_tx_param()".
527 ib_set_tx_param
529 $dbh->func(
530 -access_mode => 'read_write',
531 -isolation_level => 'read_committed',
532 -lock_resolution => 'wait',
533 'ib_set_tx_param'
534 );
535 Valid value for "-access_mode" is "read_write", or "read_only".
537 Valid value for "-lock_resolution" is "wait", or "no_wait". In
539 Firebird 2.0, a timeout value for wait is introduced. This can be
540 specified using hash ref as lock_resolution value:
541 $dbh->func(
543 -lock_resolution => { wait => 5 }, # wait for 5 seconds
544 'ib_set_tx_param'
545 );
546 "-isolation_level" may be: "read_committed", "snapshot",
548 "snapshot_table_stability".
549 If "read_committed" is to be used with "record_version" or
551 "no_record_version", then they should be inside an anonymous array:
552 $dbh->func(
554 -isolation_level => ['read_committed', 'record_version'],
555 'ib_set_tx_param'
556 );
557 Table reservation is supported since "DBD::Firebird 0.30". Names of
559 the tables to reserve as well as their reservation params/values
560 are specified inside a hashref, which is then passed as the value
561 of "-reserving".
562 The following example reserves "foo_table" with "read" lock and
564 "bar_table" with "read" lock and "protected" access:
565 $dbh->func(
567 -access_mode => 'read_write',
568 -isolation_level => 'read_committed',
569 -lock_resolution => 'wait',
570 -reserving =>
571 {
572 foo_table => {
573 lock => 'read',
574 },
575 bar_table => {
576 lock => 'read',
577 access => 'protected',
578 },
579 },
580 'ib_set_tx_param'
581 );
582 Possible table reservation parameters are:
584 "access" (optional)
586 Valid values are "shared" or "protected".
587 "lock" (required)
589 Valid values are "read" or "write".
590 Under "AutoCommit" mode, invoking this method doesn't only change
592 the transaction parameters (as with "AutoCommit" off), but also
593 commits the current transaction. The new transaction parameters
594 will be used in any newly started transaction.
595 "ib_set_tx_param()" can also be invoked with no parameter in which
597 it resets transaction parameters to the default value.
598
600 "DBD::Firebird" supports various formats for query results of DATE,
601 TIME, and TIMESTAMP types.
602 By default, it uses "%c" for TIMESTAMP, "%x" for DATE, and "%X" for
604 TIME, and pass them to ANSI C's strftime() function to format your
605 query results. These values are respectively stored in
606 ib_timestampformat, ib_dateformat, and ib_timeformat attributes, and
607 may be changed in two ways:
608 • At $dbh level
610 This replaces the default values. Example:
612 $dbh->{ib_timestampformat} = '%m-%d-%Y %H:%M';
614 $dbh->{ib_dateformat} = '%m-%d-%Y';
615 $dbh->{ib_timeformat} = '%H:%M';
616 • At $sth level
618 This overrides the default values only for the currently prepared
620 statement. Example:
621 $attr = {
623 ib_timestampformat => '%m-%d-%Y %H:%M',
624 ib_dateformat => '%m-%d-%Y',
625 ib_timeformat => '%H:%M',
626 };
627 # then, pass it to prepare() method.
628 $sth = $dbh->prepare($sql, $attr);
629 Since locale settings affect the result of strftime(), if your
631 application is designed to be portable across different locales, you
632 may consider using these two special formats: 'TM' and 'ISO'. "TM"
633 returns a 9-element list, much like Perl's localtime(). The "ISO"
634 format applies sprintf()'s pattern "%04d-%02d-%02d %02d:%02d:%02d.%04d"
635 for TIMESTAMP, "%04d-%02d-%02d" for DATE, and "%02d:%02d:%02d.%04d" for
636 TIME.
637 "$dbh->{ib_time_all}" can be used to specify all of the three formats
639 at once. Example:
640 $dbh->{ib_time_all} = 'TM';
642
644 Event alerter is used to notify client applications whenever something
645 is happened on the database. For this to work, a trigger should be
646 created, which then calls POST_EVENT to post the event notification to
647 the interested client. A client could behave in two ways: wait for the
648 event synchronously, or register a callback which will be invoked
649 asynchronously each time a posted event received.
650 "ib_init_event"
652 $evh = $dbh->func(@event_names, 'ib_init_event');
653 Creates an event handle from a list of event names.
655 "ib_wait_event"
657 $dbh->func($evh, 'ib_wait_event');
658 Wait synchronously for particular events registered via event
660 handle $evh. Returns a hashref containing pair(s) of posted
661 event's name and its corresponding count, or undef on failure.
662 "ib_register_callback"
664 my $cb = sub { my $posted_events = $_[0]; ++$::COUNT < 6 };
665 $dbh->func($evh, $cb, 'ib_register_callback');
666 sub inc_count { my $posted_events = shift; ++$::COUNT < 6 };
668 $dbh->func($evh, \&inc_count, 'ib_register_callback');
669 # or anonyomus subroutine
671 $dbh->func(
672 $evh,
673 sub { my ($pe) = @_; ++$::COUNT < 6 },
674 'ib_register_callback'
675 );
676 Associates an event handle with an asynchronous callback. A
678 callback will be passed a hashref as its argument, this hashref
679 contains pair(s) of posted event's name and its corresponding
680 count.
681 It is safe to call "ib_register_callback" multiple times for the
683 same event handle. In this case, the previously registered callback
684 will be automatically cancelled.
685 If the callback returns FALSE, the registered callback will be no
687 longer invoked, but internally it is still there until the event
688 handle goes out of scope (or undef-ed), or you call
689 "ib_cancel_callback" to actually disassociate it from the event
690 handle.
691 "ib_cancel_callback"
693 $dbh->func($evh, 'ib_cancel_callback');
694 Unregister a callback from an event handle. This function has a
696 limitation, however, that it can't be called from inside a
697 callback. In many cases, you won't need this function, since when
698 an event handle goes out of scope, its associated callback(s) will
699 be automatically cancelled before it is cleaned up.
700
702 "ib_tx_info"
703 $hash_ref = $dbh->func('ib_tx_info');
704 Retrieve information about current active transaction.
706 "ib_database_info"
708 $hash_ref = $dbh->func(@info, 'ib_database_info');
709 $hash_ref = $dbh->func([@info], 'ib_database_info');
710 Retrieve database information from current connection.
712 "ib_plan"
714 $plan = $sth->func('ib_plan');
715 Retrieve query plan from a prepared SQL statement.
717 my $sth = $dbh->prepare('SELECT * FROM foo');
719 print $sth->func('ib_plan'); # PLAN (FOO NATURAL)
720 "ib_drop_database"
722 $result = $dbh->func('ib_drop_database');
723 Drops the database, associated with the connection. The database
725 handle is no longer valid after calling this function.
726 Caution is advised as the drop is irrevocable.
728
730 Here is a list of SQL statements which can't be used. But this
731 shouldn't be a problem, because their functionality are already
732 provided by the DBI methods.
733 • SET TRANSACTION
735 Use "$dbh-"func(..., 'set_tx_param')> instead.
737 • DESCRIBE
739 Provides information about columns that are retrieved by a DSQL
741 statement, or about placeholders in a statement. This functionality
742 is supported by the driver, and transparent for users. Column names
743 are available via $sth->{NAME} attributes.
744 • EXECUTE IMMEDIATE
746 Calling do() method without bind value(s) will do the same.
748 • CLOSE, OPEN, DECLARE CURSOR
750 $sth->{CursorName} is automagically available upon executing a
752 "SELECT .. FOR UPDATE" statement. A cursor is closed after the last
753 fetch(), or by calling $sth->finish().
754 • PREPARE, EXECUTE, FETCH
756 Similar functionalities are obtained by using prepare(), execute(),
758 and fetch() methods.
759
761 "DBD::Firebird" is known to work with "DBIx::Recordset" 0.21, and
762 "Apache::DBI" 0.87. Yuri Vasiliev <yuri.vasiliev@targuscom.com>
763 reported successful usage with Apache::AuthDBI (part of "Apache::DBI"
764 0.87 distribution).
765 The driver is untested with "Apache::Session::DBI". Doesn't work with
767 "Tie::DBI". "Tie::DBI" calls $dbh->prepare("LISTFIELDS $table_name") on
768 which Firebird fails to parse. I think that the call should be made
769 within an eval block.
770
772 DBD::Firebird->create_database( { params... } )
773 A class method for creating empty databases.
774 The method croaks on error. Params may be:
776 db_path (string, required)
778 Path to database, including host name if necessary.
779 Examples:
781 server:/path/to/db.fdb
783 /srv/db/base.fdb
784 user (string, optional)
785 User name to be used for the request.
786 password (string, optional)
788 Password to be used for the request.
789 page_size (integer, optional)
791 Page size of the newly created database. Should be something
792 supported by the server. Firebird 2.5 supports the following page
793 sizes: 1024, 2048, 4096, 8192 and 16384 and defaults to 4096.
794 character_set (string, optional)
796 The default character set of the database. Firebird 2.5 defaults to
797 "NONE".
798 dialect (integer, optional)
800 The dialect of the database. Defaults to 3.
801 After creation, the new database can be used after connecting to it
803 with the usual DBI->connect(...)
804 DBD::Firebird->gfix( { params } )
806 A class method for simulating a subset of the functionality of the
807 Firebird's gfix(1) utility.
808 Params is a hash reference, with the following keys:
810 db_path (string, required)
812 The path to the database to connect to. Should include host name if
813 necessary.
814 user (string, optional)
816 User name to connect as. Must be SYSDBA or database owner.
817 password (string, optional)
819 Password to be used for the connection.
820 Note that user and password are not needed for embedded
822 connections.
823 forced_writes (boolean, optional)
825 If given, sets the forced writes flag of the database, causing
826 Firebird to use synchronous writes when working with that database.
827 buffers (integer, optional)
829 If given, sets the default number of buffers for the database. Can
830 be overridden on connect time. Note that buffers are measured in
831 database pages, not bytes.
832
834 Why do some operations performing positioned update and delete fail when
835 AutoCommit is on?
836 For example, the following code snippet fails:
837 $sth = $dbh->prepare(
839 "SELECT * FROM ORDERS WHERE user_id < 5 FOR UPDATE OF comment");
840 $sth->execute;
841 while (@res = $sth->fetchrow_array) {
842 $dbh->do("UPDATE ORDERS SET comment = 'Wonderful' WHERE
843 CURRENT OF $sth->{CursorName}");
844 }
845 When AutoCommit is on, a transaction is started within prepare(), and
847 committed automatically after the last fetch(), or within finish().
848 Within do(), a transaction is started right before the statement is
849 executed, and gets committed right after the statement is executed. The
850 transaction handle is stored within the database handle. The driver is
851 smart enough not to override an active transaction handle with a new
852 one. So, if you notice the snippet above, after the first
853 fetchrow_array(), the do() is still using the same transaction context,
854 but as soon as it has finished executing the statement, it commits the
855 transaction, whereas the next fetchrow_array() still needs the
856 transaction context!
857 So the secret to make this work is to keep the transaction open. This
859 can be done in two ways:
860 • Using AutoCommit = 0
862 If yours is default to AutoCommit on, you can put the snippet
864 within a block:
865 {
867 $dbh->{AutoCommit} = 0;
868 # same actions like above ....
869 $dbh->commit;
870 }
871 • Using $dbh->{ib_softcommit} = 1
873 This is a driver-specific attribute,You may want to look at
875 t/70-nested-sth.t to see it in action.
876 Why do nested statement handles break under AutoCommit mode?
878 The same explanation as above applies. The workaround is also much
879 alike:
880 {
882 $dbh->{AutoCommit} = 0;
883 $sth1 = $dbh->prepare("SELECT * FROM $table");
884 $sth2 = $dbh->prepare("SELECT * FROM $table WHERE id = ?");
885 $sth1->execute;
886 while ($row = $sth1->fetchrow_arrayref) {
888 $sth2->execute($row->[0]);
889 $res = $sth2->fetchall_arrayref;
890 }
891 $dbh->commit;
892 }
893 You may also use $dbh->{ib_softcommit} please check t/70nested-sth.t
895 for an example on how to use it.
896 Why do placeholders fail to bind, generating unknown datatype error
898 message?
899 You can't bind a field name. The following example will fail:
900 $sth = $dbh->prepare("SELECT (?) FROM $table");
902 $sth->execute('user_id');
903 There are cases where placeholders can't be used in conjunction with
905 COLLATE clause, such as this:
906 SELECT * FROM $table WHERE UPPER(author) LIKE UPPER(? COLLATE FR_CA);
908 This deals with the Firebird's SQL parser, not with "DBD::Firebird".
910 The driver just passes SQL statements through the engine.
911 How to do automatic increment for a specific field?
913 Create a sequence and a trigger to associate it with the field. The
914 following example creates a sequence named PROD_ID_SEQ, and a trigger
915 for table ORDERS which uses the generator to perform auto increment on
916 field PRODUCE_ID with increment size of 1.
917 $dbh->do("create sequence PROD_ID_SEQ");
919 $dbh->do(
920 "CREATE TRIGGER INC_PROD_ID FOR ORDERS
921 BEFORE INSERT POSITION 0
922 AS BEGIN
923 NEW.PRODUCE_ID = NEXT VALUE FOR PROD_ID_SEQ;
924 END");
925 From Firebird 3.0 there is Identity support
927 How can I perform LIMIT clause as I usually do in MySQL?
929 "LIMIT" clause let users to fetch only a portion rather than the whole
930 records as the result of a query. This is particularly efficient and
931 useful for paging feature on web pages, where users can navigate back
932 and forth between pages.
933 Using Firebird 2.5.x this can be implemented by using "ROWS" .
935 http://www.firebirdsql.org/refdocs/langrefupd21-select.html#langrefupd21-select-rows
937 For example, to display a portion of table employee within your
939 application:
940 # fetch record 1 - 5:
942 $res = $dbh->selectall_arrayref("SELECT * FROM employee rows 1 to 5)");
943 # fetch record 6 - 10:
945 $res = $dbh->selectall_arrayref("SELECT * FROM employee rows 6 to 10)");
946 How can I use the date/time formatting attributes?
948 Those attributes take the same format as the C function strftime()'s.
949 Examples:
950 $attr = {
952 ib_timestampformat => '%m-%d-%Y %H:%M',
953 ib_dateformat => '%m-%d-%Y',
954 ib_timeformat => '%H:%M',
955 };
956 Then, pass it to prepare() method.
958 $sth = $dbh->prepare($stmt, $attr);
960 # followed by execute() and fetch(), or:
961 $res = $dbh->selectall_arrayref($stmt, $attr);
963 Can I set the date/time formatting attributes between prepare and fetch?
965 No. "ib_dateformat", "ib_timeformat", and "ib_timestampformat" can only
966 be set during $sth->prepare. If this is a problem to you, let me know,
967 and probably I'll add this capability for the next release.
968 Can I change ib_dialect after DBI->connect ?
970 No. If this is a problem to you, let me know, and probably I'll add
971 this capability for the next release.
972
974 Private Method
975 "set_tx_param()" is obsoleted by "ib_set_tx_param()".
976
978 Clients
979 Linux
980 FreeBSD
981 Solaris
982 Win32
983 Servers
985 Firebird 2.5.x SS , SC and Classic for Linux (32 bits and 64)
986 Firebird 2.5.x for Windows, FreeBSD, Solaris
987
989 • DBI by Tim Bunce <Tim.Bunce@pobox.com>
990 • DBD::Firebird by Edwin Pratomo <edpratomo@cpan.org>, Daniel Ritz
992 <daniel.ritz@gmx.ch> and many others. See "COPYRIGHT & LICENSE".
993 This module is originally based on the work of Bill Karwin's
995 IBPerl.
996
998 Please report bugs and feature suggestions using
999 http://rt.cpan.org/Public/Dist/Display.html?Name=DBD-Firebird .
1000 This module doesn't work with MSWin32 ActivePerl iThreads, and its
1002 emulated fork. Tested with MSWin32 ActivePerl build 809 (Perl 5.8.3).
1003 The whole process will block in unpredictable manner.
1004 Under Linux, this module has been tested with several different
1006 iThreads enabled Perl releases.
1007 No problem occurred so far.. until you try to share a DBI handle ;-)
1009 But if you plan to use thread, you'd better use the latest stable
1011 version of Perl
1012 On FreeBSD you need a Perl compiled with thread support.
1014 Limitations:
1016 • Arrays are not (yet) supported
1018 • Read/Write BLOB fields block by block not (yet) supported. The
1020 maximum size of a BLOB read/write is hardcoded to about 1 MB.
1021 • service manager API is not supported.
1023
1025 DBI(3).
1026
1028 Copyright (c) 2010- 2012 Popa Adrian Marius <mapopa@gmail.com>
1029 Copyright (c) 2011- 2012 Stefan Suciu <stefbv70@gmail.com>
1030 Copyright (c) 2011, 2017 Damyan Ivanov <dmn@debian.org>
1031 Copyright (c) 2011 Alexandr Ciornii <alexchorny@gmail.com>
1032 Copyright (c) 2010, 2011 Mike Pomraning <mjp@pilcrow.madison.wi.us>
1033 Copyright (c) 1999-2008 Edwin Pratomo
1034 Portions Copyright (c) 2001-2005 Daniel Ritz
1035 The DBD::Firebird module is free software. You may distribute under
1037 the terms of either the GNU General Public License or the Artistic
1038 License, as specified in the Perl README file.
1039
1041 An attempt to enumerate all who have contributed patches (may misses
1042 some): Michael Moehle, Igor Klingen, Sergey Skvortsov, Ilya Verlinsky,
1043 Pavel Zheltouhov, Peter Wilkinson, Mark D. Anderson, Michael Samanov,
1044 Michael Arnett, Flemming Frandsen, Mike Shoyher, Christiaan Lademann.
1045perl v5.34.0 2021-11-10 Firebird(3)