PreparedStatement executing successfully in oracle

Question

I have this below query that I execute using java PreparedStatement:

String dml=insert into users(name, addr, city, sex, dob) values(?,?,?,?,?);
PreparedStatement stmt = conn.prepareStatement(dml);
stmt.setString(1,"abcd");
stmt.setString(2,"def");
stmt.setString(3,"ghij");
stmt.setString(4,"m");
stmt.setString(5,"1-Jan-1987");
stmt.executeQuery();

It executes successfully when the database is Oracle, but when the database is Microsoft SQL, then it throws an exception "java.sql.SQLException: The executeQuery method must return a result set". Could someone please tell what is the issue here. Why is the same query executing successfully in oracle but not in microsft sql?

Answer 1

The answer is in the message - ExecuteQuery requires a result set. Use executeUpdate instead.

From the above Link:

• boolean execute() Executes the SQL statement in this PreparedStatement object, which may be any kind of SQL statement.

• ResultSet executeQuery() Executes the SQL query in this PreparedStatement object and returns the ResultSet object generated by the query.

• int executeUpdate() Executes the SQL statement in this PreparedStatement object, which must be an SQL INSERT, UPDATE or DELETE statement; or an SQL statement that returns nothing, such as a DDL statement.

the fact that it works on oracle is probably just a side effect which you've discovered cannot be relied upon.

Answer 2

use stmt.execute(); instead of stmt.executeQuery();

• stmt.execute(); or executeUpdate(); for INSERT, UPDATE, DELETE (etc.)
• stmt.executeQuery(); for SELECT

Answer 3

If you're performing an INSERT/UPDATE statement, you should be calling stmt.executeUpdate() rather than stmt.executeQuery(). I imagine there's a difference (though I don't know exactly what) between the Oracle and SQL Server drivers you're using that means that one works and the other one doesn't.

Answer 4

This depends upon driver that are using and underlying implementation of executeQuery() method. While using Java Prepared statement the underlying implementation allow this but the driver of SQL server doesnot allow this.

Try to use correct method to execute insert statement like executeUpdate().

go trough this links:-

Sql server:- http://msdn.microsoft.com/en-us/library/ms378540%28v=sql.90%29.aspx

Oracle:- http://docs.oracle.com/javase/1.4.2/docs/guide/jdbc/getstart/statement.html

Answer 5

try using the method executeUpdate instead of executeQuery.

since the query at hand is not a select-query, it fails. executeQuery is for select-queries, executeUpdate is for insert, delete and update-queries.

Answer 6

There is the problem on stmt.executeQuery();

• executeQuery() is used for SELECT sql operation

• executeUpdate() is used for INSERT, UPDATE and DELETE sql operation.

your query is for INSERT operation thus please use stmt.executeUpdate();