SQLTemplate not returning all columns?

From: Adrianne D Akins (adriann..aytheon.com)
Date: Tue Oct 31 2006 - 09:16:24 EST

  • Next message: Watkins, Garry: "Re: Is there an awakeFromInsertion equivalent in cayenne?"

    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 - 09:41:25 EST