parameterized insert/update in sql

Question

I am trying to insert into a db that I have, and I'd like to do so through parameters. I am connecting to a postgres db using java.

I can connect to the db just fine. I know that because I have various operations that I am using that are already working were I can see, and update existing rows in my db. I am having trouble with INSERT.

I have the following:

private String _update_rentals = "INSERT into rentals (cid, mid) values (?,?)"; 
private PreparedStatement _update_rentals_statement; 

private String _update_movie_status = "UPDATE Movie SET checkedout = true WHERE mid = ?";
private PreparedStatement _update_movie_status_statement;

And I initialize them:

_update_movie_status_statement = _customer_db.prepareStatement(_update_movie_status); 
        _update_rentals_statement = _customer_db.prepareStatement(_update_rentals);

And

while (movieAvail.next()){
            System.out.println(movieAvail.getBoolean(1));
            if (movieAvail.getBoolean(1) == false){
                //Do chekcout

                _update_rentals_statement.clearParameters();
                _update_rentals_statement.setInt(1, cid);
                _update_rentals_statement.setInt(2, mid);
                _update_rentals_statement.executeQuery(); 

                _update_movie_status_statement.clearParameters();
                _update_movie_status_statement.setInt(1, mid);
                _update_movie_status_statement.executeQuery(); 


                System.out.println("Enjoy your movie!"); 
            } 
}

I am getting an error with both of the executeQuery() calls. For some reason I am getting the following error with both:

Exception in thread "main" org.postgresql.util.PSQLException: No results were returned by the query.

I looked at other posts, and I believed that I was following syntax for both insert/ update correctly, so maybe I am overlooking some aspect of this.

This is all part of a larger code base, so I did not want to include the methods these pieces of code are in. But these are the isolated instances which play a part with this code.

I'm assuming you actually created the PreparedStatement instances? — DBug
– DBug, Commented Nov 16, 2015 at 22:24

Ivaylo Toskov · Accepted Answer · 2015-11-16 22:29:38Z

In general, when you execute a query, you are willing to retrieve some kind of information from the database. This is usually the case when you are executing SELECT queries. However, with INSERT and UPDATE statements, you are not querying the database, you are simply executing an update or inserting new rows. In the documentation of PreparedStatement you can see in which cases an exception is being thrown when you try to call executeQuery:

Throws: SQLException - if a database access error occurs; this method is called on a closed PreparedStatement or the SQL statement does not return a ResultSet object

So in your case the problem is that your statements do not return a ResultSet. You should use execute or executeUpdate instead. The former simply executes the update, while the latter does the same, but also returns the number of affected rows.

DBug · Accepted Answer · 2015-11-16 22:27:17Z

1

I think the main issue is that you are calling executeQuery(), which expects a result to be returned, but Insert/Update are not queries and don't return a result. Try just calling execute().

answered Nov 16, 2015 at 22:27

DBug

2,5661 gold badge14 silver badges26 bronze badges

1 Comment

mufc Over a year ago

This was exactly it. Thank you!

Collectives™ on Stack Overflow

parameterized insert/update in sql

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related