db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Brendan Miller" <bmil...@dotster.com>
Subject Wrong SQL generation from Criteria
Date Wed, 18 Apr 2007 15:31:23 GMT

Maybe I'm doing something very poor, but I thought I had used these
constructs successfully before.  (This is Oracle, BTW.)

My RESOURCE_LOCK table schema:

    <table name="RESOURCE_LOCK">
        <column name="ITEM_ID" size="27" type="DECIMAL" javaName="ItemID"/>
        <column name="RESOURCE_DESCRIPTOR" primaryKey="true"
            required="true" size="100" type="VARCHAR" javaName="ResourceDescriptor"/>
        <column name="ENTRY_TIMESTAMP" type="TIMESTAMP" javaName="EntryTimestamp"/>
        <column name="LOCKER_ID" size="27" type="DECIMAL" javaName="LockerID"/>
        <column name="LOCKER_TYPE" size="100" type="VARCHAR" javaName="LockerType"/>
        <column name="RESOURCE_TYPE" size="27" type="DECIMAL" javaName="ResourceTypeID"/>
    </table>

My code:

    Criteria crit = new Criteria();
    crit.addGroupByColumn(ResourceLockPeer.LOCKER_TYPE);
    List<ResourceLock> locks = ResourceLockPeer.doSelect(crit);

The observed SQL from log4j when turned on:

    SELECT RESOURCE_LOCK.ITEM_ID, RESOURCE_LOCK.RESOURCE_DESCRIPTOR, RESOURCE_LOCK.ENTRY_TIMESTAMP,
RESOURCE_LOCK.LOCKER_ID, RESOURCE_LOCK.LOCKER_TYPE, RESOURCE_LOCK.RESOURCE_TYPE FROM DNS.RESOURCE_LOCK
GROUP BY RESOURCE_LOCK.LOCKER_TYPE

The exception:

    org.apache.torque.TorqueException: java.sql.SQLException: ORA-00979: not a GROUP BY expression

DNS is the torque.database.schema, but I don't understand why it is 
inconsistent in adding the database prefix in the FROM clause but not
the GROUP clause.

Joins are similarly affected:

    Criteria crit = new Criteria();
    crit.add(ResourceLockPeer.LOCKER_ID, new Long(2115));
    crit.add(ResourceLockPeer.LOCKER_TYPE, "RECURBILLINGPROC");
    crit.addJoin(ResourceLockPeer.RESOURCE_DESCRIPTOR, RecurBillingPeer.ID);
    crit.addGroupByColumn(RecurBillingPeer.ACCOUNT_ID);
    crit.addGroupByColumn(RecurBillingPeer.ACCOUNT_PAYMENT_ID);
    List<RecurBilling> recBilling = RecurBillingPeer.doSelect(crit);

The SQL is even worse, as it contains both the tablename and dbprefix.tablename 
in the where clause, which gives Oracle confusion fits.

    SELECT <..all RECUR_BILLING fields..> FROM RESOURCE_LOCK, RECUR_BILLING, DNS.RECUR_BILLING,
DNS.RESOURCE_LOCK WHERE RESOURCE_LOCK.RESOURCE_DESCRIPTOR=RECUR_BILLING.ID AND RESOURCE_LOCK.LOCKER_TYPE='RECURBILLINGPROC'
AND RESOURCE_LOCK.LOCKER_ID=2115 GROUP BY RECUR_BILLING.ACCOUNT_ID, RECUR_BILLING.ACCOUNT_PAYMENT_ID

Brendan

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