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
2
Answers
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
Would raise
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:Note the
?
in theinto
-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),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.
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.