Padding of CHAR(n) columns with trailing spaces

[areider <op...@reider.net>]

When a fixed-length CHAR column is mapped to a Java string, in JPA1.2.3 the
value does not contain trailing spaces. In JPA 2.0.2, the value is
blank-added to the length of column in the database (DB2 with JDBC Universal
driver). For example a column is defined as CHAR(5). The value M1C1 appears
as "M1C1 " and "M1C1" respectively. 

I'm assuming that padding (or not trimming) is the correct behavior although
I cannot find a definitive cite (either for what a JDBC driver should return
or what JPA should do to the value, if anything). 

Is JPA2.0.2 deliberately padding to the length of the field or is JPA1.2.3
deliberately trimming? Or is JPA leaving the field as it comes back from the
JDBC driver? I think I have established that the driver is the same in both
environments (Rad7 7.0.0.13 with openjpa-1.2.3-SNAPSHOT-r422266:965640 and
Websphere Application Server 7.0.0.13 with
openjpa-2.0.2-SNAPSHOT-r422266:1032678)

--
View this message in context: http://openjpa.208410.n2.nabble.com/Padding-of-CHAR-n-columns-with-trailing-spaces-tp6364510p6364510.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Padding of CHAR(n) columns with trailing spaces

[Michael Dick <mi...@gmail.com>]

In general the contents of the entity should match what we get back from the
JDBC driver. I don't think OpenJPA intentionally pads or trims the contents
of a String (although you're seeing otherwise). 

That said, DB2 does not differentiate between 'abc' and 'abc   '. So
something like this will work :
INSERT INTO s (name) VALUES ('abc   ') 
SELECT * FROM s WHERE name = 'abc'  // returns the row you just inserted. 

hth
-mike 





--
View this message in context: http://openjpa.208410.n2.nabble.com/Padding-of-CHAR-n-columns-with-trailing-spaces-tp6364510p6428535.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Padding of CHAR(n) columns with trailing spaces

[areider <op...@reider.net>]

correction - on JPA1.2.3 the field in both tables is padded. going to
JPA2.0.2, one is padded and one is trimmed.

--
View this message in context: http://openjpa.208410.n2.nabble.com/Padding-of-CHAR-n-columns-with-trailing-spaces-tp6364510p6386967.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Padding of CHAR(n) columns with trailing spaces

[areider <op...@reider.net>]

bump..

Since the OP I have observed that a CHAR(n) field is mapped to a string, it
may be padded OR trimmed, even on the same version of JPA (either JPA1.2.3
OR JPA2.0.2). The behavior appears to be by table. Eg in one table where the
column CHAR(2) the values come back padded:

'C '
'1 '
'X '
'B '
'22'

In another table where the column is CHAR(5), the values come back trimmed:
'CTDB'
'CTSUP'
'CT1A1'
'CT2B'
'CT3A'
'M1FH'
'M2CPH'
'M3FL'
'M4CPL'

If I use the tooling in RAD(Eclipse)  (which presumably just passes SQL to
JDBC) to SQL such as:
select concat(code,'.') from mytable;

then for both tables, the values returned are always padded, eg 'CTDB .'.

 Assuming that values should always be padded to the column width, what can
be causing the varying behavior? It is now apparent that this is not a JPA
version issue.

Could this be tied to how the data is stored in DB2? IOW, if 'CTDB' is
inserted vs 'CTDB ', is this distinction 'lost' internally in DB2 by always
padding before storing? Or as I could imagine, does the implementation
always store fixed length fields as the actual data passed plus a length
attribute (just like variable length) and only enforce the fixed lengthiness
on fetch operations by padding it?

The question matters, because if the former is true, then the question of
how the data was inserted into one or the other tables could be completely
ruled out, otherwise it could not (even though the driver behavior shown by
executing SQL directly, would suggest it could).

--
View this message in context: http://openjpa.208410.n2.nabble.com/Padding-of-CHAR-n-columns-with-trailing-spaces-tp6364510p6386498.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.