How to get the SQL Executor to write a NULL when using parameterized statement with FME @Value(---) syntax

Whether reading SQL data using the Executor or FeatureReader, I can attach an Inspector and see that FME reads <null> values. What happens to them when I immediately connect to a Writer and it spits out empty strings? Is there global setting for "null-handling" I'm not aware of?

• I see you have the ability to "Ignore Nulls" in SQL Executor when deciding what to include in your output but when the Executor does an INSERT as the last step of your pipeline that doesn't really apply. None of my attempt using that setting have borne any fruit.
• Also a NullAttributeMapper hasn't worked as expected - likely because the NULLs exist and mapping them to NULL... well, doesn't make sense, does it?
• So I've used the NullAttributeMapper to map NULL to "WISHTHEREWEREANULLHERE" and then run a cleanup script to replace those occurrences with NULL at the end. But that's clunky and crude.
• What is the solution here?

I would add a conditional in e.g. an AttributeManager before the SQLExecutor, so that your attribute either gets the single quotes applied there, or that the value gets set to the string value "null". You can then reference FMEATTR directly without the single quotes in the SQLExecutor:

INSERT INTO MYTABLE
(FIELD1, FIELD2)
VALUES (
@Value(FMEATTR1),
)

INSERT INTO MYTABLE
(FIELD1, FIELD2)
VALUES (
@Value(FMEATTR1),
)

@david_r​ That's a good thought and clearly goes at the heart of the issue here - that the single quotes around @Value() in my Executor for NULL evaluate to an empty string. My only reservation is that doing this for numerous attributes becomes cumbersome and in terms of readability/maintainability the similar level of effort of converting '' to Null in "cleanup" SQL following the insert is clearer. Appreciate you chiming in!

I agree that it's cumbersome, but that's unfortunately a side effect of using the SQLExecutor, as you're basically sidestepping all the logic that's built into the FME writers to handle such issues.

Any particular reason for not wanting to use a FeatureWriter rather than the SQLExecutor? It will both avoid this issue, as well as have better performance due to more intelligent use of transactions.

@david_r​ David, I might add that in my never ceasing quest to understand FME better and build up a best-practices sense of sorts, I should probably revisit why I'm using the Executor here in the first place which introduces the problem by nature of using the @Value(). One of the main drivers is really that it allows me to collect per feature success/failure output that can be processed further. Using a FeatureWriter to just dump everything straight into SQL causes no issues processing the incoming Nulls correctly. But I don't believe there is a way to get a 'Rejected' output channel, is there?

There's no easy way to see what's rejected when using the FeatureWriter, unfortunately. This is a fairly long-standing issue: https://community.safe.com/s/idea/0874Q000000TkwiQAC/detail

I suspect there are some technical issues in how writing is performed internally since this hasn't been implemented a long time ago.

The best workaround is to make sure your data is valid before sending it to the FeatureWriter ;-)

@agelfert​  Using the NullAttributeMapper approach works for numeric fields - but map to a literal "NULL" rather than <null>. But for the fields that have to be quoted (text, char) that is more problematic.

In the attached example, I've come up with a hack. Create the 'Value' string of the SQL in an AttributeCreator. Then, using StringReplacer, search for 'NULL' and replace with NULL. It seems to work.

Example workspace (FME 2021):

Nice! Thanks for hearing my call and taking a look, Mark. Haha. I'll give it a try!

@Mark Stoakes​ ,

while opening the SQLInsertNULL fmwt I get an alert that i need more licensing.

Is there any hack that would allow me to get past this with just the esri license?

EDIT: I think I see now that you are using it to create spatial tables from a non-spatial table. (I am not trying to do this) I am using a nonspatial table to select the smaller portion of an sde spatial table. I use 1 record from the non-spatial table to return many records from sde.

ORIG: I am using DatabaseJoiner simply because of the size of my sde table. (77MM rows) I have ~80k key IDs that i am selecting for update with, and 1000 key IDs to be deleted. If I use the FeatureReader it runs 3 updates and then sits for hours.

@Brad Nesom​ You should be able to open the workspace OK, just not run it. Then just replace the SQL Server Spatial reader with a SQL Server Non-Spatial reader