Grouper Users - Open Discussion List

[Chris Hyzer <>]

It needs to look like this:

     <search>
        <searchType>search</searchType>
         <param>
             <param-name>sql</param-name>
                          
             <param-value>
             select
   subject.subjectid as id, subject.name as name,
   lfnamet.lfname as lfname, loginidt.loginid as loginid,
   desct.description as description, emailt.email as email
from
   subject
   left join (select subjectid, value as lfname from subjectattribute
     where name='name') lfnamet
     on subject.subjectid=lfnamet.subjectid
   left join (select subjectid, value as loginid from subjectattribute
     where name='loginid') loginidt
     on subject.subjectid=loginidt.subjectid
   left join (select subjectid, value as description from subjectattribute
      where name='description') desct
     on subject.subjectid=desct.subjectid
   left join (select subjectid, value as email from subjectattribute
      where name='email') emailt
     on subject.subjectid=emailt.subjectid
where
   (lower(name) like '%' || ? || '%')
   or (lower(lfnamet.lfname) like '%' || ? || '%')
   or (lower(loginidt.loginid) like '%' || ? || '%')
   or (lower(desct.description) like '%' || ? || '%')
   or (lower(emailt.email) like '%' || ? || '%')
             </param-value>
         </param>
     </search>

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

 
[mailto:]
 On Behalf Of Keith Hazelton
Sent: Tuesday, September 25, 2012 2:19 PM
To: 

Subject: Re: [pb-iam] [grouper-users] Error when running Grouper Export

Had done that already in $GROUPER_HOME/conf/source.xml as part of running 
Grouper Installer:
I removed the <!--    --> around the select statement below.  

Hmm....

       --Keith
____________
On Sep 25, 2012, at 12:59 PM, Chris Hyzer wrote:

