Grouper Users - Open Discussion List

[Chris Hyzer <>]

First off, if you have everything in one table including attributes, you 
might want to consider using the GrouperJdbcSourceAdapter2.

If you want to use the one you are using...  some comments:

1. SELECT DISTINCT(user_id),fullname,organisation FROM vw_users
WHERE uid IS NOT NULL and organisation = 'TERENA' ORDER BY user_id;

Im not a postgres expert, but why would distinct go around one column?  

Is that the same as

SELECT DISTINCT user_id,fullname,organisation FROM vw_users
WHERE uid IS NOT NULL and organisation = 'TERENA' ORDER BY user_id;

Do you not have a unique constraint on user_id??  If so, you don’t need 
distinct

2. The question mark you have is in quotes, so it wont be recognized as a 
bind variable:

3. The inclause should only be the part that will be OR'ed together:

FROM:
     <search>
         <searchType>searchSubject</searchType>
         <param>
             <param-name>sql</param-name>
             <param-value>
                        SELECT DISTINCT(user_id),fullname,organisation FROM 
vw_users WHERE {inclause}
            </param-value>
         </param>
         <param>
             <param-name>inclause</param-name>
             <param-value>
                                uid IS NOT NULL and organisation = 'TERENA' 
AND user_id = '?'
            </param-value>
         </param>
         </search>

TO:

     <search>
         <searchType>searchSubject</searchType>
         <param>
             <param-name>sql</param-name>
             <param-value>
                        SELECT user_id,fullname,organisation FROM vw_users 
WHERE uid IS NOT NULL and organisation = 'TERENA' AND {inclause}
            </param-value>
         </param>
         <param>
             <param-name>inclause</param-name>
             <param-value>
                                user_id = ?
            </param-value>
         </param>
         </search>

Not sure if there are other issues...    if you want to send me the DDL of 
your subject table with a couple of test row inserts, I could try it out...

Thanks,
Chris


-----Original Message-----
From: 

 
[mailto:]
 On Behalf Of Dick Visser
Sent: Wednesday, August 15, 2012 9:57 AM
To: 

Subject: [grouper-users] sources.xml question agina

Hi

I;m trying to get the sources.xml configured properly.
We have a database with users, that I;d like to use as test subject
source. This is in the form of a flat table.

tnc2012=# SELECT DISTINCT(user_id),fullname,organisation FROM vw_users
WHERE uid IS NOT NULL and organisation = 'TERENA' ORDER BY user_id;

 user_id |                  fullname                  | organisation
---------+--------------------------------------------+--------------
       1 | Dick Visser                                | TERENA
       2 | Christian Gijtenbeek                       | TERENA
      10 | Hanna Cherigui                             | TERENA
      23 | Jim Buddin                                 | TERENA
      25 | Gyöngyi Horváth                            | TERENA
...
(26 rows)


Now I have configured this:

    <init-param>
       <param-name>SubjectID_AttributeType</param-name>
       <param-value>user_id</param-value>
     </init-param>
     <init-param>
       <param-name>Name_AttributeType</param-name>
       <param-value>fullname</param-value>
     </init-param>
     <init-param>
       <param-name>Description_AttributeType</param-name>
       <param-value>organisation</param-value>
     </init-param>


According to the subject-0.3.1.doc:

searchSubject <!-- Select the unique record or entry with
subjectId=%TERM%, or none if %TERM% is no subject's subjectId. -->


So I have this as the first 'search' element:

     <search>
         <searchType>searchSubject</searchType>
         <param>
             <param-name>sql</param-name>
             <param-value>
                        SELECT DISTINCT(user_id),fullname,organisation FROM 
vw_users WHERE {inclause}
            </param-value>
         </param>
         <param>
             <param-name>inclause</param-name>
             <param-value>
                                uid IS NOT NULL and organisation = 'TERENA' 
AND user_id = '?'
            </param-value>
         </param>
         </search>


Now if I run gsh.sh I get:

root@grouper:/opt/GROUPER_2_1_BRANCH/grouper/bin#
 ./gsh.sh
