1Firebird(3)           User Contributed Perl Documentation          Firebird(3)
2
3
4

NAME

6       DBD::Firebird - DBI driver for Firebird RDBMS server
7

SYNOPSIS

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

DESCRIPTION

16       DBD::Firebird is a Perl module which works with the DBI module to
17       provide access to Firebird databases.
18

MODULE DOCUMENTATION

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

THE DBI CLASS

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

METHODS COMMON TO ALL DBI HANDLES

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

ATTRIBUTES COMMON TO ALL DBI HANDLES

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

DATABASE HANDLE OBJECTS

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

STATEMENT HANDLE OBJECTS

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

TRANSACTION SUPPORT

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

DATE, TIME, and TIMESTAMP FORMATTING SUPPORT

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

EVENT ALERT SUPPORT

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

RETRIEVING FIREBIRD / INTERBASE SPECIFIC INFORMATION

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

UNSUPPORTED SQL STATEMENTS

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

COMPATIBILITY WITH DBIx::* MODULES

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

SERVICE METHODS

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

FAQ

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

OBSOLETE FEATURES

974       Private Method
975           "set_tx_param()" is obsoleted by "ib_set_tx_param()".
976

TESTED PLATFORMS

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

AUTHORS

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

BUGS/LIMITATIONS

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

SEE ALSO

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

ACKNOWLEDGEMENTS

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.32.0                      2020-07-28                       Firebird(3)
Impressum