Insert after Select (Java sql)

Question

Struggling with some java / sql code that carries out a stored procedure call, followed by a select followed by an insert into –

The call to the stored procedure works and creates the ‘duration’ table –

while(true) {

  boolean var1 = false;
  Connection var2 = DBConnPool.getInstance().getConnection();
  Statement var3 = null;
  ResultSet var4 = null;

try {
     var3 = var2.createStatement();
     String var5;
     var5 = "CALL totalduration('%', NOW() - INTERVAL 20 MINUTE, NOW());";
     var3.executeQuery(var5);

The select statement then runs and correctly outputs multiple rows –

var5 = "SELECT duration.name, zone, duration, asset.id as assetid, node.id as nodeid\n" +
        "FROM duration\n" +
        "inner join asset\n" +
        "on asset.name = duration.name\n" +
        "inner join node\n" +
        "on node.zonename = duration.zone\n" +
        "where Duration > '00:10:00'\n" +
        "and Zone = 'Reception'\n" +
        "group by duration.name DESC";
        var4 = var3.executeQuery(var5);

I then want to insert into a table data from the resulting ResultSet, if I use the following then it inserts only 1 row

 If (var4.next()) 
             {
                int var6 = var4.getInt("assetid");
                int var7 = var4.getInt("nodeid");
                String var8;
                var8 = "insert into alerts (ts, assetid, alerttypeid, nodeid, ack) values (now(), " + var6 + ", " + 11 + ", " + var7 + ", 0)";
                var3.executeUpdate(var8);
         }

So, I assumed the following would apply the insert to all the ResultSet rows –

while(var4.next()) 
         {
            int var6 = var4.getInt("assetid");
            int var7 = var4.getInt("nodeid");
            String var8;
var8 = "insert into alerts (ts, assetid, alerttypeid, nodeid, ack) values (now(), " + var6 + ", " + 11 + ", " + var7 + ", 0)";
            var3.executeUpdate(var8);
         }

But the error ‘Operation not allowed after ResultSet closed’ implies that var4 is closed?

I am obviously doing something silly and perhaps the insert into should be before the select so would appreciate some guidance on the ‘correct way’ to solve this.

Regards Active

Always use PreparedStatement when you have parameters. Take a look — Gurwinder Singh
– Gurwinder Singh, Commented Oct 9, 2017 at 7:18
Why though does the insert occur only once and not for every row? The insert probably needs to be before the select but I am not sure of the correct syntax. — Active
– Active, Commented Oct 9, 2017 at 7:21
FYI : you should names your variables something more usefull than var# — AxelH
– AxelH, Commented Oct 9, 2017 at 7:37

AxelH · Accepted Answer · 2017-10-09 07:54:16Z

1

JavaDoc from ResultSet :

A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.

You are using Statement var3 to execute the update query in your loop. Since this is the same Statement used to recover ResultSet var4, that instance will be close on the next execute#. This is why you are able to read the first line, execute the update and then the error occus.

You need to use a different Statement instance to keep the ResultSet open. Indeed, a ResultSet is bind to the Statement.

Note :

var# are not good names for variable, stmt, rs, ... are some names that fit more and are easy to understand what each instance are without searching the declaration.
Note that you should also close the Statement, ResultSet, ... when you are done with them (even in case of exception). Here, I will add in the following sample a try-with-resource
Use PreparedStament instead of concatenate String, this will be more readable and in case of String value, this will be safer (against SQL-Injection)

Sample (with some improvement from the notes) :

try (PreparedStatement ps = conn.prepareStatement("insert into a(b, c) values (?, ?)") ) {
    while(var4.next()) {
        ps.setInt(1, rs.getInt("b"));
        ps.setInt(2, rs.getInt("c"));
        ps.executeUpdate();
    }
} catch( SQLException e){
    ...
}

edited Oct 9, 2017 at 7:54

answered Oct 9, 2017 at 7:42

AxelH

14.6k2 gold badges27 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Active Over a year ago

Thanks AxelH, though I get try-with-resources not supported in source 1.6 (using JDK1.6), is there an equivalent command for 1.6?

AxelH Over a year ago

@active you have to do it yourself in the finally clause, checking in order if the instance is null or close it (with try-catch) for each (ResultSet, Statement, Connection). The connection should be release to the pool in your case.

Active Over a year ago

Thanks AxelH, could you provide an example finally clause (I am a newbie)

Active Over a year ago

Thanks, I will try and get my head around that though I may have to get some external help on this one. It seems overly complex (to me)

AxelH Over a year ago

@active What is ? A PreparedStatement is a specific Statement that you can parametrize, you set the query once and set the parameter on the fly to execute the query. If you need more help on this, I suggest you take a look to some tutorial on JDBC.

Collectives™ on Stack Overflow

Insert after Select (Java sql)

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related