On Fri, 2004-04-30 at 15:01, Chuck Fox wrote:
> [EMAIL PROTECTED] wrote:
>
> >If you are using Sybase then the default behavior is to allow comparison
> >to NULL. This is NOT ANSI SQL compliant, and one should use "where foo
> >is NULL" instead of "where foo = NULL" to be on the safe side.
> >
> >This behavior is controlled by the ANSINULL option - see the SET T-SQL
> >command.
> >
> >Michael
> >
> >
> Michael and others
>
> Thanks for the lesson in non-compliant behavior in one's favorite
> product. I commonly use this trick to solve the exact problem the
> original questioner posted and assumed the technique would be as
> universal as duct tape. Sadly, this is not the case. However, another
> poster, Alan, did resolve the issue in the Oracle fashion with a
> technique that was quite similar in nature.
>
> Try to add nvl function to both side of equal in the where clause.
>
> eg. where nvl(column, '~') = nvl(?, '~')
>
> Bad case of the heart was in the right place, but the head was in the
> wrong db.
:-)
Note that Sybase (via DBD::Sybase) will NOT accept a placeholder (?)
that does not directly refer to a column, so using something like
where isnull(?, '~') ...
won't work. Similarly the code snippet mentioned in the DBI docs to
handle NULL parameters
where foo = ? or (? is null and foo is null)
will NOT work, because Sybase's API can't figure out what type the
second ? refers to.
Ah the fun of writing portable SQL... :-)
Michael
--
Michael Peppler Data Migrations, Inc.
[EMAIL PROTECTED] http://www.peppler.org/
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short
or long term contract positions - http://www.peppler.org/resume.html
