> I think I went off the deep end in trying to understand what you were
saying
> Frans. After looking at your examples and re-reading what you're saying,
it
> seems much simpler than what I thought you meant.
> It now sounds like the function definition is basically just the pattern.
> Any {x} members are just replaced by whatever was in that argument slot,
be
> they other function calls, parameters, subqueries, or constants. Is that
> right now?
You got it right :)
> Interestingly, I had assumed that was exactly what SQLFunctionTemplate
> already did. It doesn't seem very intuitive that I can't make a template
> like (?1 + ?1 = ?2) where it basically just replaces the ?x parameters
with
> whatever was generated by the original function arguments.
I have no deep knowledge of ISQLFunction and the current
implementation, so I can't help you further, I just commented on how it
might be solved. What you suggest below seems about right to me, but I don't
know how much work it is to implement it in NH
FB
>
> As suggested, I've produced a unit test patch that demonstrates the
failure
> using the existing trim function.
>
> http://216.121.112.228/browse/NH-2318
>
> Though I haven't analyzed it much, perhaps it would be possible to reuse
> ISQLFunction by passing a special set of placeholder args like ?
> 1, ?2, and ?3 to ISQLFunction.Render. The result would then be post-
> processed and the placeholders could be replaced with whatever was
supposed
> to be there.
>
> Patrick Earl
>
> On Aug 30, 4:43 am, "Frans Bouma" <[email protected]> wrote:
> > > 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
