Re: ⚠ Re: Bug in JDBC.driver CopyManager method when copying json output - Mailing list pgsql-jdbc

⚠ This email originated from outside of Brambles.  

I suggest this is a backend problem...

This is done with psql...

select to_json('my name is \"rabbit\"'::text);

           to_json

-----------------------------

 "my name is \\\"rabbit\\\""

(1 row)

test=# COPY (select to_json('my name is \"rabbit\"'::text)) TO STDOUT WITH NULL AS ''";

test"#

test=# COPY (select to_json('my name is \"rabbit\"'::text)) TO STDOUT WITH NULL AS '';

"my name is \\\\\\"rabbit\\\\\\""

Dave Cramer

davec@postgresintl.com

www.postgresintl.com

On Fri, 19 Oct 2018 at 10:12, Stadlmann, Gerson <Gerson.Stadlmann@ifco.com> wrote:

Dear JDBC-Devs,

i think i found an escaping bug that causes duplicate escaping with backslash when using jdbc drivers CopyManager with JSON results.

 

Sample Query:

select to_json( 'my name is "rabbit"'::text);

 

Executing this query as Statement

<java>

Statement statement = connection.createStatement()

ResultSet resultSet = statement.executeQuery("select to_json('my name is \"rabbit\"'::text)");

String result=resultSet.next().getString(1);// => "my name is \"rabbit\""

</java>

 

Execution of this query using CopyManager (see result file attachment)

This Execution does the duplicate escaping for JSON output causing invalid json text.

<java>

BaseConnection bc=(BaseConnection) connection;                                     

final CopyManager cm=new CopyManager(bc);

File someFile=new File("test.json");

try(FileWriter fw=new FileWriter(someFile)){

                cm.copyOut("COPY (select to_json('my name is \"rabbit\"'::text)) TO STDOUT WITH NULL AS ''", fw);

                // => my name is \\"rabbit\\" instead

}

</java>

 

 

My Driver:

<dependency>

           <groupId>org.postgresql</groupId>

           <artifactId>postgresql</artifactId>

           <version>42.2.5</version>

       </dependency>

 

My pg Server (Docker):

10.4 (Debian 10.4-2.pgdg90+1)

 

If that is required behavior I suggest to introduce another format “json” in addition to “text” and “csv”.

 

Kind regards

 

Gerson Stadlmann

Manager Software Development International

 

 

IFCO Systems Austria GmbH

Unterthalham Straße 2  |  4694 Ohlsdorf |  Austria
T: +43 (0)7612-787 782   |  M: +43 664 3855 154
gerson.stadlmann@ifco.com  |  www.ifco.com

 

A better supply chain serves us all. Let’s eat.

 

Board of Management: Wolfgang Orgeldinger / Chairman, Margit Kendler, Christoph Trixl, Heimo Thaler FN213156s, LG Wels, UID-Nr. ATU 52552107 SAVE PAPER - THINK BEFORE YOU PRINT

Board of Management: Wolfgang Orgeldinger / Chairman, Margit Kendler, Christoph Trixl, Heimo Thaler FN213156s, LG Wels, UID-Nr. ATU 52552107 SAVE PAPER - THINK BEFORE YOU PRINT