[SOLVED] Use JDBC placeholder into string literal

Use JDBC placeholder into string literal

looking for a way to submit parameterized queries through dblink (PostgreSQL) using JDBC (PreparedStatement).

This is a sample query I want to use:

select * from dblink('host=localhost user=*** password=***', $$
    select 'abc'
$$) as tmp (n text)

select * from dblink(?, $$
    select ?
$$) as tmp (n text)

However, JDBC provides an option to pass only the first parameter (connstr). The second one is enclosed in a string literal and therefore not processed. At the moment I'm solving the problem by using concatenation, but I don't like this approach and that's why I'm looking for an alternative

Solution

You can use the PostgreSQL "format" function to inject the parameter on the server side, with proper escaping and quoting:

select * from dblink(?, format('select %L',?)) as tmp (n text)

JDBC probably provides helper functions which should also let you do this client side more safely than just naive concatenation, but I've not investigated that.