I am trying to use the SQL Change Tracking Origin to Create data ingestion pipeline. I have connected the origin and specified all the neccessary jdbc parameters and the pipeline validates successfully. However on running the pipeline, I get the following exception in the logs
java.util.concurrent.ExecutionException: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '13'.
at com.google.common.util.concurrent.AbstractFuture$Sync.getValue(AbstractFuture.java:299)
at com.google.common.util.concurrent.AbstractFuture$Sync.get(AbstractFuture.java:286)
at com.google.common.util.concurrent.AbstractFuture.get(AbstractFuture.java:116)
at com.google.common.util.concurrent.Uninterruptibles.getUninterruptibly(Uninterruptibles.java:137)
at com.google.common.cache.LocalCache$Segment.getAndRecordStats(LocalCache.java:2348)
at com.google.common.cache.LocalCache$Segment.loadSync(LocalCache.java:2320)
at com.google.common.cache.LocalCache$Segment.lockedGetOrLoad(LocalCache.java:2282)
at com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2197)
at com.google.common.cache.LocalCache.get(LocalCache.java:3937)
at com.google.common.cache.LocalCache.getOrLoad(LocalCache.java:3941)
at com.google.common.cache.LocalCache$LocalLoadingCache.get(LocalCache.java:4824)
at com.streamsets.pipeline.lib.jdbc.multithread.JdbcBaseRunnable.getOrLoadTableReadContext(JdbcBaseRunnable.java:432)
at com.streamsets.pipeline.lib.jdbc.multithread.JdbcBaseRunnable.generateBatchAndCommitOffset(JdbcBaseRunnable.java:217)
at com.streamsets.pipeline.lib.jdbc.multithread.JdbcBaseRunnable.run(JdbcBaseRunnable.java:143)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at com.streamsets.pipeline.lib.executor.SafeScheduledExecutorService$SafeCallable.lambda$call$0(SafeScheduledExecutorService.java:227)
at com.streamsets.datacollector.security.GroupsInScope.execute(GroupsInScope.java:33)
at com.streamsets.pipeline.lib.executor.SafeScheduledExecutorService$SafeCallable.call(SafeScheduledExecutorService.java:223)
at com.streamsets.pipeline.lib.executor.SafeScheduledExecutorService$SafeRunnable.run(SafeScheduledExecutorService.java:189)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at com.streamsets.pipeline.lib.executor.SafeScheduledExecutorService$SafeCallable.lambda$call$0(SafeScheduledExecutorService.java:227)
at com.streamsets.datacollector.security.GroupsInScope.execute(GroupsInScope.java:33)
at com.streamsets.pipeline.lib.executor.SafeScheduledExecutorService$SafeCallable.call(SafeScheduledExecutorService.java:223)
at com.streamsets.pipeline.lib.executor.SafeScheduledExecutorService$SafeRunnable.run(SafeScheduledExecutorService.java:189)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '13'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1635)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:426)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:372)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:6276)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1794)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:184)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:159)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:284)
at com.zaxxer.hikari.proxy.PreparedStatementProxy.executeQuery(PreparedStatementProxy.java:52)
at com.zaxxer.hikari.proxy.HikariPreparedStatementProxy.executeQuery(HikariPreparedStatementProxy.java)
at com.streamsets.pipeline.lib.jdbc.multithread.TableReadContext.<init>(TableReadContext.java:66)
at com.streamsets.pipeline.lib.jdbc.multithread.TableReadContext.<init>(TableReadContext.java:50)
at com.streamsets.pipeline.lib.jdbc.multithread.cache.SQLServerCTContextLoader.load(SQLServerCTContextLoader.java:73)
at com.streamsets.pipeline.lib.jdbc.multithread.cache.SQLServerCTContextLoader.load(SQLServerCTContextLoader.java:32)
at com.google.common.cache.LocalCache$LoadingValueReference.loadFuture(LocalCache.java:3527)
at com.google.common.cache.LocalCache$Segment.loadSync(LocalCache.java:2319)
... 29 more
Under closer observation, I saw that the query being sent to SQL Server was wrong
SELECT TOP 1000 * FROM CHANGETABLE(CHANGES dbo.tbl_transactions, 612546687) AS CT
WHERE (CT.transaction_id > 796867843
AND CT.req_datetime > 2018-06-07 13:44:29.707
AND (CT.SYS_CHANGE_VERSION = 612546771 ) OR (CT.SYS_CHANGE_VERSION > 612546771 ) )
ORDER BY SYS_CHANGE_VERSION, CT.transaction_id, CT.req_datetime TableReadContext
as against
SELECT TOP 1000 * FROM CHANGETABLE(CHANGES dbo.tbl_transactions, 612546687) AS CT
WHERE (CT.transaction_id > 796867843
AND CT.req_datetime > '2018-06-07 13:44:29.707'
AND (CT.SYS_CHANGE_VERSION = 612546771 ) OR (CT.SYS_CHANGE_VERSION > 612546771 ) )
ORDER BY SYS_CHANGE_VERSION, CT.transaction_id, CT.req_datetime TableReadContext
The problem being that the parameter is not quoted. Please can Anyone Direct me on what I am doing wrong or how i can mitigate against this behaviour
