NAME

DBD::InterBase - DBI driver for InterBase RDBMS server

SYNOPSIS

  use DBI;

  $dbh = DBI->connect("dbi:InterBase:db=$dbname", "sysdba", "masterkey");

  # See the DBI module documentation for full details

DESCRIPTION

DBD::InterBase is a Perl module which works with the DBI module to provide access to InterBase databases.

MODULE DOCUMENTATION

This documentation describes driver specific behavior and restrictions. It is not supposed to be used as the only reference for the user. In any case consult the DBI documentation first !

THE DBI CLASS

DBI Class Methods

connect

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

  $dbh = DBI->connect("dbi:InterBase:dbname=$dbname", "sysdba", "masterkey");

This connects to the database $dbname at localhost as SYSDBA user with the default password.

The following connect statement shows all possible parameters:

 $dsn =
 "dbi:InterBase:dbname=$dbname;host=$host;ib_dialect=$dialect;ib_role=$role;ib_charset=$charset;ib_cache=$cache";
 $dbh =  DBI->connect($dsn, $username, $password);

The $dsn is prefixed by 'dbi:InterBase:', and consists of key-value parameters separated by semicolons. The following is the list of valid parameters and their respective meanings:

    parameter   meaning                             optional?
    ---------------------------------------------------------
    database    path to the database                mandatory
    dbname      path to the database
    db          path to the database
    host        hostname (not IP address)           optional
    ib_dialect  the SQL dialect to be used          optional
    ib_role     the role of the user                optional
    ib_charset  character set to be used            optional
    ib_cache    number of database cache buffers    optional

database could be used interchangebly with dbname and db. If a host is specified, connection will be made using TCP/IP sockets. For example, to connect to a Windows host, the $dsn will look like this:

 $dsn = "dbi:InterBase:db=C:/temp/test.gdb;host=rae.cumi.org;ib_dialect=3";

InterBase 6.0 introduces SQL dialect to provide backward compatibility with databases created by older versions of InterBase. In short, SQL dialect controls how InterBase interprets:

 - double quotes
 - the DATE datatype
 - decimal and numeric datatypes
 - new 6.0 reserved keywords

Valid values for ib_dialect are 1, 2, and 3. The driver's default value is 1. For more detail reading on this topic, please refer to:

 http://www.interbase.com/open/research/art_60dialects.html

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

 http://www.interbase.com/downloads/sqlroles.pdf

If ib_cache is not specified, the default database's cache size value will be used. The InterBase Operations Guide discusses in full length the importance of this parameter to gain the best performance.

available_drivers

  @driver_names = DBI->available_drivers;

Implemented by DBI, no driver-specific impact.

data_sources

This method is not yet implemented.

trace

  DBI->trace($trace_level, $trace_file)

Implemented by DBI, no driver-specific impact.

DBI Dynamic Attributes

See Common Methods.

METHODS COMMON TO ALL HANDLES

err

  $rv = $h->err;

Supported by the driver as proposed by DBI.

errstr

  $str = $h->errstr;

Supported by the driver as proposed by DBI.

state

This method is not yet implemented.

trace

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

Implemented by DBI, no driver-specific impact.

trace_msg

  $h->trace_msg($message_text);

Implemented by DBI, no driver-specific impact.

func

See Transactions section for information about invoking set_tx_param() from func() method.

ATTRIBUTES COMMON TO ALL HANDLES

Warn (boolean, inherited)

Implemented by DBI, no driver-specific impact.

Active (boolean, read-only)

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

Kids (integer, read-only)

Implemented by DBI, no driver-specific impact.

ActiveKids (integer, read-only)

Implemented by DBI, no driver-specific impact.

CachedKids (hash ref)

Implemented by DBI, no driver-specific impact.

CompatMode (boolean, inherited)

Not used by this driver.

InactiveDestroy (boolean)

Implemented by DBI, no driver-specific impact.

PrintError (boolean, inherited)

Implemented by DBI, no driver-specific impact.

RaiseError (boolean, inherited)

Implemented by DBI, no driver-specific impact.

ChopBlanks (boolean, inherited)

Supported by the driver as proposed by DBI.

LongReadLen (integer, inherited)

Supported by the driver as proposed by DBI.The default value is 80 bytes.

LongTruncOk (boolean, inherited)

Supported by the driver as proposed by DBI.

Taint (boolean, inherited)

Implemented by DBI, no driver-specific impact.

