NAME
FAQ - The Frequently Asked Questions for DBD::InterBase
SYNOPSIS
perldoc DBD::InterBase::FAQ
DESCRIPTION
This document serves to answer the most frequently asked questions regarding the uses of DBD::InterBase. Current version refers to DBD::InterBase version 0.41 available on SourceForge.
SQL Operations
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:
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; }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.
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 consult t/70nestedon.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(
"EXECUTE PROCEDURE paging_forum(5,5) RETURNING_VALUES :id, :title, :ctime,
:author");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 should be self-explanatory.
Uses of attributes
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. But it's true that for statements other than SELECT, it returns -1, because I don't know how to get the number of affected rows :-}
Sources for Help
I can't find the answer for my question here, where should I direct my question?
For questions regarding InterBase itself, you can join the InterBase mailing list at http://groups.yahoo.com/group/ib-support/, or if it is not enough, I believe there are some commercial supports available out there. http://www.ibphoenix.com/ is a good place to check.
For questions about DBD::InterBase, try to look for the answer on DBI man page, and DBI::FAQ. If your question is still unanswered, you can drop me message or you can post your question to the DBI users mailing list.
The Development Project
How can I join the development project?
The project is hosted at sourceforge.net. So send me your sourceforge username, and let me know what areas you are interested in.
SourceForge.net project page: http://sourceforge.net/projects/dbi-interbase/
Where can I get the latest release of DBD::InterBase ?
http://dbi.interbase.or.id/ (stable and development release), and http://www.cpan.org/modules/by-module/DBD/ (stable release only).
AUTHORS AND COPYRIGHT
Copyright (C) 2000-2003, Edwin Pratomo edpratomo@cpan.org. Daniel Ritz daniel.ritz@gmx.ch also writes necessary updates.
Michael Samanov samanov@yahoo.com contributed some important correction.