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?
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?
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
