Is SetCacheable supported for SQL queries?

370 views
Skip to first unread message

Kent Boogaart

unread,
Apr 9, 2010, 12:26:45 PM4/9/10
to nhusers
Hi,

I have a SQL query:

var filterValues = session
.CreateSQLQuery(sql)
////.SetCacheable(true)
.SetParameter("parent", qualifiedName)
.SetResultTransformer(Transformers.AliasToBean(typeof(FilterValue)))
.List<FilterValue>();

The actual SQL does not appear to matter. If I uncomment the
SetCacheable call I get an exception:

System.IndexOutOfRangeException: Index was outside the bounds of the
array.
at NHibernate.Type.TypeFactory.Disassemble(Object[] row,
ICacheAssembler[] types, Boolean[] nonCacheable, ISessionImplementor
session, Object owner)
at NHibernate.Cache.StandardQueryCache.Put(QueryKey key,
ICacheAssembler[] returnTypes, IList result, Boolean
isNaturalKeyLookup, ISessionImplementor session)
at
NHibernate.Loader.Loader.PutResultInQueryCache(ISessionImplementor
session, QueryParameters queryParameters, IType[] resultTypes,
IQueryCache queryCache, QueryKey key, IList result)
at NHibernate.Loader.Loader.ListUsingQueryCache(ISessionImplementor
session, QueryParameters queryParameters, ISet`1 querySpaces, IType[]
resultTypes)
at NHibernate.Loader.Loader.List(ISessionImplementor session,
QueryParameters queryParameters, ISet`1 querySpaces, IType[]
resultTypes)
at NHibernate.Loader.Custom.CustomLoader.List(ISessionImplementor
session, QueryParameters queryParameters)
at NHibernate.Impl.SessionImpl.ListCustomQuery(ICustomQuery
customQuery, QueryParameters queryParameters, IList results)
at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification
spec, QueryParameters queryParameters, IList results)
at NHibernate.Impl.SessionImpl.List[T](NativeSQLQuerySpecification
spec, QueryParameters queryParameters)
at NHibernate.Impl.SqlQueryImpl.List[T]()

I've seen this already (http://www.mailinglistarchive.com/html/
nhu...@googlegroups.com/2010-01/msg00404.html) but I'm not using
named queries.

Is what I'm doing supported? Is there some way I can specify whatever
extra info is required to support caching?

Thanks,
Kent

John Davidson

unread,
Apr 9, 2010, 2:17:43 PM4/9/10
to nhu...@googlegroups.com
The links below give hints as to the secret sauce for configuring cache

http://ayende.com/Blog/archive/2006/08/21/TheSecretLifeOfNHibernatesCaches.aspx

John Davidson


--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.


Kent Boogaart

unread,
Apr 12, 2010, 4:34:04 AM4/12/10
to nhusers
Thanks for the links, but neither appears to address the problem. The
second link didn't work but I found what I think is the same article
at http://davybrion.com/blog/2009/02/quickly-setting-up-and-using-nhibernates-second-level-cache/.

My problem is that I'm using SQL queries without a named query. Doing
so throws an exception as per my original post. Is this scenario not
supported?

Thanks,
Kent

On Apr 9, 7:17 pm, John Davidson <jwdavid...@gmail.com> wrote:
> The links below give hints as to the secret sauce for configuring cache
>

> http://ayende.com/Blog/archive/2006/08/21/TheSecretLifeOfNHibernatesC...http://nhforge.org/blogs/nhibernate/archive/2009/02/09/quickly-settin...

> > nhusers+u...@googlegroups.com<nhusers%2Bunsu...@googlegroups.com>

John Davidson

unread,
Apr 12, 2010, 6:47:47 AM4/12/10
to nhu...@googlegroups.com
SQL queries work without required them to be named queries. You were asking about caching, so I thought you had the query working before you tried putting it into a cache. I suggest that you should try to approach it by making the sql query you want work first, then try adding it to your cache setup.


John Davidson

To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.

Kent Boogaart

