Sorting Error

User 8ad6931a80

24-10-2013 20:19:35

 

Hi.


I have promoted an Oracle view to a structure entity in IJC but I have a problem applying a sort to any column after running a query. See error below:


org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT DISTINCT EV_KINASES.SUMMARY."IJC_JCX_ROWID", EV_KINASES.SUMMARY."COMPOUND_ID" FROM EV_KINASES.SUMMARY WHERE EV_KINASES.SUMMARY."IJC_JCX_ROWID" IN (AAAmx0AAEAABl4TAAA, AAAmx0AAVAAAlvZAAA, AAAmx0AAWAAAqHPAAA, AAAmx0AAEAABl4aAAA, AAAmx0AAVAAAlvyAAA, AAAmx0AAVAAAlvzAAA, AAAmx0AAWAAAqHbAAA, AAAmx0AAWAAAqHcAAA, AAAmx0AAEAABl4sAAB, AAAmx0AAEAABl4rAAB, AAAmx0AAEAABl4uAAB, AAAmx0AAVAAAlvQAAA, AAAmx0AAEAABlvnAAB, AAAmx0AAEAABlviAAB, AAAmx0AAWAAAqHIAAA, AAAmx0AAEAABluMAAQ, AAAmx0AAEAABluMAAR, AAAmx0AAEAABl4SAAA, AAAmx0AAEAABl4YAAA, AAAmx0AAEAABl4cAAA, AAAmx0AAVAAAlv3AAB, AAAmx0AAEAABl4tAAA, AAAmx0AAVAAAnj7AAB, AAAmx0AAVAAAnj9AAB, AAAmx0AAEAABluOAAA, AAAmx0AAWAAAqG9AAB, AAAmx0AAEAABlvoAAB, AAAmx0AAWAAAqHHAAB, AAAmx0AAEAABluMAAJ, AAAmx0AAVAAAnj6AAA, AAAmx0AAEAABluMAAX, AAAmx0AARAAAkr+AAB, AAAmx0AAVAAAlvfAAA, AAAmx0AAWAAAqHOAAA, AAAmx0AAEAABl4cAAC, AAAmx0AAVAAAlv3AAA, AAAmx0AAEAABl4gAAB, AAAmx0AAVAAAlv0AAB, AAAmx0AAWAAAqHZAAB, AAAmx0AAEAABluMAAI, AAAmx0AAEAABluQAAB, AAAmx0AAEAABluPAAA, AAAmx0AAVAAAlvTAAA, AAAmx0AAWAAAqHBAAA, AAAmx0AAEAABluMAAY, AAAmx0AAEAABluMAAN, AAAmx0AAEAABluMAAH, AAAmx0AAEAABluMAAb, AAAmx0AAEAABluMAAl, AAAmx0AAEAABluMAAM, AAAmx0AAWAAAqHdAAA, AAAmx0AAEAABl4rAAA, AAAmx0AAEAABl4sAAA, AAAmx0AAVAAAnj7AAA, AAAmx0AAEAABluMAAd, AAAmx0AAEAABluMAAn, AAAmx0AAVAAAnj8AAA, AAAmx0AAEAABluMAAE, AAAmx0AAVAAAlvNAAA, AAAmx0AAVAAAlvMAAB, AAAmx0AAVAAAlvKAAB, AAAmx0AAWAAAqHAAAB, AAAmx0AAEAABlvhAAB, AAAmx0AAWAAAqHIAAB, AAAmx0AAEAABluMAAP, AAAmx0AAEAABluMAAL, AAAmx0AAEAABl4UAAA, AAAmx0AAEAABl4fAAA, AAAmx0AAVAAAlvzAAB, AAAmx0AAVAAAlv0AAA, AAAmx0AAVAAAlv1AAB, AAAmx0AAWAAAqHaAAA, AAAmx0AAWAAAqHeAAA, AAAmx0AAEAABluMAAF, AAAmx0AAEAABl4qAAB, AAAmx0AAEAABluMAAf, AAAmx0AAEAABluMAAB, AAAmx0AAEAABluMAAK, AAAmx0AAEAABluPAAB, AAAmx0AAWAAAqG8AAB, AAAmx0AAVAAAlvXAAB, AAAmx0AAVAAAlvVAAA, AAAmx0AAEAABluMAAV, AAAmx0AAEAABluMAAZ, AAAmx0AAEAABluMAAD, AAAmx0AAVAAAnj6AAB, AAAmx0AAEAABluMAAa, AAAmx0AAEAABl4vAAB, AAAmx0AAEAABluMAAG, AAAmx0AAVAAAnj+AAA, AAAmx0AAEAABluMAAm, AAAmx0AAEAABluNAAA, AAAmx0AAVAAAlvOAAA, AAAmx0AAVAAAlvPAAA, AAAmx0AAWAAAqHAAAA, AAAmx0AAEAABlvlAAA, AAAmx0AAEAABlviAAA, AAAmx0AAEAABlvhAAA, AAAmx0AAWAAAqHBAAB, AAAmx0AAWAAAqHGAAA, AAAmx0AAEAABluMAAj, AAAmx0AAEAABluMAAO, AAAmx0AAEAABluMAAU, AAAmx0AAVAAAlveAAB, AAAmx0AAVAAAlvyAAB, AAAmx0AAVAAAlv4AAA, AAAmx0AAWAAAqHfAAA, AAAmx0AAWAAAqHgAAA, AAAmx0AAEAABl4uAAA, AAAmx0AAVAAAnj9AAA, AAAmx0AAEAABluMAAe, AAAmx0AAEAABluMAAA, AAAmx0AAVAAAlvNAAB, AAAmx0AAVAAAlvKAAA, AAAmx0AAWAAAqHCAAA, AAAmx0AAVAAAlvVAAB, AAAmx0AAEAABluMAAW, AAAmx0AAEAABluMAAT, AAAmx0AAEAABl4WAAA, AAAmx0AAVAAAlvaAAA, AAAmx0AAEAABl4eAAA, AAAmx0AAEAABl4fAAB, AAAmx0AAVAAAlv2AAB, AAAmx0AAWAAAqHZAAA, AAAmx0AAEAABl4vAAA, AAAmx0AAEAABl4tAAB, AAAmx0AARAAAkr8AAB, AAAmx0AAVAAAnj+AAB, AAAmx0AAVAAAnj8AAB, AAAmx0AAVAAAlvOAAB, AAAmx0AAWAAAqG7AAA, AAAmx0AAEAABlvmAAA, AAAmx0AAWAAAqHGAAB, AAAmx0AAWAAAqHHAAA, AAAmx0AAEAABluMAAC, AAAmx0AAEAABluMAAS, AAAmx0AAEAABluMAAc) ORDER BY EV_KINASES.SUMMARY."COMPOUND_ID" ASC]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00904: "AAAMX0AAEAABLUMAAC": invalid identifier


