0

Im implementing a subquery in java which requires 2 parameters to be fed using a prepared statement.

Passing parameter within the inner query is working but then parameter is not passed to the outer one.

During execution i get an error on stacktrace saying :No operations allowed after statement closed. and hence the prepared statement returns wrong results.Question: How best can i pass the parameter successfully to the outer table.

Below is a sample code of my implementation

pst = conn.prepareStatement("SELECT slot.time_slot_id,slot.`date`,slot.count FROM 
    (SELECT time_slot_id,ue.`date`,COUNT(ue.user_id) AS count FROM user_event ue 
    RIGHT JOIN `time_slot` t ON ue.time_slot_id = t.id
    WHERE ue.status= 1 AND event_id=?
    GROUP  BY ue.`date`,time_slot_id
) AS slot WHERE slot.count >=? ");

pst.setInt(1,eventId);
//here is the parameter that is passed to the outer table
pst.setInt(2,count);
rs = pst.executeQuery();

here is an update for the entire method am implementing as below;

public static DefaultListModel Fetch(int eventId){
    DBconnection.connect();
    DefaultListModel fetchedSlots= new DefaultListModel();
    try{ 
        //
        pst = conn.prepareStatement("SELECT slot.time_slot_id,slot.`date`,slot.count FROM \n" +
        "(SELECT time_slot_id,ue.`date`,COUNT(ue.user_id) AS count FROM user_event ue \n" +
        "RIGHT JOIN `time_slot` t ON ue.time_slot_id = t.id\n" +
        "WHERE ue.status= 1 AND event_id=?\n" +
        "GROUP  BY ue.`date`,time_slot_id) AS slot\n" +
        "WHERE slot.count >? ");
        pst.setInt(1,eventId);
        //here a function is invocked using eventId to get counts
        pst.setInt(2,FetchCounts(eventId));
        rs = pst.executeQuery();
        while(rs.next()){
            fetchedSlots.addElement(new ModelSlot(rs.getInt("time_slot_id"),rs.getString("date")));
        }
    DBconnection.CloseConnection();
    }catch (SQLException ex){
        System.out.println(ex.getMessage());
    }
    return fetchedSlots;
}
Ford
  • 1
  • 2
  • This looks like some weird mix of PHP and Java code. Please post the actual Java code. – john16384 Apr 04 '17 at 12:10
  • 1
    The error you mention is never because of sub-query params not being allowed. Pls post the full code, including the line where you get this error. – dev8080 Apr 04 '17 at 12:12
  • can you please show us the error and some other code how you open and close your connection? – Youcef LAIDANI Apr 04 '17 at 12:12
  • the error in stack trace is : No operations allowed after statement closed. – Ford Apr 04 '17 at 12:17
  • @Ford are there any other errors? because this (No operations ...) is not the one which is causing issue to your code, may be you are looking at incorrect log file – Vasu Apr 04 '17 at 12:18
  • pst is declared as a prepared statement and conn is declared as a connection to database. All this are done in a database connection class which i can ascertain the access to database is successful – Ford Apr 04 '17 at 12:19
  • Then can you post the full method? – Vasu Apr 04 '17 at 12:20
  • @javaguy if i test my code by manually passing a value to the second required parameter, i get a correct result but then i require it passed to the prepared statement with as variable count as in: pst.setInt(2,count);before executing. – Ford Apr 04 '17 at 12:26

2 Answers2

0

I assume that the error happen in case like this :

Open statement
...
Use statement
...
Close statement           <---------- You can't use your statement after close it
...
Use same statement again  <---------- You are here, your statement is close

To solve your problem you have two choices :

  • Close the statement in the end of your work, you can check this Closing Database Connections in Java
  • Open a new statement, for example statement = connection.createprePareStatement();

EDIT

Like i said before, your problem is with connection, when you call this method :

pst.setInt(2, FetchCounts(eventId));
//---------------^^^

In fact this open a new connection, then close it in the end, so when you execute your query it rs = pst.executeQuery();, the connection is closed, and this cause this problem, so don't close the connection in your method FetchCounts(T eventId);.

Community
  • 1
  • 1
Youcef LAIDANI
  • 55,661
  • 15
  • 90
  • 140
  • @YCF_L.i have a single class for managing the database connection with methods for opening and closing ie DBconnection.connect() for opening connection implementation and DBconnection.CloseConnection(); for closing. as illustrated in the update above.. – Ford Apr 04 '17 at 12:50
  • no @Ford it is not connection check the statement if it is close or open – Youcef LAIDANI Apr 04 '17 at 12:53
  • try it, and let me know the result @Ford please – Youcef LAIDANI Apr 04 '17 at 13:08
  • 1
    i have researched and done several trials. i'll get back later for an update. thanks once more. – Ford Apr 05 '17 at 10:02
0

After trials, i concatenated the second parameter to the prepared statement rather than before and it worked. AS below;

pst = conn.prepareStatement("SELECT 
slot.time_slot_id,slot.`date`,slot.count FROM \n" +
        "(SELECT time_slot_id,ue.`date`,COUNT(ue.user_id) AS count FROM user_event ue \n" +
        "RIGHT JOIN `time_slot` t ON ue.time_slot_id = t.id\n" +
        "WHERE ue.status= 1 AND event_id=?\n" +
        "GROUP  BY ue.`date`,time_slot_id) AS slot\n" +
        "WHERE slot.count >" + FetchCounts(eventId));
        pst.setInt(1,eventId);
        rs = pst.executeQuery();

AS evident above, the result of the invoked method FetchCounts(eventId) is passed directly to the outer table of the subQuery within the prepared statement.

Ford
  • 1
  • 2