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.