Author: timbo
Date: Mon Mar 14 06:06:43 2005
New Revision: 924
Modified:
dbi/trunk/Changes
dbi/trunk/DBI.pm
dbi/trunk/ex/perl_dbi_nulls_test.pl
Log:
Updated docs for NULL Value placeholders thanks to Brian Campbell.
Modified: dbi/trunk/Changes
==============================================================================
--- dbi/trunk/Changes (original)
+++ dbi/trunk/Changes Mon Mar 14 06:06:43 2005
@@ -9,7 +9,9 @@
Fixed DBI::DBD::Metadata generation of type_info_all thanks to Steffen
Goeldner
(driver authors who have used it should rerun it).
- Registered driver prefixes: amzn_ for DBD::Amazon and yaswi_ for DBD::Yaswi.
+ Updated docs for NULL Value placeholders thanks to Brian Campbell.
+
+ Added registered driver prefixes: amzn_ for DBD::Amazon and yaswi_ for
DBD::Yaswi.
=head2 Changes in DBI 1.47 (svn rev 854), 2nd February 2005
Modified: dbi/trunk/DBI.pm
==============================================================================
--- dbi/trunk/DBI.pm (original)
+++ dbi/trunk/DBI.pm Mon Mar 14 06:06:43 2005
@@ -2282,75 +2282,96 @@
B<NULL Values>
Undefined values, or C<undef>, are used to indicate NULL values.
-You can insert update columns with a NULL value as you would a
-non-NULL value. Consider these examples that insert and update the
-column C<product_code> with a NULL value:
+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 (name, age) VALUES (?, ?)
+ INSERT INTO people (fullname, age) VALUES (?, ?)
});
$sth->execute("Joe Bloggs", undef);
$sth = $dbh->prepare(qq{
- UPDATE people SET age = ? WHERE name = ?
+ 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:
-However, care must be taken in the particular case of trying to use
-NULL values to qualify a C<WHERE> clause. Consider:
-
- SELECT name FROM people WHERE age = ?
+ SELECT fullname FROM people WHERE age = ?
Binding an C<undef> (NULL) to the placeholder will I<not> select rows
-which have a NULL C<product_code>! At least for database engines that
+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 product_code IS NULL>".
+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) in a C<WHERE> clause.
-A general way to do this is:
+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:
- if (defined $age) {
- push @sql_qual, "age = ?";
- push @sql_bind, $age;
- }
- else {
- push @sql_qual, "age IS NULL";
+ $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 id FROM products WHERE }.join(" AND ", @sql_qual).qq{
+ SELECT fullname FROM people WHERE $sql_clause
});
$sth->execute(@sql_bind);
-If your WHERE clause contains many "NULLs-allowed" columns, you'll
-need to manage many combinations of statements and this approach
-rapidly becomes more complex.
-
-A better solution would be to design a single C<WHERE> clause that
-supports both NULL and non-NULL comparisons. Several examples of
-C<WHERE> clauses that do 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
-supports, and where it can support the C<?> parameter 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)
-
+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:
+statements as follows. The arguments are required, whether their
+values are C<defined> or C<undef>.
- 0-3) $sth->execute($age);
- 4,5) $sth->execute($age, $age);
- 6) $sth->execute($age, defined($age)?0:1);
+ 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
-a few database engines anyway.
+a few database engines anyway (e.g. Sybase). Example 0 is part
+of examples 4, 5, and 6, so if example 0 works, these other
+examples may work, even if the engine does not properly support
+the right hand side of the C<OR> expression.
Examples 1 and 2 are not robust: they require that you provide a
valid column value xx (e.g. '~') which is not present in any row.
@@ -2364,18 +2385,19 @@
Example 6, the least simple, is probably the most portable, i.e., it
should work with with most, if not all, database engines.
-Here is a table that indicates which examples above are known to work on
-various database engines:
+Here is a table that indicates which examples above are known to
+work on various database engines:
+
+ -----Examples------
+ 0 1 2 3 4 5 6
+ - - - - - - -
+ Oracle 9 N Y N Y Y ? Y
+ Informix IDS 9 N N N Y N Y Y
+ MS SQL N N Y N Y ? Y
+ Sybase Y N N N N N Y
+ AnyData,DBM,CSV Y N N N Y Y* Y
- -----Examples------
- 0 1 2 3 4 5 6
- - - - - - - -
- Oracle 9 N Y
- Informix N N N Y N Y Y
- MS SQL
- DB2
- Sybase
- MySQL 4
+* Works only because Example 0 works.
DBI provides a sample perl script that will test the examples above
on your database engine and tell you which ones work. It is located
Modified: dbi/trunk/ex/perl_dbi_nulls_test.pl
==============================================================================
--- dbi/trunk/ex/perl_dbi_nulls_test.pl (original)
+++ dbi/trunk/ex/perl_dbi_nulls_test.pl Mon Mar 14 06:06:43 2005
@@ -1,13 +1,33 @@
-# This script checks with style of WHERE clause(s) will support both
+#! /usr/bin/perl -w
+
+# This script checks which style of WHERE clause(s) will support both
# null and non-null values. Refer to the NULL Values sub-section
# of the "Placeholders and Bind Values" section in the DBI
# documention for more information on this issue. The clause styles
# and their numbering (0-6) map directly to the examples in the
# documentation.
#
-# To use this script, update the connect method arguments to support
-# your database engine and database. Set PrintError to 1 if you want
-# see the reason WHY your engine won't support a particular style.
+# To use this script:
+#
+# 1) If you are not using the DBI_DSN env variable, then update the
+# connect method arguments to support your database engine and
+# database, and remove the nearby check for DBI_DSN.
+# 2) Set PrintError to 1 in the connect method if you want see the
+# engine's reason WHY your engine won't support a particular
+# style.
+# 3) If your database does not support NULL columns by default
+# (e.g. Sybase) find and edit the CREATE TABLE statement
+# accordingly.
+# 4) To properly test style #5, you need the capability to create the
+# stored procedure SP_ISNULL that acts as a function: it tests its
+# argument and returns 1 if it is null, 0 otherwise. For example,
+# using Informix IDS engine, a definition would look like:
+#
+# CREATE PROCEDURE SP_ISNULL (arg VARCHAR(32)) RETURNING INTEGER;
+# IF arg IS NULL THEN RETURN 1;
+# ELSE RETURN 0;
+# END IF;
+# END PROCEDURE;
#
# Warning: This script will attempt to create a table named by the
# $tablename variable (default dbi__null_test_tmp) and WILL DESTROY
@@ -16,16 +36,54 @@
use strict;
use DBI;
-my $tablename = "dbi__null_test_tmp"; # DESTROYs this table. Must be obscure
+# The array represents the values that will be stored in the char column of
our table.
+# One array element per row.
+# We expect the non-null test to return row 3 (Marge)
+# and the null test to return rows 2 and 4 (the undefs).
+
+my $homer = "Homer ";
+my $marge = "Marge ";
+
+my @char_column_values = (
+ $homer,
+ undef,
+ $marge,
+ undef
+);
+
+# Define the SQL statements with the various WHERE clause styles we want to
test
+# and the parameters we'll substitute.
+
+my @select_clauses =
+(
+ {clause=>qq{WHERE mycol = ?},
nonnull=>[$marge], null=>[undef]},
+ {clause=>qq{WHERE NVL(mycol, '-') = NVL(?, '-')},
nonnull=>[$marge], null=>[undef]},
+ {clause=>qq{WHERE ISNULL(mycol, '-') = ISNULL(?, '-')},
nonnull=>[$marge], null=>[undef]},
+ {clause=>qq{WHERE DECODE(mycol, ?, 1, 0) = 1},
nonnull=>[$marge], null=>[undef]},
+ {clause=>qq{WHERE mycol = ? OR (mycol IS NULL AND ? IS NULL)},
nonnull=>[$marge,$marge], null=>[undef,undef]},
+ {clause=>qq{WHERE mycol = ? OR (mycol IS NULL AND SP_ISNULL(?) = 1)},
nonnull=>[$marge,$marge], null=>[undef,undef]},
+ {clause=>qq{WHERE mycol = ? OR (mycol IS NULL AND ? = 1)},
nonnull=>[$marge,0], null=>[undef,1]},
+);
+
+# This is the table we'll create and use for these tests.
+# If it exists, we'll DESTROY it too. So the name must be obscure.
+
+my $tablename = "dbi__null_test_tmp";
+
+# Remove this if you are not using the DBI_DSN env variable,
+# and update the connect statement below.
die "DBI_DSN environment variable not defined"
unless $ENV{DBI_DSN};
-my $dbh = DBI->connect(undef, undef, undef, {
- RaiseError => 0,
- PrintError => 1
- }
-);
+my $dbh = DBI->connect(undef, undef, undef,
+ {
+ RaiseError => 0,
+ PrintError => 1
+ }
+) || die DBI->errstr;
+
+printf "Using %s, db version: %s\n", $ENV{DBI_DSN} || "connect arguments",
$dbh->get_info(18) || "(unknown)";
my $sth;
my @ok;
@@ -34,87 +92,75 @@
$sth = $dbh->do("DROP TABLE $tablename");
print "=> Create table '$tablename'...\n";
-$sth = $dbh->prepare("CREATE TABLE $tablename (key int, mycol char(8))");
-$sth->execute();
+$sth = $dbh->prepare("CREATE TABLE $tablename (myid int NOT NULL, mycol
char(8))");
-print "=> Insert 4 rows into the table...\n";
-my @stv = ('slow', undef, 'quick', undef);
-$sth = $dbh->prepare("INSERT INTO $tablename (key, mycol) VALUES (?,?)");
-for my $i (0..3)
-{
- $sth->execute($i+1, $stv[$i]);
-}
-
-# Define the SQL statements with the various WHERE clause styles we want to
test.
-
-my @sel = (
- qq{WHERE mycol = ?},
- qq{WHERE NVL(mycol, '-') = NVL(?, '-')},
- qq{WHERE ISNULL(mycol, '-') = ISNULL(?, '-')},
- qq{WHERE DECODE(mycol, ?, 1, 0) = 1},
- qq{WHERE mycol = ? OR (mycol IS NULL AND ? IS NULL)},
- qq{WHERE mycol = ? OR (mycol IS NULL AND SP_ISNULL(?) = 1)},
- qq{WHERE mycol = ? OR (mycol IS NULL AND ? = 1)},
-);
-
-# Define the execute method argument lists for non-null values.
-# The order must map one to one with the above SQL statements.
-
-my @nonnull_args = (
- ['quick'],
- ['quick'],
- ['quick'],
- ['quick'],
- ['quick','quick'],
- ['quick','quick'],
- ['quick', 0],
-);
+# Use this if your database does not support NULL columns by default.
+#$sth = $dbh->prepare("CREATE TABLE $tablename (myid int NOT NULL, mycol
char(8) NULL)");
-# Define the execute method argument lists for null values.
-# The order must map one to one with the above SQL statements.
+$sth->execute()
+ || $sth->errstr;
-my @null_args = (
- [undef],
- [undef],
- [undef],
- [undef],
- [undef, undef],
- [undef, undef],
- [undef, 1],
-);
+print "=> Insert 4 rows into the table...\n";
+$sth = $dbh->prepare("INSERT INTO $tablename (myid, mycol) VALUES (?,?)");
+for my $i (0..$#char_column_values)
+{
+ my $val = $char_column_values[$i];
+ printf "Values %d %s\n", $i+1, defined($val)? $val : "";
+ $sth->execute($i+1, $val)
+ || $sth->errstr;
+}
# Run the tests...
-for my $i ([EMAIL PROTECTED])
+for my $i (0..$#select_clauses)
{
- print "\n=> Testing clause style $i: $sel[$i]\n";
- $sth = $dbh->prepare("SELECT key,mycol FROM $tablename $sel[$i]")
+ my $sel = $select_clauses[$i];
+ print "\n=> Testing clause style $i: ".$sel->{clause}."\n";
+
+ $sth = $dbh->prepare("SELECT myid,mycol FROM $tablename ".$sel->{clause})
or next;
- $sth->execute(@{$nonnull_args[$i]})
+ $sth->execute(@{$sel->{nonnull}})
or next;
my $r1 = $sth->fetchall_arrayref();
- my $n1 = $sth->rows;
+ my $n1r = $sth->rows;
+ my $n1 = @$r1;
- $sth->execute(@{$null_args[$i]})
+ $sth->execute(@{$sel->{null}})
or next;
my $r2 = $sth->fetchall_arrayref();
- my $n2 = $sth->rows;
+ my $n2r = $sth->rows;
+ my $n2 = @$r2;
+
+ # Complain a bit...
+ print "\n=>Your DBD driver doesn't support the 'rows' method very
well.\n\n"
+ unless ($n1r == $n1 && $n2r == $n2);
+
# Did we get back the expected "n"umber of rows?
- # Did we get back the specific "r"ows we expected as identifed by the key
column?
+ # Did we get back the specific "r"ows we expected as identifed by the myid
column?
if ( $n1 == 1
- && $n2 == 2
- && $r1->[0][0] == 3
- && $r2->[0][0] == 2
- && $r2->[1][0] == 4)
+ && $n2 == 2
+ && $r1->[0][0] == 3
+ && $r2->[0][0] == 2
+ && $r2->[1][0] == 4)
{
print "=> WHERE clause style $i is supported.\n";
- push @ok, "$i: $sel[$i]";
+ push @ok, "$i: ".$sel->{clause};
}
- else {
+ else
+ {
print "=> WHERE clause style $i returned incorrect results.\n";
+ if ($n1 > 0 || $n2 > 0)
+ {
+ print " Non-Null test rows returned: ";
+ print " ", $r1->[$_][0] for (0..$#{$r1});
+ print "\n";
+ print " Null test rows returned: ";
+ print " ", $r2->[$_][0] for (0..$#{$r2});
+ print "\n";
+ }
}
}
