How to use Oracle jdbc driver fixedString property?

Question

Oracle pads values in char columns so if I insert "a" in a CHAR(2) column then I cannot get that record by comparing that column to "a", I should get it by comparing it to "a ". Right?

To solve this problem the Oracle jdbc driver has the property fixedString but I cannot make it work. (look for fixedString here)

I'm using ojdbc14.jar driver for Oracle 10gR2 and accessing an Oracle 10gR2 database.

This is my code:

    try {
        Properties props = new Properties();
        props.put("user", "****");
        props.put("password", "****");
        props.put("fixedString", true);

        Class.forName("oracle.jdbc.driver.OracleDriver");

        String jdbcUrl = "jdbc:oracle:thin:@<host>:<port>:<sid>";

        Connection connection = DriverManager.getConnection(jdbcUrl, props);

        PreparedStatement ps = connection.prepareStatement(
                           "SELECT * FROM MY_TABLE WHERE MY_TABLE_ID = ?");
        ps.setObject(1, "abc"); // (*)
        // MY_TABLE_ID is CHAR(15)

        ResultSet rs = ps.executeQuery();
        while (rs.next())
        {
            System.out.print("data: ");
            System.out.println(rs.getString("MY_TABLE_ID"));
        }
        rs.close();
        ps.close();
        connection.close();
    } catch (SQLException ex) {
        ex.printStackTrace();
    } catch (ClassNotFoundException ex) {
        ex.printStackTrace();
    }

The above code executes fine (no exceptions thrown) but the ResultSet is empty after executeQuery().

If I change the line (*) for

ps.setObject(1, "abc            ");

Then I get the column I wanted. So it seems the driver is ignoring the fixedString option.

I've also tried changing the line (*) for

ps.setObject(1, "abc", java.sql.Types.CHAR);

But the ResultSet I get is empty again. What am I missing?

Thanks in advance

Answer 1

You should only use CHAR for strings that will be the same length for every entry in the database. Use VARCHAR for variable length strings.

Answer 2

The oracle docs are infuriating, really. I'd never heard of this before your question (one of the things I like about this site). looked into this and found a few refs that mention that it'll use "FIXED CHAR" semantics but nothing that describes how they work or what your expected return values will be, only behavior regarding matching and equality. Can you do your query using "Like"? (much slower, of course) Turn off fixed char semantics for that query? Insert trimmed values only into the db?

references here,here, here.

Answer 3

Try using it as

props.put("fixedString", "true");

Oracle documentation says fixedString as String (containing boolean value)

Answer 4

I tried to configured fixedString property in the datasource.xml but doesnt work, this is in oc4j

Someone could help me please?

<data-source  
location="jdbc/prueba"  
class="com.evermind.sql.DriverManagerDataSource"  
password="dagrof" max-connect-attempts="3"  
xa-location="jdbc/xa/prueba"  
ejb-location="jdbc/prueba"  
wait-timeout="1800"  
connection-driver="oracle.jdbc.driver.OracleDriver"  
username="dagrof"  
min-connections="35"  
max-connections="300"  
url="jdbc:oracle:thin:@144.1.0.54:1521:nodox"  
inactivity-timeout="300"  
name="jdbc/prueba"> 

<property name="fixedString" value="true"/> 

</data-source>