Writing Comparisons Involving NULL in Programs

3.14.1 Problem

You're writing a program that issues a query, but it fails for NULL values.

3.14.2 Solution

Try writing the comparison selectively for NULL and non-NULL values.

3.14.3 Discussion

The need to use different comparison operators for NULL values than for non-NULL values leads to a subtle danger when constructing query strings within programs. If you have a value stored in a variable that might represent a NULL value, you must account for that if you use the value in comparisons. For example, in Perl, undef represents a NULL value, so to construct a statement that finds records in the taxpayer table matching some arbitrary value in an $id variable, you cannot do this:

$sth = $dbh->prepare ("SELECT * FROM taxpayer WHERE id = ?");
$sth->execute ($id);

The statement fails when $id is undef, because the resulting query becomes:

SELECT * FROM taxpayer WHERE id = NULL

That statement returns no recordsa comparison of = NULL always fails. To take into account the possibility that $id may be undef, construct the query using the appropriate comparison operator like this:

$operator = (defined ($id) ? "=" : "IS");
$sth = $dbh->prepare ("SELECT * FROM taxpayer WHERE id $operator ?");
$sth->execute ($id);

This results in queries as follows for $id values of undef (NULL) or 43 (not NULL):

SELECT * FROM taxpayer WHERE id = 43

For inequality tests, set $operator like this instead:

$operator = (defined ($id) ? "!=" : "IS NOT");

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References

MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net