Yeah, capitalization mismatch between the default DB behavior and
your mapping can bite you when you use "select *". You may have to
use the "#result" directive to describe all your columns:
http://cayenne.apache.org/doc/scripting-sqltemplate.html
Since this is a pretty common and annoying problem (and presuming
that most users do follow some naming convention in mapping their
case-insensitive DB), I wonder if we should add a simple fix - two
methods in a SQLTemplate - "setUppercaseColumnNames",
"setLowercaseColumnNames"? Anybody has thoughts on that? Or is there
some JDBC way to achieve the same with "SELECT *" queries?
Andrus
On Jun 5, 2007, at 8:33 AM, Marcin Skladaniec wrote:
> Hi everyone
>
> I'm having some troubles with SQLTemplate. The one I use is fairly
> simple:
>
> SQLTemplate query = new SQLTemplate(Student.class, "select * from
> Student WHERE isDeleted = 1");
>
> the student entity is defined by :
>
> <db-entity name="Student">
> <db-attribute name="studentNumber" type="BIGINT"/>
> ... some other fields here ...
> <db-attribute name="id" type="INTEGER" isPrimaryKey="true"
> isMandatory="true"/>
> </db-entity>
>
> <obj-entity name="Student"
> className="ish.oncourse.server.cayenne.Student"
> dbEntityName="Student"
> superClassName="ish.oncourse.server.cayenne.glue.CayenneDataObject" >
> <qualifier><![CDATA[(isDeleted = null) or (isDeleted = 0)]]></
> qualifier>
> <obj-attribute name="studentNumber" type="java.lang.Long" db-
> attribute-path="studentNumber"/>
> ... some other fields here ...
> <obj-attribute name="id" type="java.lang.Integer" db-attribute-
> path="id"/>
> </obj-entity>
>
> When the query is performed an exception is raised:
>
> [java] org.apache.cayenne.CayenneRuntimeException: [v.$
> {project.version} ${project.build.date} ${project.build.time}] Null
> value for 'id'. Snapshot: org.apache.cayenne.DataRo..549af[values=
> {STUDENTNUMBER=5, (Other fields in here, all in upper case),
> ID=241}, version=-9223372036854775761,
> replaces=-9223372036854775808]. Prefix: null
> [java] at
> org.apache.cayenne.access.ObjectResolver.createObjectId
> (ObjectResolver.java:280)
> [java] at
> org.apache.cayenne.access.ObjectResolver.objectFromDataRow
> (ObjectResolver.java:206)
> [java] at
> org.apache.cayenne.access.ObjectResolver.objectsFromDataRows
> (ObjectResolver.java:120)
> [java] at
> org.apache.cayenne.access.ObjectResolver.synchronizedObjectsFromDataRo
> ws(ObjectResolver.java:100)
> [java] at
> org.apache.cayenne.access.DataDomainQueryAction.interceptObjectConvers
> ion(DataDomainQueryAction.java:433)
> [java] at
> org.apache.cayenne.access.DataDomainQueryAction.execute
> (DataDomainQueryAction.java:124)
> [java] at org.apache.cayenne.access.DataDomain.onQuery
> (DataDomain.java:722)
> [java] at
> org.apache.cayenne.intercept.DataChannelCallbackInterceptor.onQuery
> (DataChannelCallbackInterceptor.java:74)
> [java] at
> org.apache.cayenne.util.ObjectContextQueryAction.runQuery
> (ObjectContextQueryAction.java:282)
> [java] at
> org.apache.cayenne.access.DataContextQueryAction.execute
> (DataContextQueryAction.java:59)
> [java] at org.apache.cayenne.access.DataContext.onQuery
> (DataContext.java:1321)
> [java] at
> org.apache.cayenne.access.DataContext.performQuery(DataContext.java:
> 1310)
> [java] at
> org.apache.cayenne.intercept.ObjectContextDecorator.performQuery
> (ObjectContextDecorator.java:98)
> [java] at
> ish.oncourse.server.services.DataPopulationService.runService
> (DataPopulationService.java:688)
> [java] at ish.oncourse.server.services.Service.run
> (Service.java:174)
> [java] at java.lang.Thread.run(Thread.java:613)
>
> What is wrong ? Why when all my field properties are set to
> lowercase (like "id") the SQLTemplate returns a DataRow with only
> capital letters ?
>
>
> I have to use the SQLTemplate since my model defines a qualifier
> "<qualifier><![CDATA[(isDeleted = null) or (isDeleted = 0)]]></
> qualifier>" and I actually want to fetch the records with
> isDeleted=1. I tried different ways to waive the constraint on the
> data like:
>
> ObjEntity studentEntity = new ObjEntity
> ("ish.oncourse.server.cayenne.Student");
> Expression exp = studentEntity.getDeclaredQualifier();
> studentEntity.setDeclaredQualifier(null);
> SelectQuery selectQuery = new SelectQuery(studentEntity);
> studentEntity.setDeclaredQualifier(exp);
>
> but nothing worked. Did someone achieve it ?
>
> Thanks
> Marcin
> -------------------------->
> ish
> http://www.ish.com.au
> Level 1, 30 Wilson Street Newtown 2042 Australia
> phone +61 2 9550 5001 fax +61 2 9550 4001
>
>
>
>
>
This archive was generated by hypermail 2.0.0 : Tue Jun 05 2007 - 07:38:27 EDT