DATABASE HANDLE OBJECTS

Database Handle Methods

selectrow_array

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

Implemented by DBI, no driver-specific impact.

selectall_arrayref

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

Implemented by DBI, no driver-specific impact.

selectcol_arrayref

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

Implemented by DBI, no driver-specific impact.

prepare

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

Supported by the driver as proposed by DBI. When AutoCommit is On, this method implicitly starts a new transaction, which will be automatically committed after the following execute() or the last fetch(), depending on the statement type. For select statements, commit automatically takes place after the last fetch(), or by explicitly calling finish() method if there are any rows remaining. For non-select statements, execute() will implicitly commits the transaction.

prepare_cached

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

Implemented by DBI, no driver-specific impact.

do

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

Supported by the driver as proposed by DBI. This should be used for non-select statements, where the driver doesn't take the conservative prepare - execute steps, thereby speeding up the execution time. But if this method is used with bind values, the speed advantage diminishes as this method calls prepare() for binding the placeholders. Instead of calling this method repeatedly with bind values, it would be better to call prepare() once, and execute() many times.

See the notes for the execute method elsewhere in this document.

commit

  $rc  = $dbh->commit;

Supported by the driver as proposed by DBI. See also the notes about Transactions elsewhere in this document.

rollback

  $rc  = $dbh->rollback;

Supported by the driver as proposed by DBI. See also the notes about Transactions elsewhere in this document.

disconnect

  $rc  = $dbh->disconnect;

Supported by the driver as proposed by DBI.

ping

  $rc = $dbh->ping;

This driver supports the ping-method, which can be used to check the validity of a database-handle. This is especially required by Apache::DBI.

table_info

  $sth = $dbh->table_info;

Supported by the driver as proposed by DBI.

tables

  @names = $dbh->tables;

Supported by the driver as proposed by DBI.

type_info_all

  $type_info_all = $dbh->type_info_all;

Supported by the driver as proposed by DBI.

For further details concerning the InterBase specific data-types please read the "InterBase Data Definition Guide".

type_info

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

Implemented by DBI, no driver-specific impact.

quote

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

Implemented by DBI, no driver-specific impact.

Database Handle Attributes

AutoCommit (boolean)

Supported by the driver as proposed by DBI. According to the classification of DBI, InterBase is a database, in which a transaction must be explicitly started. Without starting a transaction, every change to the database becomes immediately permanent. The default of AutoCommit is on, which corresponds to the DBI's default. When setting AutoCommit to off, a transaction will be started and every commit or rollback will automatically start a new transaction. For details see the notes about Transactions elsewhere in this document.

Driver (handle)

Implemented by DBI, no driver-specific impact.

Name (string, read-only)

Not yet implemented.

RowCacheSize (integer)

Implemented by DBI, not used by the driver.

STATEMENT HANDLE OBJECTS

Statement Handle Methods

bind_param

Supported by the driver as proposed by DBI. The SQL data type passed as the third argument is ignored.

bind_param_inout

Not supported by this driver.

execute

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

Supported by the driver as proposed by DBI. On success, this method returns -1 instead of the number of affected rows.

fetchrow_arrayref

  $ary_ref = $sth->fetchrow_arrayref;

Supported by the driver as proposed by DBI.

fetchrow_array

  @ary = $sth->fetchrow_array;

Supported by the driver as proposed by DBI.

fetchrow_hashref

  $hash_ref = $sth->fetchrow_hashref;

Supported by the driver as proposed by DBI.

fetchall_arrayref

  $tbl_ary_ref = $sth->fetchall_arrayref;

Implemented by DBI, no driver-specific impact.

finish

  $rc = $sth->finish;

Supported by the driver as proposed by DBI.

rows

  $rv = $sth->rows;

Supported by the driver as proposed by DBI. It returns the number of fetched rows for select statements, otherwise it returns -1 (unknown number of affected rows).

bind_col

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

Supported by the driver as proposed by DBI.

bind_columns

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

Supported by the driver as proposed by DBI.

dump_results

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

Implemented by DBI, no driver-specific impact.

Statement Handle Attributes

NUM_OF_FIELDS (integer, read-only)

Implemented by DBI, no driver-specific impact.

NUM_OF_PARAMS (integer, read-only)

Implemented by DBI, no driver-specific impact.

NAME (array-ref, read-only)

Supported by the driver as proposed by DBI.

NAME_lc (array-ref, read-only)

