IBM DB2 Named Query won't Run

Ignition
1

I’m trying to run a named query on a IBM DB2 db, but I keep getting an error. The query runs perfect while using the testing tab. I have table bindings that uses named queries to this same db without issue. When I try to run the query through a button (just for testing), I get this error

Traceback (most recent call last):

  File "<event:actionPerformed>", line 2, in <module>



com.inductiveautomation.ignition.client.gateway_interface.GatewayException: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: com.ibm.as400.access.AS400JDBCSQLSyntaxErrorException: [SQL0199] Keyword FETCH not expected. Valid tokens: FOR USE SKIP WAIT WITH OPTIMIZE. Cause . . . . . :   The keyword FETCH was not expected here.  A syntax error was detected at keyword FETCH.  The partial list of valid tokens is FOR USE SKIP WAIT WITH OPTIMIZE. This list assumes that the statement is correct up to the unexpected keyword.  The error may be earlier in the statement but the syntax of the statement seems to be valid up to this point. Recovery  . . . :   Examine the SQL statement in the area of the specified keyword.  A colon or SQL delimiter may be missing. SQL requires reserved words to be delimited when they are used as a name. Correct the SQL statement and try the request again.


	caused by GatewayException: com.ibm.as400.access.AS400JDBCSQLSyntaxErrorException: [SQL0199] Keyword FETCH not expected. Valid tokens: FOR USE SKIP WAIT WITH OPTIMIZE. Cause . . . . . :   The keyword FETCH was not expected here.  A syntax error was detected at keyword FETCH.  The partial list of valid tokens is FOR USE SKIP WAIT WITH OPTIMIZE. This list assumes that the statement is correct up to the unexpected keyword.  The error may be earlier in the statement but the syntax of the statement seems to be valid up to this point. Recovery  . . . :   Examine the SQL statement in the area of the specified keyword.  A colon or SQL delimiter may be missing. SQL requires reserved words to be delimited when they are used as a name. Correct the SQL statement and try the request again.
	caused by Exception: com.ibm.as400.access.AS400JDBCSQLSyntaxErrorException: [SQL0199] Keyword FETCH not expected. Valid tokens: FOR USE SKIP WAIT WITH OPTIMIZE. Cause . . . . . :   The keyword FETCH was not expected here.  A syntax error was detected at keyword FETCH.  The partial list of valid tokens is FOR USE SKIP WAIT WITH OPTIMIZE. This list assumes that the statement is correct up to the unexpected keyword.  The error may be earlier in the statement but the syntax of the statement seems to be valid up to this point. Recovery  . . . :   Examine the SQL statement in the area of the specified keyword.  A colon or SQL delimiter may be missing. SQL requires reserved words to be delimited when they are used as a name. Correct the SQL statement and try the request again.

Ignition v7.9.10 (b2018112821)
Java: Oracle Corporation 1.8.0_191

this is the query:

SELECT PDPLNT
FROM PURCHASE.PURCHD
WHERE PDVEND = 808 and PDRECD = 0 and PDCMPL != 'C' and PDNUMB = :po
LIMIT 1

and my code to run the query:

system.db.runNamedQuery("BOM/PlantLookup", {'po':'129902'})

EDIT: I should also mention that this db is not my default for this project, I do have it selected as the db to run on in the named query section

2

Solved: It didn’t like the
LIMIT 1
I had to change it to
GROUP BY PDPLNT

1 Like
3

The solution is a workaround, SQL via DB2 should be able to use LIMIT and FETCH FIRST… both work when trying via the testing tab in Named Queries, but neither appear to work via the Query Browser or when the named query is called via a script… tested in v7.9.20. This is the error: “Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: com.ibm.db2.jcc.am.mo: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=SELECT
attribute_value
FROM
;BEGIN-OF-STATEMENT;<query_expr_body>, DRIVER=4.7.85”