> I'm trying to understand the template definition described here.
> Would it be the case that the template defines the format string and a
list
> of "extra objects" that are utilized by the code processing the template?
>
> For example, a template for IsTrue(x) might be:
> {0} = :trueValue
> Then, the list of objects would have just a named parameter:
> NamedParam("trueValue", true)
>
> Is this what was meant?
No, you specify the SQL fragment and everywhere where you need to
specify something that's produced by the query producer, e.g. parameter, or
another function call (so you can nest function calls) you specify a {x}
placeholder, where x is the index in the list of objects contained by the
function definition.
> Regarding the comments on Linq... would the idea be to use this same
> definition style to convert from a particular LINQ signature into a
> particular HQL signature? It basically allows you to define the mapping
in
> a similar way to the conversion from HQL to SQL?
It's a way, previously discussed here, where you define a .NET
method which is mapped onto a piece of sql (or HQL, whatever rocks your
boat).
FB
>
> Sorry if I'm slow in understanding.
>
> Patrick Earl
>
> On Aug 29, 3:20 am, "Frans Bouma" <[email protected]> wrote:
> > > I think the original intention for ISQLFunction was to call
> > > (dialect-
> > > specific) user-defined functions (hence the name), not to create
> > > arbitrary snippets of SQL. Since the implementation has used
> > > positional parameters
> > I
> > > don't think you can generate the SQL you're wanting.
> >
> > > I think the two options you suggested are viable: Create a new
> > > interface/implementation that allows arbitrary hand-cut SQL from a
> > parameter
> > > list, or change the current implementation to use named parameters
> > > (which would only be needed in this specific case, and we'd need to
> > > be careful to generate a unique parameter name for each instance of
> > > the function call within a single query). Either one could be added
> > > to JIRA as a potential improvement.
> >
> > > I'm guessing (but I haven't tried it) that a simpler option might be
> > > to
> > just
> > > render the actual parameter values into the SQL string (i.e., not
> > > parameterised). Notwithstanding that the simplest option, if it's
> > > open to you, is to create a user-defined function in the database
> > > that you can
> >
> > call.
> >
> > One solution could be similar to what console.writeline does
> > and what I do in my framework as well: use {} placeholders. So if
> > someone wants to call a function Foo(@a, @b), they specify the
> > function as Foo({0}, {1}), and the function definition object has
> > besides this string also a list of objects, which are transformed into
> > parameters or pieces of this string (e.g. values, another function,
etc.).
> >
> > This way, the dialect or at least the part where db specific
> > parameters are created, can deal with the parameter creation by
> > transforming the list of objects in the function definition object to
> > parameters or snippets, place them back in the list of objects and a
> > generic piece of code can then transform the function definition
> > object to a SQL snippet. So if you have to re-use a parameter:
> "FooBar({0}, {1}) <> {0}", you can.
> >
> > This system is also usable for creating case statements and
> > for example function mappings in linq.
> >
> > FB
> >
> >
> >
> >
> >
> >
> >
> > > Regards,
> > > Richard
> >
> > > From: Fabio Maulo <mailto:[email protected]>
> > > Sent: Sunday, August 29, 2010 5:05 AM
> > > To: [email protected]
> > > Subject: Re: [nhibernate-development] Re: ISQLFunction with
> > > Repeated Arguments
> >
> > > Which is the problem you have found implementing
> > > NullSafeEquals(p1,p2) ?
> >
> > > On Sat, Aug 28, 2010 at 6:13 PM, Patrick Earl <[email protected]>
> > > wrote:
> >
> > > Thanks for your feedback Fabio. The function would be
> > similar
> > > to
> > > EqOrNullExpression but would offer the following additional
> > > functionality:
> >
> > > 1. It would work for database columns and subselects as
> > well,
> > > not
> > > just parameters passed from .NET.
> > > 3. The HQL function would make the code easy to read and
> > > eliminate
> > > the possibility of error in this area where errors are
> > common.
> > > 2. The function could easily be mapped from a LINQ
> > > method call.
> >
> > > For example, NullSafeEquals(x.Prop1, x.Prop2). I see
> > > that a method
> > > similar to EqOrNullExpression could be implemented to
> > provide
> > > support
> > > for comparing projections in the criteria API. However,
> > > my immediate
> > > problem was HQL based, and I would like this to be
> > > solvable with LINQ
> > > as well. Alternatives include implementing something
> > > like
> > "IS
> > > DISTINCT FROM" as a built-in HQL operation and then
> > > having a LINQ
> > > method map to that type of node. The dialect would then
> > > specifically
> > > handle the SQL implementation using the SQL-99 IS [NOT]
> > > DISTINCT FROM,
> > > MySQL <=>, or the complicated clause as needed.
> >
> > > Patrick Earl
> >
> > > On Aug 28, 1:13 pm, Fabio Maulo <[email protected]>
> > wrote:
> > > > Personally I didn't understand which is the function
> > > you
> > are
> > > needing.
> > > > Can you explain a usecase ?
> >
> > > > In criteria base queries with a property compared to a
> > > parameter that
> > > > function is unneeded... perhaps you are thinking
> > > something else than
> > > > EqOrNullExpression... I'll wait your explication.
> >
> > > > On Sat, Aug 28, 2010 at 4:02 PM, Patrick Earl
> > > <[email protected]> wrote:
> > > > > Solution #1 (duplicate positional parameters based on
> > > template
> > > > > contents) looks to be somewhat complicated to
implement.
> > > > > Solution #2 (manipulate HQL AST and parameter list)
> > > doesn't work for
> > > > > ICriteria / QueryOver.
> > > > > Solution #3 (replace positional parameters with
> > > named) seems
> > > > > potentially viable.
> >
> > > > > I have two "big" questions:
> >
> > > > > 1. Are there cases where positional parameters are
> > > required, or is it
> > > > > reasonable to always pre-process positional
> > > parameters into named
> > > > > parameters? Along with this, what kind of parameters
> > > do the criteria
> > > > > API and Linq generate? Are they always named
> > parameters?
> > > > > 2. Why is it required that the type of a null
> > > parameter is known. In
> > > > > other words, why is it necessary to call SetParameter
> > with
> > > a type
> > > > > overload? Is there any way to say "put NULL in the
> > > DB query and
> > > > > disregard the type?"
> >
> > > > > I'll do some digging, but if anyone knows this stuff
> > off-
> > > hand, that'd
> > > > > be great to know.
> >
> > > > > Patrick Earl
> >
> > > > > On Aug 28, 2:55 am, Patrick Earl <[email protected]>
> > wrote:
> > > > > > I'm interested in implementing a function to
> > > duplicate the behavior of
> > > > > > "IS NOT DISTINCT FROM" on SQL Server. On
> > > PostgreSQL, I'd simply use ?
> > > > > > 1 IS NOT DISTINCT FROM ?2 as the function template.
> > On
> > > SQL Server
> > > > > > though, the template involves repeating the same
> > > argument: (?1 IS NULL
> > > > > > AND ?2 IS NULL) OR (?1 IS NOT NULL AND ?2 IS NOT
> > > NULL AND ?1 = ?2)
> >
> > > > > > Unfortunately, when used with positional
> > > parameters,
> > the
> > > function
> > > > > > template is not processed correctly. The system
> > > duplicates the ?
> > > > > > within the query, but it doesn't add another copy
> > > of
> > the
> > > parameter to
> > > > > > the parameter list. Here are some possible
> > > techniques for dealing
> > > > > > with this issue:
> >
> > > > > > 1. Create another interface like
> > > ISQLParameterizedFunction that
> > > > > > allows modification of the parameter list as well.
> > > > > > 2. Allow dialects to access the HQL AST and
> > > parameter list and modify
> > > > > > them directly.
> > > > > > 3. Replace the positional parameters with named
> > > parameters.
> >
> > > > > > Sadly, I'm too tired for my brain to work properly.
> > > I just wanted to
> > > > > > get these thoughts out there to see if anyone had
> > > any feedback or work-
> > > > > > arounds.
> >
> > > > > > Patrick Earl
> >
> > > > --
> > > > Fabio Maulo
> >
> > > --
> > > Fabio Maulo
