Parameterized Oracle SQL query in Java?

Question

I've been trying to figure out why the following code is not generating any data in my ResultSet:

String sql = "SELECT STUDENT FROM SCHOOL WHERE SCHOOL = ? ";
PreparedStatement prepStmt = conn.prepareStatement(sql);
prepStmt.setString(1, "Waterloo");
ResultSet rs = prepStmt.executeQuery();

On the other hand, the following runs properly:

String sql = "SELECT STUDENT FROM SCHOOL WHERE SCHOOL = 'Waterloo' ";
PreparedStatement prepStmt = conn.prepareStatement(sql);
ResultSet rs = prepStmt.executeQuery();

The data type for SCHOOL is CHAR (9 Byte). Instead of setString, I also tried:

String sql = "SELECT STUDENT FROM SCHOOL WHERE SCHOOL = ? ";
PreparedStatement prepStmt = conn.prepareStatement(sql);
String school = "Waterloo";
Reader reader = new CharArrayReader(school.toCharArray());
prepStmt.setCharacterStream(1, reader, 9);
prepStmt.setString(1, "Waterloo");
ResultSet rs = prepStmt.executeQuery();

I'm completely stuck on what to investigate next; the Eclipse debugger says the SQL query doesn't change even after setString or setCharacterStream. I'm not sure if it's because setting parameters isn't working, or if the debugger simply can't pick up changes in the PreparedStatement.

Any help will be greatly appreciated, thanks!

Answer 1

I think the problem is that your datatype is CHAR(9) and "Waterloo" has only 8 chars. I assume that this would return the expected results (LIKE and %). Or add the missing space.

String sql = "SELECT STUDENT FROM SCHOOL WHERE SCHOOL LIKE ? ";
PreparedStatement prepStmt = conn.prepareStatement(sql);
prepStmt.setString(1, "Waterloo%");
ResultSet rs = prepStmt.executeQuery();

The best way would by to use varchar instead of char if your Strings have a flexible length. Then the PreparedStatement would work as expected.

A workaround would be to use the Oracle specific setFixedCHAR method (but it's better to change the datatype to varchar if possible).

The following is from Oracle's PreparedStatement JavaDoc:

---

CHAR data in the database is padded to the column width. This leads to a limitation in using the setCHAR() method to bind character data into the WHERE clause of a SELECT statement--the character data in the WHERE clause must also be padded to the column width to produce a match in the SELECT statement. This is especially troublesome if you do not know the column width.

setFixedCHAR() remedies this. This method executes a non-padded comparison.

Notes:

• Remember to cast your prepared statement object to OraclePreparedStatement to use the setFixedCHAR() method.
• There is no need to use setFixedCHAR() for an INSERT statement. The database always automatically pads the data to the column width as it inserts it.

The following example demonstrates the difference between the setString(), setCHAR() and setFixedCHAR() methods.

// Schema is : create table my_table (col1 char(10));
//             insert into my_table values ('JDBC');
PreparedStatement pstmt = conn.prepareStatement
("select count() from my_table where col1 = ?");
ResultSet rs;

pstmt.setString (1, "JDBC");  // Set the Bind Value
rs = pstmt.executeQuery();    // This does not match any row
// ... do something with rs
CHAR ch = new CHAR("JDBC      ", null);
((OraclePreparedStatement)pstmt).setCHAR(1, ch); // Pad it to 10 bytes
rs = pstmt.executeQuery();     // This matches one row
// ... do something with rs
((OraclePreparedStatement)pstmt).setFixedCHAR(1, "JDBC");
rs = pstmt.executeQuery();     // This matches one row
// ... do something with rs