Re: Store a file in a bytea

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.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Store-a-file-in-a-bytea-tp3379578p3379578.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

Hi,

why do you not use ResultSet.getBinaryStream() to read the content?

Bye Thomas

Am Donnerstag, 10. Februar 2011 schrieb mmg:
> 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.

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.

Thank you for your reply. You were right: I was using the 8.x JDBC driver on
a 9.x database. I downloaded the latest JDBC drivers and now the problem is
gone. Thanks again!
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Store-a-file-in-a-bytea-tp3379578p3380114.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.