Forum Discussion

rpathak's avatar
rpathak
New Contributor II
4 years ago

How to handle Null in Insert Snap?

Hi,

My pipeline is running successfully when I have data in the source table. But when there is no data on the source then the Insert snap got failed with the message that the null value is not allowed in the table.

We have mentioned the not null in the table but once we haven’t any record from the source then why the Insert Snap is looking into the table for a null value.

Mention below is the pipeline for your reference:-

5 Replies

  • bojanvelevski's avatar
    bojanvelevski
    Valued Contributor

    When Truncate StageMCRC… completes, It will produce an output, the Select AXMCRCoup… snap will try to read from MCRCouponcatalog table, but because it’s empty, it will pass through the response from the Truncating Operation snap, that will trigger the Mapper, where you mapped the required fields, but because the fields are not received on the input schema and the Null-Safe access is enabled, you’ll end up with the mapped target paths, but with null as values. The rest is clear, The CATALOG column doesn’t allow NULL as value, so you end up with an error on your Insert snap.

    • ForbinCSD's avatar
      ForbinCSD
      Contributor

      @rpathak see above, this is quite correct.

      You can handle this in the mapper by changing the “Expression” entries: replace the “bare” JSON properties with snippets of Javascript to replace null with a value of your choice.

      If you never want to insert these, an alternative solution is to insert a decision snap into your flow. Pick any property name that represents a non-nullable table column/value, and continue down the path with the INSERT snap only if that property is not null.

  • bojanvelevski's avatar
    bojanvelevski
    Valued Contributor

    Hi @rpathak,

    There’s just not enough information to go with, so I’m going to guess on one scenario. Check if you have pass through enabled on your read snap, and Null-Safe Access enabled on your Mapper, or maybe you are using the $.get() method to map the required fields. If that’s not the case, please share some more information on the flow.

    Regards,
    Bojan

    • rpathak's avatar
      rpathak
      New Contributor II

      Hi,

      The pipeline details for your reference.

      Mapper:-

  • rpathak's avatar
    rpathak
    New Contributor II

    Hi,

    This is the error on Insert Snap.

    Batch operation failed

    Resolution:

    Please check for valid Snap properties and input data.

    Reason:

    Cannot insert the value NULL into column ‘CATALOG’, table ‘ERPDW.STAGE.MCRCOUPONCATALOG’; column does not allow nulls. INSERT fails., error code: 515, SQL state: 23000

    Hide Details…

    Insert StageMCRCouponCatalog[59a02100bc9ffa611f106dbc_10281abb-3ed2-4201-aa12-02b12b41aeb8 – ac8ad0c8-3831-49d1-a01d-1ccffc625303]
    `com.snaplogic.snap.api.SnapDataException: Batch operation failed
    at com.snaplogic.snap.api.sql.DatabaseUtils.writeDocumentToErrorView(DatabaseUtils.java:762)
    at com.snaplogic.snap.api.sql.DatabaseUtils.handleUpdateResult(DatabaseUtils.java:706)
    at com.snaplogic.snap.api.sql.DatabaseUtils.handleUpdateResult(DatabaseUtils.java:616)
    at com.snaplogic.snaps.sql.SimpleSqlWriteSnap.handle(SimpleSqlWriteSnap.java:178)
    at com.snaplogic.cc.snap.common.SnapRunnableImpl.callback(SnapRunnableImpl.java:840)
    at com.snaplogic.cc.snap.common.SnapRunnableImpl.execute(SnapRunnableImpl.java:586)
    at com.snaplogic.cc.snap.common.SnapRunnableImpl.doRun(SnapRunnableImpl.java:869)
    at com.snaplogic.cc.snap.common.SnapRunnableImpl.call(SnapRunnableImpl.java:435)
    at com.snaplogic.cc.snap.common.SnapRunnableImpl.call(SnapRunnableImpl.java:117)
    at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
    at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
    at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
    at java.base/java.lang.Thread.run(Thread.java:834)
    Caused by: java.sql.BatchUpdateException: Cannot insert the value NULL into column ‘CATALOG’, table ‘ERPDW.STAGE.MCRCOUPONCATALOG’; column does not allow nulls. INSERT fails.
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:1178)
    at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:128)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeBatch(HikariProxyPreparedStatement.java)
    at org.jooq.tools.jdbc.DefaultStatement.executeBatch(DefaultStatement.java:93)
    at org.jooq.impl.BatchSingle.executePrepared(BatchSingle.java:239)
    at org.jooq.impl.BatchSingle.execute(BatchSingle.java:182)
    at com.snaplogic.snap.api.sql.operations.JdbcOperationsImpl.lambda$flushBatch$8(JdbcOperationsImpl.java:1660)
    at net.jodah.failsafe.Functions$11.call(Functions.java:263)
    at net.jodah.failsafe.SyncFailsafe.call(SyncFailsafe.java:145)
    at net.jodah.failsafe.SyncFailsafe.get(SyncFailsafe.java:69)
    at com.snaplogic.snap.api.sql.operations.JdbcOperationsImpl.flushBatch(JdbcOperationsImpl.java:1657)
    at com.snaplogic.snaps.sql.SimpleSqlWriteSnap.handle(SimpleSqlWriteSnap.java:166)
    … 11 more
    Reason: Cannot insert the value NULL into column ‘CATALOG’, table ‘ERPDW.STAGE.MCRCOUPONCATALOG’; column
    does not allow nulls. INSERT fails., error code: 515, SQL state: 23000
    Resolution: Please check for valid Snap properties and input data.

    Error Fingerprint[0] = efp:com.snaplogic.snap.api.sql.FIIDcK5m
    Error Fingerprint[1] = efp:com.microsoft.sqlserver.jdbc.X-b_znbX`

    Insert StageMCRCouponCatalog[59a02100bc9ffa611f106dbc_10281abb-3ed2-4201-aa12-02b12b41aeb8 – ac8ad0c8-3831-49d1-a01d-1ccffc625303]
    `com.snaplogic.cc.snap.common.ThreadDetails: prio=4 Id=35734 RUNNABLE
    at com.fasterxml.jackson.core.json.WriterBasedJsonGenerator._writeString2(WriterBasedJsonGenerator.java:1084)
    at com.fasterxml.jackson.core.json.WriterBasedJsonGenerator._writeString(WriterBasedJsonGenerator.java:1053)
    at com.fasterxml.jackson.core.json.WriterBasedJsonGenerator.writeString(WriterBasedJsonGenerator.java:409)
    at com.fasterxml.jackson.core.JsonGenerator.writeStringField(JsonGenerator.java:1692)
    at com.snaplogic.cc.log.JsonLogLayout.writeField(JsonLogLayout.java:182)
    at com.snaplogic.cc.log.JsonLogLayout.writeException(JsonLogLayout.java:155)
    at com.snaplogic.cc.log.JsonLogLayout.writeAllFields(JsonLogLayout.java:111)
    at com.snaplogic.cc.log.JsonLogLayout.toSerializable(JsonLogLayout.java:91)

    at com.fasterxml.jackson.core.json.WriterBasedJsonGenerator._writeString2(WriterBasedJsonGenerator.java:1084)
    at com.fasterxml.jackson.core.json.WriterBasedJsonGenerator._writeString(WriterBasedJsonGenerator.java:1053)
    at com.fasterxml.jackson.core.json.WriterBasedJsonGenerator.writeString(WriterBasedJsonGenerator.java:409)
    at com.fasterxml.jackson.core.JsonGenerator.writeStringField(JsonGenerator.java:1692)
    at com.snaplogic.cc.log.JsonLogLayout.writeField(JsonLogLayout.java:182)
    at com.snaplogic.cc.log.JsonLogLayout.writeException(JsonLogLayout.java:155)
    at com.snaplogic.cc.log.JsonLogLayout.writeAllFields(JsonLogLayout.java:111)
    at com.snaplogic.cc.log.JsonLogLayout.toSerializable(JsonLogLayout.java:91)
    at com.snaplogic.cc.log.JsonLogLayout.toSerializable(JsonLogLayout.java:43)
    at org.apache.logging.log4j.core.layout.AbstractStringLayout.toByteArray(AbstractStringLayout.java:304)
    at org.apache.logging.log4j.core.layout.AbstractLayout.encode(AbstractLayout.java:210)
    at org.apache.logging.log4j.core.layout.AbstractLayout.encode(AbstractLayout.java:37)
    at org.apache.logging.log4j.core.appender.AbstractOutputStreamAppender.directEncodeEvent(AbstractOutputStreamAppender.java:197)
    at org.apache.logging.log4j.core.appender.AbstractOutputStreamAppender.tryAppend(AbstractOutputStreamAppender.java:190)
    at org.apache.logging.log4j.core.appender.AbstractOutputStreamAppender.append(AbstractOutputStreamAppender.java:181)
    at org.apache.logging.log4j.core.appender.RollingRandomAccessFileAppender.append(RollingRandomAccessFileAppender.java:252)
    at org.apache.logging.log4j.core.config.AppenderControl.tryCallAppender(AppenderControl.java:156)
    at org.apache.logging.log4j.core.config.AppenderControl.callAppender0(AppenderControl.java:129)
    at org.apache.logging.log4j.core.config.AppenderControl.callAppenderPreventRecursion(AppenderControl.java:120)
    at org.apache.logging.log4j.core.config.AppenderControl.callAppender(AppenderControl.java:84)
    at org.apache.logging.log4j.core.config.LoggerConfig.callAppenders(LoggerConfig.java:464)
    at org.apache.logging.log4j.core.config.LoggerConfig.processLogEvent(LoggerConfig.java:448)
    at org.apache.logging.log4j.core.config.LoggerConfig.log(LoggerConfig.java:431)
    at org.apache.logging.log4j.core.config.LoggerConfig.log(LoggerConfig.java:406)
    at org.apache.logging.log4j.core.config.AwaitCompletionReliabilityStrategy.log(AwaitCompletionReliabilityStrategy.java:63)
    at org.apache.logging.log4j.core.Logger.logMessage(Logger.java:146)
    at org.apache.logging.log4j.spi.AbstractLogger.tryLogMessage(AbstractLogger.java:2170)
    at org.apache.logging.log4j.spi.AbstractLogger.logMessageTrackRecursion(AbstractLogger.java:2125)
    at org.apache.logging.log4j.spi.AbstractLogger.logMessageSafely(AbstractLogger.java:2108)
    at org.apache.logging.log4j.spi.AbstractLogger.logMessage(AbstractLogger.java:2002)
    at org.apache.logging.log4j.spi.AbstractLogger.logIfEnabled(AbstractLogger.java:1974)
    at org.apache.logging.slf4j.Log4jLogger.error(Log4jLogger.java:319)
    at com.snaplogic.cc.snap.common.SnapRunnableImpl.failSnap(SnapRunnableImpl.java:1028)
    at com.snaplogic.cc.snap.common.SnapRunnableImpl.handleException(SnapRunnableImpl.java:1014)
    at com.snaplogic.cc.snap.common.SnapRunnableImpl.doRun(SnapRunnableImpl.java:890)
    at com.snaplogic.cc.snap.common.SnapRunnableImpl.call(SnapRunnableImpl.java:435)
    at com.snaplogic.cc.snap.common.SnapRunnableImpl.call(SnapRunnableImpl.java:117)
    at java.base@11.0.10/java.util.concurrent.FutureTask.run(FutureTask.java:264)
    at java.base@11.0.10/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
    at java.base@11.0.10/java.util.concurrent.FutureTask.run(FutureTask.java:264)
    at java.base@11.0.10/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
    at java.base@11.0.10/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
    at java.base@11.0.10/java.lang.Thread.run(Thread.java:834)

    Error Fingerprint[0] = efp:com.fasterxml.jackson.core.json.M4DwoG_c`