Hi,
I have developed the pipeline which reads the data from the incoming stream and send to SQL Merge. My pipeline looks like below.
My Input JSON is as follows:
[ {
“member_id”: 13000561,
“intervention_id”: 1000034495,
“sentiment”: null,
“last_updated”: null,
“incentive_offered”: null,
“reaction_details”: null,
“dashboard_name”: “SalesForce”,
“created”: null,
“intervention_assessment”: null,
“tactic_details”: null,
“created_by”: null,
“followup_deadline”: {
“_snaptype_localdate”: “2019-10-13”
},
“followup”: false,
“last_updated_by”: null,
“contact_method”: null,
“contact_made”: false,
“date_of_intervention”: null,
“intervention_notes”: null
}]
Configuration for SQL Merge Snap:
Please note that we are allowing null for all the columns expect the intervention id which is the primary key. When we are running the pipeline, we are getting the following error.
{
“error”: “SQL operation failed”,
“stacktrace”: “java.sql.BatchUpdateException: Implicit conversion from data type varbinary to date is not allowed. Use the CONVERT function to run this query.\n\tat com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:1178)\n\tat com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:128)\n\tat com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeBatch(HikariProxyPreparedStatement.java)\n\tat org.jooq.tools.jdbc.DefaultStatement.executeBatch(DefaultStatement.java:93)\n\tat org.jooq.impl.BatchSingle.executePrepared(BatchSingle.java:239)\n\tat org.jooq.impl.BatchSingle.execute(BatchSingle.java:182)\n\tat com.snaplogic.snap.api.sql.operations.JdbcOperationsImpl.lambda$flushBatch$8(JdbcOperationsImpl.java:1547)\n\tat com.snaplogic.snap.api.sql.operations.JdbcOperationsImpl$$Lambda$394/1810556666.call(Unknown Source)\n\tat net.jodah.failsafe.Functions$11.call(Functions.java:263)\n\tat net.jodah.failsafe.SyncFailsafe.call(SyncFailsafe.java:145)\n\tat net.jodah.failsafe.SyncFailsafe.get(SyncFailsafe.java:69)\n\tat com.snaplogic.snap.api.sql.operations.JdbcOperationsImpl.flushBatch(JdbcOperationsImpl.java:1546)\n\tat com.snaplogic.snaps.sql.SimpleSqlWriteSnap.handle(SimpleSqlWriteSnap.java:136)\n\tat com.snaplogic.cc.snap.common.SnapRunnableImpl.callback(SnapRunnableImpl.java:836)\n\tat com.snaplogic.cc.snap.common.SnapRunnableImpl.executeForSuggest(SnapRunnableImpl.java:644)\n\tat com.snaplogic.cc.snap.common.SnapRunnableImpl.doRun(SnapRunnableImpl.java:852)\n\tat com.snaplogic.cc.snap.common.SnapRunnableImpl.access$000(SnapRunnableImpl.java:118)\n\tat com.snaplogic.cc.snap.common.SnapRunnableImpl$1.run(SnapRunnableImpl.java:392)\n\tat com.snaplogic.cc.snap.common.SnapRunnableImpl$1.run(SnapRunnableImpl.java:388)\n\tat java.security.AccessController.doPrivileged(Native Method)\n\tat javax.security.auth.Subject.doAs(Subject.java:422)\n\tat org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1657)\n\tat com.snaplogic.cc.snap.common.SnapRunnableImpl.call(SnapRunnableImpl.java:387)\n\tat com.snaplogic.cc.snap.common.SnapRunnableImpl.call(SnapRunnableImpl.java:118)\n\tat java.util.concurrent.FutureTask.run(FutureTask.java:266)\n\tat java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)\n\tat java.util.concurrent.FutureTask.run(FutureTask.java:266)\n\tat java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)\n\tat java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)\n\tat java.lang.Thread.run(Thread.java:745)\n”,
“reason”: “Implicit conversion from data type varbinary to date is not allowed. Use the CONVERT function to run this query., error code: 257, SQL state: S0003”,
“resolution”: “Please check for valid Snap properties and input data.”,
“status_code”: “error”,
“status”: -3,
“snap_details”: {
“label”: “SQL Server - Merge”,
“instance_id”: “7de1aaf6-483b-4e42-b1ed-46cf7f1fd2bd”,
“class_id”: “com-snaplogic-snaps-sqlserver-merge”,
“build_tag”: “snapsmrc523”,
“views”: {
“in”: {
“input0”: {
“count”: 2
}
},
“out”: {},
“error”: {
“error0”: {
“count”: 0
}
}
}
},
“original”: {
“member_id”: 13000561,
“intervention_id”: 1000034495,
“sentiment”: null,
“last_updated”: null,
“incentive_offered”: null,
“reaction_details”: null,
“dashboard_name”: “SalesForce”,
“created”: null,
“intervention_assessment”: null,
“tactic_details”: null,
“created_by”: null,
“followup_deadline”: {
“_snaptype_localdate”: “2019-10-13”
},
“followup”: false,
“last_updated_by”: null,
“contact_method”: null,
“contact_made”: false,
“date_of_intervention”: null,
“intervention_notes”: null
}
}
Please note that same thing works with the SQL Insert, but we are expecting the upsert to the table. Please help us resolving the issues.
Thanks,
Gowdhaman
