Cayenne Object Relational Mapping User Discussions

Gary Jarrel | 5 Jun 2012 08:08

Picon

Cross Database Queries

Hi Guys,

Just started a new project using Cayenne 3.1M3 set up some test code
to see whether I can get the cross database queries to work, but it
seems that every time I run a query across the 2 database it tries to
use the 1 database and hence crashes out with:

com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name
'dbo.client_contracts'.

The client_contracts table is located in the second database which it
does not seem to use.

I have 2 datamaps, 2 data nodes and a single cayenne-xxx.xml file. A
copy of the file is below, and I am certain that I have done this
before without too much hassles at all.

<domain project-version="6">
	<map name="jay"/>
	<map name="jayExtranet"/>

	<node name="jayExtranetNode"
		 factory="org.apache.cayenne.configuration.server.JNDIDataSourceFactory"
		 parameters="java:comp/env/jdbc/jayExtranet"
		 schema-update-strategy="org.apache.cayenne.access.dbsync.SkipSchemaUpdateStrategy">
		<map-ref name="jayExtranet"/>
	</node>
	<node name="jayNode"
		 factory="org.apache.cayenne.configuration.server.JNDIDataSourceFactory"
		 parameters="java:comp/env/jdbc/jay"

(Continue reading)

Bryan Lewis | 5 Jun 2012 13:14

Picon

Re: Cross Database Queries

I got cross-database queries working just recently, with a boost from
Michael Gentry.  I'd guess the culprit is that your nodes are missing the
datasource.  For example:

<node name="DataNode"
         datasource="DataNode.driver.xml"   // tell the node how to connect
to the database

In fact I'm kinda surprised it works at all.  The modeler won't load the
file.  Maybe your note didn't include an exact copy.

On Tue, Jun 5, 2012 at 2:08 AM, Gary Jarrel <garyjarrel <at> gmail.com> wrote:

> Hi Guys,
>
> Just started a new project using Cayenne 3.1M3 set up some test code
> to see whether I can get the cross database queries to work, but it
> seems that every time I run a query across the 2 database it tries to
> use the 1 database and hence crashes out with:
>
> com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name
> 'dbo.client_contracts'.
>
> The client_contracts table is located in the second database which it
> does not seem to use.
>
> I have 2 datamaps, 2 data nodes and a single cayenne-xxx.xml file. A
> copy of the file is below, and I am certain that I have done this
> before without too much hassles at all.
>

(Continue reading)

Gary Jarrel | 5 Jun 2012 13:28

Picon

Re: Cross Database Queries

Hi Bryan,

Thank you for the response.

When pasting I omitted the XML declaration at the top: <?xml
version="1.0" encoding="utf-8"?> perhaps that is why it did not load?

I am using container defined JNDI data sources, hence there is no
driver declaration. When the application starts it actually connected
to both data sources perfectly fine and I get the following log
entries:

Connecting. JNDI path: java:comp/env/jdbc/jayExtranet
+++ Connecting: SUCCESS.

Connecting. JNDI path: java:comp/env/jdbc/jay
+++ Connecting: SUCCESS.

It when I run the query, it appears to forget about the jayExtranet
datasource thinking that 'dbo.client_contracts' comes from jay data
source.

Thank you

Gary
On Tue, Jun 5, 2012 at 9:14 PM, Bryan Lewis <jbryanlewis <at> gmail.com> wrote:
> I got cross-database queries working just recently, with a boost from
> Michael Gentry.  I'd guess the culprit is that your nodes are missing the
> datasource.  For example:
>

(Continue reading)

Andrus Adamchik | 5 Jun 2012 13:42

Re: Cross Database Queries

Hi Gary,

are there any matching table names between "jay" and "jayExtranet" DataMaps?

Andrus

On Jun 5, 2012, at 2:28 PM, Gary Jarrel wrote:

> Hi Bryan,
> 
> Thank you for the response.
> 
> When pasting I omitted the XML declaration at the top: <?xml
> version="1.0" encoding="utf-8"?> perhaps that is why it did not load?
> 
> I am using container defined JNDI data sources, hence there is no
> driver declaration. When the application starts it actually connected
> to both data sources perfectly fine and I get the following log
> entries:
> 
> Connecting. JNDI path: java:comp/env/jdbc/jayExtranet
> +++ Connecting: SUCCESS.
> 
> Connecting. JNDI path: java:comp/env/jdbc/jay
> +++ Connecting: SUCCESS.
> 
> It when I run the query, it appears to forget about the jayExtranet
> datasource thinking that 'dbo.client_contracts' comes from jay data
> source.
>

