CODEWITHSUNDEEP
javapostgresqljdbc
tasslebear
tasslebear

Reputation: 209

PostgreSQL query_to_xml in JDBC PreparedStatement

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.

Upvotes: 0

Views: 393

Answers (1)

RealSkeptic
RealSkeptic

Reputation: 34638

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.

Upvotes: 1

Related Questions