setTimeStamp Method not implement Neo4j jdbc driver

[zakir_saifi]

All I am trying to import my sql database to neo4j database with pentaho kettle spoon. I am doing the following.

I am creating a table input and connecting my mysql database and then connecting table input with Execute Cypher Script . In there I am connecting Neo4j database as generic database. I am able to connect with both databases.

When I am importing timeStamp column obsdatatime I am getting the error

 Caused by: org.pentaho.di.core.exception.KettleDatabaseException: 
    2018/09/26 11:54:03 - Execute Cypher script.0 - offending row : [obs_id Integer(9)], [person_id Integer(9)], [concept_id Integer(9)], [obs_datetime Timestamp]
    2018/09/26 11:54:03 - Execute Cypher script.0 - 
    2018/09/26 11:54:03 - Execute Cypher script.0 - Timestamp : Unable to set value on prepared statement on index 4
    2018/09/26 11:54:03 - Execute Cypher script.0 - Method setTimestamp in class org.neo4j.jdbc.PreparedStatement is not yet implemented.

It say timestamp method not implement in neo4j jdbc driver. I am using neo4j jdbc driver version 3.1.0 .

Here are the screen shots regarding this.

Pentaho kettle spoon Error log

I have also asked at stackoverflow regarding this. StackOverflow Question

[michael_hunger]

Can you use the more recent 3.4.0 version which has date and time support?

GitHub

neo4j-contrib/neo4j-jdbc

Neo4j JDBC driver for Neo4j 3.x with BOLT protocol - neo4j-contrib/neo4j-jdbc

[zakir_saifi]

I am getting different error now after using the new jdbc driver.

