• EN
Solved

SQLExecutor - how to update multiple SQL Server columns in one query?

tim_wood
Rank
Badge +7

FME 2017.0.1.1 (17291) 64-bit on Windows Server 2008 connecting to SQL Server 2012 on Windows Server 2012.

MS SQL Server supports this type of update statement:

update db.owner.mytable

set field1 = ABC, field2 = XYZ

where primkey = 123

I want to do the same thing with SQLExecutor thus:

update db.owner.mytable

set field1 = @Value(fieldA), field2 = @Value(fieldB)

where primkey = @Value(uniqueid)

However when I put this into the SQL Statement parameter, the cog on the transformer goes red i.e. something is wrong, but nothing is highlighted red in the transformer properties. Is this an FME bug, or do I need to write it differently?

icon

Best answer by david_r 21 July 2017, 17:32

View original

16 replies

david_r
Rank
Userlevel 5

I can't say why the cog is red, but I doubt it's a bug. Make sure the attributes you reference in your SQL are properly exposed in the workspace and pay attention to case sensitivity.

Know that the SQLExecutor has no notion of SQL syntax on its own, it will pass on whatever you write there to your database. So if your database is MS SQL, you can basically write whatever MS SQL accepts in the SQLExecutor, FME won't validate anything but internal function references such as @Value() etc.

Note, however, that if fieldA and/or fieldB are string values, you must make sure to insert the single quotes yourself, e.g.

update dbo.owner.mytable
set field1 = '@Value(fieldA)'
where ...

Other than that you seem to be on the right track.

tim_wood
Rank
Badge +7

I can't say why the cog is red, but I doubt it's a bug. Make sure the attributes you reference in your SQL are properly exposed in the workspace and pay attention to case sensitivity.

Know that the SQLExecutor has no notion of SQL syntax on its own, it will pass on whatever you write there to your database. So if your database is MS SQL, you can basically write whatever MS SQL accepts in the SQLExecutor, FME won't validate anything but internal function references such as @Value() etc.

Note, however, that if fieldA and/or fieldB are string values, you must make sure to insert the single quotes yourself, e.g.

update dbo.owner.mytable
set field1 = '@Value(fieldA)'
where ...

Other than that you seem to be on the right track.

Ah yes the single quotes.  Good spot.  I remembered them for the Where clause, but missed them off the columns to update.  Then I had to deal with single quotes in the source values because they need to be doubled in an SQL query.

 

Set myField = '@ReplaceString(@Value(myValue),','')'

 

seems to do the trick.

 

I'm glad none of the fields I'm updating are date fields but if they were, I suspect I might have to use @DateTimeParse(@Value(myValue),%Y-%m-%d,repair)

 

tim_wood
Rank
Badge +7

I can't say why the cog is red, but I doubt it's a bug. Make sure the attributes you reference in your SQL are properly exposed in the workspace and pay attention to case sensitivity.

Know that the SQLExecutor has no notion of SQL syntax on its own, it will pass on whatever you write there to your database. So if your database is MS SQL, you can basically write whatever MS SQL accepts in the SQLExecutor, FME won't validate anything but internal function references such as @Value() etc.

Note, however, that if fieldA and/or fieldB are string values, you must make sure to insert the single quotes yourself, e.g.

update dbo.owner.mytable
set field1 = '@Value(fieldA)'
where ...

Other than that you seem to be on the right track.

Well in this case it looks like SQLExecutor loses to SQL Server Non-Spatial Writer (with fme_db_operation set to UPDATE) for the 7 fields I want to update.  Doing it the Writer way take 8 minutes for 100,000 records whereas SQLExecutor doesn't even manage 20,000 records in 20 minutes.

 

The other problem with my update statement in SQLExecutor is that nulls are getting turned into empty strings which doesn't happen with the Writer.  I suspect dealing with that would slow things down even more...

 

Interesting to compare the massive performance differences between SQLExecutor and SQL Server Non-Spatial Writer for 2 different update tasks.  Here, the Writer wins hands down, but in my other example, SQLExecutor was the clear winner:

 

https://knowledge.safe.com/questions/48437/sql-server-update-table-to-replace-empty-string-wi.html?childToView=48459#comment-48459

 

So it's worth trying both ways.

 

M
Badge +2

I can't say why the cog is red, but I doubt it's a bug. Make sure the attributes you reference in your SQL are properly exposed in the workspace and pay attention to case sensitivity.

Know that the SQLExecutor has no notion of SQL syntax on its own, it will pass on whatever you write there to your database. So if your database is MS SQL, you can basically write whatever MS SQL accepts in the SQLExecutor, FME won't validate anything but internal function references such as @Value() etc.

