Technical Support Forum Index
Technical Support Forum
Access ChemAxon scientists and developers here. For registration and login issues contact website support.

Support Ticket System is replacing forum

This forum was converted into a searchable archive. You cannot add posts here any more. For support please use our new Ticket System.

Create your first ticket
IJC date MySQL issue.
To watch this topic for replies  Register (enables digests) or give email address:
This topic is locked: you cannot edit posts or make replies.
Display posts from previous:   
    View previous topic :: View next topic    
Author Message
Marc

Joined: 03 Jun 2005
Posts: 49

View user's profile

Back to top
Link to postPosted: Tue Mar 17, 2009 7:55 amPost subject: IJC date MySQL issue. Reply with quote

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)
Tim
ChemAxon personnel
Joined: 05 Oct 2004
Posts: 1703

View user's profile

Back to top
Link to postPosted: Tue Mar 17, 2009 10:35 amPost subject: Reply with quote

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
Marc

Joined: 03 Jun 2005
Posts: 49

View user's profile

Back to top
Link to postPosted: Tue Mar 17, 2009 10:59 amPost subject: Reply with quote

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
Tim
ChemAxon personnel
Joined: 05 Oct 2004
Posts: 1703

View user's profile

Back to top
Link to postPosted: Fri May 15, 2009 9:51 amPost subject: Reply with quote

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

Tim

This topic is locked: you cannot edit posts or make replies.
Page 1 of 1


To watch this topic for replies   Register (enables digests) or give email address  
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum
You can download files in this forum