view release on metacpan or  search on metacpan

InterBase.pm  view on Meta::CPAN

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. 


=back

=head1 RETRIEVING FIREBIRD / INTERBASE SPECIFIC INFORMATION

=over

=item C<ib_tx_info>

 $hash_ref = $dbh->func('ib_tx_info');

Retrieve information about current active transaction.

=item C<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. 

=item C<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)

=back


=head1 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.

=over 4

=item * SET TRANSACTION

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

=item * 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.

=item * EXECUTE IMMEDIATE

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

=item * 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(). 

=item * PREPARE, EXECUTE, FETCH

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

=back

=head1 COMPATIBILITY WITH DBIx::* MODULES 

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

The driver is untested with C<Apache::Session::DBI>. Doesn't work with 
C<Tie::DBI>. C<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.

=head1 FAQ

=head2 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 B<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
B<commits> the transaction, whereas the next fetchrow_array() still needs
the transaction context!

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

=over 4

=item * 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;
 }

=item * 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.

=back

=head2 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.

=head2 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 C<DBD::InterBase>. The
driver just passes SQL statements through the engine.


=head2 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");


=head2 How can I perform LIMIT clause as I usually do in MySQL?

C<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