-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 17 posts ]  Go to page 1, 2  Next
Author Message
 Post subject: Decent database and drivers
PostPosted: Wed Mar 08, 2006 8:02 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 6:10 am
Posts: 8615
Location: Neuchatel, Switzerland (Danish)
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!

_________________
Max
Don't forget to rate


Top
 Profile  
 
 Post subject:
PostPosted: Wed Mar 08, 2006 7:50 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 7:19 pm
Posts: 2364
Location: Brisbane, Australia
You could use/reference the information that has been maintained as a part of Middlegen project as the issues/requirements are the same.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Mar 09, 2006 3:27 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 6:10 am
Posts: 8615
Location: Neuchatel, Switzerland (Danish)
link? (yes i know - i'm lazy :)

_________________
Max
Don't forget to rate


Top
 Profile  
 
 Post subject:
PostPosted: Fri Mar 10, 2006 7:02 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 7:19 pm
Posts: 2364
Location: Brisbane, Australia
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


Top
 Profile  
 
 Post subject: older versions of PostgreSQL
PostPosted: Sat Mar 11, 2006 3:38 pm 
Newbie

Joined: Thu Mar 09, 2006 8:16 am
Posts: 13
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

_________________
nikola_croatia


Top
 Profile  
 
 Post subject:
PostPosted: Sat Mar 11, 2006 3:42 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 6:10 am
Posts: 8615
Location: Neuchatel, Switzerland (Danish)
Please put it on the *wiki* http://www.hibernate.org/383.html in a possible shorter form - thanks ;)

_________________
Max
Don't forget to rate


Top
 Profile  
 
 Post subject: PostgreSQL
PostPosted: Sun Mar 12, 2006 3:11 pm 
Newbie

Joined: Thu Mar 09, 2006 8:16 am
Posts: 13
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]

_________________
nikola_croatia


Top
 Profile  
 
 Post subject:
PostPosted: Sun Mar 12, 2006 9:03 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 7:19 pm
Posts: 2364
Location: Brisbane, Australia
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.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Apr 11, 2006 8:17 am 
Regular
Regular

Joined: Wed Sep 28, 2005 6:45 pm
Posts: 56
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:

Code:
CREATE OR REPLACE FUNCTION information_schema._pg_keypositions()
  RETURNS SETOF int4 AS
$BODY$select g.s
        from generate_series(1,current_setting('max_index_keys')::int,1)
        as g(s)$BODY$
  LANGUAGE 'sql' IMMUTABLE;
ALTER FUNCTION information_schema._pg_keypositions() OWNER TO postgres;


This is not necessary for postgres 8.0.


Top
 Profile  
 
 Post subject: Re: older versions of PostgreSQL
PostPosted: Mon May 07, 2007 9:36 am 
Newbie

Joined: Mon May 07, 2007 7:11 am
Posts: 1
nikola_croatia wrote:
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 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.

Code:
#!/bin/bash
db=<your database name>
for table in `psql -tc '\d' $db | grep table | cut -d ' ' -f 4`
do
        psql -c "\d $table" $db | grep '\$' | while read line
        do
                number=`echo $line | cut -d ' ' -f 1`
                fk=`echo $line | cut -d ' ' -f 2-`
                echo $table "   " $line
                drop="alter table $table drop constraint $number;"
                add="alter table $table add $fk"

                psql -c "$drop" $db
                psql -c "$add" $db
        done
done


Top
 Profile  
 
 Post subject:
PostPosted: Mon May 07, 2007 1:02 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 6:10 am
Posts: 8615
Location: Neuchatel, Switzerland (Danish)
you guys should put it in the wiki so its available on the relevant pages.

_________________
Max
Don't forget to rate


Top
 Profile  
 
 Post subject:
PostPosted: Sun Nov 02, 2008 10:27 am 
Newbie

Joined: Sat Nov 01, 2008 1:19 am
Posts: 5
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


Top
 Profile  
 
 Post subject:
PostPosted: Mon Nov 03, 2008 7:26 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 6:10 am
Posts: 8615
Location: Neuchatel, Switzerland (Danish)
jeeads wrote:
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.


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)

Quote:
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:


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

Quote:
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.


Let us know!

_________________
Max
Don't forget to rate


Top
 Profile  
 
 Post subject:
PostPosted: Mon Nov 03, 2008 8:59 am 
Newbie

Joined: Sat Nov 01, 2008 1:19 am
Posts: 5
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


Top
 Profile  
 
 Post subject:
PostPosted: Mon Nov 03, 2008 10:38 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 6:10 am
Posts: 8615
Location: Neuchatel, Switzerland (Danish)
jeeads wrote:
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.


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.

Quote:
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?


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!

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


looking forward to hear if they make a difference ;)

_________________
Max
Don't forget to rate


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 17 posts ]  Go to page 1, 2  Next

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.