Re: PreparedStatement.toString() creates valid SQL; was: Q: use setObject also for int or string - Mailing list pgsql-jdbc

Hi Kris,

in my Java code I need to retrieve the SQL
from a PreparedStatement to create from this another statement.

I did this and it would be great if you could have a look
into the attached patch files and make comments.

It has to set standard_conforming_strings to false
in any case to create valid sql:
If I use setString(1, "a\\b");
the real stored characters are 'a','\','b'.
If I would use standard_conforming_strings=true
the output would be "a\b"
but with set to false the output is "a\\b" which is the correct one.

I did not understand what you mean with InputStream.

Michael

Kris Jurka schrieb:
> Michael Enke wrote:
>> The PreparedStatement.toString() returns the query,
>> replaced with available parameter, but string constants are not
>> enclosed in apostrophes:
>>
>> 74: select * from a where a='a'
>> 83: select * from a where a=a
>>
>> The 74 version put this into apostrophes if setString was used.
>> If setInt was used, no apostrophes were output.
>>
>
> We've never claimed that the output of PreparedStatement.toString would
> produce valid SQL.  There are certainly other problems with it than just
> missing apostrophes.  It currently doesn't escape values, so it breaks
> if your data contains embedded ' or \.  It doesn't understand whether
> the server has standard_conforming_strings enabled or not.   What would
> it to do with a parameter that is an InputStream?  If the toString code
> reads it, the driver cannot re-read it to send it to the server when
> executed.
>
> The InputStream is perhaps an unusual case, so I wouldn't be opposed if
> someone wanted to make the simple cases work better, but it's not
> something I'm particularly excited about.
>
> Kris Jurka


--
WINCOR NIXDORF International GmbH
Sitz der Gesellschaft: Paderborn
Registergericht Paderborn HRB 3507
Geschäftsführer: Eckard Heidloff (Vorsitzender), Stefan Auerbach, Dr. Jürgen Wunram
Vorsitzender des Aufsichtsrats: Karl-Heinz Stiller
Steuernummer: 339/5884/0020 - Ust-ID Nr.: DE812927716 - WEEE-Reg.-Nr. DE44477193

Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich
erhaltenhaben, informieren Sie bitte sofort den Absender und vernichten Sie diese E-Mail. Das unerlaubte Kopieren sowie
dieunbefugte Weitergabe dieser E-Mail ist nicht gestattet. 

This e-mail may contain confidential information. If you are not the intended recipient (or have received this e-mail
inerror) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or
distributionof the material in this e-mail is strictly forbidden.  

*** SimpleQuery.java    2008-09-21 21:15:55.000000000 +0200
--- /home/me/JDBC/postgresql-jdbc-8.3-603.src/org/postgresql/core/v3/SimpleQuery.java.orig    2008-09-21
18:51:55.000000000+0200 
*************** class SimpleQuery implements V3Query {
*** 35,66 ****

      public String toString(ParameterList parameters) {
          StringBuffer sbuf = new StringBuffer(fragments[0]);
-         int[] typeOIDs = (parameters == null) ? null : parameters.getTypeOIDs();
          for (int i = 1; i < fragments.length; ++i)
          {
              if (parameters == null)
                  sbuf.append('?');
!             else {
!         String s = parameters.toString(i);
!         if("NULL".equals(s) ||
!            typeOIDs[i-1] == Oid.INT2 || typeOIDs[i-1] == Oid.INT4 || typeOIDs[i-1] == Oid.INT8 ||
!            typeOIDs[i-1] == Oid.NUMERIC || typeOIDs[i-1] == Oid.FLOAT4 || typeOIDs[i-1] == Oid.FLOAT8 ||
!            typeOIDs[i-1] == Oid.OID) {
!             sbuf.append(s);
!         } else {
!             StringBuffer p = null;
!             try {
!             // last boolean false is: standardConformingStrings
!             // if true:  one backslash is output as one backslash
!             // if false: one backslash is output as two backslashes
!                 p = Utils.appendEscapedLiteral(null, s, false);
!                 if(p.indexOf("\\") != -1) sbuf.append(" E");
!             } catch(java.sql.SQLException sqle) {
!                 p = new StringBuffer(sqle.toString());
!             }
!                     sbuf.append("'" + p + "'");
!         }
!         }
              sbuf.append(fragments[i]);
          }
          return sbuf.toString();
--- 35,46 ----

      public String toString(ParameterList parameters) {
          StringBuffer sbuf = new StringBuffer(fragments[0]);
          for (int i = 1; i < fragments.length; ++i)
          {
              if (parameters == null)
                  sbuf.append('?');
!             else
!                 sbuf.append(parameters.toString(i));
              sbuf.append(fragments[i]);
          }
          return sbuf.toString();
*** V2Query.java    2008-09-21 21:17:34.000000000 +0200
--- /home/me/JDBC/postgresql-jdbc-8.3-603.src/org/postgresql/core/v2/V2Query.java.orig    2008-09-21 18:52:19.000000000
+0200
*************** class V2Query implements Query {
*** 86,117 ****

      public String toString(ParameterList parameters) {
          StringBuffer sbuf = new StringBuffer(fragments[0]);
-         int[] typeOIDs = (parameters == null) ? null : parameters.getTypeOIDs();
          for (int i = 1; i < fragments.length; ++i)
          {
              if (parameters == null)
                  sbuf.append("?");
!             else {
!         String s = parameters.toString(i);
!                 if("NULL".equals(s) ||
!            typeOIDs[i-1] == Oid.INT2 || typeOIDs[i-1] == Oid.INT4 || typeOIDs[i-1] == Oid.INT8 ||
!                    typeOIDs[i-1] == Oid.NUMERIC || typeOIDs[i-1] == Oid.FLOAT4 || typeOIDs[i-1] == Oid.FLOAT8 ||
!                    typeOIDs[i-1] == Oid.OID) {
!                     sbuf.append(s);
!                 } else {
!                     StringBuffer p = null;
!                     try {
!                         // last boolean false is: standardConformingStrings
!                         // if true:  one backslash is output as one backslash
!                         // if false: one backslash is output as two backslashes
!                         p = Utils.appendEscapedLiteral(null, s, false);
!                         if(p.indexOf("\\") != -1) sbuf.append(" E");
!                     } catch(java.sql.SQLException sqle) {
!                         p = new StringBuffer(sqle.toString());
!                     }
!                     sbuf.append("'" + p + "'");
!                 }
!         }
              sbuf.append(fragments[i]);
          }
          return sbuf.toString();
--- 86,97 ----

      public String toString(ParameterList parameters) {
          StringBuffer sbuf = new StringBuffer(fragments[0]);
          for (int i = 1; i < fragments.length; ++i)
          {
              if (parameters == null)
                  sbuf.append("?");
!             else
!                 sbuf.append(parameters.toString(i));
              sbuf.append(fragments[i]);
          }
          return sbuf.toString();