Grouper Developers Forum

["Cramton, James" <>]

I've been beating my head against a problem in our grouper instance
that's using a local SQL person registry as the person source using the
Subject Search form in MACE Grouper.  Before I spend time reverse
engineering the subject search code, I'd like to throw the issue out
there, in case there's an obvious solution I'm overlooking.
 
The problem is that I can't configure grouper to successfully search our
SQL person source using a wild card search. The complete search
definition from our sources.xml file is included below, but the main
issue seems to come from the where clause:

   where ( (REGISTRY_USER_UID = lower(?)) OR (REGISTRY_USER_CN LIKE
(lower(?)) ) )

This is the query that comes closest to what I want, which I've tested
against oracle successfully:

   where ( (REGISTRY_USER_UID = lower(cramton)) OR (REGISTRY_USER_CN
LIKE (lower('%cramton%')) ) )
 

The first clause, obviously, does not provide adequate functionality,
because it will only match "jcramton" or "james f. cramton", for
example. What we need to be able to support is searching for [last
name], which is normally achieved by a wild card search on the cn
attribute. I haven't found a combination of % and ' and () that returns
results in MACE Grouper, despite having established the baseline query
I'm looking for in SQL Plus.

Has anyone experience configuring a parameterized query like this to
support wild card searching in a SQL person registry?

Thanks!
 
James Cramton
Lead Programmer/Analyst
Brown University

401 863-7324

 
 
<search>
 <searchType>search</searchType>
 <param>
  <param-name>numParameters</param-name>
  <param-value>2</param-value>
 </param>
 <param>
  <param-name>sql</param-name>
  <param-value>
   select 
   "REGISTRY_USER_BROWNBRUID" as "brownBruId", 
   "REGISTRY_USER_CN" as "cn", 
   "REGISTRY_USER_UID" as "uid", 
   "REGISTRY_USER_BROWNFERPA" as "brownFerpa", 
   "REGISTRY_USER_BROWNSTATUS" as "brownStatus", 
   "REGISTRY_USER_BROWNTYPE" as "brownType", 
   "REGISTRY_USER_MAILROUTEADDR" as "mailRoutingAddress", 
   "REGISTRY_USER_BROWNNETID" as "brownNetId", 
   "REGISTRY_USER_OU" as "ou", 
   "REGISTRY_USER_BROWNSHORTID" as "brownShortId", 
   "REGISTRY_USER_MAIL" as "mail", 
   "REGISTRY_USER_TITLE" as "title", 
   "REGISTRY_USER_DISPLAYNAME" as "displayName"
   from REGISTRY_USER 
   where ( (REGISTRY_USER_UID = lower(?)) OR (REGISTRY_USER_CN LIKE
(lower(?)) ) )
  </param-value>
 </param>
</search>