Grouper Users - Open Discussion List

[Michael McManaman <>]

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(JDBCSourceAdapter.java:394)
        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:439)
        at 
edu.internet2.middleware.grouper.GrouperSession.callbackGrouperSession(GrouperSession.java:974)
        at 
edu.internet2.middleware.grouper.misc.GrouperCheckConfig.checkConfig(GrouperCheckConfig.java:435)
        at 
edu.internet2.middleware.grouper.misc.GrouperStartup.startup(GrouperStartup.java:256)
        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: 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(OraclePreparedStatement.java:7829)
        at 
oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:7783)
        at 
oracle.jdbc.driver.OraclePreparedStatementWrapper.setString(OraclePreparedStatementWrapper.java:914)
        at 
com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.setString(NewProxyPreparedStatement.java:963)
        at 
edu.internet2.middleware.subject.provider.JDBCSourceAdapter.getSqlResults(JDBCSourceAdapter.java:613)
        at 
edu.internet2.middleware.subject.provider.JDBCSourceAdapter.searchHelper(JDBCSourceAdapter.java:360)
        ... 8 more