I was able to fix with a named HQL query.
<query name="GetCountOfCategoriesWithOrnaments">
select count(c.Id)
from Category as c
where (select count(o.Id) from c.Ornaments o) > 0
</query>
This generates:
select count(category0_.CategoryId) as col_0_0_
from Categories category0_
where (select count(ornament2_.OrnamentId)
from CategoryOrnaments ornaments1_,
Ornaments ornament2_
where category0_.CategoryId = ornaments1_.CategoryId
and ornaments1_.OrnamentId = ornament2_.OrnamentId) > 0
but as that bug report points out, the other statement should be a
valid cast. Any chance that patch will be applied to 3.0 soon?
On Aug 3, 11:58 am, "Frans Bouma" <[email protected]> wrote:
> Bug in NH:http://216.121.112.228/browse/NH-2149
>
> Likely not patched in 3.0?
>
> ANyway, it's of course the fault of MySQL to require 'UNSIGNED INTEGER'
> instead of 'INTEGER', but alas, MySql has many lame things so this isn't a
> surprise.
>
> FB
>
>
>
>
>
> > Exception Details: MySql.Data.MySqlClient.MySqlException: You have an
> error
> > in your SQL syntax; check the manual that corresponds to your MySQL server
> > version for the right syntax to use near 'INTEGER) as col_0_0_ from
> > Categories category0_ where (select cast(count(*) as I'
> > at line 1
>
> > [GenericADOException: could not execute query [ select cast(count(*) as
> > INTEGER) as col_0_0_ from Categories category0_ where (select
> cast(count(*)
> > as INTEGER) from CategoryOrnaments ornaments1_, Ornaments ornament2_ where
> > category0_.CategoryId=ornaments1_.CategoryId and
> > ornaments1_.OrnamentId=ornament2_.OrnamentId)>?p0 ]
> > Name:p1 - Value:0
> > [SQL: select cast(count(*) as INTEGER) as col_0_0_ from Categories
> > category0_ where (select cast(count(*) as INTEGER) from CategoryOrnaments
> > ornaments1_, Ornaments ornament2_ where
> > category0_.CategoryId=ornaments1_.CategoryId and
> > ornaments1_.OrnamentId=ornament2_.OrnamentId)>?p0]]
> > NHibernate.Loader.Loader.DoList(ISessionImplementor session,
> > QueryParameters queryParameters) +410
>
> > Server Info:
> > Server version: 5.1.40-community MySQL Community Server (GPL)
>
> > mysql> status
> > --------------
> > mysql Ver 14.14 Distrib 5.1.40, for Win64 (unknown)
>
> > Connection id: 16
> > SSL: Not in use
> > Using delimiter: ;
> > Server version: 5.1.40-community MySQL Community Server (GPL)
> > Protocol version: 10
> > Connection: localhost via TCP/IP
> > Client characterset: latin1
> > Server characterset: latin1
> > TCP port: 3306
> > Uptime: 2 hours 47 min 20 sec
>
> > Threads: 6 Questions: 379 Slow queries: 0 Opens: 121 Flush tables:
> > 1 Open t
> > ables: 4 Queries per second avg: 0.37
> > --------------
>
> > On Aug 3, 11:16 am, "Frans Bouma" <[email protected]> wrote:
> > > MySQL's cast works that way, so it looks OK, what's the error?
>
> > > FB
>
> > > > Using NHibernate 3, I have a Linq query
>
> > > > Line 12: public long CountActive() Line 13: { Line
> > > > 14: return EntityCollection // Entity Collection is a
> > > > wrapper for ISession.Query<Category>() Line 15:
> > > > .Count(c => c.Ornaments.Count > 0); Line 16: }
>
> > > > It is generating the following MySQL query:
>
> > > > select cast(count(* ) as INTEGER) as col_0_0_ from Categories
> > > > category0_ where (select cast(count(* ) as INTEGER)
> > > > from CategoryOrnaments ornaments1_,
> > > > Ornaments ornament2_
> > > > where category0_.CategoryId = ornaments1_.CategoryId
> > > > and ornaments1_.OrnamentId = ornament2_.OrnamentId)
> > > > >0
>
> > > > If I remove the cast statements, this query will run fine in mysql.
> > > > I
> > > have
> > > > tried the same functionality on SqlServer and it works fine.
>
> > > > My NH config is as follows...
>
> > > > <hibernate-configuration
> > > > xmlns="urn:nhibernate-configuration-2.2">
> > > > <session-factory>
> > > > <property
>
> > > name='proxyfactory.factory_class'>NHibernate.ByteCode.Castle.ProxyFact
> > > oryFa c> tory,
> > > > NHibernate.ByteCode.Castle</property>
> > > > <property
>
> > > name="connection.provider">NHibernate.Connection.DriverConnectionProvi
> > > der</
>
> > > > property>
>
> > > > <!--<property
> > > > name="dialect">NHibernate.Dialect.MsSql2005Dialect</property>
> > > > <property
> > > > name="connection.driver_class">NHibernate.Driver.SqlClientDriver</
> > > > property>
> > > > <property name="connection.connection_string">..Removed
> > > > for user group post..</property>-->
>
> > > > <property
> > > > name="dialect">NHibernate.Dialect.MySQLDialect</
> > > > property>
> > > > <property
> > > > name="connection.driver_class">NHibernate.Driver.MySqlDataDriver</
> > > > property>
> > > > <property name="connection.connection_string">...Removed
> > > > for user group post...</property>
>
> > > > <property name="current_session_context_class">web</
> > > > property>
> > > > <property name="show_sql">false</property>
> > > > </session-factory>
> > > > </hibernate-configuration>
>
> > > > Am I configuring the Mysql Dialect wrong? Or is this a known issue?
>
> > > > --
> > > > You received this message because you are subscribed to the Google
> > > > Groups "nhusers" group.
> > > > To post to this group, send email to [email protected].
> > > > To unsubscribe from this group, send email to
> > > > [email protected].
> > > > For more options, visit this group at
> > > >http://groups.google.com/group/nhusers?hl=en.
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "nhusers" group.
> > To post to this group, send email to [email protected].
> > To unsubscribe from this group, send email to
> > [email protected].
> > For more options, visit this group at
> >http://groups.google.com/group/nhusers?hl=en.
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