(Continue reading)

Gary Jarrel | 5 Jun 2012 13:45

Picon

Re: Cross Database Queries

Hi Andrus,

No there are not!

I am also using CayenneFilter to initialize Cayenne, but I think I was
reading in one of the previous threads that CayenneFilter is simple
and in more complex situations Srging or Guice should be used to
initialize. I've also read that there is only 1 ServerRuntime is being
created and in order to create 2 ServerRuntimes I need a separate
DataDomain. Yet I can not create another DataDomain within the same
project in the modeller? Does that make any sense?

Gary

On Tue, Jun 5, 2012 at 9:42 PM, Andrus Adamchik <andrus <at> objectstyle.org> wrote:
> Hi Gary,
>
> are there any matching table names between "jay" and "jayExtranet" DataMaps?
>
> Andrus

Gary Jarrel | 5 Jun 2012 13:52

Picon

Re: Cross Database Queries

Also could it have something to do with the fact that I have a
flattened relationship from an entity jayExtranet to an etity in jay
which goes via a many-to-many relationship table in the jayExtranet
datasource?

G

On Tue, Jun 5, 2012 at 9:45 PM, Gary Jarrel <garyjarrel <at> gmail.com> wrote:
> Hi Andrus,
>
> No there are not!
>
> I am also using CayenneFilter to initialize Cayenne, but I think I was
> reading in one of the previous threads that CayenneFilter is simple
> and in more complex situations Srging or Guice should be used to
> initialize. I've also read that there is only 1 ServerRuntime is being
> created and in order to create 2 ServerRuntimes I need a separate
> DataDomain. Yet I can not create another DataDomain within the same
> project in the modeller? Does that make any sense?
>
> Gary
>
> On Tue, Jun 5, 2012 at 9:42 PM, Andrus Adamchik <andrus <at> objectstyle.org> wrote:
>> Hi Gary,
>>
>> are there any matching table names between "jay" and "jayExtranet" DataMaps?
>>
>> Andrus

(Continue reading)

Andrus Adamchik | 5 Jun 2012 14:01

Re: Cross Database Queries

> I am also using CayenneFilter to initialize Cayenne,

This is fine.

> but I think I was reading in one of the previous threads that CayenneFilter is simple
> and in more complex situations Srging or Guice should be used to initialize.

I'd rephrase this advice as "Cayenne can be used with a user-prefrerred configuration mechanism. If you
are already using Spring, start ServerRuntime with Spring as well, if you are using Guice, start it with
Guice, if you don't have either - use CayenneFilter or use your own bootstrap code"

> I've also read that there is only 1 ServerRuntime is being
> created and in order to create 2 ServerRuntimes I need a separate
> DataDomain. Yet I can not create another DataDomain within the same
> project in the modeller? Does that make any sense?

You are right in that there's only one DataDomain per mapping project in the Modeler and one DataDomain per
ServerRuntime. You have this part backwards: "in order to create 2 ServerRuntimes I need a separate
DataDomain". I'd say "in order to have 2 separate DataDomains, you need to create 2 separate
ServerRuntimes". I.e. ServerRuntime is a full isolated Cayenne stack. You can have 2 ServerRuntimes in
the same app started from the same XML (not sure why, but you can), or from different XML, etc. 

Back to your issue - 2 DataNodes can easily co-exist in the same ServerRuntime (under a single DataDomain).
So I am not sure what the problem is. Maybe put a breakpoint in your Query "route" method and run your app in
debugger to start unwinding this puzzle?

Andrus

On Jun 5, 2012, at 2:45 PM, Gary Jarrel wrote:

(Continue reading)

Gary Jarrel | 5 Jun 2012 14:06

Picon

Re: Cross Database Queries

Thank you Andrus, I'll run it through a debugger and see what could be
causing it, I also try perhaps using an XMLPoolingDataSourceFactory to
see if it makes a difference. Will advise!

G

