Problem with reverse engineering from PostgreeSQL

BoBaH32 · **Joined:** Mon Sep 04, 2006 9:57 am **Posts:** 10

When I do reverse engineering from PostgreeSQL (v 7.4) to hibernate, I recieve normaly pojo objects but mistaken mapping files: there are no many-to-one, many-to-many dependencies etc.

How Can I solve this problem?

Here is a fragment of eclipse .log file:

Code:

.......
org.hibernate.exception.GenericJDBCException: Error while reading exported keys meta data for public.customer
   at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
   at org.hibernate.cfg.reveng.dialect.JDBCMetaDataDialect$5.handleSQLException(JDBCMetaDataDialect.java:284)
........

I've tested another example whith HSQLDB - it worked normaly (all dependencies generated successfully)

max · **Posted:** Mon Sep 04, 2006 4:25 pm

i'm guessing the log says something about duplicate or nonunique foreign keys found, right ?

this is because older versions of postgresql would not generate unique names for its unnamed foreignkeys.

to solve it:
name your foreign keys (which makes much sense when using reveng)
or
use a more recent version of postgresql

BoBaH32 · **Joined:** Mon Sep 04, 2006 9:57 am **Posts:** 10

Great thanks. By the way, I have the same problem with the MaxDB database.

max · **Posted:** Wed Sep 06, 2006 2:48 pm

don't know maxdb, do they also not generate unique foreignkeys ?

BoBaH32 · **Joined:** Mon Sep 04, 2006 9:57 am **Posts:** 10

max wrote:

don't know maxdb, do they also not generate unique foreignkeys ?

With MaxDB situation is even worse, it generates POJO and mappings without any warnings and exeptions.
By the way, in MaxDB schema all forein keys are named.

Here is ant task:

Code:

   <target name="reverseToJavaHbmXml">
      <hibernatetool>
         <jdbcconfiguration packagename="test" configurationfile="hibernate_4SapDB.cfg.xml" revengfile="hibernate.reveng.xml"/>   
         <hbm2hbmxml destdir="src/"/>
         <hbm2java destdir="src/"/>   
         <hbm2cfgxml destdir="" ejb3="false"/>
      </hibernatetool>
   </target>

A part of hibernate log:

Code:

