Why does Emulating a Prepared Statement Fail with Null?

Question

From the PHP manual, it states that:

PDO::ATTR_EMULATE_PREPARES Enables or disables emulation of prepared statements. Some drivers do not support native prepared statements or have limited support for them. Use this setting to force PDO to either always emulate prepared statements (if TRUE), or to try to use native prepared statements (if FALSE). It will always fall back to emulating the prepared statement if the driver cannot successfully prepare the current query. Requires bool.

I have a query that goes like this:

$type = PDO::PARAM_INT;
if($_POST['code'] == "") $type = PDO::PARAM_NULL;

$stmt = $dbh->prepare("UPDATE Product SET code=? WHERE id=?");
$stmt->bindValue(1, $_POST['code'], $type);
$stmt->bindValue(2, $_SESSION['id'], PDO::PARAM_INT);
$stmt->execute();

I realise that without setting the following statement, I would get a '0' instead of a NULL value (when $_POST['code'] == "") into my database using the code above. Why is that so?

$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

UPDATE:

Although disabling of the emulation works for me, I would prefer to use it because like the OP in this question, basically all my queries are only executed once per script execution. Therefore, having the statements prepared does not gain me any advantage and creates unnecessary calls to the database as shown in the general query log below:

Emulation Disabled
22 Connect user@localhost on Database
22 Prepare UPDATE Product SET code=? WHERE id=?
22 Execute UPDATE Product SET code='abc' WHERE id='123'
22 Close stmt   
22 Quit

Emulation Enabled
22 Connect user@localhost on Database
22 Query UPDATE Product SET code='abc' WHERE id='123'
22 Quit

Any help to solve the NULL issue is greatly appreciated.

and also posing logs, irrelevant to the code above, I wouldn't call a reasonable action. — Your Common Sense
@ Col. Shrapnel. That solves it. I didn't think it is so easy. You should put it down as answer for me to accept. Thanks. — Question Overflow
The log is to show the advantages of having emulation and explain why I need to solve this question :) — Question Overflow
The only advantage of the log is to show the actual result of the actual code. Otherwise it makes very little sense. Aren't you curious yourself, what SQL query PDO created out of your code? — Your Common Sense

xdazz xdazz · Accepted Answer · 2011-12-01T09:22:42

Try bind null for PDO::PARAM_NULL.

$type = PDO::PARAM_INT;
if($_POST['code'] == "") {
    $_POST['code'] = null;
    $type = PDO::PARAM_NULL;
}

$stmt = $dbh->prepare("UPDATE Product SET code=? WHERE id=?");
$stmt->bindValue(1, $_POST['code'], $type);
$stmt->bindValue(2, $_SESSION['id'], PDO::PARAM_INT);
$stmt->execute();

In fact, it seems you don't need use PDO::PARAM_NULL, the code below will also insert null.

$stmt->bindValue(1, $_POST['code'] ? $_POST['code'] : null, PDO::PARAM_INT);

Why does Emulating a Prepared Statement Fail with Null?

1 Answers