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**](https://stackoverflow.com/questions/601300/what-is-sql-injection) — Gurwinder Singh, Oct 09 '17 at 07: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, Oct 09 '17 at 07:21
FYI : you should names your variables something more usefull than `var#` — AxelH, Oct 09 '17 at 07:37

AxelH · Accepted Answer · 2017-10-09T07:54:16.757

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 09 '17 at 07:54

answered Oct 09 '17 at 07:42

AxelH

14,325
2
25
55

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? – Active Oct 09 '17 at 08:25
@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. – AxelH Oct 09 '17 at 08:31
Thanks AxelH, could you provide an example finally clause (I am a newbie) – Active Oct 09 '17 at 08:34
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) – Active Oct 09 '17 at 08:49
@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. – AxelH Oct 10 '17 at 05:33

Insert after Select (Java sql)

1 Answers1