Skip to main content
Solved

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

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?

View original
Did this help you find an answer to your question?

3 replies

virtualcitymatt
Celebrity
Forum|alt.badge.img+35

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
  • March 31, 2022
virtualcitymatt wrote:

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
  • March 31, 2022
virtualcitymatt wrote:

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


Helpful Members This Week

Community Stats

31,341
Posts
118,857
Replies
38,930
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.