Exception in PDO Firebird prepare of query in Magento Integration

Question

I'm working on project which is integrated with client's ERP* directly through php firebird pdo extension to get information as brands, categories, products, product prices and etc.

Database is not on the server where script runs. Connection to database works over a VPN connection if this matters.

First of all script asks Firebird for few queries in one of the system tables called SYS$ACTION_DS, Firebird returns this query:

execute block (
    MARK_AS_PROCESSED DM_BOOL = :MARK_AS_PROCESSED,
    EXTENAL_SYSTEM_ID DM_123 = :EXTENAL_SYSTEM_ID)
returns (
    ID bigint,
    BRAND_NAME$1 DM_STR30,
    BRAND_NAME$2 DM_STR30,
    BRAND_PARENT_REF bigint,
    OP varchar(1))
as
DECLARE VARIABLE SL_ID BIGINT;
begin
  FOR SELECT SL.ID, SL.OPERATION, SL.TABLE_ID_REF, N.BRAND_NAME$1, N.BRAND_NAME$2, N.BRAND_PARENT_REF
      FROM  SYS$EXT_LOG SL
      LEFT JOIN NOM$BRANDS N ON (SL.TABLE_ID_REF = N.ID)
      WHERE (SL.PROCESSED = 0) AND (SL.EXTERNAL_SYSTEM_ID = :EXTENAL_SYSTEM_ID) AND (SL.TABLE_NAME = 'NOM$BRANDS')
      ORDER BY SL.ID
      INTO :SL_ID, :OP, :ID, :BRAND_NAME$1, :BRAND_NAME$2, :BRAND_PARENT_REF
  do begin
    suspend;
    if (MARK_AS_PROCESSED = 1) then
      update SYS$EXT_LOG SL set SL.PROCESSED = 1, SL.PROCESSED_ON = current_timestamp where SL.ID = :SL_ID;
  end
end

After that script tries to prepare PDO Statement but prepare throws an exception: Dynamic SQL Error SQL error code = -104 Token unknown - line 18, column 12 ?

I can't find why I've got this error but everything works fine on live server but not on my machine. I've tried few php versions (5.6, 7.0, 7.1) on my machine and nothing works, on live server php version is 5.5 but I can't find any change between 5.5 and 5.6 related with PDO or Firebird PDO.

Additional information: Firebird Server Version: 2.5.5.26

Answer 1

Currently PDO Firebird driver do a simple primitive replacement of named parameters, by replacing :param with ?, as FB does not support named parameters. More info in PDO FB driver source

Unfortunately, this does not work for "execute block" statements. I hope, and do some efforts to have this fixed in the future. This was discussed in FB forum, to add named parameters support and also in PHP dev forums and tracker to improve the driver.

For now, as an workaround you may use a stored procedure.