I've discovered a curious issue when using REPLACE() with CAST() and bind values from JDBC. The database I'm using is a Developer Edition of Informix 12.10.FC2DE on Windows.

This is the statement that is causing issues:

        PreparedStatement s = conn.prepareStatement(
            "select replace(cast(? as lvarchar), cast(? as lvarchar), cast(? as lvarchar)) "
          + "from systables where tabid = 1");
        s.setString(1, "aaa");
        s.setString(2, "a");
        s.setString(3, "b");
        ResultSet rs = s.executeQuery();
        rs.next();
        System.out.println("\"" + rs.getString(1).replace(" ", "-") + "\"");
        System.out.println(rs.getString(1).length());

When I execute the above, I'm getting this output:

"bbb-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------"
2048

As you can see, the resulting value has a fixed length of 2048. When I call LENGTH() in the database, this doesn't happen:

        PreparedStatement s = conn.prepareStatement(
            "select length(replace(cast(? as lvarchar), cast(? as lvarchar), cast(? as lvarchar))) "
          + "from systables where tabid = 1");
        s.setString(1, "aaa");
        s.setString(2, "a");
        s.setString(3, "b");
        ResultSet rs = s.executeQuery();
        rs.next();
        System.out.println(rs.getInt(1));

This yields 3, the expected result.

This issue seems to be related to the first argument being CAST to LVARCHAR.. The following two alternative statements work correctly:

CAST to VARCHAR(10):

        PreparedStatement s = conn.prepareStatement(
            "select replace(cast(? as varchar(10)), cast(? as lvarchar), cast(? as lvarchar)) "
          + "from systables where tabid = 1");

No CAST at all:

        PreparedStatement s = conn.prepareStatement(
            "select replace(cast(? as varchar(10)), cast(? as lvarchar), cast(? as lvarchar)) "
          + "from systables where tabid = 1");

I hope this helps,
Lukas