Skip to main content
Solved

Writer in update mode giving me problems ...

  • January 26, 2023
  • 1 reply
  • 107 views
lifalin2016
Supporter
Forum|alt.badge.img+38

Hi all.

I'm trying to update single values in a table, which has 3-4 keys. The three keys are mandatory, while the 4th is optional.

A writer will apparently only update records which has a value for the 4th parameter, erroring when this value is NULL.

Why is that ?

I tried to test for the NULL value, but that didn't work. Apparantly the value needed to be tested as being an empty string, not a <null> value, even though that was what it contained.

Is that a bug ?

And lastly you - again apparently - cannot simultaneously have a writer/update and a SQL Executor (to handle the NULLs), the writer blocks the SQL Executor. I had to ditch the writer and go with two SQL Executor's.

Why does a writer block transformers ?

All this a s a warning to y'all. Can anyone confirm these findings ?

Cheers.

2020.2 / MS SQL Server

Best answer by ctredinnick

For the update statement, try switching it to write your own Where Clause, instead of Match Columns. That way you can handle nulls in your 4th column with your own statement. WHERE x = @Value(x) and ISNULL(y,'') = '@Value(y)' or whatever your data needs.

Then, a SQL writer parameter is likely set to the default mode of 'bulk insert'. This Alters the table so data can be inserted quicker (bypassing triggers etc), and this is probably what is blocking your SQLExecutor.

 

-- Cheers, Lars I.
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

1 reply

ctredinnick
Supporter
Forum|alt.badge.img+19
  • ctredinnickSupporter
  • Supporter
  • 225 replies
  • Best Answer
  • January 26, 2023

For the update statement, try switching it to write your own Where Clause, instead of Match Columns. That way you can handle nulls in your 4th column with your own statement. WHERE x = @Value(x) and ISNULL(y,'') = '@Value(y)' or whatever your data needs.

Then, a SQL writer parameter is likely set to the default mode of 'bulk insert'. This Alters the table so data can be inserted quicker (bypassing triggers etc), and this is probably what is blocking your SQLExecutor.

 

Community Stats

32,068
Posts
121,762
Replies
39,693
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.