cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Francois Eyl <f...@smaeur.com>
Subject Re: Does cayenne left-trim on ibm system i db2?
Date Mon, 17 Jan 2011 16:53:11 GMT
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> <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
>
>
>
>

Mime
View raw message