Using GROUPER_HOME: /opt/GROUPER_2_1_BRANCH/grouper/bin/..
Using GROUPER_CONF: /opt/GROUPER_2_1_BRANCH/grouper/bin/../conf
Using JAVA: java
using MEMORY: 64m-750m
Grouper starting up: version: 2.1.1, build date: 2012/08/03 15:26:40,
env: <no label configured>
grouper.properties read from:
/opt/GROUPER_2_1_BRANCH/grouper/conf/grouper.properties
Grouper current directory is: /opt/GROUPER_2_1_BRANCH/grouper/bin
log4j.properties read from:
/opt/GROUPER_2_1_BRANCH/grouper/conf/log4j.properties
Grouper is logging to file:
/opt/GROUPER_2_1_BRANCH/grouper/bin/../logs/grouper_error.log, at min
level WARN for package: edu.internet2.middleware.grouper, based on
log4j.properties
grouper.hibernate.properties:
/opt/GROUPER_2_1_BRANCH/grouper/conf/grouper.hibernate.properties
grouper.hibernate.properties:
grouper_user@jdbc:postgresql://ip6-localhost:5432/grouper
sources.xml read from:        /opt/GROUPER_2_1_BRANCH/grouper/conf/sources.xml
sources.xml groupersource id: g:gsa
sources.xml groupersource id: grouperEntities
sources.xml jdbc source id:   jdbc:
core_user@jdbc:postgresql://[2001:610:148:dead::8]/tnc2012?ssl=true
Cant parse useInClauseForIdAndIdentifier: true
Subject API error: error with subject source id: jdbc, name: test JDBC
Source Adapter, problem with getSubject by id, in sources.xml: search
searchSubject: ,
edu.internet2.middleware.subject.SourceUnavailableException: problem
in sources.xml source: jdbc, sql: SELECT
DISTINCT(user_id),fullname,organisation FROM vw_users WHERE  (  ( uid
IS NOT NULL and organisation = 'TERENA' AND user_id = '?' )  ) , id
size: 1, searchSubject
        at 
edu.internet2.middleware.subject.provider.JDBCSourceAdapter.uniqueSearchBatch(JDBCSourceAdapter.java:1258)
        at 
edu.internet2.middleware.subject.provider.JDBCSourceAdapter.getSubjectsByIds(JDBCSourceAdapter.java:697)
        at 
edu.internet2.middleware.subject.provider.JDBCSourceAdapter.getSubject(JDBCSourceAdapter.java:176)
        at 
edu.internet2.middleware.subject.SubjectCheckConfig.checkConfig(SubjectCheckConfig.java:111)
        at 
edu.internet2.middleware.grouper.misc.GrouperCheckConfig$1.callback(GrouperCheckConfig.java:443)
        at 
edu.internet2.middleware.grouper.GrouperSession.callbackGrouperSession(GrouperSession.java:801)
        at 
edu.internet2.middleware.grouper.misc.GrouperCheckConfig.checkConfig(GrouperCheckConfig.java:439)
        at 
edu.internet2.middleware.grouper.misc.GrouperStartup.startup(GrouperStartup.java:132)
        at 
edu.internet2.middleware.grouper.app.gsh.GrouperShell.main(GrouperShell.java:151)
        at 
edu.internet2.middleware.grouper.app.gsh.GrouperShellWrapper.main(GrouperShellWrapper.java:31)
Caused by: org.postgresql.util.PSQLException: The column index is out
of range: 1, number of columns: 0.,
Error setting param: 1 in source: jdbc, in query: SELECT
DISTINCT(user_id),fullname,organisation FROM vw_users WHERE  (  ( uid
IS NOT NULL and organisation = 'TERENA' AND user_id = '?' )  ) , The
column index is out of range: 1, number of columns: 0., maybe not
enough question marks (bind variables) are in query, or the number of
question marks in the query is not the same as the number of
parameters (might need to set the optional param numParameters), or
the param 'numParameters' in sources.xml for that query is incorrect,
jdbc
        at 
org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:53)
        at 
org.postgresql.core.v3.SimpleParameterList.setStringParameter(SimpleParameterList.java:118)
        at 
org.postgresql.jdbc2.AbstractJdbc2Statement.bindString(AbstractJdbc2Statement.java:2188)
        at 
org.postgresql.jdbc2.AbstractJdbc2Statement.setString(AbstractJdbc2Statement.java:1304)
        at 
org.postgresql.jdbc2.AbstractJdbc2Statement.setString(AbstractJdbc2Statement.java:1290)
        at 
com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.setString(NewProxyPreparedStatement.java:963)
        at 
edu.internet2.middleware.subject.provider.JDBCSourceAdapter.getSqlResults(JDBCSourceAdapter.java:648)
        at 
edu.internet2.middleware.subject.provider.JDBCSourceAdapter.uniqueSearchBatch(JDBCSourceAdapter.java:1245)
        ... 9 more

Type help() for instructions
gsh 0%



Any idea?


Thanks!


-- 
Dick Visser
System & Networking Engineer
TERENA Secretariat
Singel 468 D, 1017 AW Amsterdam
The Netherlands