Relational Operators and Nested Queries with CDO?

  • Thread starter Thread starter Chris Fannin
  • Start date Start date
C

Chris Fannin

I'm in dire need of information on how to use relational operators and
nested queries within a CDO MessageFilter.

For a regular OOM Items.Find, I would do something like:

([InquiryStatus] <> "Closed") And ([Inquiry Type] = "Hardware/Other" Or
[Inquiry Type] = "Request") And ([Created] >= "02/21/2005" And [Created] <=
"02/25/2005") And ([Priority] > 0)

I can't figure out how to do it with a CDO MessageFilter. I'd really
appreciate it if someone could show me how to do the query above using the
Messages.Filter object. Thanks.
 
Chris Fannin said:
([InquiryStatus] <> "Closed") And ([Inquiry Type] = "Hardware/Other"
Or [Inquiry Type] = "Request") And ([Created] >= "02/21/2005" And
[Created] <= "02/25/2005") And ([Priority] > 0)

I can't figure out how to do it with a CDO MessageFilter. I'd really
appreciate it if someone could show me how to do the query above using
the Messages.Filter object. Thanks.
Click to expand...

You can't do nested filters, CDO1.21 isn't that sophisticated. The only
range operators that it provides are specific ones; there's the TimeFirst
and TimeLast properties, Size, possibly some more I'm not seeing now. The
docs for MessageFilter explain what's going on in a bit more detail, but
basically, CDO1.21's message filtering is fairly limited.

You'll need to use Extended MAPI for the sort of filter you want,
unfortunately (or the OOM).

Alternatively, you could pick the 'biggest' filter that CDO1.21 can do,
get it to filter that way, then do the rest of the filtering by hand, so
that at least your manual filter code has as few messages to look through
as possible.

-- dan
 
I figured it wasn't. I stared at the CDO help file forever and couldn't
think of a combination of properties that would do it.

I can't use the OOM because it's too slow. All of our items have heavy
scripting. Using the OOM causes the scripts to be executed. CDO is the only
way I've found to get around this problem. It's lightning fast in
comparison.

I've been using the CDO filter + manual filtering, but this is becoming
cumbersome. Not only that, but as more items are added to the system, it
will become slower due to the increasing amounts of returned items. The
"biggest" filter that CDO can do is still not sufficient. The example I gave
just covers the types of filtering I need, but is nowhere near as complex as
most of my filters are.

I looked into using Redemption for the filtering, but since it relies on the
OOM and returns OOM objects, it is just as slow as using the OOM directly. I
need a library that can do the filtering I need and return CDO objects.
 
Chris Fannin said:
I can't use the OOM because it's too slow. All of our items have heavy
scripting. Using the OOM causes the scripts to be executed.
Click to expand...

Interesting, I never knew that would happen.
I looked into using Redemption for the filtering, but since it relies
on the OOM and returns OOM objects, it is just as slow as using the
OOM directly. I need a library that can do the filtering I need and
return CDO objects.
Click to expand...

In that case, I think you're stuck with Extended MAPI to do the
filtering, unfortunately.
http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/mapi/html/87297ed0-6cc3-48aa-b35b-5e66b09943bc.asp
is the docs for a restriction in ExMAPI, you can create as complicated
a restriction as you like and as long as the server doesn't choke on it
you'll be fine.

Getting objects from CDO1.21 to ExMAPI is easy enough, you can use the
MAPIOBJECT property there; getting them back the other way you'll have
to go by ENTRYID, but that's easy enough.

Of course, Extended MAPI means writing in C/C++/Delphi, not VB, which
may be a sticking point for you. It should be possible to write just a
DLL in ExMAPI where you pass in the folder and filter to the DLL, it
does the filtering internally and returns a list of results, so at least
the rest of your code wouldn't be affected.

-- dan
 
I didn't know it would, either, until I accessed a particular item got
message boxes appearing that I knew were coded in the form.

The real unfortunate thing is that I don't know C or Delphi. I was hoping
that someone has already written a DLL that can accept a CDO Folder object
(or at least the EntryID & StoreID) and a query in some format (I don't care
what format) -- then pass back a CDO Messages collection, or an object from
the DLL that returns similar properties (Count, etc).
 
With Redemption you can use the Extended MAPI type filters using a MAPITable
object and a filter for it. The syntax is almost identical to using Extended
MAPI but you can do it from VB/VBA/VBScript.
 
Ken Slovak - said:
With Redemption you can use the Extended MAPI type filters using a
MAPITable object and a filter for it. The syntax is almost identical
to using Extended MAPI but you can do it from VB/VBA/VBScript.
Click to expand...

Hey, that's really cool, I never noticed that about Redemption. That'll
certainly do what the original poster's looking for.

-- dan
 
Yup. I've used some pretty complex filters that way, with multiple And and
Or clauses. The main thing to remember when you use that type of filter is
to check for RES_EXIST when you're looking at properties that might not be
there. Pretty cool.
 
That would be great except I'm having one heck of a time trying to get the
filter set up like I outlined. I contacted Dmitry directly months ago asking
for an example of how to do a nested filter with various relational
operators, but he just pointed me back to the site. There isn't an example
of such a filter (that I can see, at least).
 
Well, I'm not going to write your filter for you, but in general here's how
to proceed.

You can use a combination of Restriction_And and Restriction_Or clauses.
Once you have an "And" or "Or" you would use a sort of RPN notation by
adding clauses to the "And" or "Or". For user properties you would need an
And that combined the value you were looking for plust a RES_EXIST for that
property existing at all.

Here's a snippet that shows some And's mixed together. You'd proceed the
same if you were including Or's.

Dim rdmTable As Redemption.MAPITable
Dim rdmFilter As Redemption.TableFilter
Dim rdmResMask As Redemption.RestrictionBitmask
Dim rdmResExist As Redemption.RestrictionExist
Dim rdmResExistMailClass As Redemption.RestrictionExist
Dim rdmResExistIdentity As Redemption.RestrictionExist
Dim rdmResAnd As Redemption.RestrictionAnd
Dim rdmResAndExist As Redemption.RestrictionAnd
Dim rdmResContent As Redemption.RestrictionContent

Set rdmFilter = rdmTable.Filter
With rdmFilter
.Clear

Set rdmResAnd = .SetKind(RES_AND)
With rdmResAnd
set rdmResAndExist = .Add(RES_AND)
With rdmResAndExist
Set rdmResExistMailClass = .Add(RES_EXIST) 'check for custom
property named "MailClass"

With rdmResExistMailClass
.ulPropTag = glngMailClassTag
End With

'property must not only exist but also must contain "Foobar"
(lower or upper case)
Set rdmResContent = .Add(RES_CONTENT)
With rdmResContent
.ulPropTag = glngMailClassTag
.ulFuzzyLevel = FL_SUBSTRING + FL_IGNORECASE
.lpProp = "Foobar"
End With

Set rdmResExistIdentity = .Add(RES_EXIST) 'And this must exist
also
With rdmResExistIdentity
.ulPropTag = glngTrustedIdentityTag
End With

Set rdmResMask = .Add(RES_BITMASK) 'and the value in flags must
show as read item
With rdmResMask
.relBMR = BMR_EQZ
.ulPropTag = PR_MESSAGE_FLAGS
.ulMask = MSGFLAG_READ
End With
End With
 
Back
Top