Handle parameters with null value

[lauchuen]

hi,

the database schema

Code: Select all

create table items (
  id serial,
  name varchar(100),
  mfg_date date,
);
INSERT INTO items (name) VALUES ('ABC');

Code: Select all

pgQuery.Close;
pgQuery.SQL.Text := 'SELECT * FROM items WHERE name = :name AND mfg_date = :mfg_date';
pgQuery.ParamByName('name').Value := 'ABC';
pgQuery.ParamByName('mfg_date').Value := null;
pgQuery.ParamByName('mfg_date').Bound := True;
pgQuery.Open;

Result: No Record Found. (Wrong)

Even i turn on the transform_null_equals inside postgresql, the result also wrong. So, what is the right approach to handle parameters with null values?

[AlexP]

Hello,

In the PostgreSQL documentation, it is specified, that even when the transform_null_equals option is enabled, the "=" operator can be used for comparison with NULL only at explicit comparison form = NULL. When using parametrized queries, this option doesn't work.
From the documentation: Note that this option only affects the exact form = NULL.
To solve this issue, you should either rewrite this query in the following way:

Code: Select all

SELECT * FROM items WHERE name = :name AND (mfg_date = :mfg_date or (mfg_date Is Null and :mfg_date is null))

or use the second protocol

Code: Select all

PgConnection1.ProtocolVersion := pv20;

In this case, the parameters will be inserted into the query