PostgreSQL query_to_xml in JDBC PreparedStatement

Question

This question is very similar to Using query_to_xml in PostgreSQL with prepared statements however the solution there doesn't seem to be working in my case. It's actually a very odd situation. I have the following query which works fine.

String sql = "select query_to_xml('select col1,col2,col3 FROM table where col4 = '||?||' and col5 = '||?||'', true,true,'');";

and Prepared Statement like:

PreparedStatement ps0 = conn.prepareStatement(sql);
ps0.setInt(1, a);
ps0.setInt(2, b);
ResultSet rs0 = ps0.executeQuery();

However if I add another string parameter to the query it throws an error. For example;

String sql = "select query_to_xml('select col1,col2,col3 FROM table where col4 = '||?||' and col5 = '||?||' and col6 = '||?||'', true,true,'');";

PreparedStatement ps0 = conn.prepareStatement(sql);
ps0.setInt(1, a);
ps0.setInt(2, b);
ps0.setString(3, c);
ResultSet rs0 = ps0.executeQuery();

Throws the following error:

org.postgresql.util.PSQLException: ERROR: column "percent" does not exist

Where I had defined my string "c" as the word "percent". I assume this issue is actually because it is a string I'm passing into the query. I also tried hard coding the word percent into my where clause and not including it as a parameter in the prepared statement as follows;

String sql = "select query_to_xml('select col1,col2,col3 FROM table where col4 = '||?||' and col5 = '||?||' and col6 = 'percent'', true,true,'');";

but that gives the following error instead;

org.postgresql.util.PSQLException: ERROR: syntax error at or near "percent"

I'm guessing the solution is probably down to a single or double quote somewhere that needs to be added or removed, but I can't seem to see it.

RealSkeptic · Accepted Answer · 2016-05-12 13:38:41Z

1

It's a query inside a query. So it needs to have quotes inside quotes. And for this to work, the internal quotes should be doubled:

String sql = "select query_to_xml('select col1,col2,col3 FROM table where col4 = '||?||' and col5 = '||?||' and col6 = '''||?||'''', true,true,'');";

The SQL literal ' and col6 = ''' is going to be interpreted on the server side as and col6 = ', and '''' is going to be interpreted as ', thus you'll have a quote on each side of the string, which is what you need in order to tell the internal query that this is a string.

answered May 12, 2016 at 13:38

RealSkeptic

34.8k7 gold badges56 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tasslebear Over a year ago

Thanks @RealSkeptic, that worked a charm. Many thanks for the explanation too.

Collectives™ on Stack Overflow

PostgreSQL query_to_xml in JDBC PreparedStatement

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related