Firebird 2.5 and SQL_NULL

[Fabrice]

Hello,

In Firebird 2.5, now avalaible for download, there is a very interesting SQL_NULL handling to avoid "Data type unknown" error.
To handle query with ":parameters is null" :

select * from customer
where ( (field<:param1) Or (:param1 is null))

From Firebird-2.5.0-ReleaseNotes.pdf :

Addition of SQL_NULL Constant
New SQL_NULL constant was introduced to enable the predication OR ? IS NULL to be recognised and
processed with the expected outcome and without engendering the “Data type unknown” exception. This affects
how the XSQLVAR structures are populated for such queries. For information, refer to the topic SOME_COL
= ? OR ? IS NULL Predication in the DML chapter.

Does and how IBdac handle this ?
Something like this ? :

SELECT * from customers
WHERE (name starting with :name or :name is NULL) and (firstname = :firstname or :firstname is null) and (CREATIONDATE < :creation or :creation is NULL)

MyQuery.Params.ParamByName('name').isnull := True; // <= is null handle
MyQuery.Params.ParamByName('firstname').asstring := 'Jack';
MyQuery.Params.ParamByName('creation').isnull := True; // <= is null handle
MyQuery.Open;

This is really useful to handle search with field which contains null value.




Best regards,

[AndreyZ]

Hello,

This feature is supported by the server and if you are working with Firebird 2.5 you can use it. Here is an example:

Code: Select all

SELECT * from customers 
WHERE (name starting with :name or :name is NULL) and (firstname = :firstname or :firstname is null) and (CREATIONDATE < :creation or :creation is NULL)

IBCQuery.Params.ParamByName('name').Value := Null; 
IBCQuery.Params.ParamByName('firstname').AsString := 'Jack'; 
IBCQuery.Params.ParamByName('creation').Value := Null;
IBCQuery.Open;