Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages

  
 
  

home | help
InterBase(3)	      User Contributed Perl Documentation	  InterBase(3)

NAME
       DBD::InterBase -	DBI driver for Firebird	and 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 Firebird and 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.

	   Multiline DSN is acceptable.	Here is	an example of connect
	   statement which uses	all possible parameters:

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

	    $dbh =  DBI->connect($dsn, $username, $password);

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

	       parameter       meaning				       optional?
	       -----------------------------------------------------------------
	       database	       path to the database		       required
	       dbname	       path to the database
	       db	       path to the database
	       hostname	       hostname	/ IP address		       optional
	       host	       hostname	/ IP address
	       port	       port number			       optional
	       ib_dialect      the SQL dialect to be used	       optional
	       ib_role	       the role	of the user		       optional
	       ib_charset      character set to	be used		       optional
	       ib_cache	       number of database cache	buffers	       optional
	       ib_dbkey_scope  change default duration of RDB$DB_KEY   optional

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

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

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

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

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

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

	   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.

	   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.ibphoenix.com/ibp_sqlroles.html

	   If ib_cache is not specified, the default database's	cache size
	   value will be used. The InterBase Operation 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 DBI HANDLES
       err
	     $rv = $h->err;

	   Supported by	the driver as proposed by DBI.

       errstr
	     $str = $h->errstr;

	   Supported by	the driver as proposed by DBI.

       state
	   This	method is not yet implemented.

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

	   Implemented by DBI, no driver-specific impact.

       trace_msg
	     $h->trace_msg($message_text);

	   Implemented by DBI, no driver-specific impact.

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

ATTRIBUTES COMMON TO ALL DBI HANDLES
       Warn (boolean, inherited)
	   Implemented by DBI, no driver-specific impact.

       Active (boolean,	read-only)
	   Supported by	the driver as proposed by DBI. A database handle is
	   active while	it is connected	and  statement handle is active	until
	   it is finished.

       Kids (integer, read-only)
	   Implemented by DBI, no driver-specific impact.

       ActiveKids (integer, read-only)
	   Implemented by DBI, no driver-specific impact.

       CachedKids (hash	ref)
	   Implemented by DBI, no driver-specific impact.

       CompatMode (boolean, inherited)
	   Not used by this driver.

       InactiveDestroy (boolean)
	   Implemented by DBI, no driver-specific impact.

       PrintError (boolean, inherited)
	   Implemented by DBI, no driver-specific impact.

       RaiseError (boolean, inherited)
	   Implemented by DBI, no driver-specific impact.

       ChopBlanks (boolean, inherited)
	   Supported by	the driver as proposed by DBI.

       LongReadLen (integer, inherited)
	   Supported by	the driver as proposed by DBI.The default value	is 80
	   bytes.

       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.
	   Unlike the execute method, currently	this method doesn't return the
	   number of affected rows.

       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.

       ib_softcommit  (driver-specific,	boolean)
	   Set this attribute to TRUE to use InterBase's soft commit feature
	   (default to FALSE). Soft commit retains the internal	transaction
	   handle when committing a transaction, while the default commit
	   behavior always closes and invalidates the transaction handle.

	   Since the transaction handle	is still open, there is	no need	to
	   start a new transaction upon	every commit, so applications can gain
	   performance improvement. Using soft commit is also desirable	when
	   dealing with	nested statement handles under AutoCommit on.

	   Switching the attribute's value from	TRUE to	FALSE will force hard
	   commit thus closing the current transaction.

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.

       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.

TRANSACTION SUPPORT
       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,
       "ib_set_tx_param()" database handle method is available.

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

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

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

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

	   Valid value for "-access_mode" is "read_write", or "read_only".

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

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

	   "-isolation_level" may be: "read_committed",	"snapshot",
	   "snapshot_table_stability".

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

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

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

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

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

	   Possible table reservation parameters are:

	   "access" (optional)
	       Valid values are	"shared" or "protected".

	   "lock" (required)
	       Valid values are	"read" or "write".

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

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

DATE, TIME, and	TIMESTAMP FORMATTING SUPPORT
       "DBD::InterBase"	supports various formats for query results of DATE,
       TIME, and TIMESTAMP types.

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

       o   At $dbh level

	   This	replaces the default values. Example:

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

       o   At $sth level

	   This	overrides the default values only for the currently prepared
	   statement. Example:

	    $attr = {
	       ib_timestampformat => '%m-%d-%Y %H:%M',
	       ib_dateformat =>	'%m-%d-%Y',
	       ib_timeformat =>	'%H:%M',
	    };
	    # then, pass it to prepare() method.
	    $sth = $dbh->prepare($sql, $attr);

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

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

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

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

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

	   Creates an event handle from	a list of event	names.

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

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

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

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

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

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

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

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

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

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

