Hi all, We are facing a problem at the company where I work. let me describe, and please any help would be highly appreciated.
Requirement: The company has an old really complicated Database (MS) that has all the data needed for running the company. As well as it has a new Database (postgres) that is for Ofbiz which is still being worked on. Now what is needed is to be able to do JOINs across these two different DBs, from within Ofbiz using the Entity Engine. Problem: When choosing Ofbiz, they actually thought that OOTB Ofbiz could deal with JOINing different DBs. And I think I just found out from this old post: http://ofbiz.135035.n4.nabble.com/ViewEntity-across-multiple-databases-td152683.html <http://ofbiz.135035.n4.nabble.com/ViewEntity-across-multiple-databases-td152683.html> that "All member-entities in a view-entity must be in the same database, but not necessarily in the same entity group." So What I have been telling is that OOTB with Ofbiz it is not possible to make JOINS across multiple DBs. Am I totally correct? Solution: Now the solutions we were thinking about at first and that were refused: a) moving the data which is in MS DB into Ofbiz (postgres) ==> not doable because of the complexity of the DB & and all the references being made... b) copy the data from MS to postgres (which means keeping 2 copies of MS DB) ==> not to be considered because of impracticality and bad for the business flow (sync problems...) Now how I am currently thinking to solve the problem: 1) edit some of the Entity Engine code so that it can do the JOINS as needed. Here I am also having this http://ofbiz.135035.n4.nabble.com/Use-of-external-database-for-generate-product-pages-td4637770.html <http://ofbiz.135035.n4.nabble.com/Use-of-external-database-for-generate-product-pages-td4637770.html> post as a reference. Jacques Le Roux is actually advising to: /"Actually if you look at the entityengine.xml file which defines delegators (access to DB) you will see that OFBiz uses already 3 different DB OOTB You can follow this way and add your own group-map, of course you will need to add custom code. Have a look into the bi component for instance... " / But I actually think the problem posted there is not the same as ours (difference is in the need to have JOINs) 2) write separate java code that can be integrated into Ofbiz as a service for example. In that code actually do the queries as you would normally do in java. I think of this post as a reference for this solution: http://ofbiz.135035.n4.nabble.com/Help-Connecting-to-2nd-External-Database-td149600.html#a149601 <http://ofbiz.135035.n4.nabble.com/Help-Connecting-to-2nd-External-Database-td149600.html#a149601> 3) use UnityJDBC http://www.unityjdbc.com/ <http://www.unityjdbc.com/> that could make the 2 DBs look as only one. But the problems here is that 1st of all solution would not be free. 2nd I am still not sure, if just having UnityJDBC and using it instead of the jdbc driver, would make it actually work correctly or would result in any problems with Ofbiz. Please help me or send me in the best direction, as my company is considering to migrate from Ofbiz if no real solution is found. Thanks, Jad ----- Jad El Omeiri -- View this message in context: http://ofbiz.135035.n4.nabble.com/JOINs-across-different-DBs-tp4647103.html Sent from the OFBiz - User mailing list archive at Nabble.com.
