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 - 08:37:08 EST