Query works with PreparedStatement, but not with NamedParameterJdbcTemplate

Viewed 232

I have a query that runs just fine when I use a PreparedStatement to run it, but it won't find the table when I run the query with a NamedParameterJdbcTemplate. Am I forgetting to set something up on Spring?

This code works:

StringBuilder sql = new StringBuilder();
String schema = "MY_SCHEMA.";
String countryCode = "123";
String hsnsac = "456";

sql.append("SELECT DISTINCT CATCODE2 from ");
sql.append(schema);
sql.append("MY_$TABLE where CTRYCODE = ? and HSSACODE ");
if (hsnsac.length() < 8) {
    sql.append("LIKE ?");
    hsnsac += "%";
} else {
    sql.append("= ?");
}
try {
    conn = this.getDataSourceTransactionManager().getDataSource().getConnection();
    stmt = new PreparedStatement(conn, sql.toString());
    stmt.setString(1, countryCode);
    stmt.setString(2, hsnsac);
    rs = stmt.executeQuery();
    
    while(rs.next()) {
        System.out.println(rs.getString(1));
    }

} catch(SQLException e) {
    e.printStackTrace();
}

Although, this one doesn't:

StringBuilder sql = new StringBuilder();
String schema = "MY_SCHEMA.";
String countryCode = "123";
String hsnsac = "456";

sql.append("SELECT DISTINCT CATCODE2 from ");
sql.append(schema);
sql.append("MY_$TABLE where CTRYCODE = :countryCode and HSSACODE ");
if (hsnsac.length() < 8) {
    sql.append("LIKE :hsnsac");
    hsnsac += "%";
} else {
    sql.append("= :hsnsac");
}
SqlParameterSource parameters = new MapSqlParameterSource()
        .addValue("countryCode", indiaCountryCode)
        .addValue("hsnsac", hsnsac);
try {
    List<String> catcodes2 = this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), parameters, String.class);
} catch(DataAccessException e) {
    e.printStackTrace();
}

It gives me the following error message:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT DISTINCT CATCODE2 from MY_SCHEMA.MY_$TABLE where CTRYCODE = ? and HSSACODE LIKE ?]; nested exception is com.ibm.as400.access.AS400JDBCSQLSyntaxErrorException: [SQL0204] MY_$TABLE in MY_SCHEMA type *FILE not found.

I'm using DB2 driver 11.1 and Spring JDBC 4.3.7.

0 Answers
Related