|
|
php.net | support | documentation | report a bug | advanced search | search howto | statistics | random bug | login |
[2012-09-02 14:46 UTC] 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.
PatchesPull RequestsHistoryAllCommentsChangesGit/SVN commits
|
|||||||||||||||||||||||||||
Copyright © 2001-2024 The PHP GroupAll rights reserved. |
Last updated: Sat Dec 21 15:01:29 2024 UTC |
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));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')";(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);