4 Replies Latest reply on Oct 26, 2006 2:48 PM by 541804

    Exception when executing prepared statement

    541804
      I have the following code:

      public static Vector doSubjectSearch(String search_key) {
           Vector vec = new Vector();
           try {
           // Prepare SQL
           Connection con = getConnection();
           PreparedStatement statement = con.prepareStatement
                ("SELECT * FROM item, author WHERE item.i_a_id = author.a_id AND item.i_subject = ? AND rownum <= 50 ORDER BY item.i_title");
           
           // Set parameter
           statement.setString(1, search_key);
           ResultSet rs = statement.executeQuery();
           
           // Results
           while(rs.next()) {
                vec.addElement(new Book(rs));
           }
           rs.close();
           statement.close();
           con.commit();
           returnConnection(con);
           } catch (java.lang.Exception ex) {
           ex.printStackTrace();
           }
           return vec;     
      }

      At runtime it throws the following exception:

      java.sql.SQLException: ORA-00936: missing expression
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
      at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623)
      at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:181)
      at oracle.jdbc.driver.T4CPreparedStatement.execute_for_rows(T4CPreparedStatement.java:543)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1028)
      at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2888)
      at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:2960)
      ...

      Anyone know what the problem is?
        • 1. Re: Exception when executing prepared statement
          414247
          I would be curious to see what parameter value(s) are set
          when the failure occurs. I suggest printing the parameter
          value out when you do the printStackTrace()...
          Joe Weinstein at BEA Systems
          • 2. Re: Exception when executing prepared statement
            Avi Abrami
            user538801,
            Does the query work when you try it from SQL*Plus?
            I don't think so, because I don't think you can do "select *" when you are querying two or more tables.

            In any case, the Oracle JDBC Developer's Guide and Reference advises against using "select *" and recommends explicitly listing the required columns instead.

            Good Luck,
            Avi.
            • 3. Re: Exception when executing prepared statement
              castorp
              I don't think so, because I don't think you can do
              "select *" when you are querying two or more tables.
              SELECT * is legal with more than one table. It will simply return all columns from all tables.
              • 4. Re: Exception when executing prepared statement
                541804
                I would be curious to see what parameter value(s) are
                set
                when the failure occurs. I suggest printing the
                parameter
                value out when you do the printStackTrace()...
                Joe Weinstein at BEA Systems
                I did that and on one of the errors the search_key is the string MYSTERY

                To answer Avi's question, it does work in SQLPlus when I substitute 'MYSTERY' for the ? in the query. So I assume that somehow this value isn't being substituted in. That or my prepared statement is wrong. I don't have much JDBC experience so this is possible, but as far as I can tell it is the same as the examples that I have seen.