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


Show source
| php   | pdo   | magento   | prepared-statement   | firebird   2017-11-11 23:11 2 Answers

Answers to Exception in PDO Firebird prepare of query in Magento Integration ( 2 )

  1. 2017-11-12 10:11

    The problem is that Firebird doesn't have named parameter outside PSQL blocks, and PDO Firebird seems to provide a translation to simulate support for this.

    For example, raw execution of

    execute block (
        MARK_AS_PROCESSED DM_BOOL = :MARK_AS_PROCESSED,
        EXTENAL_SYSTEM_ID DM_123 = :EXTENAL_SYSTEM_ID)
    .. etc..
    

    Would raise

    SQL Message : -104
    Invalid token
    
    Engine Code    : 335544569
    Engine Message :
    Dynamic SQL Error
    SQL error code = -104
    Token unknown - line 2, column 33
    :
    

    The PDO Firebird library will replace the parameter names with a ?, and given the error, and named parameter replacement, what gets executed is something like:

    execute block (
        MARK_AS_PROCESSED DM_BOOL = ?,
        EXTENAL_SYSTEM_ID DM_123 = ?)
    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 = ?) AND (SL.TABLE_NAME = 'NOM$BRANDS')
          ORDER BY SL.ID
          INTO ?, ?, ?, ?, ?, ?
      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 = ?;
      end
    end
    

    Note the ? in the into-clause. This is what triggers the exception, because a ? is not valid here.

    I don't actually know PDO Firebird, but it looks like it will replace all occurrences of :<variablename> with a ?. As PDO Firebird seems to do wholesale replacement of everything that looks like :<variable>, then you could try using positional parameters (and hope it doesn't still replace every :<variable> with a ? as well),

    execute block (
        MARK_AS_PROCESSED DM_BOOL = ?,
        EXTENAL_SYSTEM_ID DM_123 = ?)
    .. etc..
    

    or you will need to create an actual stored procedure (and not use PDO Firebird to create it), and execute that from PDO Firebird.

    If this worked in early version of PHP, then you seem to have hit a regression, and I suggest you report a regression bug with the PDO project.

  2. 2017-11-12 11:11

    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.

Leave a reply to - Exception in PDO Firebird prepare of query in Magento Integration

◀ Go back