Hi Will,
Even though you do not show the relevant piece of the DataMap, from the
generated Java class I figured that ETL_MAP_NME_KEY column is defined as a
primary key, and at the same time it is not unique in the DB! So that's
the main cause of the problems you are having.
One way to "bridge" inconsistent data with consistent object view is to
mark both columns (ETL_MAP_NME_KEY and TABLE_NAME) as PK, and then use a
regular SelectQuery with DISTINCT set to true.
Andrus
> Hi Mike/Andrus,
>
> I'm working with Brian on this project and we've been able to try some
> of your suggestions. We dropped all of the other attributes from the
> objEntity and only have the TABLE_NAME attribute in the object.
>
> XML Mapping:
> <obj-entity name="EtlMapNme_DIS"
> className="errors.cayenne.EtlMapNme_DIS" dbEntityName="ETL_MAP_NME">
> <obj-attribute name="tableName" type="java.lang.String"
> db-attribute-path="TABLE_NAME"/> </obj-entity>
>
> Corresponding class:
> public class _EtlMapNme_DIS extends
> org.objectstyle.cayenne.CayenneDataObject {
>
> public static final String TABLE_NAME_PROPERTY = "tableName";
>
> public static final String ETL_MAP_NME_KEY_PK_COLUMN =
> "ETL_MAP_NME_KEY";
>
> public void setTableName(String tableName) {
> writeProperty("tableName", tableName);
> }
> public String getTableName() {
> return (String)readProperty("tableName");
> }
>
>
> }
>
>
> We use this SQL template:
> String distinctSql = "SELECT DISTINCT TABLE_NAME FROM IHA.ETL_MAP_NME";
>
> and run the following java code:
>
> SQLTemplate query = new SQLTemplate(EtlMapNme_DIS.class, distinctSql,
> true); List resultList = context.performQuery(query);
>
> For some reason, all of the objects in the resultList have the same
> value when calling getTableName(). getTableName() returns "Vendor"
> which corresonds to the TABLE_NAME column of the the last record in the
> IHA.ETL_MAP_NME table.
>
> But when we run the query with query.setFetchingDataRows(true), the
> resultList contains the results that we want. This leads me to believe
> that perhaps there is some sort of problem mapping the database results
> to the object.
>
> Any ideas? Thanks!
>
> -Will
>
>
>
>
>
>>>> Mike Kienenberger <mkienen..laska.net> 11/12/04 05:15PM >>>
> Brian Plotner <BPLOTNE..ndependenthealth.com> wrote:
>> Do you have an alternative to the SetDistinct method to achieve a
>> distinct
> result?
>
> It should work if you drop the ETL_MAP_NME_KEY column from the results
> returned.
>
> I don't know enough about how you're creating the query to say the best
> method for that.
>
> You might look at using an SQLTemplate. A lot of cayenne users are
> using them, but I haven't needed to do so yet.
>
> http://www.objectstyle.org/cayenne/userguide/fetch/sqltemplate.html
>
> Another possibility is to remove the object attribute mapping to
> ETL_MAP_NME_KEY from your ObjEntity (if that's how you're currently
> fetching it).
>
> -Mike
This archive was generated by hypermail 2.0.0 : Mon Nov 15 2004 - 13:33:30 EST