Mailing-List: contact empire-db-user-help@incubator.apache.org; run by ezmlm
Precedence: bulk
Reply-To: empire-db-user@incubator.apache.org
Received-SPF: pass (nike.apache.org: local policy)
Content-class: urn:content-classes:message
Subject: re: Add one table from a different schema
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Date: Fri, 13 Jan 2012 12:31:09 +0100
Message-ID: <B297D4737108A0459B66C3A85BB7F90133B984@kn.esteam.de>
Thread-Topic: re: Add one table from a different schema
Thread-Index: AczR5tckH0fTBI2BShW2rY6vxwyiaw==
From: =?iso-8859-1?Q?Rainer_D=F6bele?= <doebele@esteam.de>
To: <empire-db-user@incubator.apache.org>

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:Alain.Becam@embl.de]
> to: empire-db-user@incubator.apache.org
> re: Re: Add one table from a different schema
>=20
> 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:
>=20
> 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)
>=20
>=20
> 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.
>=20
> 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 :) ).
>=20
> 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.
>=20
> Thanks for the help,
>      Alain
>=20
>=20
> On 12.01.2012 20:58, Rainer D=F6bele 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 =3D
> >> 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
>=20
> --
> ----------------------------
>        Alain Becam, PhD
> IT Services, EMBL Heidelberg
>   mailto:Alain.Becam@embl.de
>   Tel +49 (0) 6221 387 8593
> ----------------------------