[hibernatetool] Executing Hibernate Tool with a JDBC Configuration (for reverse engineering)
[hibernatetool] 1. task: hbm2hbmxml (Generates a set of hbm.xml files)
[hibernatetool] 10:27:59,326  INFO Environment:500 - Hibernate 3.2 cr3
[hibernatetool] 10:27:59,341  INFO Environment:533 - hibernate.properties not found
[hibernatetool] 10:27:59,341  INFO Environment:667 - Bytecode provider name : cglib
[hibernatetool] 10:27:59,357  INFO Environment:584 - using JDK 1.4 java.sql.Timestamp handling
[hibernatetool] 10:27:59,482  INFO Configuration:1384 - configuring from file: hibernate_4SapDB.cfg.xml
[hibernatetool] 10:27:59,576  INFO Configuration:1465 - Configured SessionFactory: GenericSessionFc
[hibernatetool] 10:27:59,685  INFO OverrideRepository:100 - Override file: C:\usr\projects\TestDiploma\hibernate.reveng.xml
[hibernatetool] 10:27:59,763  INFO DriverManagerConnectionProvider:41 - Using Hibernate built-in connection pool (not for production use!)
[hibernatetool] 10:27:59,763  INFO DriverManagerConnectionProvider:42 - Hibernate connection pool size: 20
[hibernatetool] 10:27:59,763  INFO DriverManagerConnectionProvider:45 - autocommit mode: false
[hibernatetool] 10:27:59,794  INFO DriverManagerConnectionProvider:80 - using driver: com.sap.dbtech.jdbc.DriverSapDB at URL: jdbc:sapdb://localhost/MAXDB1
[hibernatetool] 10:27:59,794  INFO DriverManagerConnectionProvider:86 - connection properties: {user=mona, password=****}
[hibernatetool] 10:28:00,123  INFO SettingsFactory:81 - RDBMS: SAP DB, version: KERNEL    7.6.00   BUILD 032-123-130-190
[hibernatetool] 10:28:00,123  INFO SettingsFactory:82 - JDBC driver: SAP DB, version: package com.sap.dbtech.jdbc, MaxDB JDBC Driver, MySQL MaxDB, 7.6.0    Build 030-000-005-567
[hibernatetool] 10:28:00,154  INFO Dialect:141 - Using dialect: org.hibernate.dialect.SAPDBDialect
[hibernatetool] 10:28:00,232  INFO TransactionFactoryFactory:31 - Using default transaction strategy (direct JDBC transactions)
[hibernatetool] 10:28:00,279  INFO TransactionManagerLookupFactory:33 - No TransactionManagerLookup configured (in JTA environment, use of read-write or transactional second-level cache is not recommended)
[hibernatetool] 10:28:00,279  INFO SettingsFactory:134 - Automatic flush during beforeCompletion(): disabled
[hibernatetool] 10:28:00,279  INFO SettingsFactory:138 - Automatic session close at end of transaction: disabled
[hibernatetool] 10:28:00,279  INFO SettingsFactory:145 - JDBC batch size: 15
[hibernatetool] 10:28:00,279  INFO SettingsFactory:148 - JDBC batch updates for versioned data: disabled
[hibernatetool] 10:28:00,279  INFO SettingsFactory:153 - Scrollable result sets: enabled
[hibernatetool] 10:28:00,279  INFO SettingsFactory:161 - JDBC3 getGeneratedKeys(): disabled
[hibernatetool] 10:28:00,279  INFO SettingsFactory:169 - Connection release mode: auto
[hibernatetool] 10:28:00,279  INFO SettingsFactory:196 - Default batch fetch size: 1
[hibernatetool] 10:28:00,294  INFO SettingsFactory:200 - Generate SQL with comments: disabled
[hibernatetool] 10:28:00,294  INFO SettingsFactory:204 - Order SQL updates by primary key: disabled
[hibernatetool] 10:28:00,294  INFO SettingsFactory:369 - Query translator: org.hibernate.hql.ast.ASTQueryTranslatorFactory
[hibernatetool] 10:28:00,294  INFO ASTQueryTranslatorFactory:24 - Using ASTQueryTranslatorFactory
[hibernatetool] 10:28:00,294  INFO SettingsFactory:212 - Query language substitutions: {}
[hibernatetool] 10:28:00,294  INFO SettingsFactory:217 - JPA-QL strict compliance: disabled
[hibernatetool] 10:28:00,294  INFO SettingsFactory:222 - Second-level cache: enabled
[hibernatetool] 10:28:00,310  INFO SettingsFactory:226 - Query cache: disabled
[hibernatetool] 10:28:00,310  INFO SettingsFactory:356 - Cache provider: org.hibernate.cache.NoCacheProvider
[hibernatetool] 10:28:00,310  INFO SettingsFactory:241 - Optimize cache for minimal puts: disabled
[hibernatetool] 10:28:00,310  INFO SettingsFactory:250 - Structured second-level cache entries: disabled
[hibernatetool] 10:28:00,326  INFO SettingsFactory:277 - Statistics: disabled
[hibernatetool] 10:28:00,326  INFO SettingsFactory:281 - Deleted entity synthetic identifier rollback: disabled
[hibernatetool] 10:28:00,326  INFO SettingsFactory:296 - Default entity-mode: pojo
[hibernatetool] 10:28:01,060  INFO DriverManagerConnectionProvider:147 - cleaning up connection pool: jdbc:sapdb://localhost/MAXDB1
[hibernatetool] 10:28:01,201  INFO Version:15 - Hibernate Tools 3.2.0.beta7
10:28:01,810  INFO DriverManagerConnectionProvider:147 - cleaning up connection pool: jdbc:sapdb://localhost/MAXDB1
            ...............................................
[hibernatetool] 3. task: cfg2cfgxml (Generates hibernate.cfg.xml)
BUILD SUCCESSFUL

BoBaH32 · **Joined:** Mon Sep 04, 2006 9:57 am **Posts:** 10

max wrote:

i'm guessing the log says something about duplicate or nonunique foreign keys found, right ?

this is because older versions of postgresql would not generate unique names for its unnamed foreignkeys.

to solve it:
name your foreign keys (which makes much sense when using reveng)
or
use a more recent version of postgresql

I've examined the schema of databases in PostrgreSQL: all foreing keys are named unique, the problem is other.
In the stack of exeptions I've mentioned next:

Code:

Caused by: org.postgresql.util.PSQLException: The column name PKTABLE_SCHEM not found

Is it a problem with driver?

Here is full stack trace:

Code:

