Custom SQL function for NHibernate dialect

[Kristoffer Ahl]

I want to be able to call a custom function called "recent_date" as
part of my HQL. Like this: [Date] >= recent_date()

I created a new dialect, inheriting from MsSql2000Dialect and
specified the dialect for my configuration.

public class NordicMsSql2000Dialect : MsSql2000Dialect
{
public NordicMsSql2000Dialect()
{
RegisterFunction(
"recent_date",
new SQLFunctionTemplate(
NHibernateUtil.Date,
"dateadd(day, -15, getdate())"
)
);
}
}

var configuration = Fluently.Configure()
.Database(
MsSqlConfiguration.MsSql2000
.ConnectionString(c => .... )
.Cache(c => c.UseQueryCache().ProviderClass<HashtableCacheProvider>
())
.Dialect<NordicMsSql2000Dialect>()
)
.Mappings(m => ....)
.BuildConfiguration();

When calling recent_date() I get the following error:
System.Data.SqlClient.SqlException: 'recent_date' is not a recognized
function name

I'm using it in a where statement for a HasMany-mapping like below.

HasMany(x => x.RecentValues)
.Access.CamelCaseField(Prefix.Underscore)
.Cascade.SaveUpdate()
.Where("Date >= recent_date()");

What am I missing here?

PS. I've also posted this question on Stackoverflow if you prefer to
answer it there. Thanks!
http://stackoverflow.com/questions/1845884/custom-sql-function-for-nhibernate-dialect

[Hudson Akridge]

A dialect's purpose is to manage the nuances between different Sql languages. You're using it as a customization to an existing Sql language. Basically think of it like this, it's intent isn't for you to add functionality to that doesn't already exist, it's for you to register differences in functionality between different query languages.

What you're looking to do with registering your own function for use elsewhere in your mappings would probably lead you to use a Database object.

--

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

--
- Hudson
http://www.bestguesstheory.com
http://twitter.com/HudsonAkridge

[Kristoffer Ahl]

Hudson,
Actually the end goal for me is to create a second dialect for SQLite
which uses a different syntax. So I still think this might be the way
but I can't get it to register the functions it seems. Any ideas on
that?
Thanks for your reply!
/ Kristoffer

On 4 Dec, 15:16, Hudson Akridge <hudson.akri...@gmail.com> wrote:
> A dialect's purpose is to manage the nuances between different Sql
> languages. You're using it as a customization to an existing Sql language.
> Basically think of it like this, it's intent isn't for you to add
> functionality to that doesn't already exist, it's for you to register
> differences in functionality between different query languages.
>
> What you're looking to do with registering your own function for use
> elsewhere in your mappings would probably lead you to use a Database

> object<http://ayende.com/Blog/archive/2009/04/16/nhibernate-mapping-ltdataba...>
> .

> >http://stackoverflow.com/questions/1845884/custom-sql-function-for-nh...

>
> > --
>
> > You received this message because you are subscribed to the Google Groups
> > "Fluent NHibernate" group.
> > To post to this group, send email to fluent-n...@googlegroups.com.
> > To unsubscribe from this group, send email to

> > fluent-nhibern...@googlegroups.com<fluent-nhibernate%2Bunsu...@googlegroups.com>
> > .

[Hudson Akridge]

I still believe you're thinking about dialects the wrong way :) Consider dialects a projection, an API in a sense. They allow you to register SQL functions for use in HQL queries, or Sql column types to .NET system or user types. They do not create those functions or columns in the SQL schema just by the nature of being registered. You'll still need to use something like a database object for custom SQL function creation to happen automatically. That creates your function in SQL, and the dialect can then register that function for use in HQL.

However, specifically with your setup, you're using a where="xxxx" attribute in the collection. Where's use SQL syntax, not HQL. So I'm pretty sure that registering the function with the dialect still won't help you out there.

To unsubscribe from this group, send email to fluent-nhibern...@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/fluent-nhibernate?hl=en.

--

[Kristoffer Ahl]

Hudson,
Thanks alot!
In that case I think I need to find another solution as I need to use
a where clause in my mapping like above.
If anyone has any ideas on an alternative approach I could use it
would be much appreciated.
/ Kristoffer

> > <fluent-nhibernate%2Bunsu...@googlegroups.com<fluent-nhibernate%252Buns...@googlegroups.com>

[Hudson Akridge]

For an article that goes over what I was speaking about, here's one from Ayende that covers all the bases.

http://ayende.com/Blog/archive/2006/10/01/UsingSQLFunctionsInNHibernate.aspx

To unsubscribe from this group, send email to fluent-nhibern...@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/fluent-nhibernate?hl=en.

--

[Kristoffer Ahl]

Thanks, that was a good read! But as you wrote earlier, this will not
solve my problem. Back to the drawingboard.

On 4 Dec, 16:47, Hudson Akridge <hudson.akri...@gmail.com> wrote:
> For an article that goes over what I was speaking about, here's one from

> Ayende that covers all the bases.http://ayende.com/Blog/archive/2006/10/01/UsingSQLFunctionsInNHiberna...
> <http://ayende.com/Blog/archive/2006/10/01/UsingSQLFunctionsInNHiberna...>

> > > > <fluent-nhibernate%2Bunsu...@googlegroups.com<fluent-nhibernate%252Buns...@googlegroups.com>
> > <fluent-nhibernate%252Buns...@googlegroups.com<fluent-nhibernate%25252Bun...@googlegroups.com>