Stop JDBC from removing whitespaces from a char field

user_773921 0
Viewed 263

I have a table with a set of char(5) fields in an Informix database.

For reasons that I don't know, there's some validation in the backend that will actually check if the object coming from the Informix DB contains a field set to " " (five blank spaces).

That means another system is storing a new object leaving this field blank (not null), so I've checked the DB, and the field contains the white spaces. But once I start debugging the Java application, the spaces are removed after getting an object from the DB query.

informix jdbc java
3 Answers

Add the jdbc parameter to your url: ;IFX_TRIMTRAILINGSPACES=0

In example: jdbc:informix-sqli://dbhost:service/database:INFORMIXSERVER=ifxserver;IFX_TRIMTRAILINGSPACES=0

And you can add extra parameters like that:

jdbc:informix-sqli://dbhost:service/database:INFORMIXSERVER=ifxserver;IFX_TRIMTRAILINGSPACES=0;IFX_LOCK_MODE_WAIT=1
user_145603390

You might need a more recent JDBC driver? I tested with Informix 4.50.JC5 JDBC driver and it appears to preserve spaces correctly.

try(Statement s = c.createStatement()) {
    s.execute("CREATE TABLE IF NOT EXISTS foo( a1 char(5))");
    s.execute("INSERT INTO foo (a1) VALUES('     ')");
    s.execute("INSERT INTO foo (a1) VALUES('')");
    s.execute("INSERT INTO foo (a1) VALUES(null)");
    try(ResultSet rs = s.executeQuery("SELECT a1 FROM foo")) {
        while(rs.next()) {
            System.out.println("Output: '" + rs.getString(1) + "'");
        }
    }
}

Yields results that I would expect

Output: '     '
Output: '     '
Output: 'null'

There does exist a connection parameter that will automatically trim char columns in the JDBC driver, but it is OFF by default.

user_83341220

I have a table with a set of char(5) fields in an Informix database.

... in which case, each non-null value in any of those columns contains exactly 5 characters, no fewer. Values inserted into those columns will be truncated or padded with spaces as necessary to ensure that.

once I start debugging the Java application, the spaces are removed after getting an object from the DB query.

That is no doubt a behavior of your JDBC driver. It may or may not be configurable, depending on the driver. Presumably the driver is trimming spaces to effect pretty much exactly the effect you observe, but this is not arbitrary. Trimming spaces has the effect of reversing the space padding that is automatically added, with the result that, for example, if you store an empty string then you read back an empty string.

It is an inherent characteristic of char(n) data types* that they do not distinguish between values that differ only in the number of trailing spaces, so your code needs to deal with that one way or another regardless of whether you can persuade the driver to stop trimming trailing spaces. The most robust way to do this would be to manually trim (or pad) values read from the database, and to do the same with otherwise-sourced values you want to compare with them. For example:

boolean isEqualTrimmed(String s1, String s2) {
    // assumes s1 and s2 are both non-null:
    return s1.stripTrailing().equals(s2.stripTrailing());
}

*As distinguished from varchar(n) data types.

user_24022720
Related