Changing DADE query in code - adding fields

rbuser

Delphi 6, RB 9.03

We have made some substantial database structure changes in the current
version of our software and are writing a conversion utility to ensure our
customers' user-defined reports (written using ReportBuilder) will continue
to work when this is released.

Thanks to the well-structured objects behind the scenes in ReportBuilder,
we've easily been able to load the rtm, change field pipelines on the
report, add and amend RAP code and amend the SQL in the DADE query data
pipelines. It is this last item we are having one problem with though.

We have successfully added tables, changed table names, changed field names,
removed and added joins and all sorts of other things to the DADE query,
using the TdaSQLBuilder classes. However, one thing we're trying to do is
add 2 new fields to the query.

We perform the following steps:

1. Get the Query data view:
{get the datamodule}
lDataModule := daGetDataModule(aReport);

{get the query dataview}
if (lDataModule <> nil) then
aDataView := TdaQueryDataView(...);

2. Get the SQL:

lSQL := lQueryDataView.SQL;

3. Add the field to the query. Note: the table this field is in already
exists in the query:

lSQL.AddSelectField(...);

Now this successfully adds the field to the query, if we save & open the
report, the new field is listed in the Query Designer, Fields view, and also
in the SQL text view. However, on the DADE pipeline itself, the field is not
listed and we can't therefore use it to link to another DADE pipeline.

We've noticed the difference between this field in the .rtm and others that
are correct is that this new field doesn't have a corresponding TppField
object. We've dabbled with the daFieldToppField method and the
Pipeline.AddField(pField) method but with no joy.

What are we missing?

Thanks,
Jason.

Jason Sweby, Software Development Manager
Carval Computing Limited

Tel: 01752 764290
jason.sweby@carval.co.uk
www.carval.co.uk

nardmoseley

- make sure that have a statement to free the SQLBuilder

lSQLBuilder.Free;

- you can also try adding a statement to explicitly call ApplyUpdates prior
to Free

lSQLBuilder.ApplyUpdates;
lSQLBuilder.Free;


--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com

rbuser

Nard,

Thanks for the response. It turns out we're not actually using the
SQLBuilder class itself, we're manually getting the dataview, it's query,
etc. I'm assuming this is what the SQLBuilder class was introduced for in
v9, to wrap up all of this for us. We're coming from a pre-v9 background
here though hence doing it the old way (daGetDataModule, etc).

As I said, we're able to add fields to the query (lSQL.AddSelectField), they
just don't seem to appear in the pipeline overview, bnecause no ppField is
created for them. Is this not automatic when you call AddSelectField to a
DADE query pipeline?

Jason Sweby, Software Development Manager
Carval Computing Limited

nardmoseley

I would convert the report to use SQLBuilder if at all possible.

I think what is happening is that you are modifying the TdaSQL object (or
more likely a copy of it), but the TdaQueryDataView does not know about it.
If you get the TdaSQL from the query dataview and modify it, you need to
assign it back. You can also try calling QueryDataView.RefreshQuery.

The QueryDataView has an internal method called to SQLToDataView. This
method gets the SQL statement from the TdaSQL object, assigns it the query
dataset, creates the datapipeline field objects etc.



--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com

rbuser

That's terrific thanks Nard, helpful as always.

We have started to use the SQLBuilder stuff now and we're getting further
with it, so that's good.

You're right about assigning the TdaSQL back to the TdaQueryDataView, we
realised we weren't doing that although it did cause every table and field
to disappear from the pipeline at one point! I'm not sure how the developer
who is working on this got around that, if he ever did. But as I said, we
having more success with the SQLBuilder.

We're now stuck getting the links to work between the 3 pipelines we have in
the DADE. We've actually removed any that were there before we start
(SQLBuilder.SQL.ClearLinks), because they seemed to cause a problem when
trying to add and remove fields and joins to the child pipelines. Now
however we can't get them to add back. How can we do this?

Thanks again for your excellent support. We've been with RB for approaching
6 years now for this very reason: great support and extremely well-designed
classes behind the reports.

Jason.

rbuser

We've answered our own question. We found SQLBuilder.LinkBroker and
CreateLink.

Jason.

nardmoseley

Ok, great.

For a future release, perhaps we can enhance TdaSQLBuilder to provide direct
method calls for managing the links. I will add that to the list.



--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com