Edit report at https://bugs.php.net/bug.php?id=62998&edit=1
ID: 62998
Comment by: php at richardneill dot org
Reported by: php at richardneill dot org
Summary: pg_query_params() doesn't handle NULL
Status: Not a bug
Type: Bug
Package: PostgreSQL related
Operating System: Linux
PHP Version: 5.3.16
Block user comment: N
Private report: N
New Comment:
Not sure if this is now an RFE, but pg_query_params() should IMHO, allow NULL.
For example, the following works fine.
$sql = "INSERT into authors (name, age) VALUES ($1, $2)";
$params = "Austen, 250";
pg_query_params ($sql, $params);
However, what if we don't know the author's age, and want to insert a null value, using
the same function? i.e.
$sql = "INSERT into authors (name, age) VALUES ($1, $2)";
$params = "Dickens, NULL";
pg_query_params ($sql, $params);
This Ought to work, generating the SQL:
INSERT into authors (name, age) VALUES ('Dickens', NULL)";
However, it actually generates the invalid:
INSERT into authors (name, age) VALUES ('Dickens', 'NULL')";
Previous Comments:
------------------------------------------------------------------------
[2013-06-05 14:16:01] mbeccati@php.net
Looks like you got it backwards. "IS NULL" is a string literal and you can't use a
parameter to construct it. Try this instead:
$result = pg_query_params ($conn,
"select CASE WHEN $1::text is null then 'it is null' ELSE 'it is not
null' END",
array(NULL) );
echo pg_last_error($conn);
print_r (pg_fetch_assoc($result));
------------------------------------------------------------------------
[2012-09-02 14:46:16] php at richardneill dot org
Description:
------------
pg_query_params($conn,$sql,$params) is supposed to correctly handle NULL->SQL_NULL. It
doesn't do this (despite the documentation); instead it breaks the query.
Documentation
"If parameters are used, they are referred to in the query string as $1, $2, etc. params
specifies the actual values of the parameters. A NULL value in this array means the corresponding
parameter is SQL NULL. "
Test script:
---------------
Here's a simple example:
$result = pg_query_params ($conn,
"select CASE WHEN NULL is $1 then 'it is null' ELSE 'it is not null'
END",
array(NULL) );
echo pg_last_error($conn);
print_r (pg_fetch_assoc($result));
This query fails, because the parameter $1 is not actually passed as NULL, instead it seems to be
the empty string.
My SQL is definitely right:
pg_query_params ($conn,
"select CASE WHEN NULL is NULL then 'it is null' ELSE 'it is not null'
END",
array() );
works fine, to return the string "it is null".
Expected result:
----------------
It should be possible to pass SQL_NULL to the database in a parameter.
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=62998&edit=1