span8
span4
span8
span4
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?
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.mytableset field1 = '@Value(fieldA)'where ...
Other than that you seem to be on the right track.
field1 =If field1 has a value Then '@ReplaceString(@Value(field1),','')'Else null (a character string value "null",not thevalue)
Then,naturally the SQl statement should be changed to:
update owner.table set field1 = @Value(field1),...
To avoid writing this code FME has theDatabaseUpdatertransformer.
Have you considered using that?
Try something like the following: Creator -> SQLExecutor with the following code:
update owner.dtableset 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.
© 2019 亚搏在线Safe Software Inc |Legal