Re: SQLTemplate not returning all columns?

From: Joshua Pyle (joshua.t.pyl..mail.com)
Date: Tue Oct 31 2006 - 15:05:50 EST

  • Next message: Andrus Adamchik: "Re: Capturing DataContextEvent or GraphEvent"

    Resolved.

    We played with the Query and the positioning of the #result and were
    able to get the results expected in a reliable manner. It looks like
    it is an Oracle thing.

    -- 
    Joshua T. Pyle
    Go has always existed.
    

    On 10/31/06, Adrianne D Akins <adriann..aytheon.com> wrote: > I'm having a problem with selecting columns from a query using datarows. > The original SQL statement was written as follows: > > String sql = " select bvd.*, level, r.route_id, r.int_material_number, > r.plant_id, p.plant_number" + > " from bomview_detail bvd, > routing r, manufacturing_plant p" + > " where bvd.bomview_id = " + > viewId + > " and r.plant_id=p.plant_id"+ > " and > r.bomview_detail_id=bvd.bomview_detail_id"+ > " connect by prior > bvd.bomview_detail_id = bvd.parent_id" + > " start with bvd.parent_id is > null "; > > SQLTemplate query = new SQLTemplate(BomviewDetail.class, sql, true); > query.setFetchingDataRows(true); > List result = context.performQuery(query); > > Which worked correctly for a while, but as of yesterday, we started > getting ClassCastExceptions (java.math.BigDecimal) on some of the Integers > that are stored in the bvd table. I'd used the #result directive before, > so we changed the query to read: > > String sql = "select * from (" + > "select " + > "#result('BOMVIEW_DETAIL_ID' 'Integer'), " + > "#result('BOMVIEW_ID' 'Integer'), " + > "#result('MATERIAL_NUMBER' 'VARCHAR'), " + > "#result('MATERIAL_DESCRIPTION' 'VARCHAR'), " + > "#result('PARENT_ID' 'Integer'), " + > "#result('AUTO_DRILL' 'Integer'), " + > "#result('WEEKLY_GOAL' 'Integer'), " + > "#result('DAILY_GOAL' 'Integer'), " + > "#result('MONTHLY_GOAL' 'Integer'), " + > > " level" + > " from bomview_detail" + > " where bomview_id = " + viewId + > " connect by prior bomview_detail_id = parent_id" > + > " start with parent_id is null) bvd" + > " left join routing r on r.bomview_detail_id = > bvd.bomview_detail_id" + > " left join manufacturing_plant p on p.plant_id = > r.plant_id"; > > where we still want to get the same columns but also need to specify the > data types. Now, for some reason unknown to me, we only get a subset of > the columns we need. As a matter of fact, the only columns that come back > are those that are specified in the bvd table but not in the associated > join tables. I've tried removing the 'level' attribute from the query as > it is not stored in the DB but is actually a derived column from the > "connect by" clause, but had no luck retrieving the columns from any of > the other tables. Additionally, the #result directive already contains all > the columns from the bvd table. > > Can you shed any light on this column issue as well as why the original > code worked for so long using the correct 'Integer' datatype? There have > been no changes to the underlying database or the Cayenne mapping files. > > If it would help to see the basic DB structure, please let me know. >



    This archive was generated by hypermail 2.0.0 : Tue Oct 31 2006 - 15:06:20 EST