1

SnappyData v.0-5 w/ ClientDriver JDBC driver.

I have a persistent row table in SnappyData called: sensor_data.

From the snappy> shell, this query returns thousands for rows.

snappy> select * from sensor_data where year_num = 2013 and month_num = 1;

When run from a JDBC connection in SpringBoot, I get this error:

PreparedStatementCallback; uncategorized SQLException for SQL [select * from sensor_data where year_num = ? and month_num = ?]; SQL state [XCL14]; error code [20000]; (SQLState=XCL14 Severity=20000) The column position '1' is out of range. The number of columns for this ResultSet is '0'.

Java Code is:

List<SensorData> list = jdbcTemplateBean.query("select * from sensor_data where year_num = ? and month_num = ?", 
                new Object[] {year, month},  new SensorDataRowMapper());

What do I need to do to fix this JDBC issue?

Trimmed Stacktrace on Spring boot:

org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [select * from sensor_data where year_num = ? and month_num = ?]; SQL state [XCL14]; error code [20000]; (SQLState=XCL14 Severity=20000) The column position '1' is out of range. The number of columns for this ResultSet is '0'.; nested exception is java.sql.SQLException: (SQLState=XCL14 Severity=20000) The column position '1' is out of range. The number of columns for this ResultSet is '0'. at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:645) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:680) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:707) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:757) at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:192) at org.kritek.scalability.repository.SensorDataRepository.findByYearAndMonth(SensorDataRepository.java:58) ... at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:555) at java.lang.Thread.run(Thread.java:745) Caused by: java.sql.SQLException: (SQLState=XCL14 Severity=20000) The column position '1' is out of range. The number of columns for this ResultSet is '0'. ... Caused by: ERROR XCL14: The column position '1' is out of range. The number of columns for this ResultSet is '0'. at com.pivotal.gemfirexd.internal.client.am.ColumnMetaData.checkForValidColumnIndex(ColumnMetaData.java:856) at com.pivotal.gemfirexd.internal.client.am.ColumnMetaData.getColumnType(ColumnMetaData.java:638) ... 72 more

Sumedh
  • 383
  • 2
  • 7
Jason
  • 2,006
  • 3
  • 21
  • 36
  • Could you please add the exception stack trace from snappydata server log file? – suranjan Jul 27 '16 at 04:06
  • There is zero logging happening on the server. It does not get past the ClientDriver. It appears the ClientDriver does not like parameterized PreparedStatements (e.g., ?). I took out the two parameters and made the year and month hardcoded and it executed the SQL to the snappy server. – Jason Jul 27 '16 at 04:18

2 Answers2

1

From the stack it looks like NamedParameterJdbcTemplate is being used but '?' placeholders are being used. For NamedParameterJdbcTemplate you need to use named parameters like here.

I will recommend using the standard '?' placeholder mechanism with JdbcTemplate like:

private JdbcTemplate jdbcTemplateBean;

public void setDataSource(DataSource dataSource) {
  this.jdbcTemplateBean = new JdbcTemplate(dataSource);
}

List<SensorData> list = jdbcTemplateBean.query(
    "select * from sensor_data where year_num = ? and month_num = ?",
    new Object[] { year, month },  new SensorDataRowMapper());
Sumedh
  • 383
  • 2
  • 7
  • I tried both approaches (named using :year and :month and again with old-fashioned '?') and they both fail with the same error. I erroneously posted the stacktrace from my first attempt using NamedParameterJdbcTemplate as I scanned the logs to put the error into StackOverflow. Sorry for that. In summary, neither approach works. – Jason Jul 27 '16 at 12:08
1

The issue has been resolved. Here is what happened.... SnappyData routed the query to Spark because it determined it could not handle it. Spark knows nothing about JDBC PreparedStatements or bind variables and raised the error. To fix, I had to set the SnappyData JDBC property "route-query = false" in my DataSource configuration. This ensured it was not routed to Spark.

Jason
  • 2,006
  • 3
  • 21
  • 36
  • Note that we've fixed this issue in the master branch and the fix will be in the next version. – plamb Aug 04 '16 at 16:23