Parameters in UDTF - DB2 for IBM i

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?

7 comments
Comment actions Permalink

What errors are you getting when running those queries in PhpStorm?

0
Comment actions Permalink

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.

```

0
Comment actions Permalink

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;

0
Comment actions Permalink

Is there some sort of setting I need to change or syntax I need to use for specifying my parameters?

0
Comment actions Permalink

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.

0
Comment actions Permalink

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 ?

0

Please sign in to leave a comment.