Jeff Urlwin writes:
> This might be a bit off topic, but...
> [snip]
> >
> > ... WHERE (product_code = ? OR (? IS NULL AND
> > product_code IS NULL))
> >
> > and bind the same value to both placeholders.
> >
> > I understand the reasoning for this, and why it's the only way for
> > database servers that don't understand "= null".
> >
> > However this syntax does NOT work with DBD::Sybase because of the way
> > OpenClient handles the prepare. The issue is the "? is null" part:
> > neither the Sybase client libs, nor the server can determine what
> > datatype the placeholder is supposed to be, and I see no way to add a
> > directive to tell the server that this item is of the same type as
> > "product_code".
>
> Does any database understand ? is null or ? = 3 or ? = 'foo', given that ?
> must be a column/function or some other name important for the query parsing
> (i.e. not a "value" per se)?
Well - according to the DBI docs they do.
And I can definitely write similar code in Transact SQL, for example
in a stored proc:
create proc foo (@p varchar)
as
select ... from sometable where (name = @p or (@p is null and name is
null))
etc...
> > Sybase, on the other hand, understands "= null".
> >
>
> That's handy.
Indeed.
> > I'll document this in the DBD::Sybase docs, but I just wanted to flag
> > the fact that the method documented in the DBI docs is unfortunately
> > not portable to Sybase (and *probably* not to ODBC when using a
> > Sybase). I don't know if ODBC w/MS-SQL handles this correctly or not.
>
> Which part, the = null or the ? is null?
>
> With SQL Server 2000 and DBD::ODBC,
> col_a = null returns no rows
> col_a is null returns 1 row
> ? is null (passing col_a) returns 0 rows
Ah. I wonder how the "? is null" bit is handled at the protocol
level...
Michael
--
Michael Peppler - Data Migrations Inc. - http://www.mbay.net/~mpeppler
[EMAIL PROTECTED] - [EMAIL PROTECTED]
International Sybase User Group - http://www.isug.com
