Skip to main content
  • EN

In my SQLCreator Parameters, I have created the following statement in the SQL Statement:

SELECT * FROM contributor_dg.$(THEME)_$(AOI) WHERE SUPPLY_DATE = '$(SUPPLY_DATE)';

...$(THEME), $(AOI) and $(SUPPLY_DATE) are all published parameters.

I was able to run the process with no issues on version 2015.0. Since upgrading to 2015.1 and opening the same workbench with 2015.1, I can no longer expose the attributes based on the above sql statement. An error "Failed to execute the SQL Query" appears saying of an syntax error at or near "$".

This is the log that it generates:

FME Configuration: Using Named Connection values from 'xxx' for R_11

Creating reader for format: PostGIS

Trying to find a DYNAMIC plugin for reader named `POSTGIS'

FME Configuration: Source coordinate system for reader R_11[postgis] set to `LL-WGS84'

Coordinate System `LL-WGS84' parameters: CS_NAME=`LL-WGS84' DESC_NM=`WGS84 Lat/Longs' DT_NAME=`WGS84' GROUP=`LL' MAP_SCL=`1' PROJ=`LL' QUAD=`1' SCL_RED=`1' UNIT=`DEGREE'

FME API version of module 'POSTGIS' matches current internal version (3.7 20150407)

Opening POSTGIS reader for dataset 'xxx'

PostGIS client encoding: 'UTF8'

PostgreSQL 9.3.13 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit

2.1 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

Error executing SQL command ('declare "public_custom_query_crsr" cursor for SELECT * FROM contributor_dg.$(THEME)_$(AOI) WHERE SUPPLY_DATE = '$(SUPPLY_DATE)';'): 'ERROR: syntax error at or near "$"

A fatal error has occurred. Check the logfile above for details

Successfully closed POSTGIS database reader

Not sure if it's a bug, but it seems you no longer can reference parameters when populating attributes from the SQL query. Seems to be the same in FME 2017.0

Has it ever been the case that you can reference parameters when populating attributes from the query?

I didn't think it was so I suggested idea here

https://knowledge.safe.com/content/idea/32077/request-published-parameters-when-using-populate-f.html

Yeah, you can't.

that's why you have an attribute creator after the Creator to create attributes form parameters tot populate the attributes in your SQLExecutor...

(that just rolled out o' my pen..;)

I do not think it is the published parameter that is causing the issue.

I tried it on my Oracle database and it works as designed:

Could it be the quotes? If the value of the SUPPLY_DATE is quoted, you end up with two quotes and the $ will be outside the quotation.

I did not need to put in the semi colon at the end. Could it be that ?

I do not think it is the published parameter that is causing the issue.

I tried it on my Oracle database and it works as designed:

Could it be the quotes? If the value of the SUPPLY_DATE is quoted, you end up with two quotes and the $ will be outside the quotation.

I did not need to put in the semi colon at the end. Could it be that ?

The problem occurs when you want to specify the attributes to expose, and you use the "import from sql" button.

Yeah, you can't.

that's why you have an attribute creator after the Creator to create attributes form parameters tot populate the attributes in your SQLExecutor...

(that just rolled out o' my pen..;)

Not sure I follow. You can use published or private parameters embedded into your SQL, no problem, at least since FME 2015 or thereabout. It's only when importing the "parameters to expose" into the SQLExecutor that it doesn't work.
The problem occurs when you want to specify the attributes to expose, and you use the "import from sql" button.
So I would copy the query, remove the where clause as it does not define the attributes, fill in the parameters, populate from SQL and then paste the query back in.

 

 

ok, misunderstood the issue.. :0

well, didn't know the import would be considered a problem.

I at least thought everyone knows you can't very well run a script with missing statements (which is what happens when trying to populate from a parameterized query (whether using attributes or parameters).

Reply


Community Stats

30,129
Posts
114,377
Replies
37,768
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.