update on entity bean closes open cursor
hello
summary:
we have a table with thousands of records. to query the table we
created what we call "paged queries" with plain jdbc using a datasource
to get the connection to the database; the first call gets the resources
and executes the statement, the consecutive calls read n rows, the last
call or the "abort" call closes the resources.
to modify (read/write) an entry of the table we use entity beans.
there is one situation where this causes problems. when executing a paged query
and at the same time updating an entry using an entity bean, the cursor of the
paged query is closed when the update on the entity bean commits. it doesn't matter
whether the query and the entity bean use the same or different datasources.
detail:
we use ingres as database and jboss3.2.1 and jboss3.2.2.rc2.
we have a standalone java client using the command pattern to send request to the
server over http. every request is executed in the servlet-engine, where calls
to stateless session beans are initiated. those session beans either use jdbc or
entity beans, depending on the purpose of the call.
for the paged queries we have three methods with transaction attribute "supports"
(start, next and abort). for saving an entry we have one method with transaction
attribute "required". we can execute multiple paged queries in parallel, we can
open (read) entries while executing paged queries, but we cannot update entries
while executing queries. if we do this, the next call on the paged query fails with
an ingres jdbc exception stating: "No open cursor exists matching the requested ID".
pure jdbc tests have shown, that this only happens, when you create a connection,
execute a query receiving a resultset, commit the connection and then call next()
on the resultset.
the question is, why does the update of the entity bean commit the connection of the
paged query (it shouldn't be the same connection)?
the interesting part is, that this does not happen, if we use the drivermanager and
not the datasource to get the connection for the paged query. does this allow the
conclusion, that jboss handles connections of datasources in a "strange" manner?
any help is more than welcome.
thanx, robert