Hello Daniel,

> I'm trying to have an existing perl program, that is using the DBI
> system for theoretical database interoperability, to use Firebird. I've
> discovered that, as a standard, this program executes statements such as,
>
> select * from table where pkey=''
>
> That apparently works with Mysql - doing such a select with a specified
> empty where clause will return the full table.

I can assure you that not even MySQL would do something as outrageous as that *. At least not current versions. It must be something in an application layer or library (DBI?). This kind of behaviour sometimes makes sense in the backend of a user interface, where the user may fill in filter terms. If he leaves a field open, the program assumes that any value for that field is OK (as opposed to only the empty string).

* (unless, of course, every record in the table has pkey=''. But that doesn't seem likely, since pkey is probably the primary key.)

BTW, "where pkey=''" is not an empty where clause. It's a perfectly valid search condition - provided that pkey is a text field.

> Firebird - probably more correctly -

You can leave out the 'probably'. There is only one correct behaviour here.

> does a comparison for empty/null and returns...nothing.

Firebird will look for '' (empty string) in this case, *not* for null, which is something different. If there are records where pkey = '', it will return them. Otherwise, it will return an empty result set.

> Without changing the perl script, is there a way I can have Firebird
> actually return the table using the above SQL?

No. You would have to intercept the SQL at some point and change it. If you want the whole table, leave out the WHERE clause and if that isn't possible, use a tautology, e.g. "...where 1=1" (or append " or 1=1" to the existing clause).


Good luck,
Paul Vinkenoog