view release on metacpan or  search on metacpan

DBI.pm  view on Meta::CPAN

or the following, to select the description for a product:

  SELECT description FROM products WHERE product_code = ?

The C<?> characters are the placeholders.  The association of actual
values with placeholders is known as I<binding>, and the values are
referred to as I<bind values>.

Note that the C<?> is not enclosed in quotation marks, even when the
placeholder represents a string.  Some drivers also allow placeholders
like C<:>I<name> and C<:>I<n> (e.g., C<:1>, C<:2>, and so on)
in addition to C<?>, but their use is not portable.

With most drivers, placeholders can't be used for any element of a
statement that would prevent the database server from validating the
statement and creating a query execution plan for it. For example:

  "SELECT name, age FROM ?"         # wrong (will probably fail)
  "SELECT name, ?   FROM people"    # wrong (but may not 'fail')

Also, placeholders can only represent single scalar values.
For example, the following
statement won't work as expected for more than one value:

  "SELECT name, age FROM people WHERE name IN (?)"    # wrong
  "SELECT name, age FROM people WHERE name IN (?,?)"  # two names

When using placeholders with the SQL C<LIKE> qualifier, you must
remember that the placeholder substitutes for the whole string.
So you should use "C<... LIKE ? ...>" and include any wildcard
characters in the value that you bind to the placeholder.

B<NULL Values>

Undefined values, or C<undef>, are used to indicate NULL values.
You can insert and update columns with a NULL value as you would a
non-NULL value.  These examples insert and update the column
C<age> with a NULL value:

  $sth = $dbh->prepare(qq{
    INSERT INTO people (fullname, age) VALUES (?, ?)
  });
  $sth->execute("Joe Bloggs", undef);

  $sth = $dbh->prepare(qq{
    UPDATE people SET age = ? WHERE fullname = ?
  });
  $sth->execute(undef, "Joe Bloggs");
  
However, care must be taken when trying to use NULL values in a
C<WHERE> clause.  Consider:

  SELECT fullname FROM people WHERE age = ?

Binding an C<undef> (NULL) to the placeholder will I<not> select rows
which have a NULL C<age>!  At least for database engines that
conform to the SQL standard.  Refer to the SQL manual for your database
engine or any SQL book for the reasons for this.  To explicitly select
NULLs you have to say "C<WHERE age IS NULL>".

A common issue is to have a code fragment handle a value that could be
either C<defined> or C<undef> (non-NULL or NULL) at runtime.
A simple technique is to prepare the appropriate statement as needed,
and substitute the placeholder for non-NULL cases:

  $sql_clause = defined $age? "age = ?" : "age IS NULL";
  $sth = $dbh->prepare(qq{
    SELECT fullname FROM people WHERE $sql_clause
  });
  $sth->execute(defined $age ? $age : ());

The following technique illustrates qualifying a C<WHERE> clause with
several columns, whose associated values (C<defined> or C<undef>) are
in a hash %h:

  for my $col ("age", "phone", "email") {
    if (defined $h{$col}) {
      push @sql_qual, "$col = ?";
      push @sql_bind, $h{$col};
    }
    else {
      push @sql_qual, "$col IS NULL";
    }
  }
  $sql_clause = join(" AND ", @sql_qual);
  $sth = $dbh->prepare(qq{
      SELECT fullname FROM people WHERE $sql_clause
  });
  $sth->execute(@sql_bind);

The techniques above call prepare for the SQL statement with each call to
execute.  Because calls to prepare() can be expensive, performance
can suffer when an application iterates many times over statements
like the above.

A better solution is a single C<WHERE> clause that supports both
NULL and non-NULL comparisons.  Its SQL statement would need to be
prepared only once for all cases, thus improving performance.
Several examples of C<WHERE> clauses that support this are presented
below.  But each example lacks portability, robustness, or simplicity.
Whether an example is supported on your database engine depends on
what SQL extensions it provides, and where it supports the C<?>
placeholder in a statement.

  0)  age = ?
  1)  NVL(age, xx) = NVL(?, xx)
  2)  ISNULL(age, xx) = ISNULL(?, xx)
  3)  DECODE(age, ?, 1, 0) = 1
  4)  age = ? OR (age IS NULL AND ? IS NULL)
  5)  age = ? OR (age IS NULL AND SP_ISNULL(?) = 1)
  6)  age = ? OR (age IS NULL AND ? = 1)
	
Statements formed with the above C<WHERE> clauses require execute
statements as follows.  The arguments are required, whether their
values are C<defined> or C<undef>.

  0,1,2,3)  $sth->execute($age);
  4,5)      $sth->execute($age, $age);
  6)        $sth->execute($age, defined($age) ? 0 : 1);

Example 0 should not work (as mentioned earlier), but may work on