Thanks Andrus. Sorry, I should probably have said that I generated the model whilst using 1.2M6. I didn't generate it from an eomodel. I reverse engineered the database schema. When I wasn't seeing the RTRIMs, I downloaded M7 and used it at runtime without regenerating the model. You were right - I had "JDBC" instead of "Oracle" for DB Adaptor. I have another Data Node in my model and that already had "Oracle". No doubt this was user error - sorry.
I edited my model with 1.2M7 modeler. I changed Custom Adapter to org.objectstyle.cayenne.dba.oracle.OracleAdapter. Then, at runtime, the SQL did have the RTRIM in the where clause and also trimmed the attribute values in memory. I went back into the model and blanked out the Custom Adapter value. The auto-detection worked too.
Many thanks,
Alex
-----Original Message-----
From: Andrus Adamchik [mailto:andru..bjectstyle.org]
Sent: 22 November 2005 13:54
To: cayenne-use..bjectstyle.org
Subject: Re: Oracle, CHARs and RTRIM in 1.2
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 message is confidential. It may also be privileged or otherwise protected by work product immunity or other legal rules. If you have received it by mistake please let us know by reply and then delete it from your system; you should not copy it or disclose its contents to anyone. All messages sent to and from Linklaters may be monitored to ensure compliance with internal policies and to protect our business. Emails are not secure and cannot be guaranteed to be error free as they can be intercepted, amended, lost or destroyed, or contain viruses. Anyone who communicates with us by email is taken to accept these risks.
The contents of any email addressed to our clients are subject to our usual terms of business; anything which does not relate to the official business of the firm is neither given nor endorsed by it.
The registered address of the UK partnership of Linklaters is One Silk Street, London, EC2Y 8HQ. Please refer to http://www.linklaters.com/regulation for important information on the regulatory position of the firm.
This archive was generated by hypermail 2.0.0 : Tue Nov 22 2005 - 10:15:20 EST