Note, however, that if fieldA and/or fieldB are string values, you must make sure to insert the single quotes yourself, e.g.

update dbo.owner.mytable
set field1 = '@Value(fieldA)'
where ...

Other than that you seem to be on the right track.

 

@tim_wood don't happen to have a commit in the SQLExecutor do you? That will slow it down massively!
tim_wood
Rank
Badge +7

 

@tim_wood don't happen to have a commit in the SQLExecutor do you? That will slow it down massively!
This is my SQL Statement:

 

update owner.table

 

set field1 = ,

 

field2 = ,

 

field3 = ,

 

field4 = ,

 

field5 = ,

 

field6 = ,

 

field7 =

 

where UID =

 

I can't see any other parameters in the transformer that refer to commit rates.

 

takashi
Rank
Userlevel 3
Badge +17
Well in this case it looks like SQLExecutor loses to SQL Server Non-Spatial Writer (with fme_db_operation set to UPDATE) for the 7 fields I want to update.  Doing it the Writer way take 8 minutes for 100,000 records whereas SQLExecutor doesn't even manage 20,000 records in 20 minutes.

 

The other problem with my update statement in SQLExecutor is that nulls are getting turned into empty strings which doesn't happen with the Writer.  I suspect dealing with that would slow things down even more...

 

Interesting to compare the massive performance differences between SQLExecutor and SQL Server Non-Spatial Writer for 2 different update tasks.  Here, the Writer wins hands down, but in my other example, SQLExecutor was the clear winner:

 

https://knowledge.safe.com/questions/48437/sql-server-update-table-to-replace-empty-string-wi.html?childToView=48459#comment-48459

 

So it's worth trying both ways.

 

Regarding the NULL.

 

Since the @Value() function returns the empty string if the attribute is one of the non-value trio - <empty>, <null>, and <missing>, your SQL statement never write the NULL value into the destination fields.

 

To write NULL with a SQL statement, you will have to replace the attribute value with a string value "null" (not the <null> value) beforehand when the attribute was one of the trio, using the conditional value setting in AttributeCreator or AttributeManager.

 

e.g.

 

field1 =
If field1 has a value Then '@ReplaceString(@Value(field1),','')'
Else null (a character string value "null", not the <null> value)

0684Q00000ArN2lQAF.png

 

Then, naturally the SQl statement should be changed to:
update owner.table 
set field1 = @Value(field1),
... 
david_r
Rank
Userlevel 5
Well in this case it looks like SQLExecutor loses to SQL Server Non-Spatial Writer (with fme_db_operation set to UPDATE) for the 7 fields I want to update. Doing it the Writer way take 8 minutes for 100,000 records whereas SQLExecutor doesn't even manage 20,000 records in 20 minutes.

 

The other problem with my update statement in SQLExecutor is that nulls are getting turned into empty strings which doesn't happen with the Writer. I suspect dealing with that would slow things down even more...

 

Interesting to compare the massive performance differences between SQLExecutor and SQL Server Non-Spatial Writer for 2 different update tasks. Here, the Writer wins hands down, but in my other example, SQLExecutor was the clear winner:

 

https://knowledge.safe.com/questions/48437/sql-server-update-table-to-replace-empty-string-wi.html?childToView=48459#comment-48459

 

So it's worth trying both ways.

 

That doesn't make any sense. How many features are you sending into the SQLExecutor? If you send more features than there are columns to update (not records!) then there's the problem.
david_r
Rank
Userlevel 5
This is my SQL Statement:

 

update owner.table

 

set field1 = ,

 

field2 = ,

 

field3 = ,

 

field4 = ,

 

field5 = ,

 

field6 = ,

 

field7 =

 

where UID =

 

I can't see any other parameters in the transformer that refer to commit rates.

 

Do you really need that where-clause? I thought you were updating the whole table and in that case the where-clause is just complicating things. Let the database do the work for you.
david_r
Rank
Userlevel 5

Try something like the following: Creator -> SQLExecutor with the following code:

update owner.dtable
set field1 = iif(field1 = '', null, field1),
set field2 = iif(field2 = '', null, field2),
set field3 = iif(field3 = '', null, field3),
...etc

Remark that there's no where-clause and that the SQLExecutor is only called once.

Documentation for the iif function here.

Hint: always have a backup of your table before executing queries like this.

tim_wood
Rank
Badge +7
Regarding the NULL.

 

Since the @Value() function returns the empty string if the attribute is one of the non-value trio - <empty>, <null>, and <missing>, your SQL statement never write the NULL value into the destination fields.

 

To write NULL with a SQL statement, you will have to replace the attribute value with a string value "null" (not the <null> value) beforehand when the attribute was one of the trio, using the conditional value setting in AttributeCreator or AttributeManager.

 

