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, Commented May 13, 2016 at 21:15
@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, Commented May 13, 2016 at 23:06

nyluje · Accepted Answer · 2016-05-13 23:07:57Z

1

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

answered May 13, 2016 at 23:07

nyluje

3,8037 gold badges42 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related