Re: [PATCHES] patch for JDBC PreparedStatement

Alexander,

> Hello Barry,
>
> First of all, it is possible that this patch is no needed to be applied.
>
> I will try to explain the problem:
>
> Some times ago I have to insert some texts into filed with type text.
> Everything was great until I have read the log file of postges: I was
> shocked. There were unquoted non-printable chars like \r, \n, \t and all
> chars with code > 128. I don't realy know is it correct, by I have skiped
> this trouble.

These non-printable characters should not present any problem for the
JDBC driver or the server and should not need to be escaped.  The only
characters that would present a problem are the single-quote, backslash
and nullterminator (i.e. ', \, and \000).

>
> Later, I have to store files. I was not too smart and start to store files in
> the 'text' type fields. Text file were inserted correctly but binary files
> was not. The previous trouble has recalled in my head. I did not think too
> much - I have patched PreparedStatement. This patch I have submited to list.
>
> But .......
>
> When my boss found this he said 'this is not good - use bytea'. I have tried.

bytea is the correct data type to use for storing binary data.
Unfortunately the JDBC driver does not currently support the bytea data
type.  It will very soon, as I plan to work on that functionality this
weekend.  The 7.2 version of the driver will support using
setBytes()/getBytes() on a bytea column.


> And again. Who has create the rule that every char should be replaced by
> \\xxx !!! There is no functions for this action in the JDBC. I have written
> my own function (call it 'quote'). Now I have the following code:
>
> ..
> st.setString(1, quote(myCoolBinaryData));
> ...
>
> Of couse get my file back was no easy. I have to write the same function:
> ...
> String myCoolBinaryData = unquote(rs.getString("file"));
> ...
>

You have found a creative workaround for the lack of bytea support in
the JDBC driver.  What you have done should work very well.


> Finally:
> 1. I don't sure that my patch is should be applied.

I don't think it should.

> 2. Postgres have no function to work with big 'bytea' and 'text': there are
> no getBytes/setBytes, setInputStream/getOutputStream.
>

Yes this is true, and hopefully will be fixed soon.

> P.S. Sorry for my english. Hope my poem is written good enouth to understand
> it.
>

Your english is actually very good.

> With best wishes,
> Alexander
>

best wishes to you also,
--Barry


> On Tuesday 14 August 2001 23:29, you wrote:
>
>>I do not understand the need for this patch.  Can you more clearly
>>explain the problem you think exists in the current code that you are
>>trying to fix?  I don't understand why this patch is needed and I don't
>>think it should be applied until more clarification is provided.
>>
>>A specific test case would be great.
>>
>>thanks,
>>--Barry
>>
>>Alexander Litvinov wrote:
>>
>>>Hello,
>>>
>>>I don't know the correct way to post patches so I will try this.
>>>
>>>I have found the following bug:
>>>When I try to insert strings using class PreparedStatement from JDBC with
>>>non-printable chars it sometimes fail. During my investigations I have
>>>found that current version of PreparedStatement quotes only ' (ampersand)
>>>and \ (slash). I have made some changes and now PreparedStatement change
>>>all non-printable (possible some printable too) chars to \xxx where xxx
>>>is octal code of char.
>>>
>>>folder : src/interfaces/jdbc/org/postgresql/jdbc2
>>>file : PreparedStatement.java
>>>
>>>
>>>------------------------------------------------------------------------
>>>
>>>--- PreparedStatement.java.orig    Fri Feb 16 22:45:00 2001
>>>+++ PreparedStatement.java    Tue Jul 17 15:32:09 2001
>>>@@ -285,13 +285,22 @@
>>>               int i;
>>>
>>>               sbuf.append('\'');
>>>-              for (i = 0 ; i < x.length() ; ++i)
>>>-                {
>>>-                  char c = x.charAt(i);
>>>-                  if (c == '\\' || c == '\'')
>>>-                    sbuf.append((char)'\\');
>>>-                  sbuf.append(c);
>>>-                }
>>>+            for (i = 0 ; i < x.length() ; ++i)
>>>+          {
>>>+        char c = x.charAt(i);
>>>+        if (c == '\\' || c == '\'') {
>>>+          sbuf.append((char)'\\');
>>>+          sbuf.append(c);
>>>+        }
>>>+        else if (Character.isLetterOrDigit(c) || c == ' ' || c == ',' || c ==
>>>'.' || c == '@' || c == '-' || c == '+' || c =='/' || c == '%') +
>>>sbuf.append(c);
>>>+        else {
>>>+          String oct = Integer.toOctalString(c);
>>>+          if (oct.length() == 1) sbuf.append("\\00" + oct);
>>>+          else if (oct.length() == 2) sbuf.append("\\0" + oct);
>>>+          else if (oct.length() == 3) sbuf.append("\\" + oct);
>>>+        }
>>>+          }
>>>               sbuf.append('\'');
>>>               set(parameterIndex, sbuf.toString());
>>>             }
>>>
>>>
>>>------------------------------------------------------------------------
>>>
>>>
>>>---------------------------(end of broadcast)---------------------------
>>>TIP 5: Have you checked our extensive FAQ?
>>>
>>>http://www.postgresql.org/users-lounge/docs/faq.html
>>>
>>>PreparedStatement.java.patch
>>>
>>>Content-Type:
>>>
>>>text/x-diff
>>>Content-Encoding:
>>>
>>>base64
>>>
>>>
>>>------------------------------------------------------------------------
>>>Part 1.3
>>>
>>>Content-Type:
>>>
>>>text/plain
>>>Content-Encoding:
>>>
>>>binary
>>>
>