How to handle Null pointer exception in JDBC Query Consumer
I am using a JDBC Query Consumer to query a table in a DB. The output of JDBC Query Consumer is then sent to Kafka Producer. Only insert operations (in RDBMS) are done on the table that I am querying and insert are done almost on a daily basis. The workflow works fine until all the records have been consumed. Once, there are no new records to the fetched, the workflow errors out with "Null pointer Exception". I understand, this is an intended behavior, but is there any way to handle the Null Pointer Exception?
My expectation from the workflow is that, it should wait till new records arrive in the DB Table and then it should fetch the new records as they arrive. And it has to wait for the next batch of records to arrive with out throwing the exception.
I appreciate your help. Thanks!
Note: ORA-01830 appears just before Null Pointer Exception. Before Null Pointer Exception occurred, it ran successfully for 5 hours without any date format error
SDC version 3.3.0
SQL Query Used:
SELECT gir.* FROM GL.GL_IMPORT_REFERENCES gir WHERE gir.LAST_UPDATE_DATE > TO_DATE('${OFFSET}','YYYY-MM-DD HH:MI:SS') ORDER BY gir.LAST_UPDATE_DATE
Initial Offset:
2018-07-15 12:00:00
Offset Column:
LAST_UPDATE_DATE
Log:
Error Code: 1830
Message: ORA-01830: date format picture ends before converting entire input string
java.sql.SQLDataException: ORA-01830: date format picture ends before converting entire input string
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:210)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:30)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:762)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1111)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1309)
at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:422)
at com.zaxxer.hikari.proxy.StatementProxy.executeQuery(StatementProxy.java:110)
at com.zaxxer.hikari.proxy.HikariStatementProxy.executeQuery(HikariStatementProxy.java)
at com.streamsets.pipeline.stage.origin.jdbc.JdbcSource.produce(JdbcSource.java:387)
at com.streamsets.pipeline.api.base.configurablestage.DSource.produce(DSource.java:38)
at com.streamsets.datacollector.runner.StageRuntime.lambda$execute$2(StageRuntime.java:242)
at com.streamsets.pipeline.api.impl.CreateByRef.call(CreateByRef.java:40)
at com.streamsets.datacollector.runner.StageRuntime.execute(StageRuntime.java:195)
at com.streamsets.datacollector.runner.StageRuntime.execute(StageRuntime.java:257)
at com.streamsets.datacollector.runner.StagePipe.process(StagePipe.java:219)
at com.streamsets.datacollector.execution.runner.common.ProductionPipelineRunner.processPipe(ProductionPipelineRunner.java:801)
at com.streamsets.datacollector.execution.runner.common.ProductionPipelineRunner.runPollSource(ProductionPipelineRunner.java:548)
at com.streamsets.datacollector.execution.runner.common.ProductionPipelineRunner.run(ProductionPipelineRunner.java:383)
at com.streamsets.datacollector.runner.Pipeline.run(Pipeline.java:512)
at com.streamsets.datacollector.execution.runner ...
Please edit your answer to include the full stack trace from sdc.log including any 'caused by' sections after the first trace. Also, what version of Data Collector are you running?
Do you set an initial offset value?
metadaddy, I have added the log.
Dean Han, Yes I have set an initial offset value.
I think the problem is where when finishing reading and set the last LAST_UPDATE_DATE to offset. Because LAST_UPDATE_DATE is a DATE type, then offset is set with DATE type as well, so there is no need to convert again, please try to just use WHERE gir.LAST_UPDATE_DATE > ${offset}