Exception in PDO Firebird prepare of query in Magento Integration

Admin 1
Viewed 550

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

firebird prepared-statement magento pdo php
2 Answers

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.

user_3537140

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.

user_4668620
Related