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.
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)
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.
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 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.
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)
Then, naturally the SQl statement should be changed to:
update owner.table
set field1 = @Value(field1),
...
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.
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.
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.
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)
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?
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...
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.
To avoid writing this code FME has the DatabaseUpdater transformer.
Have you considered using that?
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.
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.
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.
