4 Replies Latest reply on May 19, 2006 4:00 PM by Mike Duffy

    MySQL Connector/J 3.1

    Mike Duffy Novice

      Has anyone else had problems using MySQL Connector/J 3.1 with EJB 3.0?

      Even if I don not have any '0000-00-00 ...' dates in my table I still get an exception when I try to use this driver with the latestest and greatest from JBoss.

      From the MySQL docs:

      Datetimes with all-zero components ('0000-00-00 ...') ? These values can not be represented reliably in Java. Connector/J 3.0.x always converted them to NULL when being read from a ResultSet.

      Connector/J 3.1 throws an exception by default when these values are encountered as this is the most correct behavior according to the JDBC and SQL standards. This behavior can be modified using the ' zeroDateTimeBehavior ' configuration property. The allowable values are: 'exception' (the default), which throws an SQLException with an SQLState of 'S1009', 'convertToNull', which returns NULL instead of the date, and 'round', which rounds the date to the nearest closest value which is '0001-01-01'.

      http://dev.mysql.com/doc/refman/5.0/en/cj-upgrading.html

      Any suggestions?

      Mike

        • 1. Re: MySQL Connector/J 3.1
          Jens Elkner Apprentice

          You already tried:

          <connection-property name="zeroDateTimeBehavior"
           >convertToNull</connection-property>
          


          or something like that ?

          • 2. Re: MySQL Connector/J 3.1
            Mike Duffy Novice

            I saw a reference to this late last night. I did not see the right syntax.

            This goes in the [data_source].xml file for JBoss, is that correct?

            • 3. Re: MySQL Connector/J 3.1
              Jens Elkner Apprentice

              Here an example, which I usually place directly into the $jboss/server/$instance/deploy/ directory:

              <datasources>
               <local-tx-datasource>
               <jndi-name>TestDS</jndi-name>
               <connection-url>jdbc:mysql://localhost:3306/test</connection-url>
               <driver-class>com.mysql.jdbc.Driver</driver-class>
               <user-name>test.dba</user-name>
               <password>test</password>
               <connection-property name="useCompression">false</connection-property>
               <connection-property name="logSlowQueries">true</connection-property>
               <connection-property name="zeroDateTimeBehavior"
               >convertToNull</connection-property>
              <!--
               <connection-property name="characterEncoding"
               >utf8</connection-property>
               <connection-property name="useUnicode">true</connection-property>
               <connection-property name="connectionCollation">utf8_general_ci</connection-property>
              -->
               <exception-sorter-class-name
               >com.mysql.jdbc.integration.jboss.ExtendedMysqlExceptionSorter</exception-sorter-class-name>
               <valid-connection-checker-class-name
               >com.mysql.jdbc.integration.jboss.MysqlValidConnectionChecker</valid-connection-checker-class-name>
               <metadata>
               <type-mapping>mySQL</type-mapping>
               </metadata>
               </local-tx-datasource>
              </datasources>
              


              • 4. Re: MySQL Connector/J 3.1
                Mike Duffy Novice

                Worked perfectly with the new MySQL driver.

                Thx!