Oracle schemas and Report Builder designer.

rbuser

Hello everyone,

We are implementing the Report Builder designer in our application. It works
fine, though when connecting to an Oracle database we encounter a
difficulty.

Our application, which includes the Report Builder designer, connects to an
Oracle database with a specified username. This user might have read/write
rights on several schemas in the Oracle database. However, our application
is only interested in the data within the schema of this specific user. What
we experience is that, in the query designer of the report builder designer,
a list shows up of all tables of all schemas this specific user has access
rights for. We would like to see this being filtered for just his own
schema. Tables in other schemas the user may has access to, are not relevant
for our application.
We connect using a Oracle OLE DB provider to the Oracle database. We are
using Report Builder 6 (it's old, I know).

Does anyone have a suggestion on how to solve this problem?

Thanks,

Arnaud de Klerk
IKM Engineering B.V.
Gouda
The Netherlands

nardmoseley

1. For end-user examples using Oracle, check out RBuilder\Demos\EndUser
Databases\Oracle. There are examples there for BDE, dbExpress, and DOA
(Direct Oracle Access).

If you are using Oracle and Delphi, I recommend that evaluate using Direct
Oracle Access from www.AllroundAutomations.nl. We have no affiliation, but
based on feeback from customers, it is the best solution for using Delphi
and Oracle.

2. There are a couple of options for limiting tables available to the user.

a. Use the TppDataDictionary component. The DataDictionary enables a
developer to define user friendly table and field aliases and to optionally
pre-define join conditions. For Oracle, you probably need to set
DataDictionary.UseTableOwnerName to true.

b. You could modify the source code for the DADE plug-in that you are using.
For BDE, its daDBBDE.pas, for dbExpress daDBExpress.pas, for DOA, daDOA.pas.
There is a GetTableNames method that retrieves the table names from the
database.



--
Nard Moseley
Digital Metaphors Corporation
http://www.digital-metaphors.com

rbuser

By coincidence I ran into the same problem today, only I'm using ODAC from
CRLab with the NET option (I do not like the overhead in installing and
maintaining all those external components/dll's etc. on all those clients).
I think however I've found a solution for my and possibly your problem. It
certainly means modifying the code in the GetTableNames :

1. first you need to establish what your current schema is:
select TRIM(sys_context(''USERENV'',''CURRENT_SCHEMA'')) as Schema from dual
will do this in Oracle 8.1.5 and on

2. Secondly you need to modify the tablenames retrieve SQL statement:
'select owner, object_name ' +
'from all_objects ' +
'where (object_type = ''TABLE'' ' +
'or object_type = ''VIEW'') ' +
'and
owner='''+lContext.FieldByName('SCHEMA').AsString+''''

3. Change the name of the table that is stored in the string list (the
original code always stores the schema name):
lsTableName := lQuery.Fields[1].AsString;

If you use the Ado connection you can rebuild the complete GetTableNames
with the DOA example as a template (the one from ODAC is almost identical).

About the dictionary:
- useTableOwnerName has no influence on the generated SQL whatsoever (see
3.)
- when deciding to stop using the Dictionary make sure you not only uncheck
the Use Dictionary checkbox but you should also clear the dictionary name,
otherwise it still uses the dictionary (or caches the result).

hth,
Martin

nardmoseley

Thanks for supplying those tips and sharing your Oracle expertise


--
Nard Moseley
Digital Metaphors Corporation
http://www.digital-metaphors.com

rbuser

> Thanks for supplying those tips and sharing your Oracle expertise
You are welcome, now that I've got things running I'm somewhat dissapointed
with what I got.

I'm currently looking at EMS Querybuilder as an alternative for the
querybuilding part but I haven't figured out how to use this (or any other
alternative) and still have the ability to use the Autosearch Criteria. I
need them because I want the reports to be able to run in the Client/Server
as wel as the Web server (otherwise I would create my own inout routines
just as I'm doing now with 6.0x).

Any suggestions would be welcome.

regards,
Martin

nardmoseley

1. You can use the new Report.OnInitailizeParameters event to define the
autosearch parameters that you want to be displayed. Then in the
Report.OnGetAutoSearchParameters event you can access the values entered by
the user and apply them to your custom query.

2. The architecture is open to creating custom dataview classes. And these
can optionally have custom data designers. See RBuilder\Demos\EndUser\Custom
DataViews.





--
Nard Moseley
Digital Metaphors Corporation
http://www.digital-metaphors.com