Grouper Users - Open Discussion List

[Scott Koranda <>]

> Hi,
> 
> Attached is the sources.xml I am attempting to use to retrieve
> subjects from a relational database.
> 
> I can retrieve subjects using the appropriate id:
> 
> gsh 0% GrouperSession.startRootSession();
> edu.internet2.middleware.grouper.GrouperSession:
> fa94c2ea90cb45e4890dfa0b6d9d1ad1,'GrouperSystem','application'
> gsh 1% findSubject("1")
> subject: id='1' type='person' source='jdbc' name='Scott Koranda' 
> gsh 2% findSubject("2")
> subject: id='2' type='person' source='jdbc' name='Stewie Anderson' 
> 
> I cannot, however, retrieve subjects by identifier:
> 
> gsh 3% 
> findSubject("")
> // Error: unable to evaluate command: Sourced file: inline
>  evaluation of: 
> ``findSubject("");''
>  :
>  Error invoking compiled command: : Error in compiled
>  command:
>  edu.internet2.middleware.subject.InvalidQueryException: Why
>  is this subject not able to be referenced by id or
>  identifier (do you need to add identifierAttributes to your
>  sources.xml???) Subject id: 1, sourceId: jdbc
> gsh 4% 
> findSubject("")
> // Error: unable to evaluate command: Sourced file: inline
>  evaluation of: 
> ``findSubject("");''
>  : Error invoking compiled command: : Error in compiled
>  command:
>  edu.internet2.middleware.subject.InvalidQueryException: Why
>  is this subject not able to be referenced by id or
>  identifier (do you need to add identifierAttributes to your
>  sources.xml???) Subject id: 2, sourceId: jdbc
> 
> The data is available in the tables. Here is what I get when I
> copy the SQL from sources.xml and substitute the search string
> by hand:
> 
> mysql> SELECT 
>     ->     cm_co_people.id as id,
>     ->     CONCAT(cm_names.given, ' ',cm_names.family) as name, 
>     ->     CONCAT(cm_names.given, ' ',cm_names.family, '(', 
> cm_cos.description, ')') as description,
>     ->     CONCAT(cm_names.family, ', ',cm_names.given) as lfname,
>     ->     cm_email_addresses.mail as email,
>     ->     cm_identifiers.identifier as loginid 
>     -> FROM 
>     ->     cm_co_people 
>     ->     JOIN cm_names ON cm_co_people.id = cm_names.co_person_id 
>     ->     JOIN cm_cos ON cm_co_people.co_id = cm_cos.id
>     ->     LEFT JOIN cm_email_addresses ON cm_co_people.id = 
> cm_email_addresses.co_person_id 
>     ->     LEFT JOIN cm_identifiers ON cm_co_people.id = 
> cm_identifiers.co_person_id
>     -> WHERE
>     ->     cm_identifiers.identifier = 
> ''\G
> *************************** 1. row ***************************
>          id: 1
>        name: Scott Koranda
> description: Scott Koranda(COmanage Gears Internal CO)
>      lfname: Koranda, Scott
>       email: NULL
>     loginid: 
> 
> 
> mysql> SELECT 
>     ->     cm_co_people.id as id,
>     ->     CONCAT(cm_names.given, ' ',cm_names.family) as name, 
>     ->     CONCAT(cm_names.given, ' ',cm_names.family, '(', 
> cm_cos.description, ')') as description,
>     ->     CONCAT(cm_names.family, ', ',cm_names.given) as lfname,
>     ->     cm_email_addresses.mail as email,
>     ->     cm_identifiers.identifier as loginid 
>     -> FROM 
>     ->     cm_co_people 
>     ->     JOIN cm_names ON cm_co_people.id = cm_names.co_person_id 
>     ->     JOIN cm_cos ON cm_co_people.co_id = cm_cos.id
>     ->     LEFT JOIN cm_email_addresses ON cm_co_people.id = 
> cm_email_addresses.co_person_id 
>     ->     LEFT JOIN cm_identifiers ON cm_co_people.id = 
> cm_identifiers.co_person_id
>     -> WHERE
>     ->     cm_identifiers.identifier = 
> ''\G
> *************************** 1. row ***************************
>          id: 2
>        name: Stewie Anderson
> description: Stewie Anderson(LIGO Laboratory)
>      lfname: Anderson, Stewie
>       email: 
> 
>     loginid: 
> 
> 
> I appreciate any input anybody has on what I am doing wrong in
> my sources.xml file.
> 
> Thanks,
> 
> Scott
> 

For the email archives, the problem is that the Grouper code
uses getColumnName() instead of getColumnLabel() and the MySQL
JDBC connector made the change to be strictly compliant so that
getColumnName() only returns the name and not the label.

A work around is to add

?useOldAliasMetadataBehavior=true

to the JDCB connection url:

jdbc:mysql://localhost:3306/registry?useOldAliasMetadataBehavior=true

Thanks much to Shilen and Chris for the help.

Cheers,

Scott