Query use in pipeline?
Hello,
Using RB 17.03 in Delphi 10.1. I am connecting my reports to a
DBPipeline (SQLite) which contains a query. This seems to work better
for me than connecting to the DB in the report designer and running the
query there. Just because I want some calculated fields that need lots
of freedom (various variable and data access) to calculate in the code
of main program.
That works, but gives a background to the question. I have a simple
query that includes all the fields (regular and calculated) I need for
about 20 or more reports. Now in each of those reports, I access the
data detail in the designer. BEFORE I run the report in my delphi form,
I re-run the same query - BUT depending on the report, with different
sorts and filters than the original query used to set up the design.
This WORKS, but my question is whether this is SAFE and efficient??
The reason I do it this way is that I need only one query in my
datamodule to maintain, and then depending on which of many reports I
run, I can change the filtration (WHERE clauses) and sort orders by
re-stating the SQL text.
Related question to the efficiency part - speed is not too bad, but am
I running the entire query an extra time during run time? Would it help
speed to add a WHERE clause to the query in the data module ensuring
hardly any data is selected, since the real data selection occurs just
before the report is run?
Scott
Using RB 17.03 in Delphi 10.1. I am connecting my reports to a
DBPipeline (SQLite) which contains a query. This seems to work better
for me than connecting to the DB in the report designer and running the
query there. Just because I want some calculated fields that need lots
of freedom (various variable and data access) to calculate in the code
of main program.
That works, but gives a background to the question. I have a simple
query that includes all the fields (regular and calculated) I need for
about 20 or more reports. Now in each of those reports, I access the
data detail in the designer. BEFORE I run the report in my delphi form,
I re-run the same query - BUT depending on the report, with different
sorts and filters than the original query used to set up the design.
This WORKS, but my question is whether this is SAFE and efficient??
The reason I do it this way is that I need only one query in my
datamodule to maintain, and then depending on which of many reports I
run, I can change the filtration (WHERE clauses) and sort orders by
re-stating the SQL text.
Related question to the efficiency part - speed is not too bad, but am
I running the entire query an extra time during run time? Would it help
speed to add a WHERE clause to the query in the data module ensuring
hardly any data is selected, since the real data selection occurs just
before the report is run?
Scott
This discussion has been closed.
Comments
The simplest solution is to generate the SQL prior to loading the report.
For the 'design SQL' you can limit the number of records returned in a
couple of different ways.
a. Dummy search condition.
In the RB Data environment, when we want the query to return the field
information for designing, we add a dummy condition to the where clause like
this...
Where 'c' <> 'c'
b. SQLite Limit clause.
The RB Query Designer also provides an option to specify a limit on the
number of records. The SQL syntax varies by database engine, but for SQLite
it is the 'Limit' clause.
Then when you want to run report for real, you can modify the SQL prior to
calling Report.Print. (i.e. Remove the dummy search condition or remove the
Limit clause.
Best regards,
-
Nard Moseley
Digital Metaphors
www.digital-metaphors.com
Best regards,
Nard Moseley
Digital Metaphors
www.digital-metaphors.com