CODEWITHSUNDEEP
javaoracle-databasejdbc
Erik L
Erik L

Reputation: 1194

How to set a JDBC PreparedStatement parameter for an Oracle CHAR field?

I think I am missing something obvious with my Oracle JDBC prepared statement. I am trying to create a PreparedStatement that selects column A from TABLE where B is the parameter. For example, this is what I've boiled down the test code too: 

Class.forName("oracle.jdbc.driver.OracleDriver");
final Connection connection = DriverManager.getConnection(<Oralce JDBC connection stuff>);
final PreparedStatement findStatement = connection.prepareStatement("SELECT A FROM TABLE WHERE B = ?");

findStatement.setString(1, "TEST");

final ResultSet results = findStatement.executeQuery();
results.next();

System.out.println(results.getString("A"));

Now this doesn't work, I currently get java.sql.SQLException: Result set after last row because of the ResultSet being empty.

However, this works:

Class.forName("oracle.jdbc.driver.OracleDriver");
final Connection connection = DriverManager.getConnection(<Oralce JDBC connection stuff>);
final PreparedStatement findStatement = connection.prepareStatement("SELECT A FROM TABLE WHERE B = 'TEST'");

final ResultSet results = findStatement.executeQuery();
results.next();

System.out.println(results.getString("A"));

Notice how I hard coded the WHERE statement with a string.

Anything obvious I am missing?

My maven dependency is defined as:

<dependency>
  <groupId>com.oracle.jdbc</groupId>
  <artifactId>ojdbc7</artifactId>
  <version>12.1.0.2</version>
 </dependency>

Column B is defined as a CHAR(50)

Here is some debug information from Oracle

select
  s.sql_text,
  bc.position,
  bc.value_string
from v$sql s
  left join v$sql_bind_capture bc
  on bc.sql_id = s.sql_id
  and bc.child_number = s.child_number
where
  s.sql_text like 'SELECT A%'
order by s.sql_id, bc.position;

With output

SQL_TEXT                             | Position                       | Value_String
SELECT A FROM TABLE WHERE B = :1     | 1                              | TEST
SELECT A FROM TABLE WHERE B = 'TEST' | <null>                         | <null>

Upvotes: 2

Views: 1732

Answers (2)

Erik L
Erik L

Reputation: 1194

In addition to @LppEdd answer you can also do this for setting the parameter:

((OraclePreparedStatement)findStatement).setFixedCHAR(1, "TEST");

Upvotes: 5

LppEdd
LppEdd

Reputation: 21104

As the definition of your field is CHAR(50), I think you should pad your String with blanks.

Upvotes: 3

Related Questions