Parameters in UDTF - DB2 for IBM i Follow
Answered
I'm having some difficulty executing a query which uses a User Defined Table Function with parameters. I'm not sure if my issue is UDTF support, or if it is how I am specifying the parameters.
Example of what I'm doing:
SELECT *
FROM TABLE(MY_UDTF$('ABC123')) AS T;
OR
SELECT *
FROM TABLE(MY_UDTF$(?)) AS T;
Please note, when I run this query in another editor (more specific to DB2 on IBM i) - it works as expected.
Also note, I can get something simple such as the following to work with query parameters:
SELECT *
FROM MY_TABLE
WHERE MY_FIELD = ?;
Am I doing something wrong? Or are UDTF's just not supported in PHPStorm?
Please sign in to leave a comment.
What errors are you getting when running those queries in PhpStorm?
I get the parameter prompt, fill in the value and hit "Execute". Then I get this error:
```
2020-04-16 08:59:09] [42601][-104] [SQL0104] Token ) was not valid. Valid tokens: (. Cause . . . . . : A syntax error was detected at token ). Token ) is not a valid token. A partial list of valid tokens is (. This list assumes that the statement is correct up to the token. The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery . . . : Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token ). Correct the statement. The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL statement because it does not end with a valid clause.
```
Also, it seems like this isn't specific to UDTF's either. It occurs on UDF's in the "Select" clause as well. But for both UDF's and UDTF's, if there are no parameters (either hard-coded or using markers) it runs perfectly fine.
Example of UDF usage which triggers the behavior as well:
SELECT MY_UDF$(?)
from myTable;
And
SELECT MY_UDF$('ABC123')
from myTable;
Is there some sort of setting I need to change or syntax I need to use for specifying my parameters?
I also want to note again, the queries are syntactically and logically valid for the DB2 for i engine. I can run them without issue in more IBMi-native tools for running queries. It would just be nice if I could do it from within PHPStorm.
Could you please create issue with code examples and your thoughts on our tracker and attach there IDE SQL logs - it can be found in 'Help -> Show SQL log in..' menu ?
Created an issue:
https://youtrack.jetbrains.com/issue/WI-52791