Question

How to prevent single quotes turning my parameter into a string when using a workspace runner?

  • 25 November 2016
  • 13 replies
  • 18 views
P
Badge

I have run the below query successfully on a spatial database in a standalone workspace:

st_intersects(st_setsrid(st_geomfromtext('$(poly)'), 27700), geom)

The poly parameter brings in the relevant wkt.

When I run this query with the paramater passed through a master workspace it treats '$poly' as a string rather than a parameter. If i remove the single quotes the wkt comes through as expected, however the single quotes are part of the spatial query so the sql fails.

Has anyone got any advice on how to get around this problem?

Many Thanks,

13 replies

G
Badge
What is the type of parameter ?

 

 

itay
Rank
Badge +16

I would try the following: remove the quotes from the parameter and add it to the sql query.

fmelizard
Rank
Userlevel 4
Badge +13

I can advise that FME doesn't ever touch ' on its own. That said, if you're passing the entire query as a parameter, it is possible that the operating system may get involved with the single quotes.

Would you be able to post a stripped down version of both the master and the child workspace?

Without knowing more details, I'd definitely try to pass "poly" from the master to the child, and then build the query string up in the child. I don't see any fundamental reason that this wouldn't work.

P
Badge

"poly" is passed from the master to the child and used in the postgis reader, so the query can't be 'built up' in the child workspace as it is used straightaway (unless I am missing something). An example workspace would be, a parameter that is a wkt string (set the default to a wkt string as a test), this is passed from master to child and used in the sql where clause, on the postgis reader

st_intersects(st_setsrid(st_geomfromtext('$(poly)'), 27700), geom)

 

I tried concatenating the parameter with ' in the master and passed this through as an attribute into the child workspace, but the text was broken at the first comma. If there is a way to prevent delimiting attributes I would be able to use this workaround.

 

Thanks,

G
Badge

@proudgis an another option.

In your master :

  1. Before your translation, create a temporary table
  2. In master, clean the table and insert your parameter with sqlexecutor
  3. In child, get the last record and clean your table
No parameter issue :)
P
Badge

Thanks for the suggestion @Gistack. This does seem a bit of a 'dirty' solution. I could run the process entirely within the master as a solution but would prefer to move it to a child workspace for future development. This issue has occurred to me on a couple of occassions and would love an explanation/solution.

G
Badge +3

create a attirubte with value someattribute = '$poly' (including quoutes)

And the sql as

st_intersects(st_setsrid(st_geomfromtext(@value(someattribute), 27700), geom)

P
Badge
@gio

I have tried exactly that. When I pass the attribute through to the child and run the query it delimits and cuts the data to the first comma. If there is a way to prevent the attribute being delimited. I find both issues so bizarre, it's such a simple task, but neither work for one reason or another.

G
Badge +3

Ok.

Did you try putting entire sql in a attribute?

"Query" = st_intersects(st_setsrid(st_geomfromtext('$(poly)'), 27700), geom)

And the pass that attribute trough the workspacecaller?

To create the attribute take care to use the string editor (NOT the arithmiticeditor)

Maybe you can use a sql creator/executor to do the reading in the child.

I never use a reader in the children (partly of the limitations of the where clause) , always an sql creator/executor and create dynamic queries by passing attributes and parameters, to build up entire queries.

P
Badge

I did not. I have just tried putting the entire query through as an attribute and I get the same problem.

poly value is

POLYGON ((436958.714 174250.836, 436958.714 174251.1, 436971.632 174267.445, 436994.041 174263.755, 437039.914 174255.318, 437071.55 174246.882, 437086.314 174209.973, 437050.986 174194.155, 437008.014 174177.809, 436985.868 174167.527, 436954.232 174174.909, 436946.323 174223.682, 436959.241 174250.836, 436958.714 174250.836))

The attribute passed to the child

This comes through as:

{st_intersects(st_setsrid(st_geomfromtext('POLYGON ((436958.714 174250.836} {-- 436958.714 174251.1} { 436971.632 174267.445} {-- 436994.041 174263.755} { 437039.914 174255.318} {-- 437071.55 174246.882} { 437086.314 174209.973} {-- 437050.986 174194.155} { 437008.014 174177.809} {-- 436985.868 174167.527} { 436954.232 174174.909} {-- 436946.323 174223.682} { 436959.241 174250.836} {-- 436958.714 174250.836))')} { 27700)} {-- geom)

As you can see comma's are removed, breaking the string.

ebygomm
Rank
Userlevel 1
Badge +21

Instead of using a reader can you use a featurereader which would allow you to do some string manipulation prior?

G
Badge +3

Automatic placeholder.

at least it replaced the comma but let the entire string trough, now you can replace all "}{" by a comma...:)

But it means you need to use a sql creator or executor to execute your spatial query

ebygomm
Rank
Userlevel 1
Badge +21

I did not. I have just tried putting the entire query through as an attribute and I get the same problem.

poly value is

POLYGON ((436958.714 174250.836, 436958.714 174251.1, 436971.632 174267.445, 436994.041 174263.755, 437039.914 174255.318, 437071.55 174246.882, 437086.314 174209.973, 437050.986 174194.155, 437008.014 174177.809, 436985.868 174167.527, 436954.232 174174.909, 436946.323 174223.682, 436959.241 174250.836, 436958.714 174250.836))

The attribute passed to the child

This comes through as:

{st_intersects(st_setsrid(st_geomfromtext('POLYGON ((436958.714 174250.836} {-- 436958.714 174251.1} { 436971.632 174267.445} {-- 436994.041 174263.755} { 437039.914 174255.318} {-- 437071.55 174246.882} { 437086.314 174209.973} {-- 437050.986 174194.155} { 437008.014 174177.809} {-- 436985.868 174167.527} { 436954.232 174174.909} {-- 436946.323 174223.682} { 436959.241 174250.836} {-- 436958.714 174250.836))')} { 27700)} {-- geom)

As you can see comma's are removed, breaking the string.

It appears that you cannot send a comma in a parameter at all...

 

Just trying to send Apple,Orange results in just Apple in the child workbench

 

 

https://knowledge.safe.com/questions/31426/published-parameter-values-gets-cutoff.html

 

Reply


Community Stats

28,723
Posts
109,510
Replies
36,559
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.