AR / PDO binding issue

[ldkafka]

I have an application using MySQL spatial data types and I need to compare if a point is within an area, basically I have to use a MySQL function with a hexadecimal number as a string comparison.

// DATA stored as a MySQL blob WKB (ie. comes from DB into AR). Below sample just re-creates the $mbr data for testing purposes. $mbr = hex2bin('000000000102000000020000000e260f5542b52740d7e3198fad514b403250be45ccf52740d7e3198fad514b40');

// $mbr needs to be used within ST_Within function as a hexadecimal number WKB. // PDO binding converts :mbr to a quoted ('') varchar, which is not a suitable argument type for ST_Within. // I add an 'X' before :mbr to comply with the PHP hexadecimal string notation.

return Property::find() ->where(['ST_Within(gis_point, X:mbr)' => true]) ->addParams([':mbr' => bin2hex($mbr)]) ->count();

// as soon as the string becomes "X:mbr", the binding doesn't work: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens The SQL being executed was: SELECT COUNT(*) FROM property WHERE ST_Within(gis_point, X'000000000102000000020000000e260f5542b52740d7e3198fad514b403250be45ccf52740d7e3198fad514b40')=TRUE

// if the string is just ":mbr", the binding works, but then the data type is wrong - as expected

SQLSTATE[HY000]: General error: 4079 Illegal parameter data type varchar for operation 'st_within' The SQL being executed was: SELECT COUNT(*) FROM property WHERE ST_Within(gis_point, '000000000102000000020000000e260f5542b52740d7e3198fad514b403250be45ccf52740d7e3198fad514b40')=TRUE

// Basically PDO binding gets confused if there is a character pre-pended before the colon ":" in the name of a binding param. It needs to be nothing, or a space for the binding to work.

Is this a Yii parsing issue or a PDO issue?

Thanks.

[samdark]

Is this a Yii parsing issue or a PDO issue?

The only way to find out is to try doing the query via PDO.