Invalid character value for cast specification: 0

Question

I would like to load some entities from the repository, but I get this error:

SQLSTATE[22018]: Invalid character value for cast specification: 0 [Microsoft][ODBC Driver 17 for SQL Server]Invalid character value for cast specification (SQLExecute[0] at /builddir/build/BUILD/php-7.3.20/ext/pdo_odbc/odbc_stmt.c:259) in file /srv/www/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php on line 185

Doctrine tries to execute this query:

SELECT t0.id as id_1, t0.name as name_2 FROM users t0 WHERE t0.is_deleted = ? ORDER BY t0.name ASC

I am able to execute the same query in SQL Server Management Studio, but for some unknown reason, it gives this error in doctrine.

The entity is defined well, because the column and table names are correct, and this query should work. The collation of the database is not unicode, but other tables have no issue with it.

I tried to find a solution, but I was only able to find someone else encountering a similar error when processing a csv file, but it is not relevant for me. My data is already in the database, I just try to read it.

Are You sure that erros comes from that query ? Did You check profiler ? Also how are You fetching this data from repository ? — michal, Apr 08 '21 at 10:31
Please could you try running the following in SSMS and check the output to find out if there are any NUL ('\0') characters in the data: `SELECT id, REPLACE(name, CHAR(0), '****** NUL FOUND!!! ******') FROM users` — Steve Chambers, May 13 '21 at 13:42
What happens when you remove `ORDER BY t0.name ASC` from the query, does Doctrine executes it successfully or still same error? — Haridarshan, May 13 '21 at 17:35
'The collation of the database is not unicode' -- What is the collation set to? This would be invoked in your Order By clause. If NULLs are the problem, you can try : `ORDER BY COALESCE(t0.name,'zzz') ASC` — Matt E., May 17 '21 at 14:19

score 1 · Answer 1 · answered May 18 '21 at 11:13

The ODBC SqlSrv driver throws this exeption, if PDOStatement::bindValue is called with PDO::PARAM_BOOL. To solve this, it has to be called with PDO::PARAM_INT.

This can be achieved, by overriding the default PDOStatement class, and then replacing PDO::PARAM_BOOL with PDO::PARAM_INT:

class MyPDOStatement extends \PDOStatement {

    public function bindValue($param, $value, $type = PDO::PARAM_STR) {

        if($type == PDO::PARAM_BOOL) {
            $type = PDO::PARAM_INT;
        }

        if(is_null($value)) {
            $type = PDO::PARAM_NULL;
        }

        if($type == PDO::PARAM_INT) {
            $value = intval($value);
        }

        return parent::bindValue($param, $value, $type);
        
    }
}

$pdo->setAttribute(PDO::ATTR_STATEMENT_CLASS, [MyPDOStatement::class]);

Invalid character value for cast specification: 0

1 Answers1