2 Replies Latest reply: Apr 25, 2012 6:21 PM by jschellSomeoneStoleMyAlias RSS

    PreparedStatement only returning 256 results

    721850

      Hi!
      I'm working on a project where the user can enter up to 1000 ids to fetch the corresponding objects from our Oracle database, connected using the 11.1.0.7.0 jdbc drivers. However, only 256 rows were available in the result. So I made a simple test, like so:

      public static void main(String[] args) throws Exception {         Class.forName("oracle.jdbc.OracleDriver");         Connection connection = DriverManager.getConnection("jdbc:oracle:thin:myuser/mypassword@myurl:1521:mydb");     PreparedStatement select = connection.prepareStatement("SELECT id FROM t01abc_regenh WHERE id in (?, ?,..., ?)"); // 1000 ?s         String[] idArray = new String[] {"010000010", "010000026", ..., "010000981"}; // 1000 ids         for(int i = 0; i<idArray.length; i++) {       select.setString(i+1, idArray);
      }

      ResultSet result = select.executeQuery();

      boolean hasNext = result.next();
      for(int i = 1; hasNext; i++) {
      System.out.println(i + " : " + result.getString(1));
      hasNext = result.next();
      }

      result.close();
      select.close();
      connection.close();
      }
      The database logs show that the query is using the 1000 ids, but only the first 256 results were printed. So I tried again, using a non-prepared statement:
      public static void main(String[] args) throws Exception {

      Class.forName("oracle.jdbc.OracleDriver");

      Connection connection = DriverManager.getConnection("jdbc:oracle:thin:myuser/mypassword@myurl:1521:mydb");
      Statement select = connection.createStatement();

      String[] idArray = new String[] {"010000010", "010000026", ..., "010000981"}; // 1000 ids

      StringBuilder query = new StringBuilder("SELECT id FROM t01abc_regenh WHERE id in (");
      for(int i = 0; i<idArray.length; i++) {
      query.append('\'').append(idArray[i]).append("', ");
      }
      query.delete(query.length() - 2, query.length());
      query.append(')');

      ResultSet result = select.executeQuery(query.toString());

      boolean hasNext = result.next();
      for(int i = 1; hasNext; i++) {
      System.out.println(i + " : " + result.getString(1));
      hasNext = result.next();
      }

      result.close();
      select.close();
      connection.close();
      }
      This time the full 1000 results where printed. Is this a general thing with prepared statements or is it an issue with the Oracle database or the jdbc drivers? Is there a way to increase the number of returned results? Please help!                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
        • 1. Re: PreparedStatement only returning 256 results
          rp0428
          >
          Is this a general thing with prepared statements or is it an issue with the Oracle database or the jdbc drivers?
          >
          None of the above - it is an issue with your code. You are comparing apples to oranges; an invalid comparison.

          The queries are not identical so you should not expect identical results.
          You are using this data
              String[] idArray = new String[] {"010000010", "010000026", ..., "010000981"}; // 1000 ids
          in two different queries.

          The first test constructs a query of the form
              "SELECT id FROM t01abc_regenh WHERE id in (010000010, 010000026,..., ?)"); // 1000 ?s
          And the second constructs a query of the form
              "SELECT id FROM t01abc_regenh WHERE id in ('010000010', '010000026',..., ?)"); // 1000 ?s
          That is the first query uses numeric values for the IN clause and the second query uses string values.
          If the actual data is VARCHAR2 these will not match the same values.

          A string value of '010000010' will not match a numeric value of 010000010 because when 010000010 is converted back to a string for the comparison there will be no leading zeroes so the converted value will be '10000010'

          So the first query tries to match '010000010' with '10000010' and they do not match.
          • 2. Re: PreparedStatement only returning 256 results
            jschellSomeoneStoleMyAlias
            aznan wrote:

            I'm working on a project where the user can enter up to 1000 ids to fetch the corresponding objects
            ...
            Please help!
            Besides the above you should note that that code is SPECIFICALLY open to sql injection attacks.

            The solution is to use replacement parameters ('?') with a Prepared Statement.

            And a side effect of that would be that it would solve your current problem too.