Doctrine 2 DBAL expressions in update or insert methods

Question

I like the convenience methods for data manipulation queries $conn->insert() and $conn->update() in doctrine 2 DBAL because insert/update values can be held an passed as associative array. But how can i pass a NULL value, a MySQL function or other expressions as value?

E.g:

/* $conn is a \Doctrine\DBAL\Connection object */
$conn->update('person', array('phone' => 'NULL'), array('id' => 1));
$conn->update('person', array('lastlogin' => 'NOW()'), array('id' => 1));
$conn->update('person', array('visit' => 'visit + 1'), array('id' => 1));

These function calls would create prepared statements like

UPDATE person SET phone = ? WHERE id = ?

and thus the values would be treated as strings. Is there a way to make this work using this technique?

Answer 1

There is an optional $types argument, which defaults to an empty array:

    public function update($tableExpression, array $data, array $identifier, array $types = array())

The $types array can contain PDO type constants which will change how the corresponding $data values are treated.

So I'd try:

$conn->update(
    'person',                          // $tableExpression
    array('phone' => null),            // $data
    array('id' => 1),                  // $identifier
    array('phone' => \PDO::PARAM_NULL) // $types
);