Skip to main content
Question

Published Parameter in SQL Statement to Execute Before Translation of ESRI GDB Reader

  • October 11, 2016
  • 7 replies
  • 165 views
ostoja
Contributor
Forum|alt.badge.img+6

I have an issue with running a SQL Statements which should be executed Before Translation of ESRI GDB Reader. SQL statement looks like like following:

FME_SQL_DELIMITER ;

 

DELETE FROM UTARC.FCL_FP_GEOD_ALTI_PT_EVW WHERE AUTOMODE = 1 AND OFFSET = $(OFFSET);

...where OFFSET is a published parameter - integer with default value of 50.

Error message returned is ORA-00911 - Invalid Character.

I tried also to set '$(OFFSET)' with apostrophes as string but no difference.

If i execute

FME_SQL_DELIMITER ;

 

DELETE FROM UTARC.FCL_FP_GEOD_ALTI_PT_EVW WHERE AUTOMODE = 1 AND OFFSET = 50;

workbench is successfully finished.

I imagine I am missing some point how I should reference published parameters in SQL script. Does somebody have idea what can be the problem?

Thanks in advance for any suggestion.

Dušan

7 replies

itay
Supporter
Forum|alt.badge.img+17
  • itaySupporter
  • Supporter
  • October 11, 2016

Hi @ostoja, probably the parameter value is not accessible for the before translation SQL statement, I would try a creator + feature reader combination.

Hope this helps

takashi
Influencer
  • takashiInfluencer
  • October 11, 2016

Hi @ostoja, a user parameter seems not to be accessible directly in a reader parameter setting, but it might work if you created a private parameter linked to the "SQL Statement to Execute Before Translation" parameter. You can create the private parameter with: Right-click on the "SQL Statement ..." parameter > select "Create User Parameter".

mark2atsafe
Safer
Forum|alt.badge.img+44
  • mark2atsafeSafer
  • Safer
  • October 11, 2016
takashi wrote:

Hi @ostoja, a user parameter seems not to be accessible directly in a reader parameter setting, but it might work if you created a private parameter linked to the "SQL Statement to Execute Before Translation" parameter. You can create the private parameter with: Right-click on the "SQL Statement ..." parameter > select "Create User Parameter".

I don't know if it's true that a user parameter isn't available in an FME parameter. For example, I can have a user select a path, and enter their name, and concatenate it together into a destination dataset parameter. The advanced desktop training, chapter 1 (parameters), exercise 3 shows me that this is possible.

 

 

Dušan, in the log window does it report what the SQL statement it is trying to send is? That might show how that is being rendered (or not). Turning on Log Debug in the FME options might help provide extra info.

 

 

I'm going to be out of the office for the rest of this week, so check in with the support team if you don't get any further with this issue.

 

takashi
Influencer
  • takashiInfluencer
  • October 12, 2016
takashi wrote:

Hi @ostoja, a user parameter seems not to be accessible directly in a reader parameter setting, but it might work if you created a private parameter linked to the "SQL Statement to Execute Before Translation" parameter. You can create the private parameter with: Right-click on the "SQL Statement ..." parameter > select "Create User Parameter".

Hi @Mark2AtSafe, I tested it with a PostgreSQL database and FME 2016.1.2.1, and yes, FME logged that the DBMS tried executing the statement and then detected an error.

 

However, it's not always. In my observation, looks like the error tends to occur immediately after setting the published parameter ("OFFSET" in this example).

 

Anyway it's true that an error may occur in a certain condition. Accessing via a private parameter is just an interim workaround.

 

ostoja
Contributor
Forum|alt.badge.img+6
  • ostojaContributor
  • Author
  • Contributor
  • October 12, 2016
mark2atsafe wrote:
I don't know if it's true that a user parameter isn't available in an FME parameter. For example, I can have a user select a path, and enter their name, and concatenate it together into a destination dataset parameter. The advanced desktop training, chapter 1 (parameters), exercise 3 shows me that this is possible.

 

 

Dušan, in the log window does it report what the SQL statement it is trying to send is? That might show how that is being rendered (or not). Turning on Log Debug in the FME options might help provide extra info.

 

 

