Re: Store a file in a bytea

I guess you are using PostgreSQL 9.x

You shouldn't get this (download newest driver). If it wont work I will try to
find something more.

mmg <mathias.degroof@gmail.com> Thursday 10 February 2011 17:01:52
> Hi all,
>
> I'm creating an application that will store files in a postresql database
> and which will retrieve them later on. I store my data in a bytea column.
> The problem I'm having is that when I retrieve the data later and write it
> out, the resulting file is not the same as the original. When I open the
> file it looks as though the ASCII codes were written out instead of the
> data itself (so it looks as if the ASCII was encoded again as ASCII,
> causing me to see ASCII codes when I open the file). I created a simple
> test program to demonstrate this:
>
>         Connection conn =
> DriverManager.getConnection("jdbc:postgresql://localhost/testdb", "test",
> "test");
>         conn.createStatement().executeUpdate("DELETE FROM testtable");
>         PreparedStatement pstmt = conn.prepareStatement("INSERT INTO
> testtable(data) VALUES(?)");
>         File file = new File("c:/t.txt");
>         FileInputStream fis = new FileInputStream(file);
>         pstmt.setBinaryStream(1, fis, (int) file.length());
>         pstmt.executeUpdate();
>         ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM
> testtable");
>         if (rs.next()) {
>             byte[] data = rs.getBytes("data");
>             FileOutputStream fos = new FileOutputStream("c:/t2.txt");
>             fos.write(data);
>             fos.close();
>         }
>         rs.close();
>         conn.close();
>
> There is one table: testtable with a data colum which is a bytea. I read
> the file c:/t.txt, store this as a stream then read it out again and write
> it to c:/t2.txt. When I open t2.txt, I see the ASCII codes instead of the
> actual text. I don't know what I'm doing wrong. I've tried reading it as a
> stream from the database, but that doesn't work either. Any help is much
> appreciated.