Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null

Question

I am retrieving the certain data from table using key. I am using hibernate query to retrieve the data.

I am able to retrieve the data for particular key . But for some other keys i am getting error or exceptions. the exception i am getting is

[1/17/13 14:07:41:819 IST] 0000004c LongType      I **org.hibernate.type.NullableType nullSafeGet could not read column value from result set: BRAND1_23_2_; [jcc][t4][10120][10898][3.58.81] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null**
[1/17/13 14:07:41:822 IST] 0000004c JDBCException W org.hibernate.util.JDBCExceptionReporter logExceptions SQL Error: -4470, SQLState: null
[1/17/13 14:07:41:823 IST] 0000004c JDBCException E org.hibernate.util.JDBCExceptionReporter logExceptions [jcc][t4][10120][10898][3.58.81] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null
[1/17/13 14:07:41:826 IST] 0000004c DefaultLoadEv I org.hibernate.event.def.DefaultLoadEventListener onLoad Error performing load command
                                 org.hibernate.exception.GenericJDBCException: could not load an entity: [com.travelport.soa.gds.airline.brandedfares.entity.FareCollection#490]
    at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:126)
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:114)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)

post some code through which you are trying this – TheWhiteRabbit Jan 17 '13 at 09:03 — TheWhiteRabbit, Jan 17 '13 at 09:03

score 3 · Accepted Answer · answered Jan 17 '13 at 09:12

3

You fetched an entity that has a one-to-many relationship represented in Java as a lazy-loaded Collection and you are trying to iterate over that collection after you have already closed the Hibernate session within which you have fetched the object. You must either fetch the collection eagerly or widen the session boundaries to enclose your iteration code.

answered Jan 17 '13 at 09:12

Marko Topolnik

195,646
29
319
436

Where actually this closing of session taking place ? anyhow i am not dealing with closing/starting the session explicitly ..?? – saagaravk Jan 17 '13 at 09:19
If you are using some kind of declarative transaction management, you must be aware of the transaction scope provided to you by the framework. I suppose the best choice will be to fetch the collection eagerly. – Marko Topolnik Jan 17 '13 at 09:21
Indeed. Probably the most commonly occurring error and consequently the most common reason for (completely unnecessary) eager loads. And it all stems from failure to understand two of the most basic and fundamental concepts of Hibernate: Persistent Collections and Session lifecycle boundaries. – pap Jan 17 '13 at 14:23
@pap For the record, I never use lazy loading or any other magic that shoots random SQL at random times. I write HQL specific to each task, which fetches exactly what I need. I find the whole idea of ORM and "persistent state management" distasteful and more trouble than it's worth. I still prefer HQL for the qualities of database neutrality and separation of concerns between join conditions and business-logic conditions. – Marko Topolnik Jan 17 '13 at 14:32
@MarkoTopolnik I sympathize, but it begs the question: why are you using Hibernate at all, if you're not going to leverage it's inherent strengths? And to add to the record, if you know what you're doing and your domain is suited for ORM, then Hibernate is really powerful. Especially when you can fully utilize capabilities like L2 cache and, yes, lazy loading. "Shoots random SQL at random times" is just another way of saying that you don't know how Hibernate works or how to use it correctly... – pap Jan 17 '13 at 14:40
@pap Actually I got into this style in Clojure, where it is next to impossible to use the mutable state model of Hibernate. I liked it so much that I now do Java projects in the same style. Never regretted it. The problem with state management is not that I **don't know** how it works; the problem is that **I know too well** how it works and all the pitfalls it brings in. As I said, I find that distasteful. Same story in Rails, BTW. – Marko Topolnik Jan 17 '13 at 14:43

score 1 · Answer 2 · edited Apr 05 '13 at 03:34

1

when using kodo set resultSetHoldability to 1 in Datasource custom property

edited Apr 05 '13 at 03:34

Jeff Wolski

6,332
6
37
69

answered Apr 05 '13 at 03:14

user2247479

11
1

score 1 · Answer 3 · answered Jul 23 '15 at 20:54

There are many ultimate causes for SQL Code -4470 in DB2. Here is a short list:

Program error (like in this case), closing the cursor before it has been fully read.
Bug in the installed JDBC driver: https://issues.jboss.org/browse/JBPAPP-2408
Improper use of the driver due to incorrect documentation: http://www-01.ibm.com/support/docview.wss?uid=swg1IV45140
Bug in DB2 server: https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.5.0/com.ibm.db2.luw.admin.trb.doc/doc/c0020806.html

score 0 · Answer 4 · edited May 23 '17 at 12:25

If lazy/eager fetch strategy is an issue, you may want to use @OneToMany(fetch=FetchType.EAGER) and perhaps read Hibernate One To Many Eager Not Pulling in all Data, How to Fetch @OneToMany and @ManyToMany Entities and A Short Primer On Fetching Strategies.

I'd recommend posting a few snippets of how you get at the database and the affected records. You may want to use https://gist.github.com/.

score 0 · Answer 5 · answered May 22 '14 at 18:54

0

The reason that I got the same error was because I closed the db2 connection and then tried to read the result set. Please emphasis that the db2 must remain connected while reading the result set.

answered May 22 '14 at 18:54

Joe Cotton

491
4
5

Yes i changed the resultSetHoldability property to 1 , so it was working fine. But may i know what is all about resultSetHoldability property?? – saagaravk May 25 '14 at 17:30

Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null

5 Answers5