BDB 2010, oracle XE,odac6.90.0.57, no search on null values?

[albourgz]

Hi, I have the following datas:

Code: Select all

create table cec.tabadresses(
ID                             NOT NULL NUMBER(8),
TELEPHONE                               VARCHAR2(25),
FAX                                     VARCHAR2(25),
GSM                                     VARCHAR2(25),
EMAIL                                   VARCHAR2(50),
ADDRESS                                 VARCHAR2(60),
ZIP                                     VARCHAR2(10),
CITY                                    VARCHAR2(50),
STATE                                   VARCHAR2(20),
COUNTRY                                 VARCHAR2(30),
SERVICE                                 VARCHAR2(30),
ADRI                                    VARCHAR2(30),
ATTN                                    VARCHAR2(30),
ADRNAME                                 VARCHAR2(50),
CYNAME                                  VARCHAR2(50),
primary key(id));

insert into cec.tabadresses (id,address,zip, city, country) 
values (1,'Bp 100','13881','GEMENOS CEDEX','FR');
commit;

In BCB:
Create a datamodule FDM, with a TOraSession and a TOraQuery named QSearchAddress with the following statement:

Code: Select all

SELECT ID 
FROM CEC.tabadresses
WHERE ADDRESS=:1
AND ZIP=:2
AND CITY=:3
AND STATE=:4
AND COUNTRY=:5
AND SERVICE=:6
AND ADRI=:7
AND ATTN=:8
AND ADRNAME=:9

Connect the TOraSession to the database, and put the following code in a button's OnClick event:

Code: Select all

    if (!FDM->QSearchAddress->Prepared)
        FDM->QSearchAddress->Prepare();
    FDM->QSearchAddress->Params->Items[0]->AsString="Bp 100";
    FDM->QSearchAddress->Params->Items[1]->AsString="13881";
    FDM->QSearchAddress->Params->Items[2]->AsString="GEMENOS CEDEX";
    FDM->QSearchAddress->Params->Items[3]->Clear();
    FDM->QSearchAddress->Params->Items[4]->AsString="FR";
    FDM->QSearchAddress->Params->Items[5]->Clear();
    FDM->QSearchAddress->Params->Items[6]->Clear();
    FDM->QSearchAddress->Params->Items[7]->Clear();
    FDM->QSearchAddress->Params->Items[8]->Clear();
    FDM->QSearchAddress->Active=true;
    if (!FDM->QSearchAddress->Eof)
        ShowMessage("found");
    FDM->QSearchAddress->Active=false;

When running, he doesn't find the line. I tried forcing parameters as strings with null values checked in TOraQuery, but it doesn't help.

Any hint?

[AlexP]

Hello,

Oracle specifics don't allow to compare varchar variables with NULL as VAR=NULL, the correct syntax is VAR IS NULL, so the correct syntax for your query is

SELECT ID
FROM CEC.tabadresses
WHERE ((ADDRESS is not null and ADDRESS=:1) or (ADDRESS is null and :1 is null))
AND ((ZIP is not null and ZIP=:2) or (ZIP is null and :2 is null))
AND ((CITY is not null and CITY=:3) or (CITY is null and :3 is null))
AND ((STATE is not null and STATE=:4) or (STATE is null and :4 is null))
AND ((COUNTRY is not null and COUNTRY=:5) or (COUNTRY is null and :5 is null))
AND ((SERVICE is not null and SERVICE=:6) or (SERVICE is null and :6 is null))
AND ((ADRI is not null and ADRI=:7) or (ADRI is null and :7 is null))
AND ((ATTN is not null and ATTN=:8) or (ATTN is null and :8 is null))
AND ((ADRNAME is not null and ADRNAME=:9) or (ADRNAME is null and :9 is null))