> You need to edit your sources.xml for postgres...  see this in the 
> sources.example.xml:
> 
>             <!--  for postgres, use this query since no concat() exists:
> 
>             select
>   subject.subjectid as id, subject.name as name,
>   lfnamet.lfname as lfname, loginidt.loginid as loginid,
>   desct.description as description, emailt.email as email
> from
>   subject
>   left join (select subjectid, value as lfname from subjectattribute
>     where name='name') lfnamet
>     on subject.subjectid=lfnamet.subjectid
>   left join (select subjectid, value as loginid from subjectattribute
>     where name='loginid') loginidt
>     on subject.subjectid=loginidt.subjectid
>   left join (select subjectid, value as description from subjectattribute
>      where name='description') desct
>     on subject.subjectid=desct.subjectid
>   left join (select subjectid, value as email from subjectattribute
>      where name='email') emailt
>     on subject.subjectid=emailt.subjectid
> where
>   (lower(name) like '%' || ? || '%')
>   or (lower(lfnamet.lfname) like '%' || ? || '%')
>   or (lower(loginidt.loginid) like '%' || ? || '%')
>   or (lower(desct.description) like '%' || ? || '%')
>   or (lower(emailt.email) like '%' || ? || '%')
> 
> -->
> 
> -----Original Message-----
> From: 
> 
>  
> [mailto:]
>  On Behalf Of Keith Hazelton
> Sent: Tuesday, September 25, 2012 1:52 PM
> To: 
> 
> Cc: pbIam
> Subject: [grouper-users] Error when running Grouper Export
> 
> I have Grouper 2.1.2 installed on a CentOS 6.2 VM and configured to use 
> Postgres. The UI works fine, I've created some folders under Root:bamboo 
> and wanted to do a trial export/import cycle. Here's the console log of the 
> export step. 
> 
> Note that there is a java error stack near the end, but that the process 
> completes with a status line and an export file is created. 
> Any clue what's causing the error? 
> Any idea if the resulting file is good?
> 
> Thoughts appreciated, --Keith
> 
> 
> ----------
> [root@arche
>  bin]# ./gsh -xmlexport -stems Root:bamboo /home/khazelton/bmboo-1.xml
> Using GROUPER_HOME: /opt/grouper-2.1.2/grouper.apiBinary-2.1.2/bin/..
> Using GROUPER_CONF: /opt/grouper-2.1.2/grouper.apiBinary-2.1.2/conf
> Using JAVA: /usr/lib/jvm/java-1.6.0-openjdk/bin/java
> using MEMORY: 64m-750m
> (note, might need to type in your response multiple times (Java stdin is 
> flaky))
> (note, you can whitelist or blacklist db urls and users in the 
> grouper.properties)
> Are you sure you want to export data to xml in db user 'grouper', db url 
> 'jdbc:postgresql://localhost:5432/grouperdb'? (y|n): 
> y
> Continuing...
> Grouper starting up: version: 2.1.2, build date: 2012/08/14 15:02:01, env: 
> <no label configured>
> grouper.properties read from: 
> /opt/grouper-2.1.2/grouper.apiBinary-2.1.2/conf/grouper.properties
> Grouper current directory is: /opt/grouper-2.1.2/grouper.apiBinary-2.1.2/bin
> log4j.properties read from: 
> /opt/grouper-2.1.2/grouper.apiBinary-2.1.2/conf/log4j.properties
> Grouper is logging to file: 
> /opt/grouper-2.1.2/grouper.apiBinary-2.1.2/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.2/grouper.apiBinary-2.1.2/conf/grouper.hibernate.properties
> grouper.hibernate.properties: 
> grouper@jdbc:postgresql://localhost:5432/grouperdb
> sources.xml read from: 
> /opt/grouper-2.1.2/grouper.apiBinary-2.1.2/conf/sources.xml
> sources.xml groupersource id: g:gsa
> sources.xml groupersource id: grouperEntities
> sources.xml jdbc source id: jdbc: GrouperJdbcConnectionProvider
> Subject API error: error with subject source id: jdbc, name: Example JDBC 
> Source Adapter, problem with search, in sources.xml: serachType search: , 
> edu.internet2.middleware.subject.SourceUnavailableException: ERROR: 
> function concat(character varying, unknown) does not exist, source: jdbc, 
> sql: select
> s.subjectid as id, s.name as name,
> (select sa2.value from subjectattribute sa2 where name='name' and 
> sa2.SUBJECTID = s.subjectid) as lfname,
> (select sa3.value from subjectattribute sa3 where name='loginid' and 
> sa3.SUBJECTID = s.subjectid) as loginid,
> (select sa4.value from subjectattribute sa4 where name='description' and 
> sa4.SUBJECTID = s.subjectid) as description,
> (select sa5.value from subjectattribute sa5 where name='email' and 
> sa5.SUBJECTID = s.subjectid) as email
> from 
> subject s
> where
> s.subjectid in (
> select subjectid from subject where lower(name) like 
> concat('%',concat(?,'%')) union
> select subjectid from subjectattribute where searchvalue like 
> concat('%',concat(?,'%'))
> )
> at 
> edu.internet2.middleware.subject.provider.JDBCSourceAdapter.searchHelper(JDBCSourceAdapter.java:380)
> at 
> edu.internet2.middleware.subject.provider.JDBCSourceAdapter.search(JDBCSourceAdapter.java:300)
> at 
> edu.internet2.middleware.subject.SubjectCheckConfig.checkConfig(SubjectCheckConfig.java:149)
> 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.xml.export.XmlExportGsh.main(XmlExportGsh.java:118)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at 
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
> at 
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:616)
> at 
> edu.internet2.middleware.grouper.app.gsh.GrouperShell.handleSpecialCase(GrouperShell.java:204)
> at 
> edu.internet2.middleware.grouper.app.gsh.GrouperShell.main(GrouperShell.java:144)
> at 
> edu.internet2.middleware.grouper.app.gsh.GrouperShellWrapper.main(GrouperShellWrapper.java:31)
> Caused by: org.postgresql.util.PSQLException: ERROR: function 
> concat(character varying, unknown) does not exist
> at 
> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592)
> at 
> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327)
> at 
> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
> at 
> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
> at 
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:350)
> at 
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
> at 
> com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
> at 
> edu.internet2.middleware.subject.provider.JDBCSourceAdapter.getSqlResults(JDBCSourceAdapter.java:625)
> at 
> edu.internet2.middleware.subject.provider.JDBCSourceAdapter.searchHelper(JDBCSourceAdapter.java:346)
> ... 14 more
> 
> 
> Starting: 458 records in the DB to be exported (not exact, might be less)
> DONE: 12:42:27: exported 458 records to: /home/khazelton/bmboo-1.xml
> [root@arche
>  bin]# vim /home/khazelton/bmboo-1.xml
>