Grouper Developers Forum

["Cramton, James" <>]

That did the trick, Tom. Thanks for thinking of that reference. 

-----Original Message-----
From: Tom Barton 
[mailto:]
 
Sent: Monday, July 30, 2007 11:01 PM
To: Cramton, James
Cc: 

Subject: Re: [grouper-dev] Help on Grouper Wildcard Subject Search in
SQL Person Registry

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>
>