[hibernatetool] 12:07:23,420  WARN JDBCReader:232 - Exception while reading foreign keys for org.hibernate.mapping.Table(public.deliverreport) [org.hibernate.exception.GenericJDBCException: Error while reading exported keys meta data for public.deliverreport]
[hibernatetool] org.hibernate.exception.GenericJDBCException: Error while reading exported keys meta data for public.deliverreport
[hibernatetool] at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
[hibernatetool] at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
[hibernatetool] at org.hibernate.cfg.reveng.dialect.JDBCMetaDataDialect$5.handleSQLException(JDBCMetaDataDialect.java:284)
[hibernatetool] at org.hibernate.cfg.reveng.dialect.ResultSetIterator.next(ResultSetIterator.java:50)
[hibernatetool] at org.hibernate.cfg.reveng.JDBCReader.processForeignKeys(JDBCReader.java:169)
[hibernatetool] at org.hibernate.cfg.reveng.JDBCReader.readDatabaseSchema(JDBCReader.java:93)
[hibernatetool] at org.hibernate.cfg.reveng.JDBCReader.readDatabaseSchema(JDBCReader.java:807)
[hibernatetool] at org.hibernate.cfg.JDBCBinder.readDatabaseSchema(JDBCBinder.java:116)
[hibernatetool] at org.hibernate.cfg.JDBCBinder.readFromDatabase(JDBCBinder.java:88)
[hibernatetool] at org.hibernate.cfg.JDBCMetaDataConfiguration.readFromJDBC(JDBCMetaDataConfiguration.java:40)
[hibernatetool] at org.hibernate.tool.ant.JDBCConfigurationTask.doConfiguration(JDBCConfigurationTask.java:83)
[hibernatetool] at org.hibernate.tool.ant.ConfigurationTask.getConfiguration(ConfigurationTask.java:55)
[hibernatetool] at org.hibernate.tool.ant.HibernateToolTask.getConfiguration(HibernateToolTask.java:278)
[hibernatetool] at org.hibernate.tool.ant.HibernateToolTask.getProperties(HibernateToolTask.java:294)
[hibernatetool] at org.hibernate.tool.ant.ExporterTask.configureExporter(ExporterTask.java:94)
[hibernatetool] at org.hibernate.tool.ant.ExporterTask.execute(ExporterTask.java:39)
[hibernatetool] at org.hibernate.tool.ant.HibernateToolTask.execute(HibernateToolTask.java:162)
[hibernatetool] at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:275)
[hibernatetool] at org.apache.tools.ant.Task.perform(Task.java:364)
[hibernatetool] at org.apache.tools.ant.Target.execute(Target.java:341)
[hibernatetool] at org.apache.tools.ant.Target.performTasks(Target.java:369)
[hibernatetool] at org.apache.tools.ant.Project.executeSortedTargets(Project.java:1216)
[hibernatetool] at org.apache.tools.ant.Project.executeTarget(Project.java:1185)
[hibernatetool] at org.apache.tools.ant.helper.DefaultExecutor.executeTargets(DefaultExecutor.java:40)
[hibernatetool] at org.eclipse.ant.internal.ui.antsupport.EclipseDefaultExecutor.executeTargets(EclipseDefaultExecutor.java:32)
[hibernatetool] at org.apache.tools.ant.Project.executeTargets(Project.java:1068)
[hibernatetool] at org.eclipse.ant.internal.ui.antsupport.InternalAntRunner.run(InternalAntRunner.java:423)
[hibernatetool] at org.eclipse.ant.internal.ui.antsupport.InternalAntRunner.main(InternalAntRunner.java:137)
[hibernatetool] Caused by: org.postgresql.util.PSQLException: The column name PKTABLE_SCHEM not found.
[hibernatetool] at org.postgresql.jdbc1.AbstractJdbc1ResultSet.findColumn(AbstractJdbc1ResultSet.java:673)
[hibernatetool] at org.postgresql.jdbc1.AbstractJdbc1ResultSet.getString(AbstractJdbc1ResultSet.java:475)
[hibernatetool] at org.hibernate.cfg.reveng.dialect.JDBCMetaDataDialect$5.convertRow(JDBCMetaDataDialect.java:272)
[hibernatetool] at org.hibernate.cfg.reveng.dialect.ResultSetIterator.next(ResultSetIterator.java:47)
[hibernatetool] ... 24 more

max · **Posted:** Thu Sep 07, 2006 4:24 pm

postgress: think it is driver error since they have issues with wantin PKTABLE_SCHEMA instead of the stadnard PKTABLE_SCHEM name.

with respect to maxdb, what is the problem ? you don't show the problem, just a log saying things runs fine.

BoBaH32 · **Joined:** Mon Sep 04, 2006 9:57 am **Posts:** 10

max wrote:

with respect to maxdb, what is the problem ? you don't show the problem, just a log saying things runs fine.

The problem is next: reverse engineering from MaxDB goes ok, but foregn keys don't transform into relationships <many-to-one>, <one-to-many>, and so on...

max · **Posted:** Fri Sep 08, 2006 5:33 am

enable full debug logging and i can see why it doesn't resolve relationships

