Close Out
ResultSet already closed – Issue
Ø Background:
In the current J2EE applications, various database related activities. It plays very important role in J2EE applications in the field of Information Management, Performance, etc. The common database operations are SELECT, INSERT, UPDATE, and DELETE stored procedures calls. These operations if not properly handled by the code, can lead to different issues such as performance hit, Deadlock and Timeout etc that can bring the complete application down. So one must not only careful design of the database tables, indexes, but also all acts to carry out carefully so it does not impede the application.
As mentioned above, the most common problems we may face Deadlock, Query timeout, poor performance due to improper searches Query structure or inefficient database design, Connection unavailability. But aside from these common problems / errors, we face a strange error while accessing ResultSet objects as "ResultSet already closed". This can occur in spite of your database design is good or your database handling code is clean. So, we must deal differently with this type of error. Let us refer a practical scenario where this problem might arise.
Ø Problem / Scenario:
Consider the following practical scenarios that can lead to the above question:
1. We have a function in class (Data Access Layer) that do the following things:
- The establishes the connection to the database.
- It creates the declaration object with the SELECT query (which returns a number of results)
- It extracts the result ResultSet object.
- In the loop for the results achieved ResultSet (in "while <>. Next ()" loop), for each iteration, call a private function and meeting the same Connection object.
2. The so-called private function uses the same Connection object. Its use, it executes the SELECT query. For this, the statement and creates new ResultSet object for that connection. We execute the query with the same Connection object and loop through the results that are retrieved and processed by this requirement. Finally, after all processing is complete, we close the ResultSet and Statement objects created in this function. It is not to close the Connection object.
3. So, if we return to the previous calling function, the Connection object is still open. But if we try the next record from the original ResultSet (which was established in the first function) to get by calling "<>. Next ()," It gives error "ResultSet already closed".
Ø Understanding with an example
- Let's say we first job as "abc ()" and the other function is called from abc "def ()".
- We SELECT query in the function abc () that takes say 10 results. So, we loop through each result to come through "<>. Next ()" to mention.
- In each iteration, we call "Def ()" and by the same Connection object with "autocommit as true".
- In "Def ()" function, run the SELECT query again using the same connection object. The process and the results before returning, in the "final" block, close the ResultSet and Statement objects created in this (DEF ()) function. But it does not close the Connection object.
- So when the control returns to abc () "function and try to go to the next iteration of the ResultSet object from" abc () "function, it says" all ResultSet closed ".
Refer to the " Appendix " section for the code above scenario.
Ø Reason:
Let's dig further into the problem. We will continue with the same given above example to understand it in a better way. In function 'Def (), we used the same Connection object that is created in "abc ()" function. In 'Def () "function, we only closed the ResultSet and Statement objects, NOT the Connection object. If we close all Connection object, all open objects associated with, and to explain ResultSet is automatically closed. If we would have closed Connection object, it would have said that in the ResultSet "abc ()" function already closed. But in the above scenario, this has not happened. Despite holding open Connection object, we have the error saying "ResultSet already closed."
This issue is common when we have the same connection object indirect nested loops to use as described in the above example. The problem is one of the ResultSet object properties, which is set by default in Application Server. This feature is ResultSet "ResultSet Hold Ability", who gets the default value as "CLOSED_AFTER_COMMIT" (Value = 2). This problem is also related to the autocommit Connection property which is "set to" true standard. Following information indicates what happened in the above Scenario:
- When we use the SELECT query in "abc ()" function, we opened two items on the same route was primarily intended is "statement" object and the other is the cursor, "ResultSet" object.
- In the while loop of its ResultSet, a function called "Def () ", We pass the same connection object (with autocommit = true) to" def () "method. When this threshold, it is transmitted to all its existing features and settings ("abc ()" method Statement and ResultSet objects open on).
- "Def ()" method has its own SELECT query running on the same Connection object passed. Yes, if the declaration is made by "executeQuery ()", Transaction and realized by the standard (As autocommit by default: true) for that connection, close any open cursors java first, and then opens the new Cursor (ResultSet) in called method.
- So, already opened cursor "abc ()" method is closed at this time alone. That is why, when control returns back from called function to try and to the next iteration of <>, says, "ResultSet already closed".
Ø Solution:
- Auto-commit: first and probably the easiest solution is to set "autocommit = false" for the connection object immediately after making it. Hence, when we query the called function, it will not close the previously opened cursors if Commit is not happened yet. So, "concluded COMMIT after "does not appear.
- Pros: This is the easiest solution, which will ensure the prevention closing of the previously opened cursors on the same Connection object.
- Disadvantages: This is advisable if all questions are just the "SELECT" queries. So, will not commit. This prevents closure of the cursor of the outer loop and the application will run smoothly. But if we need this option for UPDATE queries using we must take great care to set autocommit false because it will not immediately update the rows until we specifically say
". Commit ()". Yes this is not a generic solution as such.
- ResultSet Hold Ability: There is an option in Application Server (Database Connection settings), where we can call the attribute value of "Hold ResultSet Ability" and "HOLD_OVER_COMMIT" (Value = 1) for the ResultSet cursor type, rather than 'closed after COMMIT. This option will prevent the closure is already open ResultSet (cursors) even if the inner loop (so-called method) transaction. This option is also Hold Sustainability can be set by the code as shown below:
Statement st =. create statement (ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
After closing the Connection object, open cursors defined with HOLD remain open. The cursor is placed for the next logical row of the table of results.
- Pros: This is probably the safest solution if we already have a similar situation in the code. Parameter setting in Application Server or declare HOLD_CURSOR parameter when creating Statement object of preventing the "ResultSet closed" error occurs. This option will prevent the code changes, including setting autocommit false reducing the risk other manual errors.
- Cons: By default, the setting for "CLOSE_AFTER_COMMIT, which closes all open cursors immediately after committing. So, when we say, not close cursors on commit and then the code should be well written for a control of the number of open cursor objects. We also ensure that all open items are closed when the process is over. Just close all the objects like ResultSet, Statement and Connection will serve the purpose.
- Joins / Inner Queries: Third solution to this problem could be avoid nested loops. Write the query in such a way that they avoid nested loops (function call). This will avoid calling a different function in the ResultSet loop and in turn, will avoid this exception.
- Pros: Along with avoiding the nested calls, it will also prevent that temporary adoption of Statement and ResultSet object in the called function.
- Disadvantages: This option will need to formulate of the nested / inner questions that the application performance may suffer if the numbers of records in the database more. This will affect more in case of large user-base applications. Even if each query is more than 2-3 tables themselves, then this option is the least advisable.
- Creation the temporary connection: Forth option could be, not the Connection object to the so called private function. Instead, create the new connection objet in the called function and close the same function. This prevents the closure of the ResultSet object in the calling function as ResultSet is no longer associated with the Connection object in the calling function.
- Pros: This option will prevent closing the outer method ResultSet object as the Connection object would be different.
- Disadvantages: This option could lead to the creation of a large number of temporary objects in the Connection private functions. Sometimes when there are no connections available in the connection pool, the application time-out waiting for the Connection available. Nor is it advisable to make as many temporary connection objects when using this option, we should be very careful during encoding and the need to close all temporary objects created when their purpose has been saved.
Ø Conclusion:
As discussed above, there are four most common options and potential for errors. Each option has advantages and disadvantages. But with the amount of coding and testing should be done after the code changes, resource use and impact on performance, the second option (changing Ability of the ResultSet Hold option in the Application Server or via code), better and recommended. This will also prevent the risk of manual coding errors. Still, the final decision always depends on the situation and Demand Project. So, while deciding, we must consider all possible advantages and disadvantages that we will have after choosing certain approach.
Ø Annex:
Below is the pseudo-code to be executed, giving error as "ResultSet already closed" on the highlighted line:
Public <object> </ object> <object> </ object>
About the Author
Ashutosh Koshe is Team Lead in IBM. Prior to that, he was working in Infosys. He has 5 years of IT experience out of which has 3 years of team lead experience. He has extensively worked in Java-J2EE tecnology and has handeled various complex issues for Programming, Performance, Database. He has also handeled Quality, Performance, Automation of the Projects. He was involved in some of the technical Researhes for the company. He also conducts the Interviews for candidates.
How to connect and disconnect for a good a Yahoo email account?
How to connect and disconnect for a good a Yahoo email account?
If you want to specify yahoo page you need to delete entire account visit Account remove. https / / edit.yahoo.com / config / delete_user you will be asked to to report or verify ur password for the account you want to remove. after the deletion process is initiated, ur account can never be recovered. Good Luck ^ _ ^
Pau Gasol, on how to close out
|
|
Madame Alexander Wicked Witch 10″ Doll Wizard of Oz! CLOSE OUT PRICE! $109.95 |
|
|
Kidz’n'Cats MAREIKE- SONJA HARTMANN -18″ vinyl PLAY DOLL – NEW! Closeout Price! $107.95 |
|
|
16″ DELILAH STEAMPUNK BJD by Ashton-Drake – MANUFACTURER’S CLOSEOUT PRICE- NEW! $89.95 |
|
|
Madame Alexander Miss America Beauty Queen 10″ Cissette Doll CLOSE OUT PRICE! $86.95 |
|
|
Wizard Of Oz Glinda The Good Witch 10″ Doll CLOSE OUT PRICE! FREE USA SHIPPING! $84.95 |
|
|
CARRIE Lifelike & Interactive Baby by Ashton-Drake -16″ CLOSEOUT SALE!! NEW!! $80.95 |
|
|
Madame Alexander Sleeping Beauty 8″ Collectors Doll! CLOSEOUT PRICE! $80.95 |
|
|
Wizard Of Oz Dorothy In The Poppy Fields 10″ Collectible Doll CLOSE OUT PRICE! $79.95 |
|
|
Madame Alexander Elegant Equestrian + Horse Dolls CLOSE OUT PRICE! $74.95 |
|
|
MAGIC BUBBLE GLINDA THE GOOD WITCH by Madame Alexander – NEW!! 10″ – CLOSEOUT!! $72.95 |
|
|
DOROTHY IN THE POPPY FIELDS by Madame Alexander – NEW!! 10 Cissette – CLOSEOUT!! $72.95 |
|
|
Madame Alexander Cowardly Lion Wizard of Oz 8″ Doll! Close Out Price! $59.99 |
|
|
Madame Alexander Peppermint Plie Ballerina Collectible 8″ Doll CLOSE OUT PRICE! $69.95 |
|
|
Madame Alexander Tin Man Wizard of Oz 8″ Doll! CLOSE OUT PRICE! $59.99 |
|
|
Madame Alexander Scarecrow Wizard of Oz 8 Inch Doll! CLOSE OUT PRICE! $59.99 |
|
|
CLOSE-OUT SPECIAL Angel LE Reborn KIT by Shawna Clymer GREAT price CUTE lil CHUB $44.95 |
|
|
Madame Alexander Cotton Candy Ballerina 8-inch Collectible Doll CLOSE OUT PRICE! $54.95 |
|
|
VOGUE 1/2 PRICE CLOSEOUT 21ST CENTURY JILL STARLET WITH COMPLETE OUTFIT $45.99 |
|
|
Dollhouse Miniature Vintage Horse Statue Lamp 1:12 ’90′s? from store closeout $35.00 |
|
|
Porcelain Umbrella Doll Victorian JUDY 18″ Goldenvale Closeout! $16.99 |
|
|
German Bully Babies Miniatures 40 Pieces CLOSE OUT $29.95 |
|
|
Creole Porcelain Victorian 18″ Black Doll Closeout! $9.99 |
|
|
German Bully Babies Miniatures 20 Pieces CLOSE OUT $18.95 |
|
|
doll shoes, S254 Dressy Boot size 1=3 7/8″ close-out $2.00 |
|
|
doll shoes, S254 Dressy Boot size 3=3 1/4″ close-out $2.00 |
|
|
doll shoes, S254 Dressy Boot size 5=2 13/16″ close-out $2.00 |
|
|
doll shoes, S254 Dressy Boot size 6=2 9/16″ close-out $2.00 |
|
|
doll shoes, S254 Dressy Boot size 4=3 1/16″ close-out $2.00 |
|
|
Glasses Case with #GCASE pair of glasses CLOSE OUT $1.81 |
|
|
DOLL BEDDING Blanket Cream Blue Lace Close-Out 30″ $7.00 |
|
|
DOLL BEDDING Blanket Cream Pink Lace Close-Out 30″ $7.00 |
|
|
CLOSEOUT SALE! WHITE NYLON DOLL PANTIES for16-18″ DOLLS $2.50 |
|
|
CLOSEOUT SALE! WHITE NYLON DOLL PANTIES for 14-16″DOLLS $2.50 |
|
|
CLOSEOUT SALE WHITE NYLON DOLL PANTIES for 20-25″ DOLLS $2.50 |
|
|
CLOSE-OUT New BLACK ICE SKATE/SHOES with LACES for 14″ IDEAL TONI P-90 DOLLS $1.35 |
|
|
CLOSE-OUT New BLACK ICE SKATE/SHOES with LACES for 14″ AC BETSY McCALL DOLLS $1.35 |
|
|
Closeout!! SANTA HATS for 8″ DOLLS Ginny, Ginger, Muffie, Wendy BUY 4 NEXT YEAR $3.59 |
|
|
AMAZING!! Madame Alexander 20″ Mary Mine #6450 MINT/CRISP Box/Tags CLOSE-OUT! $65.00 |
|
|
“FRASIER” Fudgsicle Doll By JEANNE SINGER 21″ Figures 1998 Boy Dog MIB CLOSEOUT! $47.99 |
|
|
“SAMANTHA” by HILDEGARD GUNZEL Doll From Paradise Galleries MIB CLOSEOUT! $47.99 |
|
|
“ANGELICA” Osmond Doll Angelica’s 1st Birthday Child 22″ Best Friends CLOSEOUT! $39.99 |
|
|
“CHELSEA” Doll by CONNIE WALSER DEREK ~ Hamilton Collection COA CLOSEOUT! $39.99 |
|
|
“RUSTY & RACOON BANDIT” By HOLLY HUNT Doll World Gallery MIB CLOSEOUT! $31.99 |
|
|
GLORI Dawn Doll Commemorative Limited Edition Doll NEW SALE CLOSE OUT $24.00 |
|
|
DALE Dawn Doll Commemorative Ltd Ed CLOSE OUT SALE $24.00 |
|
|
Mdm Alexander 100 Ann. 8″ Miniature Madame MIB/Stand/COA -CLOSE-OUT SALE!! $13.58 |
|
|
Soft Doll Duffel Trunk fits American Girl . CLOSEOUT! FUN colors! $10.00 |
|
|
Mdm Alexander LEGENDS 8″ Scarlett/Ashley’s Birthday MIB/COA – CLOSE-OUT SALE!! $9.99 |
|
|
Madame Alexander Poor Cinderella NMIB/Tags/Stand CLOSE-OUT SALE!! $9.99 |
|
|
Enchanted Evening Barbie Brunette Mint in Box CLOSE-OUT SALE!! $8.00 |
