Hi anyz,

It seems to me that it is just luck that the mySQL dialect generated a useable query.  These are trivial tables (and I presume classes), but the mapping seems incorrect to me.  Without an explicit expresses relation between Project and Program, it seems unlikely that Hibernate can reliably process HQL (ie generate valid SQL).

I think programId in the Project mapping should be of type a.b.model.Program not java.lang.Integer, and the relationship expresses as a join in the mapping.

Anyway, I would suggest reading up on Hibernate mappings, and if you still have trouble, ask over on the Hibernate forums. I am 99% sure there is nothing wrong with the Derby dialect in this case.

Good luck.

Brett

Sent from my iPhone

On Jun 1, 2010, at 14:07, anyz <anyz111@gmail.com> wrote:

Thanks Brett for your time. Yes it is more of Hibernate issue i was just trying to find if some of derby user encounter with this.
Here are tables structure and mapping.
 

CREATE TABLE PROJECT (
 PROJECT_ID VARCHAR(50) NOT NULL,
 PROJECT_NAME VARCHAR(150) NOT NULL,
 PROJECT_MANAGER VARCHAR(150) NOT NULL,
 START_DATE TIMESTAMP NOT NULL,
 END_DATE TIMESTAMP NOT NULL,
 PROGRAM_ID INTEGER,
 PRIMARY KEY (PROJECT_ID)
);
ALTER TABLE PROJECT
 ADD FOREIGN KEY (PROGRAM_ID)
 REFERENCES PROGRAM (PROGRAM_ID);
 

CREATE TABLE PROGRAM (
 PROGRAM_ID INTEGER DEFAULT AUTOINCREMENT: start 1 increment 1 NOT NULL,
 PROGRAM_NAME VARCHAR(150) NOT NULL,
 PROGRAM_MANAGER VARCHAR(150) NOT NULL,
 PRIMARY KEY (PROGRAM_ID)
);
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
    <class name="a.b.model.Project" table="PROJECT">
        <id name="projectId" type="string">
            <column name="PROJECT_ID" length="50" />
            <generator class="assigned" />
        </id>
        <property name="projectName" type="string">
            <column name="PROJECT_NAME" length="150" not-null="true" />
        </property>
        <property name="projectManager" type="string">
            <column name="PROJECT_MANAGER" length="150" not-null="true" />
        </property>
        <property name="startDate" type="timestamp">
            <column name="START_DATE" length="26" not-null="true" />
        </property>
        <property name="endDate" type="timestamp">
            <column name="END_DATE" length="26" not-null="true" />
        </property>
        <property name="programId" type="java.lang.Integer">
            <column name="PROGRAM_ID" />
        </property>
    </class>
</hibernate-mapping>
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
    <class name="a.b.model.Program" table="PROGRAM">
        <id name="programId" type="int">
            <column name="PROGRAM_ID" />
            <generator class="identity" />
        </id>
        <property name="programName" type="string">
            <column name="PROGRAM_NAME" length="150" not-null="true" />
        </property>
        <property name="programManager" type="string">
            <column name="PROGRAM_MANAGER" length="150" not-null="true" />
        </property>
    </class>
</hibernate-mapping>
 
 
 
Thanks once again.

On Mon, May 31, 2010 at 7:55 PM, Brett Wooldridge <brett.wooldridge@gmail.com> wrote:
When you say "Hibernate 3", what specific version are you talking about?  While this is more of a Hibernate question than a Derby question, what is the structure of the two tables (for Project and Program) and what does the mapping look like?

Brett


On Mon, May 31, 2010 at 10:52 PM, anyz <anyz111@gmail.com> wrote:
Its seems to be hibernate issue. Dialect in hibernate 3 is not uptodate i think.
 
We tried it as below:

SELECT proj, prog, FROM Project proj, Program prog

but it didn't work.



On Mon, May 31, 2010 at 6:19 PM, Donald McLean <dmclean62@gmail.com> wrote:
Have you tried explicitly specifying the table? i.e. Project.proj

On Mon, May 31, 2010 at 7:00 AM, anyz <anyz111@gmail.com> wrote:
> i'm trying to use hibernate 3 with Derby 10.6. Normal queries work good but
> using left outer join creates problem. The query i'm trying loads project
> program if it exists and if programm not exists the project information is
> loaded:
>
> SELECT proj, prog FROM Project
> LEFT OUTER JOIN Program ON Program.programId = Project.programId
> WHERE Project.projectName = '"MyProject"
>
> The error on executing query is :
>
> unexpected token: ON near line 1, column xxx [SELECT proj, prog, FROM
> a.b.Project LEFT OUTER JOIN Program ON Program.programId = Project.programId
> WHERE Project.projectName = 'MyProject' ]
> at org.hibernate.hql.ast.QuerySyntax
>
> Could you please guid if Hibernate dialect support for Derbby is not that
> good of i'm missing something.
>
> thanks
>



--
Family photographs are a critical legacy for
ourselves and our descendants. Protect that
legacy with a digital backup and recovery plan.

Join the photo preservation advocacy Facebook group:
http://www.facebook.com/home.php?ref=logo#/group.php?gid=148274709288