Grouper Users - Open Discussion List

[Shilen Patel <>]

Hi,

If this is still an issue, can you send me your sources.xml file (without
any credentials) and I'll take a look.

Thanks!

- Shilen

On 10/19/16, 4:33 PM, "Michael McManaman" 
<>
 wrote:

>Hey everybody,
>
>I'm a bit stuck on the search query that lists all subjects meeting a
>given search criterion.
>
>I've read this doc -
>https://spaces.internet2.edu/display/Grouper/Subject+API
>
>...looked at samples in the sources,xml, file, found a few I believe
>working examples of the out on the Internet but I get an error when
>testing the file by starting gsh. The error says set numParameters in
>the sources.xml file, but where?
>
>This is what I have in the sources.xml file (including the numParameters )
>
>
>      <search>
>         <searchType>search</searchType>
>
>         <param>
>           <param-name>numParameters</param-name>
>           <param-value>1</param-value>
>         </param>
>
>          <param>
>              <param-name>sql</param-name>
>              <param-value>
>select
>   p.PRINCIPAL as id, s.FULL_NAME as name,
>   (p.UBAFFILIATIONS || ', ' || s.PRIMARY_DEPARTMENT || ', ' ||
>s.PRIMARY_OFFICELOCATION) as description,
>   (p.LAST_NAME || ', ' || p.FIRST_NAME) as lfname, p.EMAIL as email,
>p.PRINCIPAL as loginid
>from
>   principals p, sor s
>where
>   (p.FIRST_NAME LIKE CONCAT('%', CONCAT(?,'%')) OR p.LAST_NAME LIKE
>CONCAT('%', CONCAT(?,'%')))
>             </param-value>
>          </param>
>
>
>      </search>
>
>I've tried innumerable variations on the where clause, w/no luck.
>The error follows, I just don't get the connection on where the query
>gets the number of question marks (bind variables).
>Any help would be greatly appreciated!!
>
>
>
>Cant parse useInClauseForIdAndIdentifier: true
>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:
>Invalid column index,
>Error setting param: 1 in source: jdbc, in query: select
>   p.PRINCIPAL as id, s.FULL_NAME as name,
>   (p.UBAFFILIATIONS || ', ' || s.PRIMARY_DEPARTMENT || ', ' ||
>s.PRIMARY_OFFICELOCATION) as description,
>   (p.LAST_NAME || ', ' || p.FIRST_NAME) as lfname, p.EMAIL as email,
>p.PRINCIPAL as loginid
>from
>   principals p, sor s
>where
>   (p.FIRST_NAME LIKE CONCAT('%', CONCAT(?,'%')) OR p.LAST_NAME LIKE
>CONCAT('%', CONCAT(?,'%'))), Invalid column index, 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, source:
>jdbc, sql: select
>   p.PRINCIPAL as id, s.FULL_NAME as name,
>   (p.UBAFFILIATIONS || ', ' || s.PRIMARY_DEPARTMENT || ', ' ||
>s.PRIMARY_OFFICELOCATION) as description,
>   (p.LAST_NAME || ', ' || p.FIRST_NAME) as lfname, p.EMAIL as email,
>p.PRINCIPAL as loginid
>from
>   principals p, sor s
>where
>   (p.FIRST_NAME LIKE CONCAT('%', CONCAT(?,'%')) OR p.LAST_NAME LIKE
>CONCAT('%', CONCAT(?,'%')))
>         at 
>edu.internet2.middleware.subject.provider.JDBCSourceAdapter.searchHelper(J
>DBCSourceAdapter.java:394)
>         at 
>edu.internet2.middleware.subject.provider.JDBCSourceAdapter.search(JDBCSou
>rceAdapter.java:300)
>         at 
>edu.internet2.middleware.subject.SubjectCheckConfig.checkConfig(SubjectChe
>ckConfig.java:149)
>         at 
>edu.internet2.middleware.grouper.misc.GrouperCheckConfig$1.callback(Groupe
>rCheckConfig.java:439)
>         at 
>edu.internet2.middleware.grouper.GrouperSession.callbackGrouperSession(Gro
>uperSession.java:974)
>         at 
>edu.internet2.middleware.grouper.misc.GrouperCheckConfig.checkConfig(Group
>erCheckConfig.java:435)
>         at 
>edu.internet2.middleware.grouper.misc.GrouperStartup.startup(GrouperStartu
>p.java:256)
>         at 
>edu.internet2.middleware.grouper.app.gsh.GrouperShell.main(GrouperShell.ja
>va:151)
>         at 
>edu.internet2.middleware.grouper.app.gsh.GrouperShellWrapper.main(GrouperS
>hellWrapper.java:31)
>Caused by: java.sql.SQLException: Invalid column index,
>Error setting param: 1 in source: jdbc, in query: select
>   p.PRINCIPAL as id, s.FULL_NAME as name,
>   (p.UBAFFILIATIONS || ', ' || s.PRIMARY_DEPARTMENT || ', ' ||
>s.PRIMARY_OFFICELOCATION) as description,
>   (p.LAST_NAME || ', ' || p.FIRST_NAME) as lfname, p.EMAIL as email,
>p.PRINCIPAL as loginid
>from
>   principals p, sor s
>where
>   (p.FIRST_NAME LIKE CONCAT('%', CONCAT(?,'%')) OR p.LAST_NAME LIKE
>CONCAT('%', CONCAT(?,'%'))), Invalid column index, 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
>         at 
>oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePrepare
>dStatement.java:7829)
>         at 
>oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStateme
>nt.java:7783)
>         at 
>oracle.jdbc.driver.OraclePreparedStatementWrapper.setString(OraclePrepared
>StatementWrapper.java:914)
>         at 
>com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.setString(NewProxyPrepa
>redStatement.java:963)
>         at 
>edu.internet2.middleware.subject.provider.JDBCSourceAdapter.getSqlResults(
>JDBCSourceAdapter.java:613)
>         at 
>edu.internet2.middleware.subject.provider.JDBCSourceAdapter.searchHelper(J
>DBCSourceAdapter.java:360)
>         ... 8 more
>
>