Solved

User Parameters in MSSQL Spatial Writer's "SQL To Run After Write"

  • 30 March 2022
  • 3 replies
  • 5 views
lancec
Rank
Badge +3

Hello,

 

How can I utilize a User Parameter in the SQL To Run After Write?

 

I have a User Parameter (Text Value) with a default value of parcels@DateTimeFormat(@DateTimeNow(),%Y%m%d)

 

image 

The parameter works when setting the table name for the new table in the DB and the table is properly created:

image 

But when the user parameter is used in the SQL To Run After Write, it causes an error. Is that because the value of the User Parameter contains formatters? This line in the SQL:

UPDATE '$(ImportTableName)'

(I've tried with and without the quotes surrounding the parameter, btw)

 

evaluates to this in the console (and the SQL):

UPDATE 'parcels@DateTimeFormat(@DateTimeNow(),%Y%m%d%H%M)'

How can I use this parameter in the SQL? Alternatively, how can I utilize a name that is generated at workspace runtime for the table name and subsequently reference it in the SQL?

icon

Best answer by virtualcitymatt 31 March 2022, 13:06

View original

3 replies

virtualcitymatt
Rank
Userlevel 4
Badge +26

One trick you could try is to create a private parameter which references the $(ImportTableName) and use that one instead in your query. A scripted Parameter (python) would be the next to try if that doesn't work

 

Another option could be to replace the Writer with a FeatureWriter which will let you use an attribute value for the SQL To Run After Write field. Use a parameter fetcher before the writer to get the table name. I'm not really sure how it behave but it's worth testing. Either way it seems like a bug and someone at Safe (ping @Mark Stoakes​ ) should take a look.

 

What version of FME are you using?

lancec
Rank
Badge +3

One trick you could try is to create a private parameter which references the $(ImportTableName) and use that one instead in your query. A scripted Parameter (python) would be the next to try if that doesn't work

 

Another option could be to replace the Writer with a FeatureWriter which will let you use an attribute value for the SQL To Run After Write field. Use a parameter fetcher before the writer to get the table name. I'm not really sure how it behave but it's worth testing. Either way it seems like a bug and someone at Safe (ping @Mark Stoakes​ ) should take a look.

 

What version of FME are you using?

Thanks Matt. Still rockin' 2018.x over here.

 

I'll give your private parameter suggestion a shot.

lancec
Rank
Badge +3

One trick you could try is to create a private parameter which references the $(ImportTableName) and use that one instead in your query. A scripted Parameter (python) would be the next to try if that doesn't work

 

Another option could be to replace the Writer with a FeatureWriter which will let you use an attribute value for the SQL To Run After Write field. Use a parameter fetcher before the writer to get the table name. I'm not really sure how it behave but it's worth testing. Either way it seems like a bug and someone at Safe (ping @Mark Stoakes​ ) should take a look.

 

What version of FME are you using?

The python scripted parameter route worked! Thanks Matt.

Reply


Community Stats

28,731
Posts
109,530
Replies
36,566
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.