unread,
Apr 12, 2010, 8:26:31 AM4/12/10
to nhusers
John,

We appear to have our wires crossed.

My SQL query DOES work. My second level cache DOES work. The
combination of SQL query + second level cache DOES NOT work.

The link in my first post suggests that it CAN work for named SQL
queries, but my SQL query is not named. All I'm wondering is whether
it can be made to work for a SQL query that is not named.

Best,
Kent


On Apr 12, 11:47 am, John Davidson <jwdavid...@gmail.com> wrote:
> SQL queries work without required them to be named queries. You were asking
> about caching, so I thought you had the query working before you tried
> putting it into a cache. I suggest that you should try to approach it by
> making the sql query you want work first, then try adding it to your cache
> setup.
>

> Try:http://knol.google.com/k/fabio-maulo/nhibernate-chapter-14/1nr4enxv3d...


>
> John Davidson
>
> On Mon, Apr 12, 2010 at 4:34 AM, Kent Boogaart <ken...@internode.on.net>wrote:
>
> > Thanks for the links, but neither appears to address the problem. The
> > second link didn't work but I found what I think is the same article
> > at

> >http://davybrion.com/blog/2009/02/quickly-setting-up-and-using-nhiber...


> > .
>
> > My problem is that I'm using SQL queries without a named query. Doing
> > so throws an exception as per my original post. Is this scenario not
> > supported?
>
> > Thanks,
> > Kent
>
> > On Apr 9, 7:17 pm, John Davidson <jwdavid...@gmail.com> wrote:
> > > The links below give hints as to the secret sauce for configuring cache
>

> >http://ayende.com/Blog/archive/2006/08/21/TheSecretLifeOfNHibernatesC....

> > <nhusers%2Bunsu...@googlegroups.com<nhusers%252Buns...@googlegroups.com>

John Davidson

unread,
Apr 12, 2010, 9:03:28 AM4/12/10
to nhu...@googlegroups.com
No problem. I assume theny you have in your config

<property name="hibernate.cache.use_query_cache">true</property>

What is your mapping for FilterValues? Does it contain a collection association?

John Davidson 

To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.

Kent Boogaart

unread,
Apr 12, 2010, 9:22:04 AM4/12/10
to nhusers
Yes, that configuration is present. In fact, it was working perfectly
fine until I had to switch from using the criteria API to straight SQL
for this one particular function. That's what sparked this whole
exercise.

My FilterValue class isn't an entity, so I just have this in my hbm:

<import class="FilterValue"/>

Kent

> > > > <nhusers%2Bunsu...@googlegroups.com<nhusers%252Buns...@googlegroups.com>
> > <nhusers%252Buns...@googlegroups.com<nhusers%25252Bun...@googlegroups.com>

John Davidson

unread,
Apr 12, 2010, 9:29:48 AM4/12/10
to nhu...@googlegroups.com
I suspect that is the source of your problem. The query cache is only able to deal with value items, not object items. That is probably why you see the error.

John Davidson

To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.

Kent Boogaart

unread,
Apr 12, 2010, 9:36:39 AM4/12/10
to nhusers
Bummer. Ah well, at least that explains it.

Thanks,
Kent

> > > > > > <nhusers%2Bunsu...@googlegroups.com<nhusers%252Buns...@googlegroups.com>
> > <nhusers%252Buns...@googlegroups.com<nhusers%25252Bun...@googlegroups.com>
>
> > > > <nhusers%252Buns...@googlegroups.com<nhusers%25252Bun...@googlegroups.com>
> > <nhusers%25252Bun...@googlegroups.com<nhusers%2525252Bu...@googlegroups.com>

John Davidson

unread,
Apr 12, 2010, 11:22:53 AM4/12/10
to nhu...@googlegroups.com
This reference from the Knol of Fabio suggests that Component Mapping may be a solution as they are managed as values rather than entities

http://knol.google.com/k/fabio-maulo/nhibernate-chapter-7/1nr4enxv3dpeq/10#

John Davidson

To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.

Kent Boogaart