On Tue, Jun 5, 2012 at 10:01 PM, Andrus Adamchik <andrus <at> objectstyle.org> wrote:
>
>> I am also using CayenneFilter to initialize Cayenne,
>
> This is fine.
>
>> but I think I was reading in one of the previous threads that CayenneFilter is simple
>> and in more complex situations Srging or Guice should be used to initialize.
>
> I'd rephrase this advice as "Cayenne can be used with a user-prefrerred configuration mechanism. If you
are already using Spring, start ServerRuntime with Spring as well, if you are using Guice, start it with
Guice, if you don't have either - use CayenneFilter or use your own bootstrap code"
>
>> I've also read that there is only 1 ServerRuntime is being
>> created and in order to create 2 ServerRuntimes I need a separate
>> DataDomain. Yet I can not create another DataDomain within the same
>> project in the modeller? Does that make any sense?
>
> You are right in that there's only one DataDomain per mapping project in the Modeler and one DataDomain per
ServerRuntime. You have this part backwards: "in order to create 2 ServerRuntimes I need a separate
DataDomain". I'd say "in order to have 2 separate DataDomains, you need to create 2 separate
ServerRuntimes". I.e. ServerRuntime is a full isolated Cayenne stack. You can have 2 ServerRuntimes in
the same app started from the same XML (not sure why, but you can), or from different XML, etc.
>

(Continue reading)

Gary Jarrel | 5 Jun 2012 14:41

Picon

Re: Cross Database Queries

Just tried it with an XMLPoolingDataSourceFactory and got the same
error. To the debugger it is!

On Tue, Jun 5, 2012 at 10:06 PM, Gary Jarrel <garyjarrel <at> gmail.com> wrote:
> Thank you Andrus, I'll run it through a debugger and see what could be
> causing it, I also try perhaps using an XMLPoolingDataSourceFactory to
> see if it makes a difference. Will advise!
>
> G
>
> On Tue, Jun 5, 2012 at 10:01 PM, Andrus Adamchik <andrus <at> objectstyle.org> wrote:
>>
>>> I am also using CayenneFilter to initialize Cayenne,
>>
>> This is fine.
>>

Gary Jarrel | 6 Jun 2012 10:47

Picon

Re: Cross Database Queries

