Hello Rainer,
I actually tried that ;), and got a NullPointerException while
doing a getSelect, so it seems that the DBCommand has not been created
right. So I guess I am missing something else here: when I create my
DBCommand, it is actually a method of the DBDatabase:
dbPerson.createCommand();
So here I have two DBDatabase used in my request, but I need to create
the command only once. Here is the relevant stack trace of the exception
in the getSelect:
java.lang.NullPointerException: null
at org.apache.empire.db.DBColumn.addSQL(DBColumn.java:159)
at
org.apache.empire.db.DBCommandExpr.addListExpr(DBCommandExpr.java:366)
at
org.apache.empire.db.oracle.DBCommandOracle.getSelect(DBCommandOracle.java:137)
For the query, I used a query that works within on DBDatabase, and
simply tried to use another DBDatabase table. In the "external" schema,
I set the schema in the constructor.
I completely agree that it makes more sense to define table of a
different schema in another DBDatabase (and that the DBDatabase name is
slightly misleading so :) ).
Then a DBLink is not exactly adapted, I am well using different schemas
within the same database, I am not linking two DBs. Then using a view
works perfectly (and is rather clean thanks to empireDB's approach), and
might actually be more adapted to my problem now (I need to do that for
temporary legacy issue). But I am still curious to get the first
solution working.
Thanks for the help,
Alain
On 12.01.2012 20:58, Rainer Döbele wrote:
Hello Alain,
the solution is simple: for each schema you need a separate database object
i.e. in your case you need two classes derived from DBDatabase that define the
corresponding table(s).
The constructor of DBDatabase allows to supply a schema name.
When you join, the schema name will always be prepended.
IMO it does not make sense to define tables of a different schema in a single
DBDatabase (however I must admit, that the class therefore should rather be
called DBSchema than DBDatabase).
If you use Oracle it is even possible to work with Database Links like that.
The schema (in Oracle the user) is prepended, the Link is appended to the table
or view name.
Hope you found my answer helpful.
Regards,
Rainer
from: Alain Becam [mailto:[email protected]]
to: [email protected]
re: Add one table from a different schema
Hello,
I want to do something like that:
SELECT t10.name
FROM schema2.person t10 INNER JOIN personInGroup t11 ON t11.ID =
t10.group_ID WHERE t11.ID LIKE 'C12'
Where the person table in in another schema where I have the "SELECT"
rights. And I cannot get it to work. I could use a view, but it should
be possible without. I have seen in DBDatabase the setSchema, but it
looks global. I was expecting a way to define that in the table
definition (something like public tableName(DBDatabase theDB){
super("nameOfTable","nameOfSchema",theDB);), but it does not look
possible. So I guess I am totally in the wrong here :)
Also, I asked some time ago another simple questions, maybe the answers
should be added in the wiki? I could actually do a part of it if you'd
like (EmpireDB for dummies :) ).
Thank for your help,
Alain
--
----------------------------
Alain Becam, PhD
IT Services, EMBL Heidelberg
mailto:[email protected]
Tel +49 (0) 6221 387 8593
----------------------------
