2

I keep getting this exception 

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback;  bad SQL grammar [SELECT id, name FROM pts WHERE id IN ()]; nested exception is java.sql.SQLException: Incorrect syntax near ')'.

Here is the function where my SQL is being executed

public List<ParentPt> getParentPtsFromWorkPackage(String workPackage) {
    Set<Integer> ptNums = getParentPtNums(workPackage);

    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("ptNums", ptNums);

    String sql = "SELECT id, name FROM pts WHERE id IN (:ptNums)";

    NamedParameterJdbcTemplate namedJdbcTemplate = new NamedParameterJdbcTemplate(this.dataSource);
    List<ParentPt> pts = namedJdbcTemplate.query(sql, params, new ParentPtMapper());

    return pts;
}

And my helper function that's being called:

public Set<Integer> getParentPtNums(String workPackage) {
    String sql = "SELECT DISTINCT predecessor FROM pts WHERE RTRIM(LTRIM(work_package_name)) = ? AND predecessor IS NOT NULL";

    List<Integer> ptNums = jdbcTemplateObject.queryForList(
            sql, new Object[] {workPackage}, Integer.class);

    Set<Integer> ptNumSet = new HashSet<Integer>(ptNums);

    return ptNumSet;
}

I've already looked at How to execute IN() SQL queries with Spring's JDBCTemplate effectivly? and I'm following the same conventions they use to make the query, so I can't really tell what's going wrong.

Community
  • 1
  • 1
khorik
  • 43
  • 1
  • 6
  • 1
    Do you have null values in the List? – Neil DCruz Jun 14 '16 at 18:16
  • @NeilDCruz Which list are you talking about? If you're talking about ptNums, then yes, it can contain null values. – khorik Jun 14 '16 at 18:21
  • Yes, I am talking about `ptNums`. Can you drop them from the list before binding it to the query? – Neil DCruz Jun 14 '16 at 18:21
  • Can you use a Set, and carefully remove the null element first? – mjn Jun 14 '16 at 18:22
  • @NeilDCruz I dropped the null values and changed the list to a set, but I'm still getting the same error... – khorik Jun 14 '16 at 18:35
  • It certainly seems like the problem is that `ptNums` has a null value (or perhaps after you've dropped nulls, is unset) and if you don't substitute anything into the query for that variable, the SQL generated isn't valid. Could you simply generate a slightly different query string with no WHERE IN clause when `ptNums` is null/absent? – DVA Jun 14 '16 at 19:01
  • @DVA You're right; after much investigating I found out that getParentPtNums is returning an empty set. For some reason, the query is returning empty. I submitted the same query in my MSSQL db and there are certainly results, so I am just doing some more debugging right now. – khorik Jun 14 '16 at 20:43

0 Answers0