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;
}

@user2369812 Have you verified that PERSON_CODE has non-empty data for the given badge number? — lreeder
– lreeder, Commented Jul 8, 2013 at 15:57
@ThorbjørnRavnAndersen - ALL isn't a problem, it's just enforcing the default behaviour - i.e. not DISTINCT. Redundant, really, but valid. — Alex Poole
– Alex Poole, Commented Jul 8, 2013 at 16:08

buckaroo1177125 · Accepted Answer · 2014-02-23 22:31:16Z

4

I ran into this same problem using:

Oracle 9i Enterprise Edition 64bit (JServer Rlease 9.2.0.1.0 - Production)
JDBC 12.1.0.1.0 - ojdbc7.jar
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.

answered Feb 23, 2014 at 22:31

buckaroo1177125

1,7231 gold badge13 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

inginia Over a year ago

God bless you! Been hitting my head on the wall for days because of this

AllTooSir · Accepted Answer · 2013-07-08 16:06:11Z

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) {  }
}

Dmitry.M · Accepted Answer · 2015-12-15 10:01:35Z

2

Same problem when using:

 Oracle 9i Enterprise Edition 64bit 9.2.0.8.0 - on Sun Server Sun OS 10.
    JDBC 12.1.0.2.0 - ojdbc7.jar (thin driver)
    manifest info: Created-By: 20.75-b01 (Sun Microsystems Inc.)  
Implementation Vendor: Oracle Corporation Implementation-Version: 12.1.0.2.0

For me helped this solution, it is not the best one but it worked:

select to_clob(field_name) as field_name from table_name

Also if you are sure about the length of the field this solution might be helpful:

  select cast(field_name as char(10)) as field_name from table_name

answered Dec 15, 2015 at 10:01

Dmitry.M

3,0421 gold badge20 silver badges36 bronze badges

1 Comment

Zasz Over a year ago

This helped for me - Is there a way to get the correct text displayed in query results window, without to_clob()bing many text columns?

geert3 · Accepted Answer · 2017-11-02 11:46:54Z

2

I am happy to resport that I solved this problem, but, it was an extremely long and painfull search in my code, when in fact, the problem was actually in my Glassfish server.

I read somewhere on this site that someone had a similar problem and they had REMOVED some jar's with the following name: ojdbc18???.jar . When I searched for this, I didn't find any, so I moved on.

After pulling out most of my hair, I decided to start browsing around on the webserver, and sure enough, there were other jars in the lib dir of the WEB-SERVER, which would be visible to my app.

Those jar's were ojdbc14???.jar. Wholly freak. As soon as I DELETED those ojdbc14's, everythig worked fine.

To that end, search your class path and make sure jar's like that can not be found.

Good luck, Nick

edited Nov 2, 2017 at 11:46

geert3

7,3771 gold badge36 silver badges54 bronze badges

answered Aug 14, 2013 at 14:50

Nikita

211 silver badge4 bronze badges

Comments

Gimenez · Accepted Answer · 2021-03-26 13:31:42Z

1

The problem is a little-known incompatibility between the ojdbc8.jar driver and Oracle 9i. Replace the driver ojdbc8.jar with ojdbc6.jar and the problem will be solved. I had the same problem using Weblogic12. I had to replace the driver in Weblogic12 to create the datasource. Here is a link with a small test showing the problem:

https://github.com/paulog29/jdbc-oracle9i

Good Luck.

answered Mar 26, 2021 at 13:31

Gimenez

111 bronze badge

Comments

bowmore · Accepted Answer · 2013-07-08 16:00:24Z

0

Ignoring the all in your SQL, your basic problem is that you try to get 3 fields, when your sql query only selects 2 :

SELECT ALL BADGE_NUMBER, PERSON_CODE FROM BADGETABLE ...

answered Jul 8, 2013 at 16:00

bowmore

11.4k2 gold badges38 silver badges45 bronze badges

2 Comments

user2369812 Over a year ago

sorry for the confusion but I have already removed this line from my code. I was trying to eliminate fields to see where the problem was. My issue is definitely with the varchar2 field in Oracle. It is returned as an empty string in Java.

bowmore Over a year ago

What value do you get for that column when you execute the query in SQL plus?

Collectives™ on Stack Overflow

java returns empty String value for oracle VARCHAR2

6 Answers 6

1 Comment

Comments

1 Comment

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

Comments

1 Comment

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related