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