2018/09/27 10:09:11 - WorkingNeo4JExportZak - Dispatching started for transformation [WorkingNeo4JExportZak]
2018/09/27 10:09:12 - Table input.0 - Finished reading query, closing connection.
2018/09/27 10:09:12 - Table input.0 - Finished processing (I=10, O=0, R=0, W=10, U=0, E=0)
2018/09/27 10:09:12 - Execute Cypher script.0 - ERROR (version 8.1.0.0-365, build 8.1.0.0-365 from 2018-04-30 09.42.24 by buildguy) : Unexpected error
2018/09/27 10:09:12 - Execute Cypher script.0 - ERROR (version 8.1.0.0-365, build 8.1.0.0-365 from 2018-04-30 09.42.24 by buildguy) : org.pentaho.di.core.exception.KettleStepException: 
2018/09/27 10:09:12 - Execute Cypher script.0 - Error while running this step!
2018/09/27 10:09:12 - Execute Cypher script.0 - 
2018/09/27 10:09:12 - Execute Cypher script.0 - Couldn't execute SQL: MERGE (n:TempObs { obsId: {1}, personId: {2}, conceptId: {3}, obsDateTime: toString({4}) })
2018/09/27 10:09:12 - Execute Cypher script.0 - 
2018/09/27 10:09:12 - Execute Cypher script.0 - org.neo4j.driver.v1.exceptions.ServiceUnavailableException: Connection to the database failed
2018/09/27 10:09:12 - Execute Cypher script.0 - 
2018/09/27 10:09:12 - Execute Cypher script.0 - 
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.pentaho.di.trans.steps.sql.ExecSQL.processRow(ExecSQL.java:236)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at java.lang.Thread.run(Thread.java:745)
2018/09/27 10:09:12 - Execute Cypher script.0 - Caused by: org.pentaho.di.core.exception.KettleDatabaseException: 
2018/09/27 10:09:12 - Execute Cypher script.0 - Couldn't execute SQL: MERGE (n:TempObs { obsId: {1}, personId: {2}, conceptId: {3}, obsDateTime: toString({4}) })
2018/09/27 10:09:12 - Execute Cypher script.0 - 
2018/09/27 10:09:12 - Execute Cypher script.0 - org.neo4j.driver.v1.exceptions.ServiceUnavailableException: Connection to the database failed
2018/09/27 10:09:12 - Execute Cypher script.0 - 
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.pentaho.di.core.database.Database.execStatement(Database.java:1559)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.pentaho.di.trans.steps.sql.ExecSQL.processRow(ExecSQL.java:209)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	... 2 more
2018/09/27 10:09:12 - Execute Cypher script.0 - Caused by: java.sql.SQLException: org.neo4j.driver.v1.exceptions.ServiceUnavailableException: Connection to the database failed
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.jdbc.bolt.BoltNeo4jPreparedStatement.executeInternal(BoltNeo4jPreparedStatement.java:106)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.jdbc.bolt.BoltNeo4jPreparedStatement.execute(BoltNeo4jPreparedStatement.java:78)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at java.lang.reflect.Method.invoke(Method.java:498)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.jdbc.utils.Neo4jInvocationHandler.invoke(Neo4jInvocationHandler.java:54)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at com.sun.proxy.$Proxy68.execute(Unknown Source)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.pentaho.di.core.database.Database.execStatement(Database.java:1526)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	... 3 more
2018/09/27 10:09:12 - Execute Cypher script.0 - Caused by: org.neo4j.driver.v1.exceptions.ServiceUnavailableException: Connection to the database failed
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.async.inbound.ChannelErrorHandler.transformError(ChannelErrorHandler.java:114)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.async.inbound.ChannelErrorHandler.fail(ChannelErrorHandler.java:98)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.async.inbound.ChannelErrorHandler.exceptionCaught(ChannelErrorHandler.java:92)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.channel.AbstractChannelHandlerContext.invokeExceptionCaught(AbstractChannelHandlerContext.java:285)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.channel.AbstractChannelHandlerContext.invokeExceptionCaught(AbstractChannelHandlerContext.java:264)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.channel.AbstractChannelHandlerContext.fireExceptionCaught(AbstractChannelHandlerContext.java:256)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.channel.ChannelHandlerAdapter.exceptionCaught(ChannelHandlerAdapter.java:87)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.channel.AbstractChannelHandlerContext.invokeExceptionCaught(AbstractChannelHandlerContext.java:285)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.channel.AbstractChannelHandlerContext.invokeExceptionCaught(AbstractChannelHandlerContext.java:264)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.channel.AbstractChannelHandlerContext.fireExceptionCaught(AbstractChannelHandlerContext.java:256)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.channel.ChannelInboundHandlerAdapter.exceptionCaught(ChannelInboundHandlerAdapter.java:131)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.channel.AbstractChannelHandlerContext.invokeExceptionCaught(AbstractChannelHandlerContext.java:285)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.channel.AbstractChannelHandlerContext.invokeExceptionCaught(AbstractChannelHandlerContext.java:264)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.channel.AbstractChannelHandlerContext.fireExceptionCaught(AbstractChannelHandlerContext.java:256)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.channel.ChannelInboundHandlerAdapter.exceptionCaught(ChannelInboundHandlerAdapter.java:131)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.channel.AbstractChannelHandlerContext.invokeExceptionCaught(AbstractChannelHandlerContext.java:285)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.channel.AbstractChannelHandlerContext.invokeExceptionCaught(AbstractChannelHandlerContext.java:264)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.channel.AbstractChannelHandlerContext.fireExceptionCaught(AbstractChannelHandlerContext.java:256)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.channel.ChannelInboundHandlerAdapter.exceptionCaught(ChannelInboundHandlerAdapter.java:131)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.channel.AbstractChannelHandlerContext.invokeExceptionCaught(AbstractChannelHandlerContext.java:285)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.channel.AbstractChannelHandlerContext.invokeExceptionCaught(AbstractChannelHandlerContext.java:264)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.channel.AbstractChannelHandlerContext.fireExceptionCaught(AbstractChannelHandlerContext.java:256)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.handler.ssl.SslHandler.exceptionCaught(SslHandler.java:1030)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.channel.AbstractChannelHandlerContext.invokeExceptionCaught(AbstractChannelHandlerContext.java:285)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.channel.AbstractChannelHandlerContext.invokeExceptionCaught(AbstractChannelHandlerContext.java:264)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.channel.AbstractChannelHandlerContext.fireExceptionCaught(AbstractChannelHandlerContext.java:256)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.channel.DefaultChannelPipeline$HeadContext.exceptionCaught(DefaultChannelPipeline.java:1381)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.channel.AbstractChannelHandlerContext.invokeExceptionCaught(AbstractChannelHandlerContext.java:285)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.channel.AbstractChannelHandlerContext.invokeExceptionCaught(AbstractChannelHandlerContext.java:264)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.channel.DefaultChannelPipeline.fireExceptionCaught(DefaultChannelPipeline.java:933)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.channel.VoidChannelPromise.fireException0(VoidChannelPromise.java:236)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.channel.VoidChannelPromise.tryFailure(VoidChannelPromise.java:178)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.util.internal.PromiseNotificationUtil.tryFailure(PromiseNotificationUtil.java:64)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.channel.AbstractChannelHandlerContext.notifyOutboundHandlerException(AbstractChannelHandlerContext.java:837)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.channel.AbstractChannelHandlerContext.invokeWrite0(AbstractChannelHandlerContext.java:740)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.channel.AbstractChannelHandlerContext.invokeWrite(AbstractChannelHandlerContext.java:730)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.channel.AbstractChannelHandlerContext.write(AbstractChannelHandlerContext.java:816)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.channel.AbstractChannelHandlerContext.write(AbstractChannelHandlerContext.java:723)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.channel.DefaultChannelPipeline.write(DefaultChannelPipeline.java:1041)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.channel.AbstractChannel.write(AbstractChannel.java:295)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.async.NettyConnection.writeMessages(NettyConnection.java:205)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.async.NettyConnection.lambda$writeMessagesInEventLoop$1(NettyConnection.java:196)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.util.concurrent.AbstractEventExecutor.safeExecute(AbstractEventExecutor.java:163)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.util.concurrent.SingleThreadEventExecutor.runAllTasks(SingleThreadEventExecutor.java:404)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:463)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.util.concurrent.SingleThreadEventExecutor$5.run(SingleThreadEventExecutor.java:886)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	... 1 more
2018/09/27 10:09:12 - Execute Cypher script.0 - 	Suppressed: org.neo4j.driver.v1.exceptions.ClientException: Can't commit, transaction has been terminated
2018/09/27 10:09:12 - Execute Cypher script.0 - 		at org.neo4j.driver.internal.ExplicitTransaction.doCommitAsync(ExplicitTransaction.java:272)
2018/09/27 10:09:12 - Execute Cypher script.0 - 		at org.neo4j.driver.internal.ExplicitTransaction.lambda$commitAsync$2(ExplicitTransaction.java:172)
2018/09/27 10:09:12 - Execute Cypher script.0 - 		at java.util.concurrent.CompletableFuture.uniCompose(CompletableFuture.java:952)
2018/09/27 10:09:12 - Execute Cypher script.0 - 		at java.util.concurrent.CompletableFuture$UniCompose.tryFire(CompletableFuture.java:926)
2018/09/27 10:09:12 - Execute Cypher script.0 - 		at java.util.concurrent.CompletableFuture.postComplete(CompletableFuture.java:474)
2018/09/27 10:09:12 - Execute Cypher script.0 - 		at java.util.concurrent.CompletableFuture.complete(CompletableFuture.java:1962)
2018/09/27 10:09:12 - Execute Cypher script.0 - 		at org.neo4j.driver.internal.handlers.PullAllResponseHandler.completeFailureFuture(PullAllResponseHandler.java:318)
2018/09/27 10:09:12 - Execute Cypher script.0 - 		at org.neo4j.driver.internal.handlers.PullAllResponseHandler.onFailure(PullAllResponseHandler.java:107)
2018/09/27 10:09:12 - Execute Cypher script.0 - 		at org.neo4j.driver.internal.async.inbound.InboundMessageDispatcher.handleFatalError(InboundMessageDispatcher.java:193)
2018/09/27 10:09:12 - Execute Cypher script.0 - 		at org.neo4j.driver.internal.async.inbound.ChannelErrorHandler.fail(ChannelErrorHandler.java:99)
2018/09/27 10:09:12 - Execute Cypher script.0 - 		... 46 more
2018/09/27 10:09:12 - Execute Cypher script.0 - Caused by: java.io.IOException: Unknown type: LOCAL_DATE_TIME
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.messaging.PackStreamMessageFormatV1$WriterV1.packInternalValue(PackStreamMessageFormatV1.java:255)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.messaging.PackStreamMessageFormatV1$WriterV1.packValue(PackStreamMessageFormatV1.java:201)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.messaging.PackStreamMessageFormatV1$WriterV1.packRawMap(PackStreamMessageFormatV1.java:193)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.messaging.PackStreamMessageFormatV1$WriterV1.handleRunMessage(PackStreamMessageFormatV1.java:118)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.messaging.RunMessage.dispatch(RunMessage.java:48)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.messaging.PackStreamMessageFormatV1$WriterV1.write(PackStreamMessageFormatV1.java:262)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.async.outbound.OutboundMessageHandler.encode(OutboundMessageHandler.java:82)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.async.outbound.OutboundMessageHandler.encode(OutboundMessageHandler.java:37)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.handler.codec.MessageToMessageEncoder.write(MessageToMessageEncoder.java:88)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	at org.neo4j.driver.internal.shaded.io.netty.channel.AbstractChannelHandlerContext.invokeWrite0(AbstractChannelHandlerContext.java:738)
2018/09/27 10:09:12 - Execute Cypher script.0 - 	... 13 more
2018/09/27 10:09:12 - Execute Cypher script.0 - Finished reading query, closing connection.
2018/09/27 10:09:14 - Execute Cypher script.0 - Finished processing (I=0, O=0, R=1, W=0, U=0, E=1)
2018/09/27 10:09:14 - WorkingNeo4JExportZak - ERROR (version 8.1.0.0-365, build 8.1.0.0-365 from 2018-04-30 09.42.24 by buildguy) : Errors detected!
2018/09/27 10:09:14 - Spoon - The transformation has finished!!
2018/09/27 10:09:14 - WorkingNeo4JExportZak - ERROR (version 8.1.0.0-365, build 8.1.0.0-365 from 2018-04-30 09.42.24 by buildguy) : Errors detected!
2018/09/27 10:09:14 - WorkingNeo4JExportZak - ERROR (version 8.1.0.0-365, build 8.1.0.0-365 from 2018-04-30 09.42.24 by buildguy) : Errors detected!
2018/09/27 10:09:14 - WorkingNeo4JExportZak - Transformation detected one or more steps with errors.
2018/09/27 10:09:14 - WorkingNeo4JExportZak - Transformation is killing the other steps!

If I remove the column with type of *DATETIME , then I am able to make nodes in neo4j using the pentaho kettle spoon. but with the obsdatetime column (type DATETIME) I am getting this error.

Here's my Cypher query

MERGE (n:TempObs { obsId: {1}, personId: {2}, conceptId: {3}, obsDateTime: {4} })

[matt_casters]

If you're just loading columns into a node and it's properties, feel free to give the Neo4j Output step a try. New versions are delivered on a regular basis over here: neo4j.kettle.be

Please note that a Kettle Timestamp contains nanosecond precision. Perhaps you can convert it to a Kettle Date type with millisecond precision to allow it to be stored in Neo4j?