view release on metacpan or  search on metacpan

DBI.pm  view on Meta::CPAN

  INSERT INTO sales (product_code, qty, price) VALUES (?, ?, ?)

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);

DBI.pm  view on Meta::CPAN

  $dbh->commit;
  $dbh->disconnect;

Here's how to convert fetched NULLs (undefined values) into empty strings:

  while($row = $sth->fetchrow_arrayref) {
    # this is a fast and simple way to deal with nulls:
    foreach (@$row) { $_ = '' unless defined }
    print "@$row\n";
  }

The C<q{...}> style quoting used in these examples avoids clashing with
quotes that may be used in the SQL statement. Use the double-quote like
C<qq{...}> operator if you want to interpolate variables into the string.
See L<perlop/"Quote and Quote-like Operators"> for more details.

=head2 Threads and Thread Safety

Perl 5.7 and later support a new threading model called iThreads.
(The old "5.005 style" threads are not supported by the DBI.)

In the iThreads model each thread has it's own copy of the perl
interpreter.  When a new thread is created the original perl
interpreter is 'cloned' to create a new copy for the new thread.

If the DBI and drivers are loaded and handles created before the
thread is created then it will get a cloned copy of the DBI, the
drivers and the handles.

However, the internal pointer data within the handles will refer
to the DBI and drivers in the original interpreter. Using those
handles in the new interpreter thread is not safe, so the DBI detects
this and croaks on any method call using handles that don't belong
to the current thread (except for DESTROY).

Because of this (possibly temporary) restriction, newly created
threads must make their own connctions to the database. Handles
can't be shared across threads.

But BEWARE, some underlying database APIs (the code the DBD driver
uses to talk to the database, often supplied by the database vendor)
are not thread safe. If it's not thread safe, then allowing more
than one thread to enter the code at the same time may cause
subtle/serious problems. In some cases allowing more than
one thread to enter the code, even if I<not> at the same time,
can cause problems. You have been warned.

Using DBI with perl threads is not yet recommended for production
environments. For more information see
L<http://www.perlmonks.org/index.pl?node_id=288022>

Note: There is a bug in perl 5.8.2 when configured with threads
and debugging enabled (bug #24463) which causes a DBI test to fail.

=head2 Signal Handling and Canceling Operations

[The following only applies to systems with unix-like signal handling.
I'd welcome additions for other systems, especially Windows.]

The first thing to say is that signal handling in Perl versions less
than 5.8 is I<not> safe. There is always a small risk of Perl
crashing and/or core dumping when, or after, handling a signal
because the signal could arrive and be handled while internal data
structures are being changed. If the signal handling code
used those same internal data structures it could cause all manner
of subtle and not-so-subtle problems.  The risk was reduced with
5.4.4 but was still present in all perls up through 5.8.0.

Beginning in perl 5.8.0 perl implements 'safe' signal handling if
your system has the POSIX sigaction() routine. Now when a signal
is delivered perl just makes a note of it but does I<not> run the
%SIG handler. The handling is 'defered' until a 'safe' moment.

Although this change made signal handling safe, it also lead to
a problem with signals being defered for longer than you'd like.
If a signal arrived while executing a system call, such as waiting
for data on a network connection, the signal is noted and then the
system call that was executing returns with an EINTR error code
to indicate that it was interrupted. All fine so far.

The problem comes when the code that made the system call sees the
EINTR code and decides it's going to call it again. Perl doesn't
do that, but database code sometimes does. If that happens then the
signal handler doesn't get called untill later. Maybe much later.

Fortunately there are ways around this which we'll discuss below.
Unfortunately they make signals unsafe again.

The two most common uses of signals in relation to the DBI are for
canceling operations when the user types Ctrl-C (interrupt), and for
implementing a timeout using C<alarm()> and C<$SIG{ALRM}>. 

=over 4

=item Cancel

The DBI provides a C<cancel> method for statement handles. The
C<cancel> method should abort the current operation and is designed
to be called from a signal handler.  For example:

  $SIG{INT} = sub { $sth->cancel };

However, few drivers implement this (the DBI provides a default
method that just returns C<undef>) and, even if implemented, there
is still a possibility that the statement handle, and even the
parent database handle, will not be usable afterwards.

If C<cancel> returns true, then it has successfully
invoked the database engine's own cancel function.  If it returns false,
then C<cancel> failed. If it returns C<undef>, then the database
driver does not have cancel implemented.

=item Timeout

The traditional way to implement a timeout is to set C<$SIG{ALRM}>
to refer to some code that will be executed when an ALRM signal
arrives and then to call alarm($seconds) to schedule an ALRM signal
to be delivered $seconds in the future. For example:

  eval {