DOCTRINE PDO postgresql bindValue() with null -> SQLSTATE[22P02]

Question

I am banging my head on the wall with PDO PostgreSQL and PHP (+ some DOCTRINE and SYMFONY - but I don't use ORM)

At some point I have a null value that I want to use in the bindParam() function.

It goes like:

$sqlReadyToBindValue->bindValue(':an_integer_field',null,PDO::PARAM_INT);

And I ve tried also with:

$sqlReadyToBindValue->bindValue(':an_integer_field',null,PDO::PARAM_NULL);

In both cases it raises an exception:

SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: """ at [SYMFONY FOLDER]\vendor\doctrine\dbal\lib\Doctrine\DBAL\Driver\AbstractPostgreSQLDriver.php line 91

I don't get it because any other thing I do with the bindValue() function goes thru.

I notice reading the log that my null value becomes two simple quotes when going thru DOCTRINE, something like:

at DBALException ::driverExceptionDuringQuery (object(Driver), object(PDOException), 'SELECT an_integer_field FROM a_table WHERE an_integer_field = :an_integer_field', array(':an_integer_field' => '')) in vendor\doctrine\dbal\lib\Doctrine\DBAL\Statement.php at line 17

and then: at AbstractPostgreSQLDriver ->convertException ('An exception occurred while executing 'SELECT an_integer_field FROM a_table WHERE an_integer_field = :an_integer_field ' with params [""]: SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: ""', object(PDOException)) in vendor\doctrine\dbal\lib\Doctrine\DBAL\DBALException.php at line 116

Then it raises the first Exception, I've wrote on earlier: AbstractPostgreSQLDriver.php line 91 (the one that splashes on the screen)

Any clue? Is there maybe a way to make sure null doesn't become quotes in DOCTRINE?

Nulls are not integers so I don't see this can work. sql uses the IS NULL operator to check for null values. — Cerad
@Cerad, yes it does make sense:) Also since the field, being an integer, accept the value null, it lead me to think that binding null could work in a WHERE statement. I found a work around, that I'll post as an anwer. — nyluje

nyluje nyluje · Accepted Answer · 2016-05-13T23:07:57

I did a work around by using the 'IS null' check in the SQL query instead of binding a parameter to it.

DOCTRINE PDO postgresql bindValue() with null -> SQLSTATE[22P02]

1 Answers