NHibernate is writing wrong SQL statement..!!!!!!

luiz · **Posted:** Wed Jun 11, 2008 7:05 am

Hi all,

I don't know if I'm doing something wrong.
I'm using NHibernate 2.0 and Access database.

Here is my configuration file.

Code:

<property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
      <property name="dialect">NHibernate.Dialect.GenericDialect</property>
      <property name="connection.driver_class">NHibernate.Driver.OleDbDriver</property>
      <property name="connection.connection_string">Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\teste_hibernate.mdb</property>
      <property name="show_sql">true</property>

As you can see, I'm using GenericDialect (I don't know if there is the problem, maybe?)

I have 3 tables and wanted to join all of them. I used outer-join=true clause, in the mapping file (all 3).

But NHibernate give me wrong SQL. How come???
It doesn't put ( ), separating one outter join table from another.

Any ideias???

Tks

philxan · **Joined:** Wed May 28, 2008 3:17 am **Posts:** 10

Hi Luiz,

maybe you can post the mappping files for your objects, the SQL generated and what you expect. It will help us evaluate where the problem might lie!

Phil

luiz · **Posted:** Thu Jun 12, 2008 7:02 am

Hi Phil

This is ths SQL

Code:

SELECT this_.OBJECTID as OBJECTID3_3_, this_.UsiLogin as UsiLogin3_3_, this_.UsiSe as UsiSe3_3_, this_.UsiNm as UsiNm3_3_, this_.UsiGusCd as UsiGusCd3_3_, grupousuar2_.OBJECTID as OBJECTID2_0_, grupousuar2_.GusDs as GusDs2_0_, gruposinte3_.GifGusCd as GifGusCd5_, gruposinte3_.OBJECTID as OBJECTID5_, gruposinte3_.OBJECTID as OBJECTID1_1_, gruposinte3_.GifGusCd as GifGusCd1_1_, gruposinte3_.GifFsiCd as GifFsiCd1_1_, funcaosist4_.OBJECTID as OBJECTID0_2_, funcaosist4_.FsiDs as FsiDs0_2_ FROM GEOTB_UsuarioSistema this_ left outer join GEOTB_GrupoUsuario grupousuar2_ on this_.UsiGusCd=grupousuar2_.OBJECTID left outer join GEOTB_GrupoInterFuncao gruposinte3_ on grupousuar2_.OBJECTID=gruposinte3_.GifGusCd left outer join GEOTB_FuncaoSistema funcaosist4_ on gruposinte3_.GifFsiCd=funcaosist4_.OBJECTID WHERE this_.UsiLogin = 'teste' and this_.UsiSe = 'teste';

And these are mapping files

Code:

<class name="FuncaoSistema" 
         table="GEOTB_FuncaoSistema">

    <id name="id" column="OBJECTID" type="Int32">
      <generator class="increment"/>
    </id>

    <property name="descricao" 
              type="String" 
              column="FsiDs" 
              length="100" />

    <bag name="gruposInterFuncoes" 
         table="GEOTB_GrupoInterFuncao" 
         cascade="all" 
         inverse="true" 
         lazy="true">
      
      <key column="GifFsiCd" />
      
      <one-to-many class="GrupoInterFuncao" />
      
    </bag>
    
  </class>

Code:

<class name="GrupoInterFuncao" 
         table="GEOTB_GrupoInterFuncao">

    <id name="id" 
        column="OBJECTID" 
        type="Int32">
      
      <generator class="increment"/>
      
    </id>

    <many-to-one name="grupoUsuario" 
                 class="GrupoUsuario" 
                 cascade="save-update" 
                 column="GifGusCd" />

    <many-to-one name="funcaoSistema" 
                 class="FuncaoSistema" 
                 cascade="save-update" 
                 column="GifFsiCd" 
                 fetch="join" />

Code:

<class name="GrupoUsuario" 
         table="GEOTB_GrupoUsuario">

    <id name="id" 
        column="OBJECTID" 
        type="Int32">
      
      <generator class="increment"/>
      
    </id>

    <property name="descricao" 
              type="String" 
              column="GusDs" 
              length="50" />

    <bag name="gruposInterFuncoes" 
         table="GEOTB_GrupoInterFuncao" 
         inverse="true" 
         cascade="all" 
         fetch="join">
      
      <key column="GifGusCd" />
      
      <one-to-many class="GrupoInterFuncao" />
      
    </bag>

    <bag name="usuariosSistema" 
         table="GEOTB_UsuarioSistema" 
         inverse="true" 
         lazy="true" 
         cascade="all">
      
      <key column="UsiGusCd" />
      
      <one-to-many class="UsuarioSistema" />
      
    </bag>

  </class>

Code:

 <class name="UsuarioSistema" 
         table="GEOTB_UsuarioSistema">

    <id name="id" 
        column="OBJECTID" 
        type="Int32">
      
      <generator class="increment"/>
      
    </id>

    <property name="login" 
              type="String" 
              column="UsiLogin" 
              length="50" />
    
    <property name="senha" 
              type="String" 
              column="UsiSe" 
              length="50" />
    
    <property name="nome" 
              type="String" 
              column="UsiNm" 
              length="100" />

    <many-to-one name="grupoUsuario" 
                 class="GrupoUsuario" 
                 cascade="save-update" 
                 column="UsiGusCd" 
                 fetch="join" />
    
  </class>

Tks

wolli · **Posted:** Thu Jun 12, 2008 7:18 am

Have a look here:

http://www.hibernate.org/361.html?cmd=comphist&histnode=2713#A3

[url]http://forum.hibernate.org/viewtopic.php?t=983198&highlight=jetdriver+join
[/url]
http://forum.hibernate.org/viewtopic.php?t=976366&highlight=jetdriver+join

http://jira.nhibernate.org/browse/NH-1006

Seems to be a bug in the jet driver.

luiz · **Posted:** Thu Jun 12, 2008 7:25 am

Tks a lot

Dexter · **Joined:** Thu Jun 12, 2008 10:51 am **Posts:** 2

May be it helps you:

SQL query is built without key word 'from' in the case, when the request from 2 and more tables is done.
File: nhcontrib\trunk\src\NHibernate.JetDriver\src\NHibernate.JetDriver\JetDriver.cs
Method: private string TransformFromClause(string fromClause)
Line: 155
Old line: transformed = string.Join(",", blocks);
New line: transformed = "from " + string.Join(",", blocks);