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(dot)degroof(at)gmail(dot)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.