Implemented by DBI, no driver-specific impact.

NAME_uc (array-ref, read-only)

Implemented by DBI, no driver-specific impact.

TYPE (array-ref, read-only)

Supported by the driver as proposed by DBI, with the restriction, that the types are InterBase specific data-types which do not correspond to international standards.

PRECISION (array-ref, read-only)

Supported by the driver as proposed by DBI.

SCALE (array-ref, read-only)

Supported by the driver as proposed by DBI.

NULLABLE (array-ref, read-only)

Supported by the driver as proposed by DBI.

CursorName (string, read-only)

Supported by the driver as proposed by DBI.

Statement (string, read-only)

Supported by the driver as proposed by DBI.

RowCache (integer, read-only)

Not supported by the driver.

FURTHER INFORMATION

Transactions

The transaction behavior is controlled with the attribute AutoCommit. For a complete definition of AutoCommit please refer to the DBI documentation.

According to the DBI specification the default for AutoCommit is TRUE. In this mode, any change to the database becomes valid immediately. Any commit() or rollback() will be rejected.

If AutoCommit is switched-off, immediately a transaction will be started. A rollback() will rollback and close the active transaction, then implicitly start a new transaction. A disconnect will issue a rollback.

InterBase provides fine control over transaction behavior, where users can specify the access mode, the isolation level, the lock resolution, and the table reservation (for a specified table). For this purpose, set_tx_param() database handle method is available. Please notice that this private method is new and considered experimental at this release. Use with caution!

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

    Access mode:        read/write
    Isolation level:    concurrency
    Lock resolution:    wait

Any of the above value can be changed using set_tx_param().

set_tx_param

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

Valid value for -access_mode is read_write, or read_only. Valid value for -lock_resolution is wait, or no_wait. -isolation_level may be: read_committed, snapshot, snapshot_table_stability. If read_committed is to be used with record_version or no_record_version, then they should be inside an anonymous array:

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

Currently, table reservation is not yet supported.

Unsupported SQL Statements

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

• SET TRANSACTION

  Use $dbh-func(..., 'set_tx_param')> instead.

• DESCRIBE

  Provides information about columns that are retrieved by a DSQL statement, or about placeholders in a statement. This functionality is supported by the driver, and transparent for users. Column names are available via $sth->{NAME} attributes.

• EXECUTE IMMEDIATE

  Calling do() method without bind value(s) will do the same.

• CLOSE, OPEN, DECLARE CURSOR

  $sth->{CursorName} is automagically available upon executing a "SELECT .. FOR UPDATE" statement. A cursor is closed after the last fetch(), or by calling $sth->finish().

• PREPARE, EXECUTE, FETCH

  Similar functionalities are obtained by using prepare(), execute(), and fetch() methods.

Compatibility with DBI Extension modules

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

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

TESTED PLATFORMS

Client

Linux, glibc-2.1.2, x86 egcs-1.1.2, kernel 2.2.12-20.

FreeBSD

SPARC Solaris

Win32

Server

InterBase 6.0 SS and Classic for Linux

InterBase 6.0 for Windows, FreeBSD, SPARC Solaris

AUTHOR

• DBI by Tim Bunce <Tim.Bunce@ig.co.uk>

• DBD::InterBase by Edwin Pratomo <ed.pratomo@computer.org>

  Partially based on the work of Bill Karwin's IBPerl, Jochen Wiedmann's DBD::mysql, and Edmund Mergl's DBD::Pg.

  Daniel Ritz <daniel_ritz@gmx.ch> works on SPARC Solaris port.

BUGS

Known problem with BLOB fields on Win32 platform.

SEE ALSO

DBI(3).

COPYRIGHT

The DBD::InterBase module is a free software. You may distribute under the terms of either the GNU General Public License or the Artistic License, as specified in the Perl README file, with the exception that it cannot be placed on a CD-ROM or similar media for commercial distribution without the prior approval of the author.

ACKNOWLEDGEMENTS

Mark D. Anderson <mda@discerning.com>, and Michael Samanov <samanov@yahoo.com> gave important feedbacks and ideas during the early development days of this XS version.

Michael Arnett <marnett@mediaone.net>, Flemming Frandsen <dion@swamp.dk>, Mike Shoyher <msh@e-labs.ru>, Christiaan Lademann <cal@zls.de> sent me patches for the first version.

cpanm DBD::InterBase

perl -MCPAN -e shell
install DBD::InterBase