cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrus Adamchik <and...@objectstyle.org>
Subject Re: Does cayenne left-trim on ibm system i db2?
Date Tue, 18 Jan 2011 12:55:24 GMT
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 <feyl@smaeur.eu>
> 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                    
                                                                                         
                                                   &nbsp ;                           
                                                                                         
                                              &nbsp ;                                
                                                                                         
                                         &nbsp ;                                     
                                                                                         
                                    &nbsp ;                                          
                                                                                         
                               &nbsp ;                                               
                                                                                         
                          &nbsp ;                                                    
                                                                                         
                     &nbsp ;                                                         
                                                                                         
                &nbsp ;                                                              
                                                                                         
           &nbsp ;                                                                   
                                                                                         
      &nbsp ;                                                                        
                                                   ]
> 
> 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 
>> <feyl@smaeur.eu>
>>  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>


Mime
View raw message