5 Replies Latest reply on Oct 1, 2007 8:44 AM by bgmf

    Closing a result set you left open!, MySQL

    marcusdidiusfalco

      Hi, I am just beginning to experiment with JPA.
      I have jboss-4.2.1.GA and MySQL 5.0.45 community installed and I am using mysql-connector-java-5.0.6 as JDBC driver.

      With

      @Stateless
      public class TestBean implements TestRemote {
      
       @PersistenceContext(unitName="ejbbuch")
       EntityManager entityManager;
      
      
       @TransactionAttribute(TransactionAttributeType.REQUIRED)
       public Product addProduct(Product product) {
       entityManager.persist(product);
       return product;
       }
      
       .....
      ---------------------------------------------------------------------------------
      @Entity
      public class Product implements Serializable {
      
       @Id
       @GeneratedValue(strategy=GenerationType.IDENTITY)
       private long id;
       ........
      
      -------------------------------------------------------------------------------
      <?xml version="1.0" encoding="UTF-8"?>
      <persistence>
       <persistence-unit name="ejbbuch">
       <jta-data-source>java:/MySqlDS</jta-data-source>
       </persistence-unit>
      </persistence>
      
      ---------------------------------------------------------------------------------
      <datasources>
       <local-tx-datasource>
       <jndi-name>MySqlDS</jndi-name>
       <connection-url>jdbc:mysql://localhost:3306/jbossdb</connection-url>
       <driver-class>com.mysql.jdbc.Driver</driver-class>
       <user-name>root</user-name>
       <password>ritak1</password>
       <min-pool-size>6</min-pool-size>
       <max-pool-size>20</max-pool-size>
       <blocking-timeout-millis>5000</blocking-timeout-millis>
       <idle-timeout-minutes>0</idle-timeout-minutes>
       <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>
       <!-- should only be used on drivers after 3.22.1 with "ping" support
       -->
       <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLValidConnectionChecker</valid-connection-checker-class-name>
       <new-connection-sql>SELECT count(*) FROM product</new-connection-sql>
       <check-valid-connection-sql>SELECT count(*) FROM product</check-valid-connection-sql>
       <share-prepared-statements>true</share-prepared-statements>
       <prepared-statement-cache-size>10</prepared-statement-cache-size>
       <track-statements>true</track-statements>
       <metadata>
       <type-mapping>mySQL</type-mapping>
       </metadata>
       </local-tx-datasource>
      </datasources>
      
      
      


      I get the following exceptions in the log file:

      2007-09-28 13:41:04,265 WARN [org.jboss.resource.adapter.jdbc.WrappedConnection] Closing a result set you left open! Please close it yourself.
      java.lang.Throwable: STACKTRACE
       at org.jboss.resource.adapter.jdbc.WrappedStatement.registerResultSet(WrappedStatement.java:617)
       at org.jboss.resource.adapter.jdbc.WrappedStatement.getGeneratedKeys(WrappedStatement.java:533)
       at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
       at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)


      When I switch back to the DefaultDS (HSQL) this disappears.

      Otherwise the code works as expected with the MySQL database. But of course in a production environment you would not want to force JBoss to handle unclosed statements or resultsets.

      Is this some problem with the JDBC driver or do I have some bug in my code?

      Thanks,

      Hans

        • 1. Re: Closing a result set you left open!, MySQL
          bgmf

          Hello Marcus,

          unfortunately I cannot help you with your Problem, but maybe you can give me an Advise how to bound MySQL.

          I tried to do a working mysqlds, but it doesn't work...

          What have you done? I would be glad to have your problems :-/

          Yours,
          BGMF

          • 2. Re: Closing a result set you left open!, MySQL
            marcusdidiusfalco

            Hi BGMF,

            1. Download a JDBC driver from the MySQL site.
            http://dev.mysql.com/downloads/connector/j/5.0.html
            and put it into the lib or deploy directory of your server configuration.
            2. Copy <JBoss_Home>\docs\examples\jca\mysql-ds.xml to your deploy directory and adjust it. Basically you simply have to adjust the connection url, driver-class, user-name and password.
            If user-name or password are wrong you might get a funny exception about the wrong Hibernate dialect which can be confusing.
            3. The jndi-name of your datasource must be referenced in your persistence.xml file like java:/
            Basically all the code to make this work (except for the problem I have) is listed in my example.

            Hope that helps, otherwise what exactly is your problem?

            Greetings,

            Hans

            • 3. Re: Closing a result set you left open!, MySQL
              bgmf

              Hi Marcus!

              The funny thing is: I've done excactly all these things.
              Maybe the reason is, because I've done the jdbc-lib also in my eclipse project... Maybe jboss is confused because of duplicate drivers...

              But thank you anyway!!!

              I hope you will find the reason for your problem - I guess it's only a ridiculus little config entry missing or so...

              Good Luck!

              Daniel

              • 4. Re: Closing a result set you left open!, MySQL
                bgmf

                Thanks for your help - it was a little entry missing causing this problem:

                had forgotten to write java:/MySqlDS in persistence.xml
                instead i've written only MySqlDS

                and there was no DS with such a name...

                Thank you very much!

                Have you allready solved your problem?

                BGMF

                • 5. Re: Closing a result set you left open!, MySQL
                  bgmf

                  Hm...

                  I have no idea, but maybe you should change your persistence xml a little bit
                  I got a error because I had not entered the Hibernate Dialect

                  <properties>
                   <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect" />
                  </properties>
                  


                  how do you access those TestBean?

                  I was told to write an extra class with source code like this

                  try
                   {
                   Properties properties = new Properties();
                   properties.put("java.naming.factory.initial","org.jnp.interfaces.NamingContextFactory");
                   properties.put("java.naming.factory.url.pkgs","=org.jboss.naming:org.jnp.interfaces");
                   properties.put("java.naming.provider.url","jnp://localhost:1099");
                  
                   InitialContext iContext = new InitialContext(properties);
                  
                   TologDatabaseQueryResponderRemote remote =
                   (TologDatabaseQueryResponderRemote) iContext.lookup("TologResponderEAR/TologDatabaseQueryResponder/remote");
                   }
                   catch ( NamingException e )
                   {
                   throw new DatabaseQueryResponderException("NamingException while trying to connect to a remote interface:\n"+e.getLocalizedMessage());
                   }
                  


                  But I have to admit, that I also have the next problems - but ok - none of your business - you have your own problems...

                  Greetings
                  BGMF