Bug #62998 [Nab]: [irrelevant - misunderstanding]

Edit report at https://bugs.php.net/bug.php?id=62998&edit=1 ID: 62998 User updated by: php at richardneill dot org Reported by: php at richardneill dot org -Summary: pg_query_params() doesn't handle NULL +Summary: [irrelevant - misunderstanding] 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: OK, so I appear to be having a day of complete mental aberration. Please ignore everything I just wrote; sorry for wasting your time. To confirm: it looks like pg_query_params() does handle input NULLS correctly, so long as they are not in a Where clause. In that case, this is really an SQL problem distinguishing "where X = 7" from "where X is null" from "where X = null" from "where X = 'null'". Previous Comments: ------------------------------------------------------------------------ [2014-05-18 21:14:50] php at richardneill dot org (Sorry, I appear to be unable to type. The example should of course read:) $sql = "INSERT into authors (name, age) VALUES ($1, $2)"; $params = array ("Dickens", NULL); pg_query_params ($sql, $params); ------------------------------------------------------------------------ [2014-05-18 02:37:24] php at richardneill dot org 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')"; ------------------------------------------------------------------------ [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