Re: JDBC based idempotent repository problem
Thank you! :-) Sent from my iPhone On 9 Jul 2014, at 20:42, Christian Mueller [via Camel] ml-node+s465427n575361...@n5.nabble.com wrote: Done. Should be online in a few minutes. Best, Christian - Software Integration Specialist Apache Member V.P. Apache Camel | Apache Camel PMC Member | Apache Camel committer Apache Incubator PMC Member https://www.linkedin.com/pub/christian-mueller/11/551/642 On Tue, Jul 8, 2014 at 3:10 PM, Chris Melikian [hidden email] wrote: Is it possible to update the SQL component documentation to reflect the different datatype required for SQL Server? -- View this message in context: http://camel.465427.n5.nabble.com/JDBC-based-idempotent-repository-problem-tp4980094p5753546.html Sent from the Camel - Users mailing list archive at Nabble.com. If you reply to this email, your message will be added to the discussion below: http://camel.465427.n5.nabble.com/JDBC-based-idempotent-repository-problem-tp4980094p5753613.html To unsubscribe from JDBC based idempotent repository problem, click here. NAML -- View this message in context: http://camel.465427.n5.nabble.com/JDBC-based-idempotent-repository-problem-tp4980094p5753631.html Sent from the Camel - Users mailing list archive at Nabble.com.
Re: JDBC based idempotent repository problem
Done. Should be online in a few minutes. Best, Christian - Software Integration Specialist Apache Member V.P. Apache Camel | Apache Camel PMC Member | Apache Camel committer Apache Incubator PMC Member https://www.linkedin.com/pub/christian-mueller/11/551/642 On Tue, Jul 8, 2014 at 3:10 PM, Chris Melikian ch...@melikian.net wrote: Is it possible to update the SQL component documentation to reflect the different datatype required for SQL Server? -- View this message in context: http://camel.465427.n5.nabble.com/JDBC-based-idempotent-repository-problem-tp4980094p5753546.html Sent from the Camel - Users mailing list archive at Nabble.com.
Re: JDBC based idempotent repository problem
Is it possible to update the SQL component documentation to reflect the different datatype required for SQL Server? -- View this message in context: http://camel.465427.n5.nabble.com/JDBC-based-idempotent-repository-problem-tp4980094p5753546.html Sent from the Camel - Users mailing list archive at Nabble.com.
Re: JDBC based idempotent repository problem
See [1]. You have to use datetime or smalldatetime. And don't overlook the note: The SQL Server timestamp type is a fixed-length binary-string type. It does not map to any of the JDBC time types: DATE, TIME, or TIMESTAMP. [1] http://msdn.microsoft.com/en-us/library/ms378715%28v=sql.90%29.aspx Best, Christian
Re: JDBC based idempotent repository problem
Thanks for reply, Christian Mueller. I am using Camel 2.8.0 Simple test with jdbcTemplate threw the same exception. As a temporary remedy I simply have changed createdAt field type to varchar in SQL DB. For now route works fine. I think it proves your opinion that SQL Server built in Timestamp and javax.sql.Timestamp differ and don't suit one another. I will try to find the key in documentations. -- View this message in context: http://camel.465427.n5.nabble.com/JDBC-based-idempotent-repository-problem-tp4980094p4993500.html Sent from the Camel - Users mailing list archive at Nabble.com.
Re: JDBC based idempotent repository problem
Hello Yaroslav! The column createdAt was introduced in Camel 2.8.0. Do you use Camel 2.8.0 or a newer version? I'm not familiar with Micosoft SQLServer, but the problem looks related to the createdAt column. What we do is something like this: String INSERT_STRING = INSERT INTO CAMEL_MESSAGEPROCESSED (processorName, messageId, createdAt) VALUES (?, ?, ?); String processorName = test; String messageId = 1; jdbcTemplate.update(INSERT_STRING, processorName, messageId, new Timestamp(System.currentTimeMillis())); Could you please test whether you get the same exception with a simple test case which use this code snippets? Could you please also check the Micosoft SQLServer documentation whether they support the Timestamp type (I guess, because you could create the column) and which java type you have to use? It looks like Microsoft doesn't like the javax.sql.Timestamp type... :-( Best, Christian
JDBC based idempotent repository problem
greetings ! i am kind of newbie in camel, and cant solve one problem with the EIP of idempotent consumer. I tried to configure a simple route to filter out duplicate messages using MessageID header. With InmemoryMessageIdRepository it works just fine, but then i created CAMEL_MESSAGEPROCESSED table in my DB and tried to use JDBC based repo i am stuck, because i get one error again and again. at first sight it seems like method *add(final String messageId)* from *JdbcMessageIdRepository class* (http://camel.apache.org/maven/current/camel-sql/apidocs/src-html/org/apache/camel/processor/idempotent/jdbc/JdbcMessageIdRepository.html) simply does not replace ?,?,? mask with variables and as a result INSERT_STRING contains ? instead of values ...or problem with TIMESTAMP type ... i dont know. :) Here is an error: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [*INSERT INTO CAMEL_MESSAGEPROCESSED (processorName, messageId, createdAt) VALUES (?, ?, ?)*]; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column. at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:98)[spring-jdbc-3.0.5.RELEASE.jar:3.0.5.RELEASE] at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)[spring-jdbc-3.0.5.RELEASE.jar:3.0.5.RELEASE] at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)[spring-jdbc-3.0.5.RELEASE.jar:3.0.5.RELEASE] at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)[spring-jdbc-3.0.5.RELEASE.jar:3.0.5.RELEASE] at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:602)[spring-jdbc-3.0.5.RELEASE.jar:3.0.5.RELEASE] ... Here is my camel-context.xml with primitive route, where only the first message should be processed: beans xmlns=http://www.springframework.org/schema/beans; xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance; xsi:schemaLocation= http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://camel.apache.org/schema/spring http://camel.apache.org/schema/spring/camel-spring.xsd; bean id=messageIdDataSource class=org.springframework.jdbc.datasource.SingleConnectionDataSource property name=driverClassName value=com.microsoft.sqlserver.jdbc.SQLServerDriver / property name=url value=jdbc:sqlserver://localhost:1433;DatabaseName=camelTest / property name=username value=camel / property name=password value=Camel1234 / property name=suppressClose value=true / /bean bean id=messageIdRepository class=org.apache.camel.processor.idempotent.jdbc.JdbcMessageIdRepository constructor-arg ref=messageIdDataSource / constructor-arg value=myProcessorName / /bean camelContext xmlns=http://camel.apache.org/schema/spring; packagesimpleRoute/package route from uri=timer://foo?fixedRate=trueamp;period=3000/ setHeader headerName=messageId constantnotUniqueId/constant /setHeader idempotentConsumer messageIdRepositoryRef=messageIdRepository headermessageId/header to uri=seda:audit / /idempotentConsumer /route /camelContext Route from seda:audit implemented in java DSL: from(seda:audit) .process(new Processor() { public void process(Exchange exchange) throws Exception { System.out.println(Message processed !); } }); Simple as it can be, but doesn't work :( Any suggestions, why my repository can't insert ID's in DB. PS SQL Server use is inevitable (project term) -- View this message in context: http://camel.465427.n5.nabble.com/JDBC-based-idempotent-repository-problem-tp4980094p4980094.html Sent from the Camel - Users mailing list archive at Nabble.com.
Re: JDBC based idempotent repository problem
Hi How is the table created? What SQL did you use to create the table? Especially what type have you use to define the createdAt column? On Thu, Nov 10, 2011 at 6:51 AM, Yaroslav1988 rosi...@mail.ru wrote: greetings ! i am kind of newbie in camel, and cant solve one problem with the EIP of idempotent consumer. I tried to configure a simple route to filter out duplicate messages using MessageID header. With InmemoryMessageIdRepository it works just fine, but then i created CAMEL_MESSAGEPROCESSED table in my DB and tried to use JDBC based repo i am stuck, because i get one error again and again. at first sight it seems like method *add(final String messageId)* from *JdbcMessageIdRepository class* (http://camel.apache.org/maven/current/camel-sql/apidocs/src-html/org/apache/camel/processor/idempotent/jdbc/JdbcMessageIdRepository.html) simply does not replace ?,?,? mask with variables and as a result INSERT_STRING contains ? instead of values ...or problem with TIMESTAMP type ... i dont know. :) Here is an error: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [*INSERT INTO CAMEL_MESSAGEPROCESSED (processorName, messageId, createdAt) VALUES (?, ?, ?)*]; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column. at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:98)[spring-jdbc-3.0.5.RELEASE.jar:3.0.5.RELEASE] at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)[spring-jdbc-3.0.5.RELEASE.jar:3.0.5.RELEASE] at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)[spring-jdbc-3.0.5.RELEASE.jar:3.0.5.RELEASE] at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)[spring-jdbc-3.0.5.RELEASE.jar:3.0.5.RELEASE] at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:602)[spring-jdbc-3.0.5.RELEASE.jar:3.0.5.RELEASE] ... Here is my camel-context.xml with primitive route, where only the first message should be processed: beans xmlns=http://www.springframework.org/schema/beans; xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance; xsi:schemaLocation= http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://camel.apache.org/schema/spring http://camel.apache.org/schema/spring/camel-spring.xsd; bean id=messageIdDataSource class=org.springframework.jdbc.datasource.SingleConnectionDataSource property name=driverClassName value=com.microsoft.sqlserver.jdbc.SQLServerDriver / property name=url value=jdbc:sqlserver://localhost:1433;DatabaseName=camelTest / property name=username value=camel / property name=password value=Camel1234 / property name=suppressClose value=true / /bean bean id=messageIdRepository class=org.apache.camel.processor.idempotent.jdbc.JdbcMessageIdRepository constructor-arg ref=messageIdDataSource / constructor-arg value=myProcessorName / /bean camelContext xmlns=http://camel.apache.org/schema/spring; packagesimpleRoute/package route from uri=timer://foo?fixedRate=trueamp;period=3000/ setHeader headerName=messageId constantnotUniqueId/constant /setHeader idempotentConsumer messageIdRepositoryRef=messageIdRepository headermessageId/header to uri=seda:audit / /idempotentConsumer /route /camelContext Route from seda:audit implemented in java DSL: from(seda:audit) .process(new Processor() { public void process(Exchange exchange) throws Exception { System.out.println(Message processed !); } }); Simple as it can be, but doesn't work :( Any suggestions, why my repository can't insert ID's in DB. PS SQL Server use is inevitable (project term) -- View this message in context: http://camel.465427.n5.nabble.com/JDBC-based-idempotent-repository-problem-tp4980094p4980094.html Sent from the Camel - Users mailing list archive at Nabble.com. -- Claus Ibsen - FuseSource Email: cib...@fusesource.com Web: http://fusesource.com Twitter: davsclaus, fusenews Blog: http://davsclaus.blogspot.com/ Author of Camel in Action: http://www.manning.com/ibsen/
Re: JDBC based idempotent repository problem
Thanks for reply, CLaus Ibsen-2 ! for testing reasons i have created CAMEL_MESSAGEPROCESSED manually.. i mean via Microsoft SQL Server Management Studio, so i didn't use SQL query to create the table yet. is this allowed? in case of SQL Management Studio my table looks like this: processorName varchar(250) (not allow nulls) messageId varchar(100) (not allow nulls) createdAt timestamp (allow nulls) processorName + messageId = primary key i am oriented on tips from here http://camel.apache.org/sql-component.html should i try to create the table using SQL ? PS by the way excuse my english, not my native -- View this message in context: http://camel.465427.n5.nabble.com/JDBC-based-idempotent-repository-problem-tp4980094p4980267.html Sent from the Camel - Users mailing list archive at Nabble.com.