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
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 @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.
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.
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 MORA-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!
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'
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)
