I recently encountered some new info to add to an old thread to which I contributed.
Old Thread Briefly I responded to a query someone made about generalizing WHERE clauses so that you could use either NULL or non-NULL values to bind to parameterized ?'s. In other words, you construct a WHERE clause, prepare the statement once, then do multiple executes, and having the flexibility to bind either NULL or NON-null data each time. Background links (from April and May): http://www.mail-archive.com/[EMAIL PROTECTED]/msg21050.html <http://www.mail-archive.com/[EMAIL PROTECTED]/msg21050.html> http://www.mail-archive.com/[EMAIL PROTECTED]/msg21053.html <http://www.mail-archive.com/[EMAIL PROTECTED]/msg21053.html> http://www.mail-archive.com/[EMAIL PROTECTED]/msg21082.html <http://www.mail-archive.com/[EMAIL PROTECTED]/msg21082.html> Several solutions were offered, but they appeared to use non-ANSI features, as far as I could tell. Correct me if I'm wrong. For example: col = ? (works only if your database supports COL = NULL) NVL() ISNULL(?, col) col = ? OR (col IS NULL and ? IS NULL) I don't believe I could use any of them with my Database (Informix) nor was I aware of any other solution for Informix. My only recourse then was to create unique SQL and prepare it for each execute (customizing each SQL match to be either "col=value", or "col IS NULL"). New Info Now I am aware of two solutions that do work with Informix (and probably other databases as well). So I thought I would take the liberty to share this info; perhaps it will help someone else. These solutions are variations of the last example above, for which you have to bind a pair of values for each "nulls allowed" column in the where clause. 1. COL = ? OR (COL IS NULL AND ? = 1) Here you bind a value (e.g. $val) and then a constant 1 or not 1 (e.g. defined($val)? 0 : 1). This depends on the fact that constants can appear on the left side of "=" (whereas they can't in front of IS NULL). 2. COL = ? OR (COL IS NULL AND SP_ISNULL(?) = 1) Here you bind the same value to both ?'s (e.g. $val, $val). This depends on the ability to create a stored procedure SP_NULL, which can be defined to accept a NULL or NON-NULL value, test its NULL-ness, and then return 1 or 0. Real Examples: 1. First approach: my $emp1_sql = <<SQL; SELECT first_name, last_name FROM employees WHERE last_name LIKE ? AND ( mail_code = ? OR (mail_code IS NULL AND ? = 1)); SQL my $sth1 = $dbh->prepare($emp1_sql); for my $mail_code ('CAA05EFJ', undef) { $sth1->execute('D%', $mail_code, defined($mail_code)?0:1); show_data($sth1); } 2. Second approach: my $emp2_sql = <<SQL; SELECT first_name, last_name FROM employees WHERE last_name LIKE ? AND ( mail_code = ? OR (mail_code IS NULL AND sp_isnull(?) = 1)); SQL my $sth2 = $dbh->prepare($emp2_sql); for my $mail_code ('CAA05EFJ', undef) { $sth2->execute('D%', $mail_code, $mail_code); show_data($sth2); } FYI, here's where these approaches came from. I've been working with ADO.NET recently, and made some observations. In this data access model, data providers (analogous to DBDs in the DBI model, e.g. Oracle, Informix, MSSQL, etc.) are supposed to implement a family of classes, including one called a Command Builder. This class takes a table and automatically generates a triplet of commands that support row changes (INSERT, DELETE, and UPDATE). In order to help identify rows in the database with the original data, DELETE and UPDATE have WHERE clauses that include all columns, and for "nulls allowed" columns, must support one of the solutions above. The intent of the command triplet is to support optimistic concurrency. I could go on, but the main point is that the MSSQL provider uses the first approach in its Command Builder class. But the technique works on Informix too. The Informix provider actually uses the 2nd approach in its Command Builder class.