unread,
Apr 13, 2010, 8:25:11 AM4/13/10
to nhusers
Thanks John, but unless I'm missing something, a component has to
exist inside an entity anyway.

In a way, it makes sense that NH doesn't support what I was trying to
do because it doesn't have any concept of identity for the imported
type. It would be nice if I could give it that information.

Failing that, I think the only way for me to make this work is to
supplement my entities with extraneous information which doesn't
strictly belong in them, thus sacrificing my object model design for
cacheability.

Best,
Kent

On Apr 12, 4:22 pm, John Davidson <jwdavid...@gmail.com> wrote:
> This reference from the Knol of Fabio suggests that Component Mapping may be
> a solution as they are managed as values rather than entities
>

> http://knol.google.com/k/fabio-maulo/nhibernate-chapter-7/1nr4enxv3dp...

> > > > > > <nhusers%252Buns...@googlegroups.com<nhusers%25252Bun...@googlegroups.com>
> > <nhusers%25252Bun...@googlegroups.com<nhusers%2525252Bu...@googlegroups.com>
>
> > > > <nhusers%25252Bun...@googlegroups.com<nhusers%2525252Bu...@googlegroups.com>
> > <nhusers%2525252Bu...@googlegroups.com<nhusers%252525252B...@googlegroups.com>

> ...
>
> read more »

Gary Brunton

unread,
Feb 22, 2013, 6:52:42 PM2/22/13
to nhu...@googlegroups.com
This is way late but I came across this same issue. I was able to get caching to work using CreateSQLQuery with the following code:

session
.CreateSQLQuery(
@"
Select Count(*) As CountOfUnreadDocuments
From SomeTableThatDoesntMatter
")
.AddScalar("CountOfUnreadDocuments", NHibernateUtil.Int32)
.SetCacheable(true)
.UniqueResult<int>();

The trick for this was the AddScalar method call.

Gary Brunton

> > <nhusers%2B...@googlegroups.com<nhusers%252Bunsubscribe@googlegroups.com>
>
> > > > <nhusers%2B...@googlegroups.com<nhusers%252Bunsubscribe@googlegroups.com>
> > <nhusers%252...@googlegroups.com<nhusers%25252Bunsubscribe@googlegroups.com>
>
> > > > > > <nhusers%2B...@googlegroups.com<nhusers%252Bunsubscribe@googlegroups.com>
> > <nhusers%252...@googlegroups.com<nhusers%25252Bunsubscribe@googlegroups.com>
>
> > > > <nhusers%252...@googlegroups.com<nhusers%25252Bunsubscribe@googlegroups.com>
> > <nhusers%2525...@googlegroups.com<nhusers%2525252Bunsubscribe@googlegroups.com>
>
> > > > > > > > <nhusers%2B...@googlegroups.com<nhusers%252Bunsubscribe@googlegroups.com>
> > <nhusers%252...@googlegroups.com<nhusers%25252Bunsubscribe@googlegroups.com>
>
> > > > <nhusers%252...@googlegroups.com<nhusers%25252Bunsubscribe@googlegroups.com>
> > <nhusers%2525...@googlegroups.com<nhusers%2525252Bunsubscribe@googlegroups.com>
>
> > > > > > <nhusers%252...@googlegroups.com<nhusers%25252Bunsubscribe@googlegroups.com>
> > <nhusers%2525...@googlegroups.com<nhusers%2525252Bunsubscribe@googlegroups.com>
>
> > > > <nhusers%2525...@googlegroups.com<nhusers%2525252Bunsubscribe@googlegroups.com>
> > <nhusers%25252...@googlegroups.com<nhusers%252525252Bunsubscribe@googlegroups.com>


>
> > > > > > > > > > .
> > > > > > > > > > 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 nhu...@googlegroups.com.
> > > > > > > > To unsubscribe from this group, send email to
> > > > > > > > nhusers+u...@googlegroups.com<nhusers%2Bunsu...@googlegroups.com>

Reply all
Reply to author
Forward
0 new messages