1Firebird(3) User Contributed Perl Documentation Firebird(3)
2
3
4
6 DBD::Firebird - DBI driver for Firebird RDBMS server
7
9 use DBI;
10
11 $dbh = DBI->connect("dbi:Firebird:db=$dbname", $user, $password);
12
13 # 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
30 $dbh = DBI->connect("dbi:Firebird:dbname=$dbname", $user, $password);
31
32 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
38 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
42 $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
53 $dbh = DBI->connect($dsn, $username, $password);
54
55 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
60 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
75 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
79 $dsn = "dbi:Firebird:db=C:/temp/test.gdb;host=example.com;ib_dialect=3";
80
81 Database file alias can be used too in connection string. In the
82 following example, "billing" is defined in aliases.conf:
83
84 $dsn = 'dbi:Firebird:hostname=192.168.88.5;db=billing;ib_dialect=3';
85
86 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
91 $dsn = 'dbi:Firebird:db=/data/test.gdb;host=localhost/3060';
92
93 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
97 - double quotes
98 - the DATE datatype
99 - decimal and numeric datatypes
100 - new 1.0 reserved keywords
101
102 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
109 http://www.firebirdsql.org/file/documentation/reference_manuals/user_manuals/html/isql-dialects.html
110
111 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
115 http://www.ibphoenix.com/resources/documents/general/doc_59
116
117 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
122 available_drivers
123 @driver_names = DBI->available_drivers;
124
125 Implemented by DBI, no driver-specific impact.
126
127 data_sources
128 This method is not yet implemented.
129
130 trace
131 DBI->trace($trace_level, $trace_file)
132
133 Implemented by DBI, no driver-specific impact.
134
135 DBI Dynamic Attributes
136 See Common Methods.
137
139 err
140 $rv = $h->err;
141
142 Supported by the driver as proposed by DBI.
143
144 errstr
145 $str = $h->errstr;
146
147 Supported by the driver as proposed by DBI.
148
149 state
150 This method is not yet implemented.
151
152 trace
153 $h->trace($trace_level, $trace_filename);
154
155 Implemented by DBI, no driver-specific impact.
156
157 trace_msg
158 $h->trace_msg($message_text);
159
160 Implemented by DBI, no driver-specific impact.
161
162 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
170 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
175 Kids (integer, read-only)
176 Implemented by DBI, no driver-specific impact.
177
178 ActiveKids (integer, read-only)
179 Implemented by DBI, no driver-specific impact.
180
181 CachedKids (hash ref)
182 Implemented by DBI, no driver-specific impact.
183
184 CompatMode (boolean, inherited)
185 Not used by this driver.
186
187 InactiveDestroy (boolean)
188 Implemented by DBI, no driver-specific impact.
189
190 PrintError (boolean, inherited)
191 Implemented by DBI, no driver-specific impact.
192
193 RaiseError (boolean, inherited)
194 Implemented by DBI, no driver-specific impact.
195
196 ChopBlanks (boolean, inherited)
197 Supported by the driver as proposed by DBI.
198
199 LongReadLen (integer, inherited)
200 Supported by the driver as proposed by DBI.The default value is 80
201 bytes.
202
203 LongTruncOk (boolean, inherited)
204 Supported by the driver as proposed by DBI.
205
206 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
214 Implemented by DBI, no driver-specific impact.
215
216 selectall_arrayref
217 $ary_ref = $dbh->selectall_arrayref($statement, \%attr, @bind_values);
218
219 Implemented by DBI, no driver-specific impact.
220
221 selectcol_arrayref
222 $ary_ref = $dbh->selectcol_arrayref($statement, \%attr, @bind_values);
223
224 Implemented by DBI, no driver-specific impact.
225
226 prepare
227 $sth = $dbh->prepare($statement, \%attr);
228
229 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
238 prepare_cached
239 $sth = $dbh->prepare_cached($statement, \%attr);
240
241 Implemented by DBI, no driver-specific impact.
242
243 do
244 $rv = $dbh->do($statement, \%attr, @bind_values);
245
246 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
255 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
259 commit
260 $rc = $dbh->commit;
261
262 Supported by the driver as proposed by DBI. See also the notes
263 about Transactions elsewhere in this document.
264
265 rollback
266 $rc = $dbh->rollback;
267
268 Supported by the driver as proposed by DBI. See also the notes
269 about Transactions elsewhere in this document.
270
271 disconnect
272 $rc = $dbh->disconnect;
273
274 Supported by the driver as proposed by DBI.
275
276 ping
277 $rc = $dbh->ping;
278
279 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
283 primary_key_info
284 $sth = $dbh->primary_key_info('', '', $table_name);
285 @pks = $dbh->primary_key('', '', $table_name);
286
287 Supported by the driver as proposed by DBI. Note that catalog and
288 schema are ignored.
289
290 table_info
291 $sth = $dbh->table_info;
292
293 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
297 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
303 $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
308 Future versions of DBD::Firebird may attempt to work around this
309 irritating limitation, at the expense of efficiency.
310
311 Note that Firebird implementations do not presently support the DBI
312 concepts of 'catalog' and 'schema', so these parameters are
313 effectively ignored.
314
315 tables
316 @names = $dbh->tables;
317
318 Returns a list of tables, excluding any 'SYSTEM TABLE' types.
319
320 type_info_all
321 $type_info_all = $dbh->type_info_all;
322
323 Supported by the driver as proposed by DBI.
324
325 For further details concerning the Firebird specific data-types
326 please read the Firebird Data Definition Guide
327
328 http://www.firebirdsql.org/en/reference-manuals/
329
330 type_info
331 @type_info = $dbh->type_info($data_type);
332
333 Implemented by DBI, no driver-specific impact.
334
335 quote
336 $sql = $dbh->quote($value, $data_type);
337
338 Implemented by DBI, no driver-specific impact.
339
340 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
352 Driver (handle)
353 Implemented by DBI, no driver-specific impact.
354
355 Name (string, read-only)
356 Not yet implemented.
357
358 RowCacheSize (integer)
359 Implemented by DBI, not used by the driver.
360
361 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
367 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
372 Switching the attribute's value from TRUE to FALSE will force hard
373 commit thus closing the current transaction.
374
375 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
380 Also, any character data retrieved from the database (CHAR,
381 VARCHAR, BLOB sub_type TEXT) will be upgraded to Perl Unicode
382 strings.
383
384 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
388 Example:
389
390 $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
399 bind_param_array
400 Supported by the driver as proposed by DBI. The attributes,
401 supplied in the optional third parameter are ignored.
402
403 bind_param_inout
404 Not supported by this driver.
405
406 execute
407 $rv = $sth->execute(@bind_values);
408
409 Supported by the driver as proposed by DBI.
410
411 fetchrow_arrayref
412 $ary_ref = $sth->fetchrow_arrayref;
413
414 Supported by the driver as proposed by DBI.
415
416 fetchrow_array
417 @ary = $sth->fetchrow_array;
418
419 Supported by the driver as proposed by DBI.
420
421 fetchrow_hashref
422 $hash_ref = $sth->fetchrow_hashref;
423
424 Supported by the driver as proposed by DBI.
425
426 fetchall_arrayref
427 $tbl_ary_ref = $sth->fetchall_arrayref;
428
429 Implemented by DBI, no driver-specific impact.
430
431 finish
432 $rc = $sth->finish;
433
434 Supported by the driver as proposed by DBI.
435
436 rows
437 $rv = $sth->rows;
438
439 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
443 bind_col
444 $rc = $sth->bind_col($column_number, \$var_to_bind, \%attr);
445
446 Supported by the driver as proposed by DBI.
447
448 bind_columns
449 $rc = $sth->bind_columns(\%attr, @list_of_refs_to_vars_to_bind);
450
451 Supported by the driver as proposed by DBI.
452
453 dump_results
454 $rows = $sth->dump_results($maxlen, $lsep, $fsep, $fh);
455
456 Implemented by DBI, no driver-specific impact.
457
458 Statement Handle Attributes
459 NUM_OF_FIELDS (integer, read-only)
460 Implemented by DBI, no driver-specific impact.
461
462 NUM_OF_PARAMS (integer, read-only)
463 Implemented by DBI, no driver-specific impact.
464
465 NAME (array-ref, read-only)
466 Supported by the driver as proposed by DBI.
467
468 NAME_lc (array-ref, read-only)
469 Implemented by DBI, no driver-specific impact.
470
471 NAME_uc (array-ref, read-only)
472 Implemented by DBI, no driver-specific impact.
473
474 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
479 PRECISION (array-ref, read-only)
480 Supported by the driver as proposed by DBI.
481
482 SCALE (array-ref, read-only)
483 Supported by the driver as proposed by DBI.
484
485 NULLABLE (array-ref, read-only)
486 Supported by the driver as proposed by DBI.
487
488 CursorName (string, read-only)
489 Supported by the driver as proposed by DBI.
490
491 Statement (string, read-only)
492 Supported by the driver as proposed by DBI.
493
494 RowCache (integer, read-only)
495 Not supported by the driver.
496
497 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
505 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
509 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
514 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
519 Upon a successful "connect()", these default parameter values will be
520 used for every SQL operation:
521
522 Access mode: read_write
523 Isolation level: snapshot
524 Lock resolution: wait
525
526 Any of the above value can be changed using "ib_set_tx_param()".
527
528 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
536 Valid value for "-access_mode" is "read_write", or "read_only".
537
538 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
542 $dbh->func(
543 -lock_resolution => { wait => 5 }, # wait for 5 seconds
544 'ib_set_tx_param'
545 );
546
547 "-isolation_level" may be: "read_committed", "snapshot",
548 "snapshot_table_stability".
549
550 If "read_committed" is to be used with "record_version" or
551 "no_record_version", then they should be inside an anonymous array:
552
553 $dbh->func(
554 -isolation_level => ['read_committed', 'record_version'],
555 'ib_set_tx_param'
556 );
557
558 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
563 The following example reserves "foo_table" with "read" lock and
564 "bar_table" with "read" lock and "protected" access:
565
566 $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
583 Possible table reservation parameters are:
584
585 "access" (optional)
586 Valid values are "shared" or "protected".
587
588 "lock" (required)
589 Valid values are "read" or "write".
590
591 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
596 "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
603 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
609 · At $dbh level
610
611 This replaces the default values. Example:
612
613 $dbh->{ib_timestampformat} = '%m-%d-%Y %H:%M';
614 $dbh->{ib_dateformat} = '%m-%d-%Y';
615 $dbh->{ib_timeformat} = '%H:%M';
616
617 · At $sth level
618
619 This overrides the default values only for the currently prepared
620 statement. Example:
621
622 $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
630 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
638 "$dbh->{ib_time_all}" can be used to specify all of the three formats
639 at once. Example:
640
641 $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
651 "ib_init_event"
652 $evh = $dbh->func(@event_names, 'ib_init_event');
653
654 Creates an event handle from a list of event names.
655
656 "ib_wait_event"
657 $dbh->func($evh, 'ib_wait_event');
658
659 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
663 "ib_register_callback"
664 my $cb = sub { my $posted_events = $_[0]; ++$::COUNT < 6 };
665 $dbh->func($evh, $cb, 'ib_register_callback');
666
667 sub inc_count { my $posted_events = shift; ++$::COUNT < 6 };
668 $dbh->func($evh, \&inc_count, 'ib_register_callback');
669
670 # or anonyomus subroutine
671 $dbh->func(
672 $evh,
673 sub { my ($pe) = @_; ++$::COUNT < 6 },
674 'ib_register_callback'
675 );
676
677 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
682 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
686 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
692 "ib_cancel_callback"
693 $dbh->func($evh, 'ib_cancel_callback');
694
695 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
705 Retrieve information about current active transaction.
706
707 "ib_database_info"
708 $hash_ref = $dbh->func(@info, 'ib_database_info');
709 $hash_ref = $dbh->func([@info], 'ib_database_info');
710
711 Retrieve database information from current connection.
712
713 "ib_plan"
714 $plan = $sth->func('ib_plan');
715
716 Retrieve query plan from a prepared SQL statement.
717
718 my $sth = $dbh->prepare('SELECT * FROM foo');
719 print $sth->func('ib_plan'); # PLAN (FOO NATURAL)
720
721 "ib_drop_database"
722 $result = $dbh->func('ib_drop_database');
723
724 Drops the database, associated with the connection. The database
725 handle is no longer valid after calling this function.
726
727 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
734 · SET TRANSACTION
735
736 Use "$dbh-"func(..., 'set_tx_param')> instead.
737
738 · DESCRIBE
739
740 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
745 · EXECUTE IMMEDIATE
746
747 Calling do() method without bind value(s) will do the same.
748
749 · CLOSE, OPEN, DECLARE CURSOR
750
751 $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
755 · PREPARE, EXECUTE, FETCH
756
757 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
766 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
775 The method croaks on error. Params may be:
776
777 db_path (string, required)
778 Path to database, including host name if necessary.
779
780 Examples:
781
782 server:/path/to/db.fdb
783 /srv/db/base.fdb
784 user (string, optional)
785 User name to be used for the request.
786
787 password (string, optional)
788 Password to be used for the request.
789
790 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
795 character_set (string, optional)
796 The default character set of the database. Firebird 2.5 defaults to
797 "NONE".
798
799 dialect (integer, optional)
800 The dialect of the database. Defaults to 3.
801
802 After creation, the new database can be used after connecting to it
803 with the usual DBI->connect(...)
804
805 DBD::Firebird->gfix( { params } )
806 A class method for simulating a subset of the functionality of the
807 Firebird's gfix(1) utility.
808
809 Params is a hash reference, with the following keys:
810
811 db_path (string, required)
812 The path to the database to connect to. Should include host name if
813 necessary.
814
815 user (string, optional)
816 User name to connect as. Must be SYSDBA or database owner.
817
818 password (string, optional)
819 Password to be used for the connection.
820
821 Note that user and password are not needed for embedded
822 connections.
823
824 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
828 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
838 $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
846 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
858 So the secret to make this work is to keep the transaction open. This
859 can be done in two ways:
860
861 · Using AutoCommit = 0
862
863 If yours is default to AutoCommit on, you can put the snippet
864 within a block:
865
866 {
867 $dbh->{AutoCommit} = 0;
868 # same actions like above ....
869 $dbh->commit;
870 }
871
872 · Using $dbh->{ib_softcommit} = 1
873
874 This is a driver-specific attribute,You may want to look at
875 t/70-nested-sth.t to see it in action.
876
877 Why do nested statement handles break under AutoCommit mode?
878 The same explanation as above applies. The workaround is also much
879 alike:
880
881 {
882 $dbh->{AutoCommit} = 0;
883 $sth1 = $dbh->prepare("SELECT * FROM $table");
884 $sth2 = $dbh->prepare("SELECT * FROM $table WHERE id = ?");
885 $sth1->execute;
886
887 while ($row = $sth1->fetchrow_arrayref) {
888 $sth2->execute($row->[0]);
889 $res = $sth2->fetchall_arrayref;
890 }
891 $dbh->commit;
892 }
893
894 You may also use $dbh->{ib_softcommit} please check t/70nested-sth.t
895 for an example on how to use it.
896
897 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
901 $sth = $dbh->prepare("SELECT (?) FROM $table");
902 $sth->execute('user_id');
903
904 There are cases where placeholders can't be used in conjunction with
905 COLLATE clause, such as this:
906
907 SELECT * FROM $table WHERE UPPER(author) LIKE UPPER(? COLLATE FR_CA);
908
909 This deals with the Firebird's SQL parser, not with "DBD::Firebird".
910 The driver just passes SQL statements through the engine.
911
912 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
918 $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
926 From Firebird 3.0 there is Identity support
927
928 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
934 Using Firebird 2.5.x this can be implemented by using "ROWS" .
935
936 http://www.firebirdsql.org/refdocs/langrefupd21-select.html#langrefupd21-select-rows
937
938 For example, to display a portion of table employee within your
939 application:
940
941 # fetch record 1 - 5:
942 $res = $dbh->selectall_arrayref("SELECT * FROM employee rows 1 to 5)");
943
944 # fetch record 6 - 10:
945 $res = $dbh->selectall_arrayref("SELECT * FROM employee rows 6 to 10)");
946
947 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
951 $attr = {
952 ib_timestampformat => '%m-%d-%Y %H:%M',
953 ib_dateformat => '%m-%d-%Y',
954 ib_timeformat => '%H:%M',
955 };
956
957 Then, pass it to prepare() method.
958
959 $sth = $dbh->prepare($stmt, $attr);
960 # followed by execute() and fetch(), or:
961
962 $res = $dbh->selectall_arrayref($stmt, $attr);
963
964 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
969 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
984 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
991 · DBD::Firebird by Edwin Pratomo <edpratomo@cpan.org>, Daniel Ritz
992 <daniel.ritz@gmx.ch> and many others. See "COPYRIGHT & LICENSE".
993
994 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
1001 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
1005 Under Linux, this module has been tested with several different
1006 iThreads enabled Perl releases.
1007
1008 No problem occurred so far.. until you try to share a DBI handle ;-)
1009
1010 But if you plan to use thread, you'd better use the latest stable
1011 version of Perl
1012
1013 On FreeBSD you need a Perl compiled with thread support.
1014
1015 Limitations:
1016
1017 · Arrays are not (yet) supported
1018
1019 · 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
1022 · 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
1036 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.
1045
1046
1047
1048perl v5.28.1 2017-12-05 Firebird(3)