Обсуждение: prepared SELECT and placeholders with NULL values

Hi,

I'm having an issue using NULL values to fill placeholders in a
prepared SELECT statement. My table looks something like this:

CREATE TABLE person (id serial primary key, lname text not null, fname text);

Given queries like this (using the Perl DBI+DBD::Pg interface):
  $i = $db->prepare('INSERT INTO person (lname, fname) VALUES (?, ?)');
  $s = $db->prepare('SELECT id FROM person WHERE lname = ? AND fname = ?');

These insert operations work fine:
  $i->execute('Bono', 'Sonny');
  $i->execute('Cher', undef);

This select works properly as well, returning the appropriate "id" value:
  $s->execute('Bono', 'Sonny');

But this does not, returning an empty list:
  $s->execute('Cher', undef);

My environment:
  - PostgreSQL 8.1.3
  - Perl 5.8.8
  - DBI 1.50
  - DBD::Pg 1.43

Any ideas? Thanks.

- Mike

On Thu, Mar 23, 2006 at 15:24:16 -0600,
  Michael Lea <michael.lea@gmail.com> wrote:
>
>   $s = $db->prepare('SELECT id FROM person WHERE lname = ? AND fname = ?');
>
> But this does not, returning an empty list:
>   $s->execute('Cher', undef);

= NULL will return NULL for every row and no rows will be selected by the
WHERE clause. This is how things are supposed to work according to the
standard.

It is hard to say what you might do without knowing more about what you
are doing, but a simple possibility may be to use the empty string ('') instead
of NULL in your data.