Description
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.