2 Replies Latest reply on Aug 6, 2007 6:20 AM by Pete Muir

    using import.sql with multiple datasources

    Eric Ellis Newbie

      I am developing a Seam application in conjunction with my client who is writing a proprietary CMS system, in which the data access entities will be embedded in the principal app. My requirement is to have two datasources: mysql and oracle. One being for user registration and the other to provide site content.

      I realize that there are many outstanding CMS solutions that provide data access as a service; but... since this approach was not my decision, let's avoid recommending a different architecture :)

      My problem is that their CMS database schema is generated on the fly from the EJB's so it is necessary to import data after deployment. My problem is that there doesn't seem to be a way to specify to Hibernate which datasource should attempt to read and import the contents of import.sql. My error output would imply that both datasources attempt to load import.sql.

      If specificity is not an option I may have to manually update to datasource element to not "drop-create" and import the test data manually. I would rather avoid this if possible though.

      Here are my configuration files. Again, I am using Seam.

      --------------------------------------------------------------------------------
      persistence.xml
      --------------------------------------------------------------------------------

      <?xml version="1.0" encoding="UTF-8"?>
      <!-- Persistence deployment descriptor for dev profile -->
      <persistence xmlns="http://java.sun.com/xml/ns/persistence"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd"
      version="1.0">

      <persistence-unit name="entityManager" transaction-type="JTA">
      org.hibernate.ejb.HibernatePersistence
      <jta-data-source>java:/OracleLocalDS</jta-data-source>





      </persistence-unit>

      <persistence-unit name="someclient">
      org.hibernate.ejb.HibernatePersistence
      <jta-data-source>java:/someclientDatasource</jta-data-source>






      </persistence-unit>



      --------------------------------------------------------------------------------
      jboss-ds.xml
      --------------------------------------------------------------------------------

      <?xml version="1.0" encoding="UTF-8"?>


      <local-tx-datasource>
      <jndi-name>someclientDatasource</jndi-name>
      <connection-url>jdbc:mysql://localhost/someclient</connection-url>
      <driver-class>com.mysql.jdbc.Driver</driver-class>
      <user-name>user</user-name>
      pwd
      </local-tx-datasource>

      <local-tx-datasource>
      <jndi-name>OracleLocalDS</jndi-name>
      <connection-url>jdbc:oracle:thin:@localhost:1521</connection-url>
      <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
      <user-name>user</user-name>
      pwd
      </local-tx-datasource>



        • 1. Re: using import.sql with multiple datasources
          Pascal Mengelt Newbie

          Hi

          when I set the property hibernate.hbm2ddl.auto to create in one datasource and not in the other - it works. My problem is that I have tables that should bei created each time and some are not! here it seems I need one datasource special for the tables that I want to create each time - or is there another possilbility?

          regards Pascal

          <persistence-unit name="pms-msgbrowser">
           <provider>org.hibernate.ejb.HibernatePersistence</provider>
           <jta-data-source>java:/pms-msgbrowserDatasource</jta-data-source>
           <properties>
           <property name="hibernate.hbm2ddl.auto" value="create"/>
           <property name="hibernate.cache.use_query_cache" value="true"/>
           <property name="hibernate.show_sql" value="true"/>
           <property name="jboss.entity.manager.factory.jndi.name" value="java:/pms-msgbrowserEntityManagerFactory"/>
           </properties>
           </persistence-unit>
          
           <persistence-unit name="pms-msgbrowser-LUZ">
           <provider>org.hibernate.ejb.HibernatePersistence</provider>
           <jta-data-source>java:/pms-msgbrowser-LUZDatasource</jta-data-source>
           <properties>
           <property name="hibernate.hbm2ddl.auto" value="validate"/>
           <property name="hibernate.cache.use_query_cache" value="true"/>
           <property name="hibernate.show_sql" value="true"/>
           <property name="jboss.entity.manager.factory.jndi.name" value="java:/pms-msgbrowser-LUZEntityManagerFactory"/>
          
           </properties>
           </persistence-unit>


          • 2. Re: using import.sql with multiple datasources
            Pete Muir Master

            You'll need to ask this in a hibernate forum, but, IIRC, import.sql is always run on any PC in create mode.