Running a query over multiple Databases?

[Christian Mittendorf <ch...@freenet.de>]

Hi!

I've found the mutiple database example on cayennes homepage:

http://cwiki.apache.org/CAY/multiple-databases-example.html

That example is a pretty good resemblance of our current problem.

What we would like to do is to run a single query over both  
databases, like i.e.

SelectQuery select = new SelectQuery(Person.class);
Expression expression = ExpressionFactory.matchExp 
(Person.DEPARTMENT_PROPERTY +
	"." + Department.NAME_PROPERTY, "IT Department");

But such a query runs into an exception with the following error  
message:

Base table or view not found,  message from server: "Table 'foo.bar'  
doesn't exist"

Are such queries supported by Cayenne or do we have to split the task  
into
two single queries?

Christian

Re: Running a query over multiple Databases?

[Ahmed Mohombe <am...@yahoo.com>]

> Doing a join across DB's generally doesn't work 
What about "Clustered JDBC" ?
http://c-jdbc.objectweb.org/

Wouldn't be this a solution?

Thanks in advance,

Ahmed.

Re: Running a query over multiple Databases?

[Mike Kienenberger <mk...@gmail.com>]

Certain databases on the same server will also let you do this, if
configured correctly.

For instance, if all of your databases are on the same Oracle database
server, and you've granted appropriate privileges to your current
user, it'll work.    Mind you, this doesn't help a great deal :-)

On 3/16/07, Andrus Adamchik <an...@objectstyle.org> wrote:
> Doing a join across DB's generally doesn't work (although it may
> *appear* to work in simpler cases of matching of FK), as it will
> require Cayenne to do in memory cartesian product processing. So
> you'll have to do it manually.
>
> (actually sounds like an interesting improvement ... although
> probably low priority to me at least)
>
> Andrus
>
>
> On Mar 16, 2007, at 5:26 PM, Christian Mittendorf wrote:
> > Hi!
> >
> > I've found the mutiple database example on cayennes homepage:
> >
> > http://cwiki.apache.org/CAY/multiple-databases-example.html
> >
> > That example is a pretty good resemblance of our current problem.
> >
> > What we would like to do is to run a single query over both
> > databases, like i.e.
> >
> > SelectQuery select = new SelectQuery(Person.class);
> > Expression expression = ExpressionFactory.matchExp
> > (Person.DEPARTMENT_PROPERTY +
> >       "." + Department.NAME_PROPERTY, "IT Department");
> >
> > But such a query runs into an exception with the following error
> > message:
> >
> > Base table or view not found,  message from server: "Table
> > 'foo.bar' doesn't exist"
> >
> > Are such queries supported by Cayenne or do we have to split the
> > task into
> > two single queries?
> >
> > Christian
> >
>
>

Re: Running a query over multiple Databases?

[Andrus Adamchik <an...@objectstyle.org>]

Doing a join across DB's generally doesn't work (although it may  
*appear* to work in simpler cases of matching of FK), as it will  
require Cayenne to do in memory cartesian product processing. So  
you'll have to do it manually.

(actually sounds like an interesting improvement ... although  
probably low priority to me at least)

Andrus


On Mar 16, 2007, at 5:26 PM, Christian Mittendorf wrote:
> Hi!
>
> I've found the mutiple database example on cayennes homepage:
>
> http://cwiki.apache.org/CAY/multiple-databases-example.html
>
> That example is a pretty good resemblance of our current problem.
>
> What we would like to do is to run a single query over both  
> databases, like i.e.
>
> SelectQuery select = new SelectQuery(Person.class);
> Expression expression = ExpressionFactory.matchExp 
> (Person.DEPARTMENT_PROPERTY +
> 	"." + Department.NAME_PROPERTY, "IT Department");
>
> But such a query runs into an exception with the following error  
> message:
>
> Base table or view not found,  message from server: "Table  
> 'foo.bar' doesn't exist"
>
> Are such queries supported by Cayenne or do we have to split the  
> task into
> two single queries?
>
> Christian
>