Prepared statement returning no results from oracle XE database

Question

My JDBC PreparedStatement is not working. I'm using Oracle 11g Express Edition, Tomcat 7, Java 7, ojdbc7.jar is in $CATALINA_HOME/lib. The application I'm developing uses the spring framework. But that's irrelevant because I built a simple Java class to test the same PreparedStatement, and still got no results.

If I run the query in sqlplus, I get the expected result. If I use the same query in a regular Statement, I get the expected result. If I hack JdbcTemplate in Spring to use my hard coded value, I get results. Just not in that darned PreparedStatement.

As you'll see from the logs below, my parameter is getting inserted into the PreparedStatement in JDBC. The trace file shows that the value is binding in the database, the query is running, but fetch brings back nothing.

The log4jdbc log shows me this:

Jun 09, 2015 1:05:35 PM net.sf.log4jdbc.Slf4jSpyLogDelegator methodReturned
INFO: 3. Connection.prepareStatement(select distinct staff_id from OE_ROLES where staff_id = ?) returned net.sf.log4jdbc.PreparedStatementSpy@71449b35

Jun 09, 2015 1:05:35 PM org.springframework.jdbc.core.StatementCreatorUtils setParameterValueInternal
FINEST: Setting SQL statement parameter value: column index 1, parameter value [jibbyj], value class [java.lang.String], SQL type unknown

Jun 09, 2015 1:05:35 PM net.sf.log4jdbc.Slf4jSpyLogDelegator methodReturned
INFO: 3. PreparedStatement.setString(1, "jibbyj") returned 

Jun 09, 2015 1:05:35 PM net.sf.log4jdbc.Slf4jSpyLogDelegator sqlOccured
INFO: select distinct staff_id from OE_ROLES where staff_id = 'jibbyj' 

Jun 09, 2015 1:05:35 PM net.sf.log4jdbc.Slf4jSpyLogDelegator sqlTimingOccured
INFO: select distinct staff_id from OE_ROLES where staff_id = 'jibbyj' 
 {executed in 2 msec}

Jun 09, 2015 1:05:35 PM net.sf.log4jdbc.Slf4jSpyLogDelegator methodReturned
INFO: 3. ResultSet.new ResultSet returned 

Jun 09, 2015 1:05:35 PM net.sf.log4jdbc.Slf4jSpyLogDelegator methodReturned
INFO: 3. PreparedStatement.executeQuery() returned net.sf.log4jdbc.ResultSetSpy@34460b79

Jun 09, 2015 1:05:35 PM net.sf.log4jdbc.Slf4jSpyLogDelegator methodReturned
INFO: 3. ResultSet.next() returned false

This is from the trace file:

PARSING IN CURSOR #140603768927480 len=59 dep=0 uid=52 oct=3 lid=52 tim=1433880335336621 hv=1464048059 ad='87cfc090' sqlid='6hbrj2tbn76dv'
select distinct staff_id from OE_ROLES where staff_id = :1 
END OF STMT
PARSE #140603768927480:c=0,e=124,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4279656581,tim=1433880335336604
BINDS #140603768927480:
 Bind#0
  oacdty=01 mxl=32(24) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000010 frm=01 csi=873 siz=32 off=0
  kxsbbbfp=7fe0ddb35b88  bln=32  avl=06  flg=05
  value="jibbyj"
EXEC #140603768927480:c=0,e=87,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4279656581,tim=1433880335336761
WAIT #140603768927480: nam='SQL*Net message to client' ela= 6 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1433880335336794
FETCH #140603768927480:c=0,e=37,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1,plh=4279656581,tim=1433880335336853
STAT #140603768927480 id=1 cnt=0 pid=0 pos=1 obj=0 op='SORT UNIQUE NOSORT (cr=1 pr=0 pw=0 time=46 us cost=2 size=9 card=1)'
STAT #140603768927480 id=2 cnt=0 pid=1 pos=1 obj=24702 op='INDEX RANGE SCAN AI_OE_ROLES_3 (cr=1 pr=0 pw=0 time=34 us cost=1 size=36 card=4)'
WAIT #140603768927480: nam='SQL*Net message from client' ela= 16956 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1433880335353990
CLOSE #140603768927480:c=0,e=22,dep=0,type=0,tim=1433880335354080
XCTEND rlbk=0, rd_only=1, tim=1433880335354131

