db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Brendan Miller" <bmil...@dotster.com>
Subject Re: Wrong SQL generation from Criteria
Date Sun, 21 Oct 2007 03:05:32 GMT
On Fri, Oct 19, 2007 at 03:14:02PM -0700, Brendan Miller wrote:
> > > > 
> > > > But this still leaves me a "column ambiguously defined" error when using
> > > > addJoin():
> > > > 
> > > >     crit.addJoin(TableAPeer.ColumnX, TableBPeer.ColumnG);
> > > >     crit.addAscendingOrderByColumn(TableBPeer.ColumnQ);
> > > >     crit.addAscendingOrderByColumn(TableBPeer.ColumnR);
> > > >     List<TableB> tableBs = TableBPeer.doSelect(crit);
> > > > 
> > > > generates
> > > > 
> > > >     SELECT <..TableB fields> FROM TABLE_A, TABLE_B, DBSCHEMA.TABLE_B
WHERE TABLE_A.COLUMN_X=TABLE_B.COLUMN_G ORDER BY TABLE_B.COLUMN_Q ASC, TABLE_B.COLUMN_R ASC
> > > > 
> > > > Where does the extra DBSCHEMA.TABLENAME come from?  
> 
> I'm getting this again (wrote some more code like the above), this time
> without any addAscending...:
> 
> Criteria crit = new Criteria();
> crit.addJoin(TransactionPeer.ORDER_ID, OrderPeer.ID);
> crit.add(TransactionPeer.ACCT_ID, account.getID());
> List<Order> orders = OrderPeer.doSelect(crit);
> 
> generates
> 
> SELECT <..ORDERS columns..> FROM TRANSACTION, ORDERS, DBSCHEMA.ORDERS, DBSCHEMA.TRANSACTION

> WHERE TRANSACTION.ORDER_ID=ORDERS.ID AND TRANSACTION.ACCT_ID= ?
> 
> It seems that SQLBuilder.processJoins does not add the full table names, and 
> SQLBuilder.processCriterions does.  What am I doing wrong?
> 

Tell me when to open a JIRA, or to take this to torque-dev--I'm not sure if
I'm on the right track, but I'm trying to get my code to work. :)

Is this an appropriate solution?  It does solve my immediate problem (by
prepending the schema name on the table(s) in the from clause that
result from a join, and the unit tests still pass, but I don't know if
this is too general of a fix.

BTW, in case it matters, the database in use is Oracle.

Brendan


Index: src/java/org/apache/torque/util/JoinBuilder.java
===================================================================
--- src/java/org/apache/torque/util/JoinBuilder.java    (revision 20650)
+++ src/java/org/apache/torque/util/JoinBuilder.java    (working copy)
@@ -66,6 +66,7 @@
             return;
         }
 
+        String dbName = dbMap.getName();
         UniqueList queryFromClause = query.getFromClause();
         UniqueList queryWhereClause = query.getWhereClause();
 
@@ -91,8 +92,9 @@
             int dot = leftColumn.lastIndexOf('.');
             String leftTableName = leftColumn.substring(0, dot);
 
-            leftTableName =
-                    SQLBuilder.getTableNameForFromClause(leftTableName, criteria);
+            leftTableName = SQLBuilder.getFullTableName(
+                    SQLBuilder.getTableNameForFromClause(leftTableName, criteria),
+                    dbName);
 
             dot = rightColumn.lastIndexOf('.');
             String rightTableName = rightColumn.substring(0, dot);
@@ -115,8 +117,9 @@
                             .getType()
                             instanceof String));
 
-            rightTableName = SQLBuilder.getTableNameForFromClause(
-                    rightTableName, criteria);
+            rightTableName = SQLBuilder.getFullTableName(
+                    SQLBuilder.getTableNameForFromClause(rightTableName, criteria),
+                    dbName);
 
             // now check the join type and add the join to the
             // appropriate places in the query


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org


Mime
View raw message