Grouper Developers Forum

[Tom Barton <>]

Have you tried

where ( (REGISTRY_USER_UID = lower(?)) OR (REGISTRY_USER_CN LIKE
('%'||lower(?)||'%') ) )

I got that from the example sources.xml in the wiki 
<https://wiki.internet2.edu/confluence/display/i2miCommon/subject-0.3.1-doc>.

Tom

Cramton, James wrote:
> 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>