cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Adrianne D Akins <>
Subject SQLTemplate not returning all columns?
Date Tue, 31 Oct 2006 14:16:24 GMT
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 
                                         " 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);
                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 = 

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.
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message