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
Reply
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
Reply

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
Reply

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
Reply

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.

Reply