Persistence - Switching DB to Postgres - Schema Export fails

[tkrah]

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

[gavin.king]

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

[tkrah]

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

[tkrah]

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

[tkrah]

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

[tkrah]

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

[tkrah]

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