This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.
Accept
Reject
Preferences
FAQ
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results forย 
Search instead forย 
Did you mean:ย 

How to handle Null in Insert Snap?

rpathak
New Contributor II

โ€Ž09-17-2021 05:23 AM

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

0 Kudos
5 REPLIES 5

bojanvelevski
Valued Contributor

โ€Ž09-17-2021 05:36 AM

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

0 Kudos

rpathak
New Contributor II
In response to bojanvelevski

โ€Ž09-17-2021 06:08 AM

Hi,

The pipeline details for your reference.
image

Mapper:-
image

0 Kudos

rpathak
New Contributor II

โ€Ž09-17-2021 05:55 AM

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`

0 Kudos

bojanvelevski
Valued Contributor

โ€Ž09-17-2021 06:26 AM

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.