Join over Multiple Database

From: Adam Yocum (adamyocu..ahoo.com)
Date: Tue Jan 22 2008 - 22:15:04 EST

  • Next message: Aristedes Maniatis: "Re: Join over Multiple Database"

    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 for your
       

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

    This archive was generated by hypermail 2.0.0 : Tue Jan 22 2008 - 22:15:37 EST