RETRIEVING FIREBIRD / INTERBASE	SPECIFIC INFORMATION
       "ib_tx_info"
	    $hash_ref =	$dbh->func('ib_tx_info');

	   Retrieve information	about current active transaction.

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

	   Retrieve database information from current connection.

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

	   Retrieve query plan from a prepared SQL statement.

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

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.

       o   SET TRANSACTION

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

       o   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.

       o   EXECUTE IMMEDIATE

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

       o   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().

       o   PREPARE, EXECUTE, FETCH

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

COMPATIBILITY WITH DBIx::* 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.

FAQ
   Why do some operations performing positioned	update and delete fail when
       AutoCommit is on?
       For example, the	following code snippet fails:

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

       When AutoCommit is on, a	transaction is started within prepare(), and
       committed automatically after the last fetch(), or within finish().
       Within do(), a transaction is started right before the statement	is
       executed, and gets committed right after	the statement is executed. The
       transaction handle is stored within the database	handle.	The driver is
       smart enough not	to override an active transaction handle with a	new
       one. So,	if you notice the snippet above, after the first
       fetchrow_array(), the do() is still using the same transaction context,
       but as soon as it has finished executing	the statement, it commits the
       transaction, whereas the	next fetchrow_array() still needs the
       transaction context!

       So the secret to	make this work is to keep the transaction open.	This
       can be done in two ways:

       o   Using AutoCommit = 0

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

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

       o   Using $dbh->{ib_softcommit} = 1

	   This	driver-specific	attribute is available as of version 0.30. You
	   may want to look at t/40cursoron.t to see it	in action.

   Why do nested statement handles break under AutoCommit mode?
       The same	explanation as above applies. The workaround is	also much
       alike:

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

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

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

   Why do placeholders fail to bind, generating	unknown	datatype error
       message?
       You can't bind a	field name. The	following example will fail:

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

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

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

       This deals with the InterBase's SQL parser, not with "DBD::InterBase".
       The driver just passes SQL statements through the engine.

   How to do automatic increment for a specific	field?
       Create a	generator and a	trigger	to associate it	with the field.	The
       following example creates a generator named PROD_ID_GEN,	and a trigger
       for table ORDERS	which uses the generator to perform auto increment on
       field PRODUCE_ID	with increment size of 1.

	$dbh->do("CREATE GENERATOR PROD_ID_GEN");
	$dbh->do(
	"CREATE	TRIGGER	INC_PROD_ID FOR	ORDERS
	BEFORE INSERT POSITION 0
	AS BEGIN
	  NEW.PRODUCE_ID = GEN_ID(PROD_ID_GEN, 1);
	END");

   How can I perform LIMIT clause as I usually do in MySQL?
       "LIMIT" clause let users	to fetch only a	portion	rather than the	whole
       records as the result of	a query. This is particularly efficient	and
       useful for paging feature on web	pages, where users can navigate	back
       and forth between pages.

       Using InterBase (Firebird is explained later), this can be emulated by
       writing a stored	procedure. For example,	to display a portion of
       table_forum, first create the following procedure:

	CREATE PROCEDURE PAGING_FORUM (start INTEGER, num INTEGER)
	RETURNS	(id INTEGER, title VARCHAR(255), ctime DATE, author VARCHAR(255))
	AS
	DECLARE	VARIABLE counter INTEGER;
	BEGIN
	  counter = 0;
	  FOR SELECT id, title,	ctime, author FROM table_forum ORDER BY	ctime
	     INTO :id, :title, :ctime, :author
	  DO
	  BEGIN
	     IF	(counter = :start + :num) THEN EXIT;
	     ELSE
		IF (counter >= :start) THEN SUSPEND;
	     counter = counter + 1;
	  END
	END !!
	SET TERM ; !!

       And within your application:

	# fetch	record 1 - 5:
	$res = $dbh->selectall_arrayref("SELECT	* FROM paging_forum(0,5)");

	# fetch	record 6 - 10:
	$res = $dbh->selectall_arrayref("SELECT	* FROM paging_forum(5,5)");

       But never expect	this to	work:

	$sth = $dbh->prepare(<<'SQL');
	EXECUTE	PROCEDURE paging_forum(5,5)
	RETURNING_VALUES :id, :title, :ctime, :author
	SQL

       With Firebird 1 RCx and later, you can use "SELECT FIRST":

	SELECT FIRST 10	SKIP 30	* FROM table_forum;

       "FIRST x" and "SKIP x" are both optional. "FIRST" limits	the number of
       rows to return, "SKIP" ignores (skips) the first	x rows in resultset.

   How can I use the date/time formatting attributes?
       Those attributes	take the same format as	the C function strftime()'s.
       Examples:

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

       Then, pass it to	prepare() method.

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

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

   Can I set the date/time formatting attributes between prepare and fetch?
       No. "ib_dateformat", "ib_timeformat", and "ib_timestampformat" can only
       be set during $sth->prepare. If this is a problem to you, let me	know,
       and probably I'll add this capability for the next release.

   Can I change	ib_dialect after DBI->connect ?
       No. If this is a	problem	to you,	let me know, and probably I'll add
       this capability for the next release.

   Why do execute(), do() method and rows() method always return -1 upon a
       successful operation?
       Incorrect question. $sth->rows returns the number of fetched rows after
       a successful SELECT. Starting from version 0.43,	execute() method
       returns the number of affected rows. But	it's true that do() method
       returns -1, this	will change in future release.

OBSOLETE FEATURES
       Private Method
	   "set_tx_param()" is obsoleted by "ib_set_tx_param()".

TESTED PLATFORMS
   Clients
       Linux
       FreeBSD
       SPARC Solaris
       Win32

   Servers
       InterBase 6.0/6.01 SS and Classic for Linux
       InterBase 6.0/6.01 for Windows, FreeBSD,	SPARC Solaris
       FirebirdSS 1.0 Final for	Windows, Linux,	SPARC Solaris
       FirebirdSS 1.5.2.4731 for Windows, Linux
       FirebirdSS 2.0 RC4 for Linux. The AMD64 (64-bit)	version	is also
       tested. Should also work	with Intel EM64T.

AUTHORS
       o   DBI by Tim Bunce <Tim.Bunce@pobox.com>

       o   DBD::InterBase by Edwin Pratomo <edpratomo@cpan.org>	and Daniel
	   Ritz	<daniel.ritz@gmx.ch>.

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

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

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

       Under Linux, this module	has been tested	with several different
       iThreads	enabled	Perl releases: perl-5.8.0-88 from RedHat 9,
       perl-5.8.5-9 from Fedora	Core 3,	perl-5.8.6-15 from Fedora Core 4, and
       Perl 5.8.[78].

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

       But if you plan to use thread, you'd better use the latest stable
       version of Perl,	5.8.8 has fairly stable	iThreads.

       Limitations:

       o   Arrays are not (yet)	supported

       o   Read/Write BLOB fields block	by block not (yet) supported. The
	   maximum size	of a BLOB read/write is	hardcoded to about 1 MB.

       o   service manager API is not supported.

SEE ALSO
       DBI(3).

COPYRIGHT
       The DBD::InterBase module is Copyright (c) 1999-2008 Edwin Pratomo.
       Portions	Copyright (c) 2001-2005	Daniel Ritz.

       The DBD::InterBase module is free software.  You	may distribute under
       the terms of either the GNU General Public License or the Artistic
       License,	as specified in	the Perl README	file, 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
       An attempt to enumerate all who have contributed	patches	(may misses
       some): Michael Moehle, Igor Klingen, Sergey Skvortsov, Ilya Verlinsky,
       Pavel Zheltouhov, Peter Wilkinson, Mark D. Anderson, Michael Samanov,
       Michael Arnett, Flemming	Frandsen, Mike Shoyher,	Christiaan Lademann.

perl v5.32.0			  2008-01-08			  InterBase(3)

NAME | SYNOPSIS | DESCRIPTION | MODULE DOCUMENTATION | THE DBI CLASS | METHODS COMMON TO ALL DBI HANDLES | ATTRIBUTES COMMON TO ALL DBI HANDLES | DATABASE HANDLE OBJECTS | STATEMENT HANDLE OBJECTS | TRANSACTION SUPPORT | DATE, TIME, and TIMESTAMP FORMATTING SUPPORT | EVENT ALERT SUPPORT | RETRIEVING FIREBIRD / INTERBASE SPECIFIC INFORMATION | UNSUPPORTED SQL STATEMENTS | COMPATIBILITY WITH DBIx::* MODULES | FAQ | OBSOLETE FEATURES | TESTED PLATFORMS | AUTHORS | BUGS/LIMITATIONS | SEE ALSO | COPYRIGHT | ACKNOWLEDGEMENTS

Want to link to this manual page? Use this URL:
<https://www.freebsd.org/cgi/man.cgi?query=DBD::InterBase&sektion=3&manpath=FreeBSD+12.2-RELEASE+and+Ports>

home | help