java returns empty String value for oracle VARCHAR2

Question

I have the following code which appears to work correctly but it does not display any values for the personCode string. PERSON_CODE is a VARCHAR2 in an Oracle 9i database.

I am using Java SE 1.7 and ojdbc7.jar for my project. I am new to Java can anybody give me some help with this?

private static void GetEmployee(String input) {
String output = "";
Connection con=null;
PreparedStatement stmt = null;
String sql ="SELECT ALL BADGE_NUMBER, PERSON_CODE FROM BADGETABLE WHERE BADGE_NUMBER = ?";

try {
    //load driver
    Class.forName("oracle.jdbc.driver.OracleDriver");
    con=DriverManager.getConnection("jdbc:oracle:thin:username/password@host:1521:database");

    //declaring statement
    stmt = con.prepareStatement(sql);
    stmt.setString(1, input);

    // execute query
    ResultSet rows = stmt.executeQuery();

    int i = 0;
    while(rows.next()) {
        i++;
        String badgeCode = rows.getString(1);
        String personCode = rows.getString(2);
        String personType = rows.getString(3);
        System.out.println("Badge number: " + badgeCode);
        System.out.println("Employee ID: " + personCode);
    }
    System.out.println("Number of results: " + i);


    rows.close();    // All done with that resultset
    stmt.close();  // All done with that statement
    con.close();  // All done with that DB connection


}
catch (SQLException e) {
    System.err.println(e);
} 
catch (ClassNotFoundException e) {
    System.err.println(e);
}

return;
}

Answer 1

I ran into this same problem using:

1. Oracle 9i Enterprise Edition 64bit (JServer Rlease 9.2.0.1.0 - Production)
2. JDBC 12.1.0.1.0 - ojdbc7.jar
3. Java OpenJDK 64bit, 1.7.0_09-icedtea

with a table like this: create table person ( first_name varchar2(60) );

And query like this using sqlline: select first_name, cast(substr(first_name,0,1) as char) from person;

Would have a result set of ["","S"].

I did not have any other Oracle jars on my class path as was found to be problem for others, but when I switched from ojdbc7.jar to ojdbc6_g.jar this problem resolved. This is driver version 11.2.0.3.0 which is under the 12c download section.

Answer 2

Look at your query :

String sql = "SELECT ALL BADGE_NUMBER, PERSON_CODE FROM BADGETABLE WHERE BADGE_NUMBER = ?";

The below code will throw Exception :

String badgeCode = rows.getString(1);
String personCode = rows.getString(2);
// there is no third column in your resultset
String personType = rows.getString(3); 

You can change your query to (if you don't want to use the column names):

String sql = "SELECT * FROM BADGETABLE WHERE BADGE_NUMBER = ?";

Or specify the third column :

String sql = "SELECT ALL BADGE_NUMBER, PERSON_CODE ,PERSON_TYPE FROM BADGETABLE WHERE BADGE_NUMBER = ?";

And retrieve data using the column name :

String badgeCode = rows.getString("BADGE_NUMBER");
String personCode = rows.getString("PERSON_CODE");
String personType = rows.getString("PERSON_TYPE");

Also , move the close() statements under the finally block :

} finally {
 try { rows.close(); } catch (Exception e) {  }
 try { stmt.close(); } catch (Exception e) {  }
 try { con.close(); } catch (Exception e) {  }
}