I've been debugging & debugging, and for the life of me I can not find
where the fault is, both DataNodes are set correctly, I just can't
seem to see what I am missing. :(

Any further thoughts?!

Gary

On Tue, Jun 5, 2012 at 10:41 PM, Gary Jarrel <garyjarrel <at> gmail.com> wrote:
> Just tried it with an XMLPoolingDataSourceFactory and got the same
> error. To the debugger it is!
>
> On Tue, Jun 5, 2012 at 10:06 PM, Gary Jarrel <garyjarrel <at> gmail.com> wrote:
>> Thank you Andrus, I'll run it through a debugger and see what could be
>> causing it, I also try perhaps using an XMLPoolingDataSourceFactory to
>> see if it makes a difference. Will advise!
>>
>> G
>>
>> On Tue, Jun 5, 2012 at 10:01 PM, Andrus Adamchik <andrus <at> objectstyle.org> wrote:
>>>
>>>> I am also using CayenneFilter to initialize Cayenne,
>>>
>>> This is fine.
>>>

Andrus Adamchik | 12 Jun 2012 09:48

Re: Cross Database Queries

But what happens inside SelectQuery.route(..)? (the failing query is SelectQuery, right?). This is
where the DataNode to Query mapping logic is, so this is the place to look for clues.

Andrus

On Jun 6, 2012, at 11:47 AM, Gary Jarrel wrote:

> I've been debugging & debugging, and for the life of me I can not find
> where the fault is, both DataNodes are set correctly, I just can't
> seem to see what I am missing. :(
> 
> Any further thoughts?!
> 
> Gary
> 
> On Tue, Jun 5, 2012 at 10:41 PM, Gary Jarrel <garyjarrel <at> gmail.com> wrote:
>> Just tried it with an XMLPoolingDataSourceFactory and got the same
>> error. To the debugger it is!
>> 
>> On Tue, Jun 5, 2012 at 10:06 PM, Gary Jarrel <garyjarrel <at> gmail.com> wrote:
>>> Thank you Andrus, I'll run it through a debugger and see what could be
>>> causing it, I also try perhaps using an XMLPoolingDataSourceFactory to
>>> see if it makes a difference. Will advise!
>>> 
>>> G
>>> 
>>> On Tue, Jun 5, 2012 at 10:01 PM, Andrus Adamchik <andrus <at> objectstyle.org> wrote:
>>>> 
>>>>> I am also using CayenneFilter to initialize Cayenne,
>>>>

(Continue reading)

Andrus Adamchik | 14 Jun 2012 15:01

Re: Cross Database Queries

Gary,

just stumbled on something similar in one of our client projects. Investigating... Stay tuned.

Andrus

On Jun 12, 2012, at 10:48 AM, Andrus Adamchik wrote:

> But what happens inside SelectQuery.route(..)? (the failing query is SelectQuery, right?). This is
where the DataNode to Query mapping logic is, so this is the place to look for clues.
> 
> Andrus
> 
> On Jun 6, 2012, at 11:47 AM, Gary Jarrel wrote:
> 
>> I've been debugging & debugging, and for the life of me I can not find
>> where the fault is, both DataNodes are set correctly, I just can't
>> seem to see what I am missing. :(
>> 
>> Any further thoughts?!
>> 
>> Gary
>> 
>> On Tue, Jun 5, 2012 at 10:41 PM, Gary Jarrel <garyjarrel <at> gmail.com> wrote:
>>> Just tried it with an XMLPoolingDataSourceFactory and got the same
>>> error. To the debugger it is!
>>> 
>>> On Tue, Jun 5, 2012 at 10:06 PM, Gary Jarrel <garyjarrel <at> gmail.com> wrote:
>>>> Thank you Andrus, I'll run it through a debugger and see what could be
>>>> causing it, I also try perhaps using an XMLPoolingDataSourceFactory to

(Continue reading)

Andrus Adamchik | 15 Jun 2012 09:29

Re: Cross Database Queries

In our case it turned out a relationship query with a join across DataNodes. Since both DBs were on the same
server, and such join was supported in theory, it was fixed by specifying schemas for all tables in 2
DataMaps. If the DBs are on different servers and joins are not possible, I guess you can't map
relationships across them.

Not sure whether your case is one of the above? If not, could you post the SQL that is causing the Exception.

Andrus

On Jun 14, 2012, at 4:01 PM, Andrus Adamchik wrote:
> Gary,
> 
> just stumbled on something similar in one of our client projects. Investigating... Stay tuned.
> 
> Andrus
> 
> 
> On Jun 12, 2012, at 10:48 AM, Andrus Adamchik wrote:
> 
>> But what happens inside SelectQuery.route(..)? (the failing query is SelectQuery, right?). This is
where the DataNode to Query mapping logic is, so this is the place to look for clues.
>> 
>> Andrus
>> 
>> On Jun 6, 2012, at 11:47 AM, Gary Jarrel wrote:
>> 
>>> I've been debugging & debugging, and for the life of me I can not find
>>> where the fault is, both DataNodes are set correctly, I just can't
>>> seem to see what I am missing. :(
>>>

(Continue reading)

Gary Jarrel | 21 Jun 2012 11:49

Picon

Re: Cross Database Queries

Thank you Andrus, I was working on it for ages, and then started to
run out of time and rewrote the code to do the join in the code rather
than the DB as the number of records in each table was not very large.
I will give it a try with the schemas in both DataMaps and see how I
go. I did have the 2 SQL Server Database on the same server :)

Gary

On Fri, Jun 15, 2012 at 5:29 PM, Andrus Adamchik <andrus <at> objectstyle.org> wrote:
> In our case it turned out a relationship query with a join across DataNodes. Since both DBs were on the same
server, and such join was supported in theory, it was fixed by specifying schemas for all tables in 2
DataMaps. If the DBs are on different servers and joins are not possible, I guess you can't map
relationships across them.
>
> Not sure whether your case is one of the above? If not, could you post the SQL that is causing the Exception.
>
> Andrus
>
>
>
> On Jun 14, 2012, at 4:01 PM, Andrus Adamchik wrote:
>> Gary,
>>
>> just stumbled on something similar in one of our client projects. Investigating... Stay tuned.
>>
>> Andrus
>>
>>
>> On Jun 12, 2012, at 10:48 AM, Andrus Adamchik wrote:
>>

(Continue reading)

Return

Return to gmane.comp.java.cayenne.user.

Project Web Page

Cayenne Object Relational Mapping User Discussions

Search Archive

Language

Change language

Options

Current view: Threads only / Showing only 20 lines / Not hiding cited text.
Change to All messages, whole messages, or hide cited text.

Post a message
NNTP Newsgroup
Classic Gmane web interface
XML

XML

RSS Feed
List Information

About Gmane