incubator-empire-db-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Alain Becam <Alain.Be...@embl.de>
Subject Re: Add one table from a different schema
Date Fri, 13 Jan 2012 09:57:22 GMT
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:Alain.Becam@embl.de]
>> to: empire-db-user@incubator.apache.org
>> 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:Alain.Becam@embl.de
  Tel +49 (0) 6221 387 8593
----------------------------


Mime
View raw message