Hello firebird-net-provider,
[vb.net]
_command.CommandText = "SELECT ID FROM EMPLOYES WHERE (BIRTHDAY=@B1 OR (BIRTHDAY IS NULL AND @B2 IS NULL))"
_command.Parameters.Add("@ID", 1)
_command.Parameters.Add("@B1", DBNull.Value)
_command.Parameters.Add("@B2", DBNull.Value)
Dim __id As Integer = _command.ExecuteScalar()
and in execution gets exception: Data type unknown
all right, because fbserver unknown type of @B2 parameter (it's he
strange feautre of fbserver).
but if rewrite on this:
_command.CommandText = "SELECT ID FROM EMPLOYES WHERE " & _
"BIRTHDAY=@B1 OR " & _
"(BIRTHDAY IS NULL AND COALESCE(@B2, CAST(NULL AS DATE)) IS NULL)"
all work :)
When field is not null we may generate whereClausule2 template (as
pk field).
But when field nullable -- you generate whereClausule1, but this
incorrect (when parameter not null -- in result set returned
records and equal parameter value and field IS NULL. it's
incorrect. and as result we got spare record).
whereClausule1 = "(({0} IS NULL) OR ({0} = ?))";
whereClausule2 = "({0} = ?)";
In SqlClient use template ({0}=? OR ({0} IS NULL AND ? IS NULL)),
but it's not work on fbserver. But if we work on firebird 1.5 we
can release correct work thru, for example COALESCE keyword. When
all be works fine! (May be CASE works too, but need 3 params, but
not 2).
I think that in server this feature will else long, but we already
can get correct work, if using coalesce keyword, on FB1.5 at least
once!
--
Best regards,
Dmitry mailto:fd...@ua...
|
