Oracle JDBC Driver ResultSet PreparedStatement Throws ORA-009900 invalid SQL statement Exception

Question

I am using Oracle JDBC Driver ojdbc6.jar. When executing a PreparedStatement the ResultSet is returned. However when trying to get details back of what has been saved, I am unable to get this and it throws an Exception.

Below is the code:

public Processor {

    private Connection connection;

    public Processor() throws Exception {
        connection = DriverManager.getConnection(url, username, password);
    }

    public int saveData(String name) throws Exception {
       PreparedStatement preparedStatement = connection.prepareStatement(name);
       preparedStatement.setString(1, name);
       ResultSet resultSet = preparedStatement.executeQuery();
       resultSet.next();
       String row = resultSet.getString("NAME");
       return resultSet.getRow();
    }

    public static void main(String[] args) throws Exception {
         Processor processor = new Processor();
         int row = processor.saveData(new String("INSERT INTO NAMES (name) VALUES (nameTable)"));
    }
}

The url, username and password are setup but not shown in the code. I can connect to the Oracle database and persist the data. However, the problem arises when calling resultSet.getString("NAME"). The following Exception is shown:

ORA-009900: invalid SQL statement
java.sql.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)

When executing the following code:

ResultSet resultSet = connection.createStatement().executeQuery("SELECT NAME FROM NAMES"));
resultSet.next();
String name = resultSet.getString("NAME");

This works. I have also executed in the SQL describe NAMES and the name attribute is displayed as NAME.

The issue is something to do with the PreparedStatement object returning the ResultSet data.

Any ideas?

EDIT:

ORA-00900: invalid SQL statement
java.sql.SQLSyntaxErrorException
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:389)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:382)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:675)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:227)
at oracle.jdbc.driver.T4C8Odscrarr.doODNY(T48COdscrarr.java:98)
at oracle.jdbc.driver.T4CPreparedStatement.doDescribe(T4CPreparedStatement.java:818)
at oracle.jdbc.driver.OracleStatement.getColumnIndex(OracleResultSetImpl.java:3711)
at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:2799)
at oracle.jdbc.driver.OracleResultSet.getString(OracleResultSet.java:498)
at com.example.ProcessorTest.testExecuteInsert(ProcessorTest.java:14)

Please include the full stacktrace. I find it hard to believe a `resultSet.getString` would throw this error; I'd sooner expect it on execute — Mark Rotteveel, Apr 03 '14 at 14:31

Mark Rotteveel · Answer 1 · 2014-04-03T14:39:34.097

0

Your saveData method is the cause because an INSERT does not produce a ResultSet, so executeQuery will produce an error, as there will be no ResultSet.

Also you should only use connection.prepareCall(String) for executing stored procedures, not for other types of statements. For a prepared statement you should use connection.prepareStatement(String).

Change your code to:

public int saveData(String name) throws Exception {
   PreparedStatement preparedStatement = connection.prepareStatement(name);
   preparedStatement.setString(1, name);
   return preparedStatement.executeUpdate();
}

BTW: I hope you are aware that this method uses the parameter name both for the query and as the parameter for the query (which really does not make a lot of sense).

And finally, the new String(...) you do in your main is totally unnecessary.

edited Apr 03 '14 at 14:39

answered Apr 03 '14 at 14:32

Mark Rotteveel

100,966
191
140
197

Sorry, it is actually connection.prepareStatement(name) but it is preparedStatement.executeQuery(). – user3189663 Apr 03 '14 at 14:47
I couldn't copy the code into the browser, so I had to write the code. I want to receive data that was inserted through the get() methods. I have shown the stack trace above. – user3189663 Apr 03 '14 at 14:48
I need to get the ID the unique identifer as the return of the result. That's why I need to get the details of the Result Set. – user3189663 Apr 03 '14 at 14:55
I'd suggest you post your actual code, because I have the feeling something is missing. However getting the generated ID with Oracle as not as simple as this. You might want to take a look at http://stackoverflow.com/questions/1004128/obtain-id-of-an-insert-in-the-same-statement – Mark Rotteveel Apr 03 '14 at 15:03

SparkOn · Answer 2 · 2014-07-14T04:42:07.373

First of all this line of code

int row = processor.saveData(new String("INSERT INTO NAMES (name) VALUES (nameTable)"));

As you have specified that the method saveData() takes argument as String so there is no need of any new String(...) here and moreover you are using PreparedStatement so you can use parameterized query something like this

int row = processor.saveData("INSERT INTO NAMES (name) VALUES (?)");

Then you can use this statement in saveData()

preparedStatement.setString(1, "name");

Finally you are updating the table not quering it so change this

preparedStatement.executeQuery();

to

return preparedStatement.executeUpdate();

One more thing you should be very specific about your method name. You should always name a method based on what it is going to do. Like saveData() here must be used only for saving data not querying the data as you were trying to do by using the ResultSet. If you want to retrieve the data then use some other method.

Oracle JDBC Driver ResultSet PreparedStatement Throws ORA-009900 invalid SQL statement Exception

2 Answers2