Hello Alain, I have investigated you problem and I very much assume, that you missed to open the second database. You need to call db.open(...) for each database you are using since this will attach the driver.
However I must admit, that it is a mistake that you are getting a NullPointerException here. Instead you should get a DatabaseNotOpenException. I will change that. Thanks and regards Rainer > from: Alain Becam [mailto:[email protected]] > to: [email protected] > re: Re: Add one table from a different schema > > 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(DBCommandOra > cle.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 > ----------------------------