at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:233)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:602)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:636)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:661)
at com.im.ijcs.impl.query.Executor.executeQuery(Executor.java:299)
at com.im.ijcs.impl.query.Executor.visitSqlQuery(Executor.java:244)
at com.im.ijcs.impl.query.Executor.execute(Executor.java:165)
at com.im.df.server.local.LocalQueryExecutor.access$001(LocalQueryExecutor.java:26)
at com.im.df.server.local.LocalQueryExecutor$1.doInTransactionWithoutResult(LocalQueryExecutor.java:60)
at org.springframework.transaction.support.TransactionCallbackWithoutResult.doInTransaction(TransactionCallbackWithoutResult.java:33)
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
at com.im.df.server.local.LocalQueryExecutor.execute(LocalQueryExecutor.java:58)
at com.im.ijcs.impl.query.DefaultQueryService.findImpl(DefaultQueryService.java:138)
at com.im.ijcs.impl.query.DefaultQueryService$FindExecutor.call(DefaultQueryService.java:117)
at com.im.ijcs.impl.query.DefaultQueryService$FindExecutor.call(DefaultQueryService.java:101)
at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source)
at java.util.concurrent.FutureTask.run(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "AAAMX0AAEAABLUMAAC": invalid identifier


at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:791)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:866)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3431)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:643)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:586)
... 18 more


Please can you advise and suggest a fix


IJC version 5.9.1


Oracle 10g


Thanks,


Mike


ChemAxon 2bdd02d1e5

29-10-2013 12:42:20

Hi Mike,


this is a new issue for us and I was unable to replicate. Could you provide us more info about how the view is defined and if there are some other non-default settings set up (e.g. usehextoRaw() Oracle RAW settings)?


Thanks,


Filip 

User 8ad6931a80

31-10-2013 14:12:23

Hi Filip.


 


I have e-mailed the details of the view and how its set up in IJC to you (support e-mail address).  Please can you advise?


Let me know if you need anymore info


Thanks,


 


Mike.

ChemAxon 2bdd02d1e5

05-11-2013 15:35:30

Hi Mike,


nothing came to me. Could you please resend it to ijc-support(-at-)chemaxon.com


Thanks a lot.


Filip