Guys, I've started a page which describes known combinations of database/drivers that works at http://www.hibernate.org/383.html

Until I know just jotted the details I knew from memory
and I could use your help to fill in any missing details and additional driver/db combinations that you have successfully (or non-successfully) used the reverse engineering with.

Thanks!

You could use/reference the information that has been maintained as a part of Middlegen project as the issues/requirements are the same.

link? (yes i know - i'm lazy :)

Having has a look at it maybe its not so useful as I was first thinking.\
Anyway for the record http://boss.bekk.no/boss/middlegen/platforms/database.html

If you do reverse engineering and you are at the phase of code generation, you will be noticed by the error org.hibernate.cfg.JDBCBinderException: Foreign key name ($1) mapped to different tables!

The problem isn't in the JDBC as I tought.The problem is in the older version of
PostgreSQL.Higher versions 8.x.x numerate your foreign keys just like the primary keys in the older
versions.Thereby, if you use one kind of 7.x.x postgres and you have got foreign keys in the different
tables, the system name of your foreign keys will be automatically named with $1. Hence even if you use the postgresql-8.x.jdbc3.jar
driver that won't solve the problem if it is in the combination with an older version of Postgress.
Let we take an
further example in consideration:

create table car_owner(id serial primary key,name varchar(20),surname varchar(20));
create table address( id serial primary key, street varchar(20), city varchar(20),
id_car_owner integer references car_owner);
create table reg_plate(id serial primary key,signification char(2), reg_num integer,
id_car_owner integer references car_owner);

then if you type in your command line
\d address

your foreign key in the table address will look
"$1" FOREIGN KEY (id_car_owner) REFERENCES car_owner(id)

and

\d reg_plate

"$1" FOREIGN KEY (id_car_owner) REFERENCES car_owner(id)

so both foreign keys in different tables have got a same name "$1"

THE SOLUTION is to name uniquely your foreign keys.Further few lines of code do exactly that.

create table car_owner(id serial primary key,name varchar(20),surname varchar(20));
create table address( id serial primary key, street varchar(20), city varchar(20),
id_car_owner integer);
create table reg_plate(id serial primary key,signification char(2), reg_num integer,
id_car_owner integer);

ALTER TABLE address ADD CONSTRAINT first_foreign_key FOREIGN KEY(id_car_owner)
REFERENCES car_owner

ALTER TABLE reg_plate ADD CONSTRAINT second_foreign_key FOREIGN KEY(id_car_owner)
REFERENCES car_owner

Please put it on the *wiki* http://www.hibernate.org/383.html in a possible shorter form - thanks ;)

max Unfortunatelly, I don't know how to put solved problem on the wiki,
so do you think it could be better that You do it?

here is a shorter version that have to be put at your wiki page.

be careful of using foreign keys in the older versions of PostgreSQL.
Foreign keys have to be uniquely named,for further details visit
http://forum.hibernate.org/viewtopic.php?t=956490[/u]

The wiki is free for eveyone to edit. There is an edit button to press to change the page contents. Thank you for your information.

If you are using Postgres 8.1 for windows the jdbc driver might not manage to find the foreign keys. In that case you will get Integer fields instead of Set fields etc...

The solution is to try the newest jdbc driver, if that doesn't help this sql will let the current jdbc driver read the foreign keys:



This is not necessary for postgres 8.0.

the script below will drop foreign keys with the '$' sign in their names and recreate them. if you run the script in postgres 8. the names of newly created keys will be acceptable for hibernate. if you have postgres 7. you have to modify the script slightly so that it supplies names instead of relying on postgres.

you guys should put it in the wiki so its available on the relevant pages.

I have tried the latest ojdbc14.jar from Oracle 10g and it does not handle the TIMESTAMP(9) type properly. So that should be removed from the list on http://www.hibernate.org/383.html. When reengineering a 10g database that has TIMESTAMP(9) types those types are marshalled to java.io.Serializable even if you set the specific type mapping:

<hibernate-reverse-engineering>
<!-- Added by JEEads to take care of TIMESTAMP mapping issue 10/31/08 -->
<type-mapping>
<sql-type jdbc-type="TIMESTAMP" hibernate-type="java.sql.Timestamp" />
<sql-type jdbc-type="NUMERIC" hibernate-type="java.lang.Long" />
<sql-type jdbc-type="DECIMAL" hibernate-type="java.lang.Long" />
<sql-type jdbc-type="VARCHAR" length="1" not-null="true" hibernate-type="java.lang.Character" />
<sql-type jdbc-type="VARCHAR" length="1" hibernate-type="char" />
<sql-type jdbc-type="VARCHAR" hibernate-type="string" />
</type-mapping>


<!-- This file is intentionally generated empty by seam-gen -->
<!-- You can add any filtering/setup you want for your app -->

</hibernate-reverse-engineering>

I have posted a question onto this blog as to how to setup hibernate for the type but have yet to get a respone? I have to believe that someone has reengineered an Oracle 10g database with TIMESTAMP(9) types before. I will try the DataDirect drivers today and report back my findings.

Jerry

It is a wiki - you can edit it ;) (and no, I don't think it should be removed - it should just be documented for that one specific type it is not giving good information)



That is because oracle's TIMESTAMP is not mapped by oracle jdbc to JDBC's TIMESTAMP.



Let us know!

Max,
I was waiting on a response to my question regarding this type prior to changing the WIKI in case there was something I was missing or to see if this had been previously documented.

If oracle's TIMESTAMP is not mapped to JDBC's TIMESTAMP how does this work:

<mapping>
<java-type>java.sql.Timestamp</java-type>
<jdbc-type>TIMESTAMP</jdbc-type>
<sql-type>TIMESTAMP(9)</sql-type>
</mapping>

from ..JBOSS_HOME/server/default/conf/standardjbosscmp-jdbc.xml.

When I first started working with SEAM I used DALI to generate the EJB3 entities and using an ORACLE database the TIMESTAMP fields were mapped correctly?

I am still trying to get the DataDirect dirvers loaded for use.

Jerry

I don't know the specifics of jbosscmp-jdbc.xml but I'll assume sql-type is here the native database type and thus it will map correctly.

Hibernate Tools currently uses the jdbc-type, not the native database type to do the mapping.



Because Dalis uses Eclipse DTP which again uses the native types to map or they have their oracle specific mappings.

I would like to add native type support, thus code patches are very welcome!



looking forward to hear if they make a difference ;)