The case-setting methods sound like a fairly good idea. We're currently
running on Oracle but preparing to convert to Postgres. I sometimes
have to adjust the case of the column names in SQLTemplate sql, so those
methods might help with that. However, they wouldn't be a cure-all.
I'd still need an occasional database check in my SQLTemplate-building
code for other differences like 'limit' versus 'rownum'. It doesn't
bother me to deal with such things in the few places where I've decided
to drop down to raw SQL.
Andrus Adamchik wrote:
> 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.synchronizedObjectsFromDataRows(ObjectResolver.java:100)
>>
>> [java] at
>> org.apache.cayenne.access.DataDomainQueryAction.interceptObjectConversion(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 - 12:37:05 EDT