Running a report with user criteria

rbuser

Hi all,
This seems to be a pretty common topic, and I have gotten some good
information from this newsgroup however, I'm having a problem that does not
seem to be addressed:
I have called GetSQLObject (the method provided in the RB example) to obtain
a TdaSQL instance, then when I call certain (necessary) methods on that
instance, I get this error message:
"TdaMetaCache.GetFields: unable to find table TABLENAME in cache."

Here is a code snippet:

procedure TForm1.ApplyWhereClause(aRpt : TppReport; aWhereClause : String);
var
lSql : TdaSQL;
lTbl : TdaTable;
lCrit : TdaCriteria;
begin
if GetSQLObject(aRpt, lSql) then
begin
lTbl := lSql.GetTableForSQLAlias('TABLENAME');
if Assigned(lTbl) then
begin
// the following line causes the error message described above
lCrit := lSql.AddCriteriaField(lTbl, 'FIELDNAME', dacoEqual,
'fieldValue');
end;
end;
end;

Please tell me what it is I'm doing wrong.
Thanks!
Jef Duncan
Senior Enterprise Developer
Computer Guidance Corporation

digitalmetaphors

You need to clear the cache.

----------------------------------------
Tech Tip: Clearing the Meta Data Cache
----------------------------------------

There is a global meta data cache that the ReportBuilder uses to cache
information about the available database tables and fields. You can clear
the meta data cache using the following code.

uses
daMetaData;

begin
gMetaData.Clear;

end;

--
Tech Support mailto:support@digital-metaphors.com
Digital Metaphors http://www.digital-metaphors.com


Cheers,

Jim Bennett
Digital Metaphors

rbuser

I tried calling gMetaData.Clear in various locations throughout my code with
no success. No matter where I placed the call, I received the same error as
described below. Any other thoughts?
Thanks,
Jef

digitalmetaphors

Here is an example that does work to add to the SQL object's WHERE clause.
Test with this way of adding a field to the criteria field and see if that
helps. You shouldn't have to clear the meta data cache with this approach.

http://www.digital-metaphors.com/tips/ExtractSQLObject.zip


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Jim,
I can run this example and it works fine. Then, I change the data settings
to point to my database and query; the example runs, shows me the SQL in a
ShowMessage, which looks fine, then displays the designer. In the designer,
I can preview the report and it shows my data filtered, grouped and sorted
to reflect the criteria I set in code. When I go to the data tab and bring
up the Query Designer and select any tab except 'Tables' and 'SQL' I get the
same message described in my original post. FYI, I have one query which
joins two tables together.
Any other thoughts?
Thanks,
Jef

digitalmetaphors

After you change the datasettings to point to your database, you need to
call the gMetaData.Clear routine to clear the meta data cache. The meta data
has been populated with the demo's meta data values, so you need to clear it
so that it will populate itself with your database's table and field names.


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Sorry I didn't make this clear, but I changed my data settings at
design-time, then re-compiled and re-ran my application. So, theoretically,
when my application first runs, the meta data cache should be clear,
correct? What else could cause this problem?
Thanks,
Jef

digitalmetaphors

Yes, it should be clear when you change the datasettings at Delphi design
time and build the project. The dataviews that are in your report should be
configured to connect to the database that you are trying to connect to at
runtime, correct? The dataview stores the data settings in the TdaSQL
object. When are you creating these dataviews? What are their data settings
when you create them? Can you run DADE without trying to change the SQL
object?


Cheers,

Jim Bennett
Digital Metaphors

rbuser

I've had my head wrapped around another project for a while, but now I'm
back on this one...

A quick refresher on the basics:
RB 6.03, Delphi 4.02 Pro
I have several reports stored in a database. I use the designer to create /
modify my reports at runtime (no report design is done at design time). The
"dataviews" were created individually for each report using (on the Data
tab) File | New... | Query Wizard. My reports preview fine and appear okay
in the designer (except as noted below).

I would like you to disregard for the moment the subject of this thread. At
this point, I'm not trying to run a report with user data, I'm simply trying
to complete the creation of my data views.

My problem has at least a couple different symptoms:
1) When on the Data tab in the designer, in the "Query Designer", any
attempt to access Fields, Calcs, Group, Search, Sort or Link results in the
following message (with an error icon): "TdaMetaCache.GetFields: unable to
find table in cache." When accessing Tables or SQL,
the behavior is as expected.
2) Also on the Data tab, when I choose File | New... | Query Wizard, the
tables defined in my data dictionary do not appear (the "Available Tables"
list is empty), but I know my DataDictionary is defined/configured/setup
correctly.

The suggestions you mentioned earlier in this thread regarding the MetaData
cache have been of no help. Please help me find solutions for these two
problems; they're driving me nuts!

Thanks very much!
Jef Duncan

digitalmetaphors

The only way the meta data cache could be corrupt is if the datasettings
change after the meta data cache has been populated. It sounds as if there
is a problem wher eit thinks that it has generated the meta data cache, but
hasn't fully done so. We need to know what DADE plugin you are using as
well as the database.

What database are you using? Can you reproduce the problem with our end user
demo project? When you reproduce the problem with the demo, then tell us the
exact steps we need to take in order to reproduce the problem on our side.

Can you test with RB 7 to see if the problem goes away? That would be the
first test I would try to perform before researching it any further.


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Hmmm... what DADE plugin? I'm not sure I know how to answer but I'm
accessing my data through the BDE so am I using the DADE BDE plugin?

The database I'm using is IBM DB2 v7.2. I will attempt to recreate the
problem with the demo and let you know the results.

Can I test with RB7? No, not really because I haven't upgraded Delphi in a
while and am still on D4 (with no immediate plans to upgrade). AFAIK, RB7
does not "support" D4. Is this still correct?

Thanks and I'll get back to you with the results of the demo test.
Regards,
Jef

digitalmetaphors

Yes, if you are in the Data tab, (Data Access Development Environment -
DADE) and connecting via the BDE, then you are using the BDE DADE plugin.
The reason I wanted to know was to see if you are using one of the plugins
we maintain or a third party plugin which we haven't tested. Send us the
results of your test.

6.03 is the latest version of RB that supports Delphi 4.

RB 7 supports Delphi 5, 6 and 7.

Cheers,

Jim Bennett
Digital Metaphors