Andrus,
thanks for this little enlightenment. I do better understand, and agree that
CHAR columns should be right-trimmed by cayenne. I haven't think to map the
columns as VARCHAR to avoid trimming, which actually makes sense.
Thank you.
Francois
On 18/01/2011 13:55, Andrus Adamchik wrote:
Hi Francois,
Sorry, missed the beginning of this thread. The trimming is the
standard Cayenne behavior for the adapters where JDBC driver would
return padded values. It's always been like that and was generally
considered a good thing by the users, as automatic padding of CHARs
with spaces is usually not what a user would expect. Here is an
example of an inconsistency that NOT trimming the value would create:
Artist a1 = ...
a1.setName("a");
a1.getObjectContext().commitChanges();
Artist a2 = // fetch the same artist in a different context
assertEquals(a1.getName(), a2.getName()); // this will fail, as
a2.getName() will be something like "a ";
So trimming was introduced for everybody's sanity.
So a few thoughts on that:
1. If you don't want trimming at all, map your columns as VARCHAR, not CHAR.
2. Your example still demonstrates a problem - Cayenne is too eager in
its trimming policy. When reading a CHAR value from DB, it should
"rtrim", not "trim" the returned value. I think we should fix this
one.
Thanks,
Andrus
On Jan 17, 2011, at 6:53 PM, Francois Eyl wrote:
Hi guys,
I finally found the issue. The default db2 adapter in cayenne does the
trim for char type. See into
org.apache.cayenne.dba.db2.DB2Adapter.java line 70 :
map.registerType(new CharType(true, true));
This actually tells to the specific CharType handler to trim leading
and trailing spaces. I think the default db2 char type handler
shouldn't be set to trim anything since we want to get the reflect of
what is stored into the database as is.
I worked this out writing my own adapter extending the DB2Adapter and
overriding the "configureExtendedTypes" method ( CharType(false,
true)). I register my custom adapter to the DataNode and no more
leading/trailing space trim.
I think this change should be integrated into the DB2Adapter. I forgot
to tell that I'm using Cayenne 3.0.1. I've joined the patch in
attachment in case you would integrate it.
Cheers,
Francois
2011/1/14 Francois Eyl <[email protected]> <[email protected]>
Hi Michael,
I don't see explicitly cayenne doing it (according to the log), but,
strings are coming trimmed (leading spaces as well as trailing ones).
Plus, using the same JDBC driver, on same database and the standard
java.sql.Statement with the same sql query, I'm getting my data not
trimmed.
Here is the log dump of my test program, the database values are
surrounded by [] and the column name is SQLCMD :
using domain file name: cayenne.xml
initialize starting.
URL found with classloader:
file:/C:/Documents%20and%20Settings/admin/workspace/movex-connector/bin/cayenne.xml
started configuration loading.
loaded domain: movex-v12-domain
URL found with classloader:
file:/C:/Documents%20and%20Settings/admin/workspace/movex-connector/bin/movex-v12-dm.map.xml
loaded <map name='movex-v12-dm' location='movex-v12-dm.map.xml'>.
loading <node name='movex-v12-node'
datasource='movex-v12-node.driver.xml'
factory='org.apache.cayenne.conf.DriverDataSourceFactory'
schema-update-strategy='org.apache.cayenne.access.dbsync.SkipSchemaUpdateStrategy'>.
using factory: org.apache.cayenne.conf.DriverDataSourceFactory
loading driver information from 'movex-v12-node.driver.xml'.
URL found with classloader:
file:/C:/Documents%20and%20Settings/admin/workspace/movex-connector/bin/movex-v12-node.driver.xml
loading driver com.ibm.as400.access.AS400JDBCDriver
loading user name and password.
Created connection pool: jdbc:as400://192.168.27.35
Driver class: com.ibm.as400.access.AS400JDBCDriver
Min. connections in the pool: 1
Max. connections in the pool: 5
loaded datasource.
no adapter set, using automatic adapter.
loaded map-ref: movex-v12-dm.
added runtime complimentary ObjRelationship from MovexJobExecution to MovexJob
added domain: movex-v12-domain
finished configuration loading in 172 ms.
initialize finished.
DataRowStore property cayenne.DataRowStore.snapshot.expiration = 7200
DataRowStore property cayenne.DataRowStore.snapshot.size = 10000
DataRowStore property cayenne.DataRowStore.remote.notify = false
DataRowStore property cayenne.DataRowStore.EventBridge.factory =
org.apache.cayenne.event.JavaGroupsBridgeFactory
Connecting to jdbc:as400://192.168.27.35/MVXCDTMVXT;prompt=false
Creating new connection [19 556 074]
--- will run 1 query.
--- transaction started.
searching for resource under: org/apache/cayenne/dba/db2/types.xml
Detected and installed adapter: org.apache.cayenne.dba.db2.DB2Adapter
SELECT t0.SLBJNO, t0.SLQCMD, t0.SLJNA, t0.SLFILE, t0.SLRGTM,
t0.SLCHNO, t0.SLBJLI, t0.SLDATA, t0.SLBJLT, t0.SLDIVI, t0.SLLMDT,
t0.SLJNU, t0.SLCONO, t0.SLRGDT, t0.SLFNID, t0.SLCHID FROM
MVXCDTMVXT.CSHLIN t0 WHERE (RTRIM(t0.SLBJLT) = ?) AND
(RTRIM(t0.SLBJLI) = ?) AND (RTRIM(t0.SLFNID) = ?) [bind:
1->SLBJLT:'SLT', 2->SLBJLI:'99', 3->SLFNID:'RPS999'] - prepared in 79
ms.
=== returned 3 rows. - took 641 ms.
+++ transaction committed.
## USING CAYENNE ###################
t0.SQLCMD=[2J 2J 000008000 0 0 0 0 0
00]
t0.SQLCMD=[2S 2W 000008000 0 0 0 0 0
00]
t0.SQLCMD=[2S 2Z 000008000 0 0 0 0 0 .250-20UNC3A 9
07
. 10000]
## USING JAVA.SQL.STATEMENT #################
SQLCMD=[ 2J 2J 000008000 0 0 0 0 0
00
&n bsp;
&n bsp;
&n bsp;
&n bsp;
&n bsp;
&n bsp;
&n bsp;
&n bsp;
&n bsp;
&n bsp;
&n bsp; ]
SQLCMD=[ 2S 2W 000008000 0 0 0 0 0
00
&n bsp;
&n bsp;
&n bsp;
&n bsp;
&n bsp;
&n bsp;
&n bsp;
&n bsp;
&n bsp;
&n bsp;
&n bsp; ]
SQLCMD=[ 2S 2Z 000008000 0 0 0 0 0 .250-20UNC3A 9
07
. 10000
  ;
  ;
  ;
  ;
  ;
  ;
  ;
  ;
  ;
  ;
]
Sorry for the long dump, but I thought I could help.
Thanks,
Francois
On 14/01/2011 19:04, Michael Gentry wrote:
Hi Francois,
Do you see Cayenne doing this (look at the logs -- such as the bind:
messages to see if the string is trimmed at that point) or is it the
JDBC driver or a database setting? Many databases will trim trailing
spaces, but should leave leading space alone unless there is a
configuration that changes it.
mrg
On Fri, Jan 14, 2011 at 11:56 AM, Francois Eyl <[email protected]> <[email protected]>
wrote:
Hi guys,
I've been using cayenne in several projects on top of MS SQL Sever, and It
just makes the job easier.
I'm playing now with it against a DB2 database (embeded in AS400/i5/IBM
System i) using the JT400 JDBC Driver. I'm facing to a strange behavior. It
looks like cayenne trim the left of my property's value to remove space
characters in case of the db field is a CHAR.
If I run the same sql select query out of cayenne, the value isn't trim.
Is there any settings to turn off/on or something to avoid this with
cayenne? Is this a bug, if so, do I need to file a bug?
Thanks for your help,
Francois
<DB2-trim.patch>
