Question

SQLCreator unable to read published paramters in SQL statement

  • 12 April 2017
  • 8 replies
  • 8 views
M

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

8 replies

david_r
Rank
Userlevel 4

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

ebygomm
Rank
Userlevel 1
Badge +10

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

G
Badge +3

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..;)

erik_jan
Rank
Userlevel 2
Badge +12

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 ?

david_r
Rank
Userlevel 4

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.
david_r
Rank
Userlevel 4

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.
erik_jan
Rank
Userlevel 2
Badge +12
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.

 

 

G
Badge +3

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

28,661
Posts
109,324
Replies
36,528
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.