informix source with timestamp column doesn't work

[dboyce]

Using timestamp mode with an Informix database doesn't work

source configuration:

name: my_connector
config:
    'connector.class': io.confluent.connect.jdbc.JdbcSourceConnector
    'tasks.max': 1
    'key.converter': org.apache.kafka.connect.json.JsonConverter
    'value.converter': org.apache.kafka.connect.json.JsonConverter
    'connection.url': '...'
    'mode': timestamp
    'timestamp.column.name': create_stamp
    'topic.prefix': 'my_topic'
    'query': |
        SELECT  ...

error [2018-01-09 09:44:19,083] ERROR Failed to get current time from DB using query select CURRENT_TIMESTAMP; on database Informix Dynamic Server (io.confluent.connect.jdbc.util.JdbcUtils) java.sql.SQLSyntaxErrorException: A syntax error has occurred. at com.informix.util.IfxErrMsg.buildException(IfxErrMsg.java:465) at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:443) at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:394) at com.informix.jdbc.IfxSqli.addException(IfxSqli.java:3191) at com.informix.jdbc.IfxSqli.receiveError(IfxSqli.java:3472) at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2379) at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2304) at com.informix.jdbc.IfxSqli.executeStatementQuery(IfxSqli.java:1522) at com.informix.jdbc.IfxSqli.executeStatementQuery(IfxSqli.java:1502) at com.informix.jdbc.IfxResultSet.executeQuery(IfxResultSet.java:218) at com.informix.jdbc.IfxStatement.executeQueryImpl(IfxStatement.java:1054) at com.informix.jdbc.IfxStatement.executeQuery(IfxStatement.java:227) at io.confluent.connect.jdbc.util.JdbcUtils.getCurrentTimeOnDB(JdbcUtils.java:254) at io.confluent.connect.jdbc.source.TimestampIncrementingTableQuerier.executeQuery(TimestampIncrementingTableQuerier.java:190) at io.confluent.connect.jdbc.source.TableQuerier.maybeStartQuery(TableQuerier.java:84) at io.confluent.connect.jdbc.source.TimestampIncrementingTableQuerier.maybeStartQuery(TimestampIncrementingTableQuerier.java:55) at io.confluent.connect.jdbc.source.JdbcSourceTask.poll(JdbcSourceTask.java:225) at org.apache.kafka.connect.runtime.WorkerSourceTask.execute(WorkerSourceTask.java:179) at org.apache.kafka.connect.runtime.WorkerTask.doRun(WorkerTask.java:170) at org.apache.kafka.connect.runtime.WorkerTask.run(WorkerTask.java:214) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) Caused by: java.sql.SQLException at com.informix.util.IfxErrMsg.buildException(IfxErrMsg.java:474) at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:443) at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:394) at com.informix.jdbc.IfxSqli.receiveError(IfxSqli.java:3477)

[raghav1093]

Hi @dboyce were you able to resolve this issue?if so can you please share what should be done? I am facing the same issue with informix source right now and I'm stranded with no solution.

[vigneshtvp]

Hi @dboyce, I have fixed this issue and tested in my local. I have created new dialect for this Issue. Please let me know, whether i have to be a contributor to add this features

[vigneshtvp]

Hi @dboyce , @rhauch,@stheppi, I have a fix for this please do let me know whether i need to contributor to add this feature.

[rexitaanthuraj]

Hi @vigneshtvp - please share the resolution ? I'm having similar issue while connecting to informix DB using the JDBC source connector .

[mrabbah]

I have the same issue please can you share the solution I have done some investigation, in line 251: https://github.com/confluentinc/kafka-connect-jdbc/blob/748f500ac89b417a1118fd05ca232b076db106a6/src/main/java/io/confluent/connect/jdbc/util/JdbcUtils.java the query is "select CURRENT_TIMESTAMP" but the correct query in informix is "select CURRENT TIMESTAMP;"

[dboyce]

As I recall, we forked this connector and patched it to support Informix - as you have noted, it's a simple fix. It was a long time ago and I no longer work at the same company but AFAIK this is the approach they still take.