Sending empty values with PDO results in error

Question

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'] 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: '';

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?

Does PostgreSQL 8.4 accept NULL values for double precision ? — david strachan
– david strachan, Commented Nov 29, 2012 at 14:22
Fields are not defined as NOT NULL. @IvanHušnjak string(0) "" — grssnbchr
– grssnbchr, Commented Nov 29, 2012 at 14:27
@wnstnsmth If 0.0 is the valid value for an empty string converting it to a numeric is OK. But if empty string means no value you should store it as NULL as Ivan suggested. Obviously this will mean re- defining the field.If this is not possible you might be able to use a numeric value that is meaningless to your application.eg 99999.999 — david strachan
– david strachan, Commented Nov 29, 2012 at 15:46

Ivan Hušnjak · Accepted Answer · 2012-11-29 14:34:45Z

3

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']);

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);
}

About bindValue vs bindParam from php manual:

bindParam()

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.

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

edited Nov 29, 2012 at 14:34

answered Nov 29, 2012 at 14:22

Ivan Hušnjak

3,5034 gold badges23 silver badges31 bronze badges

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

3 Comments

grssnbchr Over a year ago

This is exactly what I am suspecting as well - but this is a quite stupid behaviour, isn't it? Is there no way to let it automatically convert to NULL?

grssnbchr Over a year ago

Concerning your updated answer: What is the difference between bindValue and bindParam?

TheCarver Over a year ago

@wnstnsmth: bindParam requires a variable, bindValue doesn't.

Collectives™ on Stack Overflow

Sending empty values with PDO results in error

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related