This is from the output after running the trace file through TKPROF:

SQL ID: 6hbrj2tbn76dv Plan Hash: 4279656581

select distinct staff_id 
from
 OE_ROLES where staff_id = :1 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          1          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          1          0           0

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 52  
Number of plan statistics captured: 1

I tried with a POJO, still no results

public static void main(String[] args) {
        String url = "jdbc:oracle:thin:@oracle-test.company.com:1521:XE";
        String user = "schema-owner";
        String passwd = "password";
        System.out.println("Go!");
        try(Connection conn = DriverManager.getConnection(url, user, passwd)){
            String pQuery = "select distinct staff_id from OE_ROLES where staff_id = ?";
            PreparedStatement pstmt = conn.prepareStatement(pQuery);
            pstmt.setString(1, "jibbyj");
            ResultSet rs = pstmt.executeQuery();
            System.out.println("Execute!");
            while (rs.next()){
                System.out.println("Work!");
                System.out.println(rs.getString(1));
            }
        } catch (Exception E) {System.out.println(E.getMessage());}

        System.out.println("No!");

    }

And the output is: Go! Execute! No!, and the trace file again shows that the query ran, but returned no results. Regular Statement returns

Go! Execute! Work! jibbyj No!

Which is right.

If anyone knows why JDBC PreparedStatement does not work on our oracle database, me and my dba would love to know. Thanks.

Answer 1

This is reason #47 that char data types are evil and should be banished. Anyone creating a char(8) column that will store strings that aren't always exactly 8 characters should be sentenced to debug the various maddening issues that creates until they see the error of their ways.

In the database, a char(8 char) will always consume 8 characters worth of space. If the data you're storing isn't actually 8 characters long, the database has to pad it with spaces out to 8 characters. So if your actual data is 6 characters long-- "jibbyj"-- the database has to add two additional spaces to the end. There are roughly 0 instances where this is of any benefit to you-- you're incurring the extra cost of storing two extra bytes of data for no reason. If you had used a varchar2(8 char) instead, your 6 character string would actually be stored as you'd expect without the extra spaces.

When you go to query the data in a char column, you have to be very cautious about whether you are using char or varchar comparison semantics. If your query includes a hard-coded literal

SELECT *
  FROM your_table
 WHERE char_column = 'jibbyj' 

Oracle assumes that your literal is a char and space-pads it before doing the comparison. So it actually searches char_column for the value "jibbyj " with two spaces at the end. When it finds that value, it returns the data and everything works.

If, on the other hand, you try to use a varchar2 (or varchar), then Oracle uses varchar comparison semantics. When that happens, the two extra spaces that are stored in your table are considered part of the data and the string you're searching for has to match exactly.

DECLARE
  l_str_wo_spaces VARCHAR2(8) := 'jibbyj';
  l_str_w_spaces  VARCHAR2(8) := 'jibbyj  ';
  l_cnt           INTEGER;
BEGIN
  -- This will find no rows
  SELECT COUNT(*)
    INTO l_cnt
    FROM your_table
   WHERE char_column = l_str_wo_spaces;
  dbms_output.put_line( l_cnt );

  -- This will find a row because it has the extra spaces
  SELECT COUNT(*)
    INTO l_cnt
    FROM your_table
   WHERE char_column = l_str_w_spaces;
  dbms_output.put_line( l_cnt );
END;

In your PreparedStatement approach, you're forcing varchar comparison semantics on a char column. You can work around the problem by padding the Java String object out to 8 characters before calling setString or by modifying the query to either trim the char(8) column or to rpad the parameter being passed in to 8 characters. But none of those options are very satisfying-- you'd either need to write a bunch of code to figure out how long the column is and, thus, how much to pad your strings or you'd end up with a bunch of column lengths stored in your code that can get out of date if someone modifies the database in the future to increase the length of the column. You would be much, much better off changing the database column to varchar2(8 char) and expunging any references to char data types.