I'm going to be out of the office for the rest of this week, so check in with the support team if you don't get any further with this issue.

 

Hi @Mark2AtSafe,

 

I receive following info in the logging window

 

INFORM|Executing Begin SQL: 'DELETE FROM UTARC.FCL_FP_GEOD_ALTI_PT_EVW WHERE AUTOMODE = 1 AND OFFSET = $(OFFSET)'

 

ERROR |Error executing SQL: 'Underlying DBMS error [ORA-00911: Invalid character]'

 

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

 

In the debuging mode I get one more error message extra

 

ERROR |BADNEWS: A fatal error has occurred. Check the logfile above for details (dynaread.cpp:215)

 

Thanks!

 

 

 

_
Forum|alt.badge.img
  • _trent
  • September 14, 2017
mark2atsafe wrote:
I don't know if it's true that a user parameter isn't available in an FME parameter. For example, I can have a user select a path, and enter their name, and concatenate it together into a destination dataset parameter. The advanced desktop training, chapter 1 (parameters), exercise 3 shows me that this is possible.

 

 

Dušan, in the log window does it report what the SQL statement it is trying to send is? That might show how that is being rendered (or not). Turning on Log Debug in the FME options might help provide extra info.

 

 

I'm going to be out of the office for the rest of this week, so check in with the support team if you don't get any further with this issue.

 

I was seeing similar behavior with a SQL Server writer (FME 2016.1 build 16609), but in my case I was using two parameters. One of the parameters was being replaced and one was not. Thinking maybe the first parameter always didn’t get replaced I added a dummy conditional using a parameter, and then all the parameters were replaced. Not sure what to make of that, but it is working for me now.

 

 

Original SQL:

 

UPDATE OMS2ARCFM.PROCESS_CONTROL

 

SET STATUS = 'UNLOCKED',

 

UPDATED_TMSTMP = CURRENT_TIMESTAMP

 

WHERE CONTROL_NAME = '$(ANNO_STG_FEATURECLASS)'

 

AND UPDATED_PROCESS = '$(DATASET)'

 

AND STATUS = 'LOCKED'

 

 

Log output of Original SQL that was executed:

 

UPDATE OMS2ARCFM.PROCESS_CONTROL

 

SET STATUS = 'UNLOCKED',

 

UPDATED_TMSTMP = CURRENT_TIMESTAMP

 

WHERE CONTROL_NAME = '$(ANNO_STG_FEATURECLASS)'

 

AND UPDATED_PROCESS = 'UNDERGROUNDSTRUCTURETEXT'

 

AND STATUS = 'LOCKED'

 

 

Updated SQL that worked:

 

UPDATE OMS2ARCFM.PROCESS_CONTROL

 

SET STATUS = 'UNLOCKED',

 

UPDATED_TMSTMP = CURRENT_TIMESTAMP

 

WHERE '1' != '$(ANNO_STG_FEATURECLASS)'

 

AND CONTROL_NAME = '$(ANNO_STG_FEATURECLASS)'

 

AND UPDATED_PROCESS = '$(DATASET)'

 

AND STATUS = 'LOCKED'

 

 

 

J
Forum|alt.badge.img
  • j
  • February 27, 2018

Hi,

I am using a published parameter to obtain a string value and then pass this to an SQL Executor to filter on the value,

what I have found is the returned value from the text value does not get evaluated by the SQL Executor as a string but as a variable as it has no enclosing apostrophes,

enclosing the FME $(...) type variable with apostrophes does not work because then the whole variable is seen as a string and not evaluated at all. CASTing to a varchar also does not work.

I have used the workaround as above, a private parameter calls the published parameter, returns it but allows enclosing apostrophes to be added,

When the Private parameter is called in the SQL it finds the value to be the string with enclosing apostrophes,

e.g. Published Param: FindID

Private Param: '$(FindID)'

SQL Code: where table1.ID like $(FindIDFixIssue)

Reply


Helpful Members This Week

Community Stats

31,313
Posts
118,738
Replies
38,900
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.