As you say, though, "ALIKE" is undocumented. In my experience, that means
that Microsoft could discontinue its use without warning or a continuity
path. Are you suggesting embedding an undocumented feature that may go
away? Won't that make maintenance that much harder?
This is a genuine dilemma for me.
My main problem is there is no published spec for Jet. Can you, for
example, point me to the section of the Access Help or a KB article
that tells me how the IIf() expression is supposed to function in a
Jet query? I have read that Jet uses the VBA expression library and
that it 'knows' how to handle certain expressions 'differently' for
Jet but there's no specifics. It seems to me that the only way forward
is one of discovery i.e. only by using IIf() in a query do we find out
how it works, for example how both true and false parts are not always
evaluated, unlike when IIf() is used in VBA. I think we can conclude
that IIf() in a Jet query is indeed 'supported' functionality because
there are many KB articles with use it. So there are areas of Jet
functionality whose use are implicit rather than explicit.
Another problem is that some features are mentioned as official
features and KB articles give usage examples but we are not given any
detail about how these should function. Let's use an old favourite of
mine: the Jet CHECK constraint. It was announced in the customary 'New
Features' announcement when Jet 4.0 was released (http://
support.microsoft.com/kb/275561) and warranted its own announcement
(
http://support.microsoft.com/kb/201888) and is mentioned in bug
reports (e.g.
http://support.microsoft.com/kb/201310) so there's a lot
of evidence of it being a 'supported' feature. Now, I have a question
about the timing of Jet CHECK constraints: when are they supposed to
be checked?
There is no Access Help topic on for CHECK constraints. Does this make
CHECK constraints an undocumented feature? My opinion is this is
merely an error of omission from a poorly edited Help.
We know that a column-level Validation Rule only gets checked if and
when that column on the current row is updated (e.g. if another column
value on the same row changes the Validation Rule is not checked); I
forget whether this is explicitly documented or something we have
discovered but anyhow can be demonstrated using non-deterministic
Validation Rule such as one using the current timestamp Now() -- let
me know if you'd like dome VBA to recreate such a scenario. A row-
level Validation Rule (confusingly called a 'Table Validation Rule')
get checked when *any* columns' value changes for the current row.
Timing of Validation Rules is intuitive and AFAIK documented.
What Validation Rules cannot do is reference other rows in the same
table and I think this is the reason CHECK constraints were
introduced. Following the logic of the timing of Validation Rules, it
would follow that they get checked when after the *table* has been
updated. Now, most SQL DBMS documentation I've seen solve the problem
of providing a detailed spec by using the ANSI/ISO SQL standards and
declaring whether the product or individual features are compliant or
non-compliant (and ideally detail the actual impedance). Jet is
clearly non-compliant with entry level SQL-92 and AFAIK Microsoft have
made no declaration of compliance for specific Jet functionality
either. There is a suggestion that in designing Jet 4.0 the SQL Server
team followed the SQL-92 spec (after all, why reinvent the wheel?):
"[Jet's ANSI-92 Query Mode] conforms closely to the ANSI-92 Level 1
specification, but is not ANSI-92 Level 1 compliant. This query mode
has more of the ANSI syntax, and the wildcard characters conform to
the SQL specification." (
http://office.microsoft.com/en-gb/access/
HP030704831033.aspx)
The SQL-92 standard specifies that table constraints get checked by
default at the end of each SQL statement; Jet would have to follow the
default behaviour because the exceptions could not apply (e.g. lacks
the syntax to defer constraint checking). However, testing reveals
that Jet's CHECK constraints are not checked on a SQL statement basis,
rather they are tested on a row-by-row basis (
http://groups.google.com/
group/microsoft.public.access/msg/8e3f2cf5f94e0b11).
Further consider that the examples of Jet CHECK constraint usage
provided by Microsoft (above) use subqueries that compare values
across tables:
ALTER TABLE Customers ADD
CHECK (Customer Limit <= (
SELECT SUM (CreditLimit) FROM CreditLimit)));
Although this is supported in SQL-92 it is arguably mot a good idea in
practice. The constraint is defined on the Customer table so if the
CreditLimit on table CreditLimit was updated the constraint would not
be tested at that time. There could now be values in the Cusomter
table that no longer satisfy the constraint and *all* updates to those
rows will now fail -- a hard to detect problem I feel. (Are the
optimizer's statistics now corrupt or does Jet's optimizer not use
statistics from CHECK constraints anyhow? Yet again, no details
exist.)
A multi-table constraint is better implemented using a SQL-92
ASSERTION, which gets checked at the schema level; of course, no
Microsoft product supports CREATE ASSERTION. Clearly, if Microsoft
want to proclaim Jet CHECK constraints as being suitable for multi-
table constraints then they at least have to be tested at the level of
*all* the tables involved in the constraint -- consider that a FOREIGN
KEY constraint simply could not work if it only considered *one*
table! But, again, testing in reality shows that CHECK constraint
checking is performed on a table-by-table basis, FWIW in left-to-right
order in the FROM clause (
http://groups.google.com/group/
microsoft.public.access/msg/80f53c76fa01c832).
Apologies for the long winded nature of this post but I hope I have
conveyed how it is not simply a case of considering whether a feature
is documented but how things work in practice because we do not have a
Jet specification in the level of detail to tell us *how* features
work.
Something to consider is even documented features can change:
DISTINCTROW (
http://support.microsoft.com/kb/168438) and collation
(
http://support.microsoft.com/kb/236952) spring to mind. Also,
Microsoft IMO has a good track record when it comes to backward-
compatibility of Jet syntax, which is really why we have ANSI Query
Modes I feel.
In conclusion and on balance, I would indeed recommend using Jet's
ALike operator. I don't think its omission from documentation is
necessarily relevant. From a practical point of view, coding for both
ANSI Query Modes is a real pain without ALike. Perhaps I'd be more
wary if it came to my attention that Microsoft has declared it an
'undocumented feature'
By the way, are you aware that responses like:
give the impression that you are impatient?
I invested many minutes writing several hundred words and in the
process provided links and accurate quotes from two different versions
of Access Help. Do you see how your request for clarity could sound
disingenuous? But, in reply, my intention was to give you the benefit
of the doubt and merely express mild surprise with a question, "Have I
not been clear?" I then went on to provide further information (with
an offer of even more); there's no way you could have known this but
what you read was a second draft: the first take had much more detail
but on re-reading I felt the point about ANSI Query Modes may have got
lost in the aside about the low quality of the Access Help in the SQL
department, so I edited it significantly. Let's face it: many replies
to this thread were knee-jerk, canned answers which gave no thought
about ANSI Query Modes, whereas I put in some time and effort. So,
again, I find myself mildly surprised at your suggestion that I may
have been *impatient* here. Actually, in all honesty, I find it a bit
galling, frankly. I don't mind if you ignore my efforts. If you don't
like me posting this kind of stuff on your turf then ask me to leave.
You could accuse me of caring too much about detail, having
unrealistic expectation of Jet being a real SQL DBMS platform, posting
information of only minority interest or being repetitive and boring,
because all those are a good fit. But please don't try to tell me I'm
impatient (if only because I'll talk the hind legs off a donkey in
defending myself <g>).
Jamie.
--
