Hi all,
One of the most annoying things about SQL is the difference between
comparing a value vs. comparing a null.
...WHERE MIDDLE_INITIAL = 'R'
vs.
...WHERE MIDDLE_INITIAL IS NULL
There's really no good reason for this... but I won't get into that debate
because it's beyond our control. I'll just say that this is really a pain
for developers generating dynamic SQL, when trying to build a query by
example (QBE).
I'm thinking of adding a new operator to dynamic SQL. There's a few
options:
-- This one would generate only the "=" or "is" based on the state of null.
WHERE MIDDLE_INITIAL <eq property="initial"> #{initial}
-- This one would generate the "= ?" or "is ?" and the parameter mapping in
one shot, but looks less like an operator.
WHERE MIDDLE_INITIAL <eq property="#{initial}">
In both of the above cases I really don't like injecting an XML element
where an operator used to be. So another option might be:
WHERE MIDDLE_INITIAL == #{initial}
This is a lot harder to implement, but might be worth it. If '==' is
reserved by any databases, then we could use other tokens, like "@=" or
"~="... we'll pick something. We'll also need "not equals" to supply '<>'
and 'is not' respectively, so something like "<neq>", "!==" or "!~="....
hmmm, starting to look like Scala LOL.
Anyway, you get the idea. Now how would you do it? The goal is to
eliminate the need for conditional SQL generation logic, just to use a = or
is based on value or NULL comparison respectively.
Cheers,
Clinton
