[Dbi-interbase-devel] FAQ errors

[Michael S. <mi...@ch...>]

Hello dbi-interbase-devel,

First, why it's named DBD::FAQ instead of DBD::InterBase::FAQ?
======================================================================
Q: How to get the latest value of an auto increment field?

A: $insert_id = $dbh->selectall_arrayref(
  "SELECT DISTINCT(GEN_ID($field_name, 0)) FROM $table_name")->[0]->[0];

No,  no,  no!!!  It  would be more convenient if we could do this, but
generators   work   outside  of  transaction  context,  so  the  other
transaction may change the generator's value just been set by previous
SQL  expression. The right answer is "SELECT field FROM table ORDER BY
field  DESC"  inside of the SAME (!!!) transaction or write the select
procedure like this (working example):

CREATE PROCEDURE ADD_REC
RETURNS (REC_NO INTEGER)
AS BEGIN
  SELECT GEN_ID(REC_GEN, 1) FROM RDB$DATABASE INTO :REC_NO;
  INSERT INTO TBL (REC_NO) VALUES (:REC_NO);
  SUSPEND;
  EXIT; /* redundant clause, but it never harms */
END  ^

or just

CREATE PROCEDURE NEXT_RECNO
RETURNS (REC_NO INTEGER)
AS BEGIN
  SELECT GEN_ID(REC_GEN, 1) FROM RDB$DATABASE INTO :REC_NO;
  SUSPEND;
END

and use this value manually

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

A:     This can be emulated by writing a stored procedure,
       although it's not as convenient to use as the LIMIT
       clause. Unfortunately, direct calls to stored procedure is
       not supported by the current version of DBD::InterBase.

What does it mean "calls are not supported"? Whether $dbh->do("EXECUTE
PROCEDURE proc(?,?,?)", $attr, @params) or
$dbh->selectrow_array("SELECT * FROM proc(?,?,?)", $attr, @params) do
not work? Nonsense! I just tested and it works.
========================================================================
Q:     Why do placeholders fail to bind, generating unknown
       datatype error message?

A:     You can't bind a field name. The following example will
       fail:

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

It's not always true. Example:
        
my $sth = $dbh->prepare("SELECT GOODS_NAME FROM GOODS WHERE UPPER(GOODS_NAME) like UPPER(?)")

works while

$sth = $dbh->prepare("SELECT GOODS_NAME FROM GOODS
  WHERE UPPER(GOODS_NAME COLLATE PXW_CYRL) like UPPER(? COLLATE PXW_CYRL)");

fails with the above error message. I don't know whether is it because
of  IB  parser  nature  or  because  of  nature of DBD::InterBase. The
parameter  meant to be some string variable like 'ps' or 'epson%1030',
never field name. BTW it works inside of the stored procedure.

Best regards,
 Michael                          mailto:mi...@ch...