CODEWITHSUNDEEP
javaoracle-databasejdbcplsql
Fuzzy Analysis
Fuzzy Analysis

Reputation: 3188

ResultSet.getNext() not working with PreparedStatement

I am trying to figure out why ResultSet.next() is never true in Java code that I am writing after I execute a SQL query that returns results from an Oracle 11g table into that ResultSet... it seems as though the code does not pick up a returned ResultSet's contents correctly when using a PreparedStatement in a java.sql.Connection. Any help appreciated, here are the details:

Table:

  CREATE TABLE "SHANDB"."ABSCLOBS" 
   (    "ID" NUMBER, 
    "XMLVAL" "XMLTYPE", 
    "IDSTRING" VARCHAR2(20 BYTE)
   )

Data:

INSERT INTO absclobs VALUES ( 1, 
   xmltype('<?xml version="1.0"?>
               <EMP>
                  <EMPNO>221</EMPNO>
                  <ENAME>John</ENAME>
               </EMP>', '1'));

INSERT INTO absclobs VALUES (2, 
   xmltype('<?xml version="1.0"?>
               <PO>
                  <PONO>331</PONO>
                  <PONAME>PO_1</PONAME>
               </PO>', '2'));

Java code I am running to get values from the above to test the code:

  public static void main(String[] args) throws Exception {
    try {
      String url = "jdbc:oracle:thin:@//localhost:1521/xe";
      String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
      String user = "shandb";
      String password = "test";
      Class.forName(driver);
      connection = DriverManager.getConnection(url,user, password);
      String selectID1 = "SELECT a.xmlval.getClobval() AS poXML FROM absclobs a where idstring=? and id=? ";      
      PreparedStatement preparedStatement = connection.prepareStatement(selectID1);

      preparedStatement.setString(1, "1"); 
      preparedStatement.setInt(2, 1); 

      rowsUpdated = preparedStatement.executeQuery(); 
      while(rowsUpdated.next()){
          String clobxml = rowsUpdated.getString(1);
          System.out.println(clobxml);
      }
    } catch (ClassNotFoundException cnfe) {
      System.err.println(cnfe);
    } catch (SQLException sqle) {
      System.err.println(sqle);
    }
    finally{
        System.out.println("Rows affected: " + rowsUpdated);
        connection.close();     
    }

  }

This part of the above code is never run, which I don't understand:

  while(rowsUpdated.next()){
      String clobxml = rowsUpdated.getString(1);
      System.out.println(clobxml);
  }

... however the final print statement shows that the ResultSet is not empty:

Rows affected: oracle.jdbc.driver.OracleResultSetImpl@15f157b

Does anyone know why I can't display the actual retrieved XML clob contents, and/or why the while block above is never true?

Thanks :)

Upvotes: 0

Views: 1995

Answers (1)

Jon Skeet
Jon Skeet

Reputation: 1502376

Your diagnostics are incorrect - this:

Rows affected: oracle.jdbc.driver.OracleResultSetImpl@15f157b

doesn't show that the result set is non-empty. It just shows that the value of rowsUpdated is a reference to an instance of oracle.jdbc.driver.OracleResultSetImpl, which doesn't override toString(). That can very easily be empty.

I suspect the problem is just that your WHERE clause doesn't match any records. For the sake of diagnostics, I suggest you change it to just:

String selectID1 = "SELECT a.xmlval.getClobval() AS poXML FROM absclobs a";

(and get rid of the parameter-setting calls, of course). That way you should be able to see all your table's values. You can then work on discovering why your WHERE clause wasn't working as expected.

(As an aside, it's not clear why you haven't declared connection or rowsUpdated in the code in the question. They should definitely be local variables...)

Upvotes: 3

Related Questions