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
