Hi Jens, here are my anwsers:
> from: Jens Breitenstein [mailto:[email protected]] > to: [email protected] > re: SCHEMA > > Hi all! > > I am dealing with several hundreds of tables spread over multiple > schema's in one database. > As "schema" is an attribute on the DBDatabase itself I worked around it > by creating several empire DBDatabase objects, each containing the > required tables per schema (correct?). Yep. > This works as expected but unfortunately I can not join tables across > schemas, means across empire DBDatabases while it is perfectly working > SQL. The question is why. If the SQL is correct, then the reason might be, that access privileges in your DBMS are wrong. Can you post some of your statements? > I tried to set "schema" attribute to "null" and including the schema in > the tables name e.g. SCHEMA.TABLENAME but empire detects the "." as > "illegal" character and the name is quoted, thus the final name becomes > "SCHEMA.TABLE" which is rejected by oracle (table not found). > Did I miss something? Well, you should not set the schema to null - cause that is what is is for. And if it is not working properly we need to fix it. Actually I use cross schema joins myself a lot with Oracle, so I doubt there is a problem with Empire-db. Please check your table grants - or simply give the user (or schema) that your connecting with the "IMPORT_FULL_DATABASE" role. > > If not: a possible workaround might be: > > Add a (optional) schema attribute on the DBTable. Due to the fact it is > a new attribute and two new methods (setter/getter to the attribute) > existing code is not affected. > > When generating SQL the existing logic has to be changed to something > like this: > > String schema = null; > if (null != table.getSchema()) { > schema = table.getSchema() > } else { > if (null != database.getSchema()) { > schema = database.getSchema() > } > } > > // continue with prepending schema before "." + tablename // > addFrom()... > Doesn't make sense to me. In fact the schema name is a property of the Database (which is in fact the schema) and not a property of each individual table within a database. > > What you think? > > (Again) > > Jens
