[
https://issues.apache.org/jira/browse/CAMEL-3936?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13028396#comment-13028396
]
Christian Müller commented on CAMEL-3936:
-----------------------------------------
{code}
SELECT COUNT(*) FROM....
{code}
also works for:
- MySQL 5.1
- Oracle 10g
It looks like it's the right query:
http://www.w3schools.com/SQL/sql_func_count.asp
I will change it
> JdbcAggregationRepository count (*) is not working with MySQL 5.0
> -----------------------------------------------------------------
>
> Key: CAMEL-3936
> URL: https://issues.apache.org/jira/browse/CAMEL-3936
> Project: Camel
> Issue Type: Bug
> Components: camel-sql
> Affects Versions: 2.7.1
> Environment: MySQL 5.0 (but I suspect the bug appears with all 5.X
> versions...)
> Reporter: Baptiste Gaillard
> Assignee: Christian Müller
> Labels: Aggregation, Camel, JDBC, MySQL, Repository
>
> The JdbcAggregationRepository makes a {noformat} 'SELECT COUNT (*)
> FROM....'{noformat} SQL request at line 131 in the source code.
> This appears to be incorrect with MySQL because theire is a space between
> COUNT and (.
> By replacing the request with {noformat} 'SELECT COUNT(*) FROM....'{noformat}
> (without the white space) the bug is corrected for MySQL (I think it should
> be tested with other database without the space).
> This request can be tested with Mysql Query Browser (the returned error is
> the same as the one encountered when we execute the JdbcAggregationRepository
> with Camel).
> The exception returned is the following:
> {noformat} ERROR DefaultErrorHandler - Failed delivery for exchangeId:
> ID:COE-WRKST2-1749-1304443569312-2:2:1:88:1. Exhausted after delivery
> attempt: 1 caught: org.springframework.jdbc.BadSqlGrammarException:
> PreparedStatementCallback; bad SQL grammar [SELECT COUNT (*) FROM
> aggregation_repo_0 WHERE id = ?]; nested exception is
> com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error
> in your SQL syntax; check the manual that corresponds to your MySQL server
> version for the right syntax to use near '*) FROM aggregation_repo_0 WHERE id
> = '92'' at line 1
> org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback;
> bad SQL grammar [SELECT COUNT (*) FROM aggregation_repo_0 WHERE id = ?];
> nested exception is
> com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error
> in your SQL syntax; check the manual that corresponds to your MySQL server
> version for the right syntax to use near '*) FROM aggregation_repo_0 WHERE id
> = '92'' at line 1
> 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:665)
> at
> org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:673)
> at
> org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:728)
> at
> org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:744)
> at
> org.springframework.jdbc.core.JdbcTemplate.queryForInt(JdbcTemplate.java:775)
> at
> org.apache.camel.processor.aggregate.jdbc.JdbcAggregationRepository$1.doInTransaction(JdbcAggregationRepository.java:131)
> at
> org.apache.camel.processor.aggregate.jdbc.JdbcAggregationRepository$1.doInTransaction(JdbcAggregationRepository.java:114)
> at
> org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
> at
> org.apache.camel.processor.aggregate.jdbc.JdbcAggregationRepository.add(JdbcAggregationRepository.java:114)
> at
> org.apache.camel.processor.aggregate.AggregateProcessor.doAggregation(AggregateProcessor.java:251)
> at
> org.apache.camel.processor.aggregate.AggregateProcessor.process(AggregateProcessor.java:189)
> at
> org.apache.camel.impl.converter.AsyncProcessorTypeConverter$ProcessorToAsyncProcessorBridge.process(AsyncProcessorTypeConverter.java:50)
> at
> org.apache.camel.util.AsyncProcessorHelper.process(AsyncProcessorHelper.java:77)
> at
> org.apache.camel.processor.DelegateAsyncProcessor.processNext(DelegateAsyncProcessor.java:98)
> at
> org.apache.camel.processor.DelegateAsyncProcessor.process(DelegateAsyncProcessor.java:89)
> at
> org.apache.camel.management.InstrumentationProcessor.process(InstrumentationProcessor.java:68)
> at
> org.apache.camel.util.AsyncProcessorHelper.process(AsyncProcessorHelper.java:77)
> at
> org.apache.camel.processor.DelegateAsyncProcessor.processNext(DelegateAsyncProcessor.java:98)
> at
> org.apache.camel.processor.DelegateAsyncProcessor.process(DelegateAsyncProcessor.java:89)
> at
> org.apache.camel.processor.interceptor.TraceInterceptor.process(TraceInterceptor.java:99)
> at
> org.apache.camel.util.AsyncProcessorHelper.process(AsyncProcessorHelper.java:77)
> at
> org.apache.camel.processor.RedeliveryErrorHandler.processErrorHandler(RedeliveryErrorHandler.java:299)
> at
> org.apache.camel.processor.RedeliveryErrorHandler.process(RedeliveryErrorHandler.java:208)
> at
> org.apache.camel.processor.DefaultChannel.process(DefaultChannel.java:269)
> at
> org.apache.camel.processor.UnitOfWorkProcessor.process(UnitOfWorkProcessor.java:109)
> at
> org.apache.camel.util.AsyncProcessorHelper.process(AsyncProcessorHelper.java:77)
> at
> org.apache.camel.processor.DelegateAsyncProcessor.processNext(DelegateAsyncProcessor.java:98)
> at
> org.apache.camel.processor.DelegateAsyncProcessor.process(DelegateAsyncProcessor.java:89)
> at
> org.apache.camel.management.InstrumentationProcessor.process(InstrumentationProcessor.java:68)
> at
> org.apache.camel.util.AsyncProcessorHelper.process(AsyncProcessorHelper.java:103)
> at
> org.apache.camel.processor.DelegateAsyncProcessor.process(DelegateAsyncProcessor.java:85)
> at
> org.apache.camel.component.jms.EndpointMessageListener.onMessage(EndpointMessageListener.java:84)
> at
> org.springframework.jms.listener.AbstractMessageListenerContainer.doInvokeListener(AbstractMessageListenerContainer.java:560)
> at
> org.springframework.jms.listener.AbstractMessageListenerContainer.invokeListener(AbstractMessageListenerContainer.java:498)
> at
> org.springframework.jms.listener.AbstractMessageListenerContainer.doExecuteListener(AbstractMessageListenerContainer.java:467)
> at
> org.springframework.jms.listener.AbstractPollingMessageListenerContainer.doReceiveAndExecute(AbstractPollingMessageListenerContainer.java:325)
> at
> org.springframework.jms.listener.AbstractPollingMessageListenerContainer.receiveAndExecute(AbstractPollingMessageListenerContainer.java:263)
> at
> org.springframework.jms.listener.DefaultMessageListenerContainer$AsyncMessageListenerInvoker.invokeListener(DefaultMessageListenerContainer.java:1058)
> at
> org.springframework.jms.listener.DefaultMessageListenerContainer$AsyncMessageListenerInvoker.executeOngoingLoop(DefaultMessageListenerContainer.java:1050)
> at
> org.springframework.jms.listener.DefaultMessageListenerContainer$AsyncMessageListenerInvoker.run(DefaultMessageListenerContainer.java:947)
> at java.lang.Thread.run(Thread.java:662)
> Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You
> have an error in your SQL syntax; check the manual that corresponds to your
> MySQL server version for the right syntax to use near '*) FROM
> aggregation_repo_0 WHERE id = '92'' at line 1
> at sun.reflect.GeneratedConstructorAccessor83.newInstance(Unknown
> Source)
> at
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
> at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
> at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
> at com.mysql.jdbc.Util.getInstance(Util.java:384)
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3566)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3498)
> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
> at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568)
> at
> com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2113)
> at
> com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2275)
> 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)
> ... 42 more{noformat}
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
