Yes, OracleAdapter still uses RTRIM in the WHERE clause and trims
char result columns in memory. The most likely cause is that you are
not using OracleAdapter, and maybe using a generic adapter instead.
See the first advice in this message on how to check that under M7:
http://objectstyle.org/cayenne/lists/cayenne-user/2005/11/0068.html
If this is indeed the case, I am curious whether this was a user
mistake or something Cayenne did wrong during reverse-engineering or
EOModel import? M7 features adapter auto-detection, and I'd like to
know about cases that are not handled well.
Andrus
On Nov 22, 2005, at 4:37 PM, Williams, Alex wrote:
> Hi,
>
> I'm new to Cayenne. I'm using 1.2M7 and I'm really enjoying it so far.
>
> I've created a model against an existing Oracle 9.2 database. I
> have a table which includes some CHAR columns. When I perform
> selects, the resulting attributes are padded with spaces. When I
> try and perform an exact match query against one of these columns,
> it fails to find a match (because the sql doesn't include an RTRIM).
>
> I searched google and found references to the fact that, in
> Cayenne, the sql generated by the Oracle adaptor will perform an
> RTRIM on CHAR columns. Is this still the case? Is this behaviour
> configurable somehow? I understand that I could use SQLTemplate,
> but I'd rather only write SQL when it is a necessary evil (I have
> an EOF background by the way).
>
> In my model, the column for StaffMember.hrNumber is defined as CHAR
> (8).
>
> For the following code:
> Expression e = ExpressionFactory.matchExp("hrNumber", '123456');
> SelectQuery q = new SelectQuery(StaffMember.class, e);
> List results = inDataContext.performQuery(q);
>
> QueryLogger shows:
> SELECT t0.NAME, t0.HRNUMBER, t0.USERNAME FROM XYUSER.STAFF t0
> WHERE t0.HRNUMBER =
> ? [bind: '123456'] - prepared in 15 ms.
> INFO QueryLogger: === returned 0 rows. - took 78 ms.
>
>
> HRNUMBER and USERNAME are CHAR columns. However, there are no
> RTRIMs. How do I configure things so the generated sql is:
>
> SELECT t0.NAME, RTRIM(t0.HRNUMBER), RTRIM(t0.USERNAME) FROM
> XYUSER.STAFF t0
> WHERE RTRIM(t0.HRNUMBER) = ? [bind: '123456'] - prepared in 15 ms.
>
>
> I have no control over the database schema, so I can't simply
> switch to VARCHAR as I would like to. I understand that there may
> be performance implications to using RTRIM in the where clause. It
> probably makes things worse that HRNUMBER is the PK for this table.
> However, at least I only require read-only access to this db.
>
> Any tips would be very gratefully received.
>
> Thanks,
> Alex
>
This archive was generated by hypermail 2.0.0 : Tue Nov 22 2005 - 08:54:12 EST