Author: timbo
Date: Wed Dec 8 04:38:50 2004
New Revision: 604
Added:
dbi/trunk/ex/
dbi/trunk/ex/perl_dbi_nulls_test.pl
Modified:
dbi/trunk/Changes
dbi/trunk/DBI.pm
dbi/trunk/MANIFEST
Log:
Updated docs to recommend common some DSN string attributes.
Updated docs for NULL Value placeholders thanks to Brian Campbell.
Modified: dbi/trunk/Changes
==============================================================================
--- dbi/trunk/Changes (original)
+++ dbi/trunk/Changes Wed Dec 8 04:38:50 2004
@@ -20,6 +20,9 @@
Changed DBD::Proxy to treat Username as a local attribute
so recent DBI version can be used with old DBI::ProxyServer.
+ Updated docs to recommend common some DSN string attributes.
+ Updated docs for NULL Value placeholders thanks to Brian Campbell.
+
=head2 Changes in DBI 1.46 (svn rev 584), 16th November 2004
Fixed parsing bugs in DBI::SQL::Nano thanks to Jeff Zucker.
Modified: dbi/trunk/DBI.pm
==============================================================================
--- dbi/trunk/DBI.pm (original)
+++ dbi/trunk/DBI.pm Wed Dec 8 04:38:50 2004
@@ -2273,25 +2273,109 @@
So you should use "C<... LIKE ? ...>" and include any wildcard
characters in the value that you bind to the placeholder.
-B<Null Values>
+B<NULL Values>
-Undefined values, or C<undef>, can be used to indicate 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:
+
+ $sth = $dbh->prepare(qq{
+ INSERT INTO people (name, age) VALUES (?, ?)
+ });
+ $sth->execute("Joe Bloggs", undef);
+
+ $sth = $dbh->prepare(qq{
+ UPDATE people SET age = ? WHERE name = ?
+ });
+ $sth->execute(undef, "Joe Bloggs");
+
However, care must be taken in the particular case of trying to use
-null values to qualify a C<SELECT> statement. Consider:
+NULL values to qualify a C<WHERE> clause. Consider:
- SELECT description FROM products WHERE product_code = ?
+ SELECT name FROM people WHERE age = ?
Binding an C<undef> (NULL) to the placeholder will I<not> select rows
-which have a NULL C<product_code>! Refer to the SQL manual for your database
+which have a NULL C<product_code>! 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>" and to make that
-general you have to say:
+NULLs you have to say "C<WHERE product_code IS NULL>".
- ... WHERE (product_code = ? OR (? IS NULL AND product_code IS NULL))
-
-and bind the same value to both placeholders. Sadly, that more general
-syntax doesn't work for Sybase and MS SQL Server. However on those two
-servers the original "C<product_code = ?>" syntax works for binding nulls.
+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:
+
+ if (defined $age) {
+ push @sql_qual, "age = ?";
+ push @sql_bind, $age;
+ }
+ else {
+ push @sql_qual, "age IS NULL";
+ }
+ $sth = $dbh->prepare(qq{
+ SELECT id FROM products WHERE }.join(" AND ", @sql_qual).qq{
+ });
+ $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)
+
+Statements formed with the above C<WHERE> clauses require execute
+statements as follows:
+
+ 0-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.
+
+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.
+That means you must have some notion of what data won't be stored
+in the column, and expect clients to adhere to that.
+
+Example 5 requires that you provide a stored procedure (SP_ISNULL
+in this example) that acts as a function: it checks whether a value
+is null, and returns 1 if it is, or 0 if not.
+
+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:
+
+ -----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
+
+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
+in the F<ex/> subdirectory of the DBI source distribution, or here:
+L<http://svn.perl.org/modules/dbi/trunk/ex/perl_dbi_nulls_test.pl>
+Please use the script to help us fill-in and maintain this table.
B<Performance>
@@ -2415,9 +2499,17 @@
driver is free to use whatever syntax it wants. The only requirement the
DBI makes is that all the information is supplied in a single string.
You must consult the documentation for the drivers you are using for a
-description of the syntax they require. (Where a driver author needs
-to define a syntax for the C<$data_source>, it is recommended that
-they follow the ODBC style, shown in the last example above.)
+description of the syntax they require.
+
+It is recommended that drivers support the ODBC style, shown in the
+last example above. It is also recommended that that they support the
+three common names 'C<host>', 'C<port>', and 'C<database>' (plus 'C<db>'
+as an alias for C<database>). This simplifies automatic construction
+of basic DSNs: C<"dbi:$driver:database=$db;host=$host;port=$port">.
+Drivers should aim to 'do something reasonable' when given a DSN
+in this form, but if any part is meaningless for that driver (such
+as 'port' for Informix) it should generate an error if that part
+is not empty.
If the environment variable C<DBI_AUTOPROXY> is defined (and the
driver in C<$data_source> is not "C<Proxy>") then the connect request
Modified: dbi/trunk/MANIFEST
==============================================================================
--- dbi/trunk/MANIFEST (original)
+++ dbi/trunk/MANIFEST Wed Dec 8 04:38:50 2004
@@ -17,6 +17,7 @@
dbiprof.PL
dbiproxy.PL Frontend for DBI::ProxyServer
dbivport.h DBI version portability macros (for drivers to
copy)
+ex/perl_dbi_nulls_test.pl A test script for forms of IS NULL
qualification in SQL
lib/Bundle/DBI.pm A bundle for automatic installation via CPAN.
lib/DBD/DBM.pm A driver for DBM files (uses DBD::File)
lib/DBD/ExampleP.pm A very simple example Driver module
Added: dbi/trunk/ex/perl_dbi_nulls_test.pl
==============================================================================
--- (empty file)
+++ dbi/trunk/ex/perl_dbi_nulls_test.pl Wed Dec 8 04:38:50 2004
@@ -0,0 +1,125 @@
+# This script checks with 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.
+#
+# Warning: This script will attempt to create a table named by the
+# $tablename variable (default dbi__null_test_tmp) and WILL DESTROY
+# any pre-existing table so named.
+
+use strict;
+use DBI;
+
+my $tablename = "dbi__null_test_tmp"; # DESTROYs this table. Must be obscure
+
+die "DBI_DSN environment variable not defined"
+ unless $ENV{DBI_DSN};
+
+my $dbh = DBI->connect(undef, undef, undef, {
+ RaiseError => 0,
+ PrintError => 1
+ }
+);
+
+my $sth;
+my @ok;
+
+print "=> Drop table '$tablename', if it already exists...\n";
+$sth = $dbh->do("DROP TABLE $tablename");
+
+print "=> Create table '$tablename'...\n";
+$sth = $dbh->prepare("CREATE TABLE $tablename (key int, mycol char(8))");
+$sth->execute();
+
+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],
+);
+
+# Define the execute method argument lists for null values.
+# The order must map one to one with the above SQL statements.
+
+my @null_args = (
+ [undef],
+ [undef],
+ [undef],
+ [undef],
+ [undef, undef],
+ [undef, undef],
+ [undef, 1],
+);
+
+# Run the tests...
+
+for my $i ([EMAIL PROTECTED])
+{
+ print "\n=> Testing clause style $i: $sel[$i]\n";
+ $sth = $dbh->prepare("SELECT key,mycol FROM $tablename $sel[$i]")
+ or next;
+
+ $sth->execute(@{$nonnull_args[$i]})
+ or next;
+ my $r1 = $sth->fetchall_arrayref();
+ my $n1 = $sth->rows;
+
+ $sth->execute(@{$null_args[$i]})
+ or next;
+ my $r2 = $sth->fetchall_arrayref();
+ my $n2 = $sth->rows;
+
+ # 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?
+
+ if ( $n1 == 1
+ && $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]";
+ }
+ else {
+ print "=> WHERE clause style $i returned incorrect results.\n";
+ }
+}
+
+$dbh->disconnect();
+
+printf "\n%d styles are supported\n", scalar @ok;
+print "$_\n" for @ok;
+print "\n";
