IJC date MySQL issue.

I have a MySQL5 IJC project that has a main table and batch subtable. The intertable relationships seem to work - ie I can search for values in the main or batch subtable - no problem. I can enter and delete records from either table.





One of my subtable fields is a DATE type. If I try to search for dates, I get the error at the end of this post - I have highlighted the SQL statement. Notice the date in the SQL is "?" and I the error mentions being unable to cast types. If I directly query the database with the bold highlited statement, but replace ? with a legit date value (eg < 2001-01-01), the query works.





I have created a derby database containing the exact same data and the date searching works. Can anyone suggest a solution?





WinXP SP2, JRE 6U11, JWS 2.42





java.sql.SQLException: Cannot convert class java.lang.String to SQL type requested due to java.lang.IndexOutOfBoundsException - Index: 0, Size: 0


 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)


 at com.mysql.jdbc.PreparedStatement.setObject(PreparedStatement.java:3304)


 at com.mysql.jdbc.PreparedStatement.setObject(PreparedStatement.java:3088)


 at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:163)


 at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:163)


 at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:195)


 at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:88)


 at com.im.ijcs.impl.query.DefaultParamProcessor.bindParameters(DefaultParamProcessor.java:50)


 at com.im.ijcs.impl.query.DefaultParamProcessor.bindParameters(DefaultParamProcessor.java:19)


 at com.im.ijcs.impl.query.Executor$PreparedStatementCreatorImpl.setValues(Executor.java:482)


 at com.im.ijcs.impl.query.Executor$PreparedStatementCreatorImpl.createPreparedStatement(Executor.java:431)


 at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:560)


Caused: org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [SELECT DISTINCT D3_ROOT.cd_id FROM D3_ROOT, D3_BATCH WHERE (D3_BATCH.BATCH_DATE < ?  AND  (D3_BATCH.D3_ROOT_ID = D3_ROOT.cd_id))]; SQL state [S1000]; error code [0]; Cannot convert class java.lang.String to SQL type requested due to java.lang.IndexOutOfBoundsException - Index: 0, Size: 0; nested exception is java.sql.SQLException: Cannot convert class java.lang.String to SQL type requested due to java.lang.IndexOutOfBoundsException - Index: 0, Size: 0


 at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:121)


 at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:322)


 at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:582)


 at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:616)


 at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:641)


 at com.im.ijcs.impl.query.Executor.executeQuery(Executor.java:255)


 at com.im.ijcs.impl.query.Executor.visitSqlQuery(Executor.java:214)


 at com.im.ijcs.impl.query.Executor.execute(Executor.java:141)


 at com.im.df.server.local.LocalQueryExecutor.access$001(LocalQueryExecutor.java:15)


 at com.im.df.server.local.LocalQueryExecutor$1.doInTransactionWithoutResult(LocalQueryExecutor.java:49)


 at org.springframework.transaction.support.TransactionCallbackWithoutResult.doInTransaction(TransactionCallbackWithoutResult.java:33)


 at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:128)


 at com.im.df.server.local.LocalQueryExecutor.execute(LocalQueryExecutor.java:47)


 at com.im.ijcs.impl.query.DefaultQueryService.findIDs(DefaultQueryService.java:33)


 at com.im.df.impl.db.DBSchemaManager.queryForIds(DBSchemaManager.java:783)


 at com.im.df.impl.db.DBEntityDataProviderImpl.queryForIds(DBEntityDataProviderImpl.java:127)


 at com.im.df.impl.db.ResultSetImpl.applyQueryInternal(ResultSetImpl.java:191)


 at com.im.df.impl.db.ResultSetImpl.applyWorkingQuery(ResultSetImpl.java:145)


 at com.im.ijc.core.query.exec.ExecutableQueryPanel$2.phase1InRequestProcessor(ExecutableQueryPanel.java:232)


 at com.im.df.api.util.BackgroundRunner.processTheTask(BackgroundRunner.java:127)


 at com.im.df.api.util.BackgroundRunner$1.run(BackgroundRunner.java:91)


 at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:561)


[catch] at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:986)

Thanks for the report. I can reproduce this.


It is caused by the MySQL JDBC driver being less tolerant in the way that dates are specified.


We will fix this.





Tim

thanks for the speedy confirmation Tim. It is nice to have the error externally verified.





We can work around the issue until it is fixed in a future release.





mc

This problem has now been fixed in the IJC 2.5 release.

Tim