Обсуждение: BLOB support problem !!!!
Hi all,
I have some java code to r/w a binary file in the database.
According to the tutorial (http://doc.postgresintl.com/jdbc/ch08.html#jdbc-binary-data-example)
there are 2 ways:
1) To use a 'bytea' type in the database
The first one throw an OutofMemoryException for files > 1.5 Mbyte (is this some kind of bug ??)
And the second one doesn't throw any Exception or Error but it does NOT write anything in the database !!
I did something wrong ?
Has anyone experience in r/w blobs in postgres through jdbc ??
Thanks in advance
George
I have some java code to r/w a binary file in the database.
According to the tutorial (http://doc.postgresintl.com/jdbc/ch08.html#jdbc-binary-data-example)
there are 2 ways:
1) To use a 'bytea' type in the database
CREATE TABLE images (imgname text, img bytea);
File file = new File("myimage.gif"); FileInputStream fis = new FileInputStream(file); PreparedStatement ps = conn.prepareStatement("INSERT INTO images VALUES (?, ?)"); ps.setString(1, file.getName()); ps.setBinaryStream(2, fis, file.length()); ps.executeUpdate(); ps.close(); fis.close();2) Use the LargeObject API and the 'oid' type in the databse
CREATE TABLE imageslo (imgname text, imgoid oid); // All LargeObject API calls must be within a transaction block conn.setAutoCommit(false); // Get the Large Object Manager to perform operations with LargeObjectManager lobj = ((org.postgresql.PGConnection)conn).getLargeObjectAPI(); // Create a new large object int oid = lobj.create(LargeObjectManager.READ | LargeObjectManager.WRITE); // Open the large object for writing LargeObject obj = lobj.open(oid, LargeObjectManager.WRITE); // Now open the file File file = new File("myimage.gif"); FileInputStream fis = new FileInputStream(file); // Copy the data from the file to the large object byte buf[] = new byte[2048]; int s, tl = 0; while ((s = fis.read(buf, 0, 2048)) > 0) { obj.write(buf, 0, s); tl += s; } // Close the large object obj.close(); // Now insert the row into imageslo PreparedStatement ps = conn.prepareStatement("INSERT INTO imageslo VALUES (?, ?)"); ps.setString(1, file.getName()); ps.setInt(2, oid); ps.executeUpdate(); ps.close(); fis.close();I tried both of them BUT
The first one throw an OutofMemoryException for files > 1.5 Mbyte (is this some kind of bug ??)
And the second one doesn't throw any Exception or Error but it does NOT write anything in the database !!
I did something wrong ?
Has anyone experience in r/w blobs in postgres through jdbc ??
Thanks in advance
George
GP wrote: > I tried both of them BUT > The first one throw an OutofMemoryException for files > 1.5 Mbyte (is > this some kind of bug ??) It's a known issue. There are patches pending to fix it that will hopefully be applied soon. A workaround is to increase your JVM's heap size. > And the second one doesn't throw any Exception or Error but it does NOT > write anything in the database !! It looks like it's missing a conn.commit() call at the end, so none of the changes will actually be committed.. -O
Thanks Oliver The conn.commit() sloved my problems ! Oliver Jowett wrote: > GP wrote: > >> I tried both of them BUT >> The first one throw an OutofMemoryException for files > 1.5 Mbyte (is >> this some kind of bug ??) > > > It's a known issue. There are patches pending to fix it that will > hopefully be applied soon. A workaround is to increase your JVM's heap > size. > >> And the second one doesn't throw any Exception or Error but it does >> NOT write anything in the database !! > > > It looks like it's missing a conn.commit() call at the end, so none of > the changes will actually be committed.. > > -O