Skip to main content
Solved

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

  • March 30, 2022
  • 3 replies
  • 45 views
lancec
Contributor
Forum|alt.badge.img+4

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?

Best answer by virtualcitymatt

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?

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.

3 replies

virtualcitymatt
Celebrity
Forum|alt.badge.img+47
  • virtualcitymattCelebrity
  • Celebrity
  • 2000 replies
  • Best Answer
  • March 31, 2022

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
Contributor
Forum|alt.badge.img+4
  • lancecContributor
  • Author
  • Contributor
  • 9 replies
  • March 31, 2022

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
Contributor
Forum|alt.badge.img+4
  • lancecContributor
  • Author
  • Contributor
  • 9 replies
  • March 31, 2022

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.

Community Stats

32,066
Posts
121,745
Replies
39,692
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.