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!

Two things to check: 1) Does it still happen if you don't use a table where one of the columns has the same name as the table? 2) Are you sure you're iterating over the result set properly - if you hard-code the query instead of using a parameter, do you get results? — Jon Skeet, Aug 10 '10 at 16:43
For what it's worth, your first example looks fine to me. Jon might be onto something with the `School` column in the `School` table - this *shouldn't* be a problem, but then your code *shouldn't* fail... — Andrzej Doyle, Aug 10 '10 at 16:53
Just out of curiosity, try the first example (the one with the `?`) but set the parameter to `"Waterloo "` with a trailing space. MySQL is supposed to disregard trailing spaces, but maybe the driver is doing something funky in this case. — Jim Garrison, Aug 10 '10 at 17:03
I tried changing the field and table names to make it easier to understand, so the actual column name is different than the table name. I guess that backfired! Need to construct better examples next time =) — echoblaze, Aug 10 '10 at 18:58

Soundlink · Accepted Answer · 2010-08-10T18:19:24.810

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

Thanks! I really wish I could change the datatype, but it's a protected database and I can only query it. — echoblaze, Aug 10 '10 at 18:55

Parameterized Oracle SQL query in Java?

1 Answers1

Linked