0 Replies Latest reply on Aug 21, 2003 5:33 AM by rtr

    update on entity bean closes open cursor

    rtr Newbie

      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