Board index » delphi » Blank values in Filter string of Delphi 5 TQuery object

Blank values in Filter string of Delphi 5 TQuery object

Hi all,

We were using blank values in a filtering string in Delphi 3 and worked
fine. The filtering looks like this:
blank:=''
ls_loc:='CMC'
qry.Filter := 'loc = ' + QuotedStr(ls_loc) + 'subloc = ' + QuotedStr(blank);
qry.filtered := True;

Rows from Oracle that has blank values for subloc used to be selected by
Delphi 3, but not any more by Delphi 5.

Has anybody seen this problem, what's the best way to upgrade Delphi 3 code
wth filter strings to Delphi 5.

Bhaskar

 

Re:Blank values in Filter string of Delphi 5 TQuery object


Well, much better idea than using filter property is to filter with "where"
clause.
Much faster, less traffic, less memory used on client.

I think in your filter expression is missing an "and":
'loc = ' + QuotedStr(ls_loc) + ' AND subloc = ' + QuotedStr(blank);
Also, if subloc is NULL, this comparison returns false, so you might check
with:
' AND (subloc = ' + QuotedStr(blank)+' OR subloc is null)';

--
----------------------
Regards
Robert Cerny
Remove both qwe when replying
email: robert.qwe.ce...@neosys.xrs.qwe.si

No questions via email, unless explicitly invited.

Quote
Bhaskar Rao wrote in message <83bu5v$e8...@forums.borland.com>...
>Hi all,

>We were using blank values in a filtering string in Delphi 3 and worked
>fine. The filtering looks like this:
>blank:=''
>ls_loc:='CMC'
>qry.Filter := 'loc = ' + QuotedStr(ls_loc) + 'subloc = ' +
QuotedStr(blank);
>qry.filtered := True;

>Rows from Oracle that has blank values for subloc used to be selected by
>Delphi 3, but not any more by Delphi 5.

>Has anybody seen this problem, what's the best way to upgrade Delphi 3 code
>wth filter strings to Delphi 5.

>Bhaskar

Re:Blank values in Filter string of Delphi 5 TQuery object


Robert,

Thanks for the reply.
Firstly, I already have a huge SQL query and there is no other choice than
filtering to display data in the report.

Secondly, I have AND in there, I missed putting in the mail.

Looks like Delphi 5 BDE is not translating Oracle nulls to Pascal blanks...

Thanks again
Bhaskar

Quote
Robert Cerny wrote in message <83c133$ef...@forums.borland.com>...
>Well, much better idea than using filter property is to filter with "where"
>clause.
>Much faster, less traffic, less memory used on client.

>I think in your filter expression is missing an "and":
>'loc = ' + QuotedStr(ls_loc) + ' AND subloc = ' + QuotedStr(blank);
>Also, if subloc is NULL, this comparison returns false, so you might check
>with:
>' AND (subloc = ' + QuotedStr(blank)+' OR subloc is null)';

>--
>----------------------
>Regards
>Robert Cerny
>Remove both qwe when replying
>email: robert.qwe.ce...@neosys.xrs.qwe.si

>No questions via email, unless explicitly invited.
>Bhaskar Rao wrote in message <83bu5v$e8...@forums.borland.com>...
>>Hi all,

>>We were using blank values in a filtering string in Delphi 3 and worked
>>fine. The filtering looks like this:
>>blank:=''
>>ls_loc:='CMC'
>>qry.Filter := 'loc = ' + QuotedStr(ls_loc) + 'subloc = ' +
>QuotedStr(blank);
>>qry.filtered := True;

>>Rows from Oracle that has blank values for subloc used to be selected by
>>Delphi 3, but not any more by Delphi 5.

>>Has anybody seen this problem, what's the best way to upgrade Delphi 3
code
>>wth filter strings to Delphi 5.

>>Bhaskar

Re:Blank values in Filter string of Delphi 5 TQuery object


Quote
Bhaskar Rao wrote in message <83duv3$m...@forums.borland.com>...
>Robert,

>Thanks for the reply.
>Firstly, I already have a huge SQL query and there is no other choice than
>filtering to display data in the report.

There surely is. You can create a view or stored proc.
Also, there are several advantages of using views:
1. Optimization plan is predetermined at view creation time - means it will
execute faster
2. you can change view logic and thus report without having to rebuild the
exe
3. instead of sending 15k sql statement to server, you send just simple
"select * from MyView..."
....

Quote

>Secondly, I have AND in there, I missed putting in the mail.

Well, happens.

Quote
>Looks like Delphi 5 BDE is not translating Oracle nulls to Pascal blanks...

Yes, and that's correct. All SQL based engines do this. Null is not the same
as blank, though on the screen (or report or...) the look the same - blank.

--
----------------------
Regards
Robert Cerny
Remove both qwe when replying
email: robert.qwe.ce...@neosys.xrs.qwe.si

No questions via email, unless explicitly invited.

Other Threads