Compatibility for data type CHAR (Derby, HSQLDB)

[GoogleCodeExporter]

Currently the data type CHAR behaves as in MySQL and PostgreSQL. However Apache 
Derby and HSQLDB (and others?) behave differently. This is a problem for 
example when using Hibernate and "Character" / "char" fields (CHAR(1)).

See also: 
http://groups.google.com/group/h2-database/browse_thread/thread/558700ab20466603
/289a29dcda7f1558

Test case:

drop table test;
create table test(id int, x char(2));
insert into test values(1, null);
insert into test values(2, '');
insert into test values(3, ' ');
insert into test values(4, 'x');
insert into test values(5, 'x ');
insert into test values(5, 'x  ');
select id, '(' || x || ')' from test;
select id, concat('(', x, ')') from test;

hsqldb, derby: with spaces, error
h2: no spaces, no spaces but no null
postgresql: no spaces, no spaces, error
mysql: 0 ?!; no spaces

Original issue reported on code.google.com by thomas.t...@gmail.com on 28 Jun 2010 at 6:35

[GoogleCodeExporter]

Original comment by thomas.t...@gmail.com on 28 Jun 2010 at 7:00

• Changed state: InRoadmap

[GoogleCodeExporter]

Oracle and Informix also produce fixed-length results for non-null values.

ie: If the column is char(5) and you insert 'abc' (or ' ') the length will be 5.

Original comment by sainsbur...@gmail.com on 28 Jun 2010 at 8:05

[GoogleCodeExporter]

any updates on this. I am using informix database and I have got the same 
problem. In the persistence I am saying
<property name="hibernate.connection.url" 
value="jdbc:informix-sqli://url:informixserver=xxx;user=xxx;password=xxx;IFX_LOC
K_MODE_WAIT=1;IFX_TRIMTRAILINGSPACES=1"/>

Original comment by charan.k...@gmail.com on 26 Sep 2012 at 1:59

[GoogleCodeExporter]

> any updates on this.

No, there is no update. Otherwise there would be an update in the issue :-)

> I am using informix

This is not the Informix support forum.

Original comment by thomas.t...@gmail.com on 29 Oct 2012 at 7:09