6 Replies Latest reply on Jul 18, 2006 1:09 PM by Torsten Krah

    Persistence - Switching DB to Postgres - Schema Export fails

    Torsten Krah Newbie

      Trying to switch the DB to PostgreSQL fails, little help would be nice.
      Using the Seam DVD Example, i configured persistence.xml:

      <persistence-unit name="dvdDatabase">
       <provider>org.hibernate.ejb.HibernatePersistence</provider>
       <jta-data-source>java:/dvdDatasource</jta-data-source>
       <properties>
       <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>
       <property name="hibernate.connection.driver_class" value="org.postgresql.Driver"/>
       <property name="hibernate.connection.url" value="jdbc:postgresql://localhost:5432/mydb"/>
       <property name="hibernate.connection.username" value="testuser"/>
       <property name="hibernate.connection.password" value="test"/>
       <property name="hibernate.default_schema" value="postgres"/>
      
       <property name="hibernate.hbm2ddl.auto" value="create-drop"/>
       <property name="hibernate.cache.use_query_cache" value="true"/>
       <property name="hibernate.show_sql" value="true"/>
       <property name="hibernate.cache.provider_class"
       value="org.hibernate.cache.HashtableCacheProvider"/>
       </properties>
      </persistence-unit -->
      
      


      The error is:

      15:08:21,754 INFO [SchemaExport] Running hbm2ddl schema export
      15:08:21,766 INFO [SchemaExport] exporting generated schema to database
      15:08:21,858 INFO [SchemaExport] Executing import script: /import.sql
      15:08:21,860 ERROR [SchemaExport] schema export unsuccessful
      


      Some hints would be nice, why the export fails.

      I guess my persistence.xml might be wrong - but looked at the documentation, should work, should it?

      kind regards

      Torsten

        • 1. Re: Persistence - Switching DB to Postgres - Schema Export f
          Gavin King Master

          The failure occurs while executing import.sql. Turn up the Hibernate log level to see what the problem is.

          • 2. Re: Persistence - Switching DB to Postgres - Schema Export f
            Torsten Krah Newbie

            Hm yes theres a sql exception:

            Caused by: java.sql.SQLException: Table not found in statement [delete from ORDERLINES]
            


            But thats from import.xml - i thought the application should work without the data provided in the import.sql script, its only pre defined data, it should work without, am i right?
            But the db schema did not get exported, there are no tables created at the database, have i have to create them manually? Didn't get that schema stuff completly in my head yet.

            kind regards

            • 3. Re: Persistence - Switching DB to Postgres - Schema Export f
              Torsten Krah Newbie

              Edit:

              Ok - forget to sync the databases in dvd-ds.xml and persistence.xml.

              Now i changed following files:

              dvd-ds.xml, persistence.xml and jboss-beans.xml to use my postgres DB.

              Now i get another exception - which confuses me, because the url is correct, and the driver class too - using this jdbc url and driver in another project, which works fine there.

              Caused by: org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (org.jboss.resource.JBossResourceException: Apparently wrong driver class specified for URL: class: org.postgresql.Driver, url: jdbc:postgresql://localhost:5432/mydb)
              


              and before that:

              Caused by: org.jboss.resource.JBossResourceException: Failed to register driver for: org.postgresql.Driver; - nested throwable: (java.lang.ClassNotFoundException: No ClassLoaders found for: org.postgresql.Driver)
              


              Included jdbc3-postgresql.jar in WEB-INF/lib directory of dvdstore example - anything else i have to do?

              kind regards


              • 4. Re: Persistence - Switching DB to Postgres - Schema Export f
                Torsten Krah Newbie

                Ok resolved the classloader issue, removed the jar from WEB-INF/lib and copyed it do server/default/lib from jboss install.

                Works now except:

                Caused by: org.postgresql.util.PSQLException: FEHLER: Wert zu lang fuer Typ character varying(1024)
                at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1512)
                 at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1297)
                 at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
                 at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:430)
                 at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:332)
                 at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:324)
                 at org.jboss.resource.adapter.jdbc.WrappedStatement.execute(WrappedStatement.java:82)
                 at org.hibernate.tool.hbm2ddl.SchemaExport.importScript(SchemaExport.java:252)
                 ... 165 more
                
                


                How can i find out which table, column and which dataset goind to insert causes this?
                And how to fix this? The schema mapping might be little bit wrong, if it maps a column to varchar(1024) but examples being inserted are bigger, so how to find out whre exactly this exception raises and where can i fix the mapping to allow verchar(4096) or anything else?

                kind regards


                • 5. Re: Persistence - Switching DB to Postgres - Schema Export f
                  Torsten Krah Newbie

                  Ok solved all issues - only one question is left.

                  Where does the length of varchar is mapped for the products table?

                  1024 for contents and 50 for title is too small for import.sql ( which misses many semicolons ).
                  Changed DB manually after jboss start and imported sql script a second time, works fine.

                  So last question is:

                  How to make those change persistent to the DB schema?

                  kind regards

                  • 6. Re: Persistence - Switching DB to Postgres - Schema Export f
                    Torsten Krah Newbie

                    Closed - all solved.

                    Overwrite the column annotation length does it - i wonder why this works with integrated hypersonic DB, if the title is longer than 50 like for prod_id=67, or the description is longer than 1024 for some examples, it should raise an exception, like postgres does - the integrated db works, dont know why, but postgresql problem is solved.

                    kind regards