• EN
Question

SQL Executor

  • 19 November 2012
  • 2 replies
  • 13 views
S
Badge
Hi!

 

 

I'm having a hard time getting SQLExecutor to interpret a SQL string from published parameters. However if I paste the exact same string directly into the transformer it executes without problem. Any ideas why???

 

 

The WHERE clause looks like this when i execute the workflow

 

 

WHERE ST_Contains(ST_GeomFromEWKT('SRID=32633;POLYGON ((183044.6766 6488587.2041,172720.1045 6526144.3677,205500.5582 6546330.9112,215416.0671 6503714.6016,183044.6766 6488587.2041))'),the_geom32633) and date >= driftsutslipp.date_to_int(date '$(date_from)') and date <= driftsutslipp.date_to_int(date '$(date_to)

 

 

And the Error is:

 

 

Error executing SQL query: ............................[edited ]

 

WHERE ST_Contains(ST_GeomFromEWKT('SRID=32633;POLYGON ((183044.6766 6488587.2041,172720.1045 6526144.3677,205500.5582 6546330.9112,215416.0671 6503714.6016,183044.6766 6488587.2041))'),the_geom32633) and date >= driftsutslipp.date_to_int(date '$(date_from)') and date <= driftsutslipp.date_to_int(date '$(date_to)') LIMIT 1'):

 

 

'ERROR:  invalid input syntax for type date: "$(date_from)"

 

LINE 1: ...32633) and date >= driftsutslipp.date_to_int(date '$(date_fr...

 

 

 

The string is published via a published parameter of type Choice with Alias as i have four different queries that one should be able to choose from.

 

 

 

Any help would be much appreciated.

 

 

Regards Stian

 

2 replies

sigtill
Rank
Badge +21
Its possible that you do not get hold of the Published parameter from within the SQLExecutor? What about using a ParameterFetcher first to get the attributes (and check that you have gotten the attributes in the Inspector)? 

 

 

Ellers hadde det vært greit å se på en enkel demo-workspace med kun dette problemet?

 

 

The real SigTill
S
Badge
Good answer that got me on the right track. The WHERE clause contains a PostGIS function to pass on "date" as a integer to the DB - this then fails since the SQLExecutor isn't able to get hold of the value from the published param. A parameter fetcher placed before the SQLExecutor did the trick.

 

 

Thanks!.

Reply


Community Stats

28,869
Posts
109,984
Replies
36,672
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.