Problems with the SearchCriteria function

rbuser

Delphi7 and RB Enterprise10.07 build 2.

Hello,

I'm running a report that looks at an Paradox table field of type
"Graphic". If the field is empty the report indicates that a users picture
is missing. This works fine in the standalone report designer that queries
all the records in the users table.

I am now trying to run the report in my custom reporting application
that allows end users to apply some form of filtering, EG only look for
users with no picture who's surnames start with a "B" etc.

To do this I use code similar to this:

theCtrl := TppReport.Create (nil) ;
lSQLBuilder := TdaSQLBuilder.Create (theCtrl) ;

aCriteria := lSQLBuilder.SearchCriteria.Add (theFilter.Table,
theFilter.Field,
theFilter.Operator,
theFilter.Value)
;


In this example, theFilter.Table = "Users", theFilter.Field = "Picture",
theFilter.Operator = "Is Not Null" and theFilter.Value is empty.

Unfortunately the "SearchCriteria.Add" function returns nil. Could this
be because the Field is a "Graphic" type ? The function works fine with
string fields. If I run the following query then I get a result set so I
can't see why the "SearchCriteria.Add" function has a problem.

Select * from Users u where u.'Picture' Is Not Null

Any ideas ?

Ian

rbuser

I've looked into this a bit further and tried a similar task but this time
against a field of type "Memo". This works fine so I guess its got a problem
with "Graphic".

I traced the code into a function called "TdaSQLFieldList.IsNamedField" in
daSQLBuilder.pas. This seems to be checking my supplied field name against
those in a "TdaField" object. I noticed that my Graphics field called
"Picture" didn't exist in the list. Is this because Graphic fields are
excluded ? Can this be overridden ?

Ian

nicocizik

Hi Ian,

Thanks, yes, ReportBuilder is not currently designed to include blob or
graphic fields as searchable fields. This is necessary if an end-user were
to try to search on a blob field with a comparison operator however you have
exposed a limitation in that the operator may be "Is Null" or "Is Not Null".

Unfortunately looking at our code this is not an easy enhancement. We now
have this marked down as something to seriously look at for a later release
of ReportBuilder. In the mean time I'm afraid you will have to edit the SQL
manually to search on a blob/graphic field.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hi Nico,

Thanks for the quick reply. A couple of questions for you. Would you be
able to place a timescale on when a fix could be made available ? Are we
talking weeks months or years ? I'm hoping to ship my application soon but
could delay it if a fix was one or two months away.

Secondly, when you say "...you will have to edit the SQL manually to
search on a blob/graphic field". Can I do this by using different Report
Builder functions (something similar to "SearchCriteria") or is there
somewhere I can place my hand crafted SQL prior to calling "Print" on the
TppReport control ?

Thanks,

Ian

nicocizik

Hi Ian,

After looking at the source, it seems like it will be a fairly complex
enhancement to allow searching on blobs and graphics based on which operator
is being used. I would not count on this being added in the near future.
If you look at the daCopyList routine in the daSQL.pas file you will see
where the list is limited...

not(lField.DataType in dacaBLOBs)

Simply removing this code does allow you to search on the graphic field
however it brings up numerous other issues including allowing the end user
to search and link on blob and graphic fields which should not be allowed.
This is why a more complex solution is needed.

Yes you can add custom SQL code to the SQL object before the report prints
using code similar to the code below.

uses
daSQL;
---

var
lDataView: TdaQueryDataView;
lSQL: TdaSQL;
begin
lDataView := TdaQueryDataView(ppReport1.DataPipeline.DataView);
lSQL := lDataView.SQL;
lSQL.EditSQLAsText := True;
lSQL.SQLText.Text := 'Select * from clients';

ppReport1.Print;
end;



--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com