Using Torque with one database schema, multiple physical databases

[Ryan Schutt <ry...@yahoo.com>]

Here's our setup:

- one database schema (call it "db-schema.xml")
- six physical databases (call them db1 thru db6)
- six application servers, each residing over one db
- one Torque.properties, replicated at each app server

Each app server needs to be able to connect to all 6 db's, so in our
Torque.properties, we have a section set up for each db.  

It is our understanding that to connect to a different db, all you have to do
is a criteria.setDbName(dbName), and everything should work from there. 
However, we have had problems in using this approach with queries that use
ORDER BY's.  

The root of the problem appears to be that Torque uses information from the
map/*MapBuilder.java classes when doing queries that use ORDER BY's.  But
Torque only generates this information for the one db in db-schema.xml.  So
when we try to query a different database, it isn't finding the necessary info
in the map/*MapBuilder.java classes.


Is there something wrong with our approach?  I know that Torque supports
multiple databases;  it is annoying that our approach is failing.

Anyone have similar problems?


Thanks,
Ryan

__________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
http://search.yahoo.com

RE: selecting from multiple tables + setting results as a session

[Shin Ohkubo <sh...@po1.iftc.co.jp>]

Dear Dieter:

Thanx for your help!
Relying on your advise, I figured out how to do my SQL.
	crit.addJoin(EmpPeer.EMPNO, PostPeer.POSTID);
	crit.addSelectColumn(EmpPeer.EMPNO);
	crit.addSelectColumn(EmpPeer.EMPNAME);
	crit.addSelectColumn(PostPeer.POSTID);
	crit.addSelectColumn(PostPeer.POSTNAME);
	List empList = org.apache.torque.util.BasePeer.doSelect(crit);
In your reply, "add" was implied but it would not compile, so I used
addSelectColumn instead.
Is this how you done it?

Additionaly, I want to set these results into a session and view them
via JSP.
When I use the normal way (use the doSelect of EmpPeer), the results
will be something like:
	 empno=1
	 empname=shin
	 postid=1
	 , empno=2
	 empname=nancy
	 postid=2

So I can get each of the session data quite easily like this:
	<logic:iterate id="oneEmp" name="imaken.empList"
scope="session">
	    <tr>
		<td><bean:write name="oneEmp" property="empno" /></td>
		<td><bean:write name="oneEmp" property="empname" /></td>
		<td><bean:write name="oneEmp" property="postid" /></td>
	    </tr>
	</logic:iterate>
But this time when I am using the doSelect of BasePeer, the results will
be something like:
	 {{1, 'shin', 1, 'sales'}, {2, 'nancy', 2, 'researcher'}}
This time I can't get the session data via the properties such as empno,
empname, postid or postname.

Anyone know a way to solve this problem?
Many thanx.
Shin O.