Skip to main content
  • EN

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?

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?

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.

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

30,168
Posts
114,527
Replies
37,825
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.