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:-
image

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

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`

Hi,

The pipeline details for your reference.

Mapper:-

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.

3 Likes

@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.