How to use Oracle fixed jdbc driver fixedString property?

Question

Oracle adds values ​​to char columns, so if I insert "a" into the CHAR (2) column, then I cannot get this record by comparing this column with "a", I have to get it by comparing it with "a". Right?

To solve this problem, the Oracle jdbc driver has a fixedString property, but I cannot get it to work. (find fixedString here )

I am using the ojdbc14.jar driver for Oracle 10gR2 and accessing the 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 runs fine (no exceptions are thrown), but the ResultSet is empty after executeQuery ().

If I change the line (*) for

 ps.setObject(1, "abc "); 

Then I get the desired column. Thus, it seems that the driver is ignoring the fixedString parameter.

I also tried changing the string (*) to

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

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

Thank you in advance

Answer 1

Try using it like

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

Oracle documentation says fixedString as String (contains a boolean)

Answer 2

You should use only CHAR for rows that will be the same length for each record in the database. Use VARCHAR for variable length strings.

Answer 3

The doctrines of the oracle are really scared. I never heard about this before your question (one of the things that I like about this site). I looked into this and found several links that mentioned that it would use the “FIXED CHAR” semantics, but nothing that describes how they work or what your expected return values ​​will be is just behavior in terms of consistency and equality. Can you make your request using "Like"? (much slower, of course) Disable fixed char semantics for this request? Paste trimmed values ​​only in db?

links here , here , here .