Join over Multiple Database

[Adam Yocum <ad...@yahoo.com>]

Hi,
   
  I have a set up where there are multiple databases on one server that I need to query using joins...
   
  example...
   
  databases...
   
  customer
  ordr
   
  tables...
   
  customer.customer
  ordr.ordr
   
  Since they are on the same server and it is a MySQL server I can query across databases...
   
  SELECT ordr.order_id, customer.customer_id, customer.first_name FROM ordr.ordr
INNER JOIN customer.customer ON customer.customer_id = ordr.customer_id
AND customer.first_name LIKE '%adam%'
   
  I know that Cayenne will not allow me to do this sort of join even though technically the server can do it if you explicitly name all the tables involved with a database prefix like the above example.
   
  Is my best bet to just grab all the ordr_ids using a JDBC resultset writing the query myself, and then create all the CayenneDataObjects using DataObjectUtils.objectForPK(id)?
   
  Any plans to implement some sort of super node that would allow joining over different databases as long as they are part of the same super node?  Would it only work on MySQL and that is why Cayenne doen't handle this sort of thing?  Does everyone else really keep their customers and orders in the same DB?
   
  Thanks 
   

       
---------------------------------
Be a better friend, newshound, and know-it-all with Yahoo! Mobile.  Try it now.