e.g.

 

field1 =
If field1 has a value Then '@ReplaceString(@Value(field1),','')'
Else null (a character string value "null", not the <null> value)

0684Q00000ArN2lQAF.png

 

Then, naturally the SQl statement should be changed to:
update owner.table 
set field1 = @Value(field1),
... 
Like it, but should it be testing for attribute is empty string rather than attribute has value?

 

 

tim_wood
Rank
Badge +7
Do you really need that where-clause? I thought you were updating the whole table and in that case the where-clause is just complicating things. Let the database do the work for you.
Maybe this is a lack of SQL Server knowledge on my part. But perhaps a better explanation of what I'm doing would help. The table is address data. I'm concatenating the values in various fields e.g. the Department field and Organisation field into one field DepartmentOrganisation, but also putting a comma between the 2 values e.g. "Sales dept, Safe Software". If there is no value for Department, I don't want the concatenated value to have the comma before Organisation (i.e. "Safe Software" not ", Safe Software"). This is done with a bunch of Testers and AttributeCreators. There are more complex concatenations involving 10 source fields which would be difficult to do with nested if functions in the SQLExecutor. So I assume that when I get to the end, I need to have the where clause so SQL Server knows which row to update.

 

 

Your comment has got me wondering whether I could call the SQLExecutor for each output field once the value has been generated, rather than waiting until the end. Perhaps some of the simpler concatenations could be converted from Tester + AttributeCreator to SQLExecutor only...

 

david_r
Rank
Userlevel 5
Maybe this is a lack of SQL Server knowledge on my part. But perhaps a better explanation of what I'm doing would help.  The table is address data.  I'm concatenating the values in various fields e.g. the Department field and Organisation field into one field DepartmentOrganisation, but also putting a comma between the 2 values e.g. "Sales dept, Safe Software".  If there is no value for Department, I don't want the concatenated value to have the comma before Organisation (i.e. "Safe Software" not ", Safe Software").  This is done with a bunch of Testers and AttributeCreators.  There are more complex concatenations involving 10 source fields which would be difficult to do with nested if functions in the SQLExecutor.  So I assume that when I get to the end, I need to have the where clause so SQL Server knows which row to update.

 

 

Your comment has got me wondering whether I could call the SQLExecutor for each output field once the value has been generated, rather than waiting until the end.  Perhaps some of the simpler concatenations could be converted from Tester + AttributeCreator to SQLExecutor only...

 

Try something like

 

update mytable
set DepartmentOrganisation = 
  COALESCE(NULLIF(department, '') + ',', '') + 
  COALESCE(NULLIF(organisation, ''), '')
You'll only have to execute it once for the whole table. No need to use any transformers other than the Creator and the SQLExecutor. Should be much faster than using FME.
erik_jan
Rank
Userlevel 2
Badge +16

To avoid writing this code FME has the DatabaseUpdater transformer.

Have you considered using that?

tim_wood
Rank
Badge +7

To avoid writing this code FME has the DatabaseUpdater transformer.

Have you considered using that?

Thanks. I'll have a look at it. I wasn't really aware of it although I think I've heard of it. I thought maybe it was an old name for the SQLExecutor but obviously not.

 

david_r
Rank
Userlevel 5
Thanks. I'll have a look at it. I wasn't really aware of it although I think I've heard of it. I thought maybe it was an old name for the SQLExecutor but obviously not.

 

Just be aware that the performance of the DatabaseUpdater will probably be identical to that of a regular writer.
fmelizard
Rank
Userlevel 4
Badge +13
Well in this case it looks like SQLExecutor loses to SQL Server Non-Spatial Writer (with fme_db_operation set to UPDATE) for the 7 fields I want to update. Doing it the Writer way take 8 minutes for 100,000 records whereas SQLExecutor doesn't even manage 20,000 records in 20 minutes.

 

The other problem with my update statement in SQLExecutor is that nulls are getting turned into empty strings which doesn't happen with the Writer. I suspect dealing with that would slow things down even more...

 

Interesting to compare the massive performance differences between SQLExecutor and SQL Server Non-Spatial Writer for 2 different update tasks. Here, the Writer wins hands down, but in my other example, SQLExecutor was the clear winner:

 

https://knowledge.safe.com/questions/48437/sql-server-update-table-to-replace-empty-string-wi.html?childToView=48459#comment-48459

 

So it's worth trying both ways.

 

One possible reason for the speed diff is that the writer can optimize and reuse the query, whereas the SQLExecutor has to send the query down for each record. So the writer has more bulk opportunities. May not be the real reason but worth being aware of.

 

 

Reply


Community Stats

28,866
Posts
109,982
Replies
36,671
Members
Terms & ConditionsCookie settings
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.