使用PDO发送空值会导致错误 [英] Sending empty values with PDO results in error

问题描述

我们有类似以下PDO语句的内容，可用于与PostgreSQL 8.4 DB通信.

We have something like the following PDO Statement which we use to communicate with a PostgreSQL 8.4 DB.

$st = $db -> prepare("INSERT INTO Saba.Betriebskosten (personalkosten)
                                            VALUES(:kd_personalkosten)");
$st -> bindParam(':kd_personalkosten', $val['kd_personalkosten']);

$ val ['kd_personalkosten']为空/空或包含双精度值.在为空/空的情况下，我们只想插入一个空值，但是会收到以下错误:

$val['kd_personalkosten'] is either empty/null or contains a double value. In the case it is empty/null, we just want to insert an empty value, but we receive the following error:

SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for type double precision: '';

这意味着将空/空转换为与双精度字段不兼容的空STRING.如何避免这个错误?

Which means that empty/null is converted to an empty STRING which is not compatible with the double precision field. How to circumvent this error?

推荐答案

在我看来，值是"(空字符串)，在SQL查询中bindParam将该值转换为"，并且由于personalkosten是Double类型，因此引发了错误.

it seems to me that value is "" (empty string) which bindParam converts to "" in SQL query, and since personalkosten is of type Double it raises the error.

这应该可以解决此问题，将空文本转换为双重转换:

This should fix this issue with empty text to double conversion:

$st -> bindParam(':kd_personalkosten', (float) $val['kd_personalkosten']);

如果您真的想在变量为空时插入NULL值，则应该执行以下操作:

If you would really want to insert NULL value when variable is empty then you should do this:

$value = $val['kd_personalkosten'];
if ($value === '' or $value === NULL) {
    $st->bindValue(':kd_personalkosten', NULL, PDO::PARAM_NULL); // note the bindValue() instead of bindParam()
} else {
    $st->bindParam(':kd_personalkosten', $value);
}

关于 bindValue vs bindParam (来自PHP手册):

About bindValue vs bindParam from php manual:

bindParam()

bindParam()

将PHP变量绑定到相应的命名或问号 用于准备SQL语句的占位符 陈述.与PDOStatement :: bindValue()不同，该变量绑定为 参考，并且只会在以下时间进行评估: PDOStatement :: execute()被调用.

Binds a PHP variable to a corresponding named or question mark placeholder in the SQL statement that was used to prepare the statement. Unlike PDOStatement::bindValue(), the variable is bound as a reference and will only be evaluated at the time that PDOStatement::execute() is called.

大多数参数是输入参数，即 以只读方式用于构建查询.一些司机 支持调用将数据作为输出返回的存储过程 参数，还有一些也作为输入/输出参数发送 数据并进行更新以接收它.

Most parameters are input parameters, that is, parameters that are used in a read-only fashion to build up the query. Some drivers support the invocation of stored procedures that return data as output parameters, and some also as input/output parameters that both send in data and are updated to receive it.

基本上，bindValue允许您绑定直接值或常量，而bindParam需要传入变量或引用.

Basically bindValue allows you to bind a direct value or constant, whilst bindParam requires a variable or reference to be passed in.

这篇关于使用PDO发送空值会导致错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！