dacafaca · **Joined:** Wed Oct 25, 2006 6:29 am **Posts:** 5

max....
about maxdb...
i got this warning during loading tables from database.
this is warning for my table BISIS4.ARCHIVE.

Massage:
WARN Worker-6 org.hibernate.cfg.reveng.JDBCReader - Exception while reading foreign keys for org.hibernate.mapping.Table(BISIS4.ARCHIVE) [org.hibernate.exception.SQLGrammarException: Error while reading exported keys meta data for BISIS4.ARCHIVE]

Stack Trace:
org.hibernate.exception.SQLGrammarException: Error while reading exported keys meta data for BISIS4.ARCHIVE
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.cfg.reveng.dialect.JDBCMetaDataDialect.getExportedKeys(JDBCMetaDataDialect.java:189)
at org.hibernate.cfg.reveng.JDBCReader.processForeignKeys(JDBCReader.java:166)
at org.hibernate.cfg.reveng.JDBCReader.readDatabaseSchema(JDBCReader.java:93)
at org.hibernate.eclipse.console.workbench.LazyDatabaseSchemaWorkbenchAdapter$1.execute(LazyDatabaseSchemaWorkbenchAdapter.java:97)
at org.hibernate.console.execution.DefaultExecutionContext.execute(DefaultExecutionContext.java:56)
at org.hibernate.eclipse.console.workbench.LazyDatabaseSchemaWorkbenchAdapter.readDatabaseSchema(LazyDatabaseSchemaWorkbenchAdapter.java:88)
at org.hibernate.eclipse.console.workbench.LazyDatabaseSchemaWorkbenchAdapter.getChildren(LazyDatabaseSchemaWorkbenchAdapter.java:57)
at org.hibernate.eclipse.console.workbench.BasicWorkbenchAdapter.fetchDeferredChildren(BasicWorkbenchAdapter.java:94)
at org.eclipse.ui.progress.DeferredTreeContentManager$1.run(DeferredTreeContentManager.java:207)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:58)
Caused by: com.sap.dbtech.jdbc.exceptions.InternalJDBCError: [getExportedKeys][-4004] (at 231): Unknown table name:SYSODBCFOREIGNKEYS
at com.sap.dbtech.jdbc.DatabaseMetaDataSapDB.internalQuery(DatabaseMetaDataSapDB.java:1028)
at com.sap.dbtech.jdbc.DatabaseMetaDataSapDB.getExportedKeys(DatabaseMetaDataSapDB.java:430)
at org.hibernate.cfg.reveng.dialect.JDBCMetaDataDialect.getExportedKeys(JDBCMetaDataDialect.java:165)
... 9 more

this could be reason why reverse engineer doesn't generate one-to-many and many-to-one mappings with maxdb.

max · **Posted:** Wed Oct 25, 2006 7:13 am

yes - sounds like the driver is asking for a table that is not in your maxdb.

Are you sure you have the right versions of the driver and/or have the permissions to read from this table ?

dacafaca · **Joined:** Wed Oct 25, 2006 6:29 am **Posts:** 5

yes i'm sure.
i have all permissions and i have suitable driver.
that table does not exist in database.
i have maxdb 7.6.00.27.

max · **Posted:** Wed Oct 25, 2006 8:15 am

well it is not hibernate tools that is asking for that table, we just ask the driver to give us the exported keys and the driver apparently thinks it shuold query this table.

Sounds to like the driver has a bug.

dacafaca · **Joined:** Wed Oct 25, 2006 6:29 am **Posts:** 5

i noticed something in stack trace...

when you ask for exported keys from jdbc you use:
com.sap.dbtech.jdbc.DatabaseMetaDataSapDB.getExportedKeys()
and that method looks in table that don't exist in maxdb.

but, there is class
com.sap.dbtech.jdbc.DatabaseMetaDataMaxDB,
and method
com.sap.dbtech.jdbc.DatabaseMetaDataMaxDB.getExportedKeys()
looks in table that exist in maxdb and contains keys.

how i can make hibernate to use
com.sap.dbtech.jdbc.DatabaseMetaDataMaxDB.getExportedKeys()
instead
com.sap.dbtech.jdbc.DatabaseMetaDataSapDB.getExportedKeys()?

ps.- i use tool for browsing database that gets exported and imported keys and everything is fine. so, it seems to me there is no bug i driver.

max · **Posted:** Thu Oct 26, 2006 1:57 am

it is the driver that decides which DatabaseMetaData instance is returned from connection.getDatabaseMetaData().

My guess is you should be using com.sap.dbtech.jdbc.MaxDBDriver or whatever the name is instead of the apparent com.sap.dbtech.jdbc.SapDBDriver