CODEWITHSUNDEEP
javaspring-jdbcnamed-parametersunnest
Ruslan S
Ruslan S

Reputation: 51

No value supplied for the SQL parameter, ']' added

I have a query that starts with WITH o_id AS (SELECT unnest(ARRAY[:orderids]) AS id) SELECT ...

In java code I have List orderIds=getOrderIds(); params.addValue("orderids", orderIds);

I'm getting Exception No value supplied for the SQL parameter 'orderids]': No value registered for key 'orderids]' Looks like Spring adds the bracket to the name of parameter.. How can I pass the orderids to the query?

Upvotes: 2

Views: 4547

Answers (2)

sawprogramming
sawprogramming

Reputation: 867

You can either add curly braces around the parameter name like this:

SELECT unnest(ARRAY[:{orderids}]) AS id

Or, as you've discovered, you can add spaces around the parameter declaration like this (though, only the space after the parameter declaration matters for parsing):

SELECT unnest(ARRAY[ :orderids ]) AS id

I've found that IntelliJ's SQL parser incorrectly considers the curly braces to be a syntax error, so I use the space option.

Upvotes: 0

Ruslan S
Ruslan S

Reputation: 51

Turns out an added space makes it work WITH o_id AS (SELECT unnest(ARRAY[:orderids ]) notice the space between ':orderids' and ']' Looks like Spring bug to me.

Upvotes: 3

Related Questions