Hi all,
I wrote a servlet which queries a database and returs the results to the calling client. Now I want to enable the client to cancel long-running queries. Is it possible to catch an event in the servlet e.g. if the client-user hits a "cancel" button?
Thank you!
Benni
I doubt it. Once the database gets the query, it begins processing it. I dont think anything in your server side JDBC will be sent to interrupt it. Even if it did, what is the user to do next? He can only retry the same query.
How about instead looking at your sql statement again and seeing if you can:
A: add a filter to the JSP page that allows the user to limit the returned amount of data. Example: If user enters 'B' in a textfield, then the sql becomes: "select * from person where lastName like '%B'.
Most users only want to see at most 30 or so rows returned, not 10000.
B: Optomize your sql statement - such as provide indexes on the database side. Also, only return those database table fields that the user needs, not all fields.
Thank you for your reply, I need to get all resulting rows.
Meanwhile I solved the issue by storing the last database statement in the session-context (session.setAttribute()) and remove it after the db-query thread stops. To cancel a long-running query I reconnect to my servlet wit a "cancel" parameter, restore the last (still active long-running) database statement with session.getAttribute() and use the Statement.cancel() function to stop the query.
Is there a better way to do it? Are there some security-issues with that solution?
Regards,
Benni
Your solution sounds ok, but I suspect the query is still being processed to completion over in the database. I dont believe there is any security issues. Only potential problem may be a hacker who keeps clicking the getQuery and cancel buttons to slow down the database to a crawl (or does so because he thinks retrying again and again may get the command completed on the nth try).
I checked it and found that Statement.cancel() really interrupts the query on the database :-)
