Обсуждение: BLOB help - yes I've read around!
Hi all
I'm a Java dev, new to postgres. I've just migrated an app during development from Derby to Postgres (due to better indexing features), but am very confused by the failing BLOB implementations.
PostgreSQL: 8.3.7
PostgreSQL-JDBC: 8.3-604
JDK: 6
A lot of what I've seen in searches and even in the google groups page, seems to be related to older jdbc drivers, where BLOBs were supported, both for OID and bytea. I'm currently getting 'unimplemented' exceptions doing anything with BLOBs.
My application saves sound files as byte arrays to the database.
Q1: Given that it's sound and not text, would it be safe to use bytea given that certain characters need to be escaped?
Q2: How on earth do I use the BLOB methods? In particular setBlob(int parameterIndex, InputStream inputStream) and/or createBlob() and setBlob(int parameterIndex, Blob x)?
Thanks a lot
Brad
I'm a Java dev, new to postgres. I've just migrated an app during development from Derby to Postgres (due to better indexing features), but am very confused by the failing BLOB implementations.
PostgreSQL: 8.3.7
PostgreSQL-JDBC: 8.3-604
JDK: 6
A lot of what I've seen in searches and even in the google groups page, seems to be related to older jdbc drivers, where BLOBs were supported, both for OID and bytea. I'm currently getting 'unimplemented' exceptions doing anything with BLOBs.
My application saves sound files as byte arrays to the database.
Q1: Given that it's sound and not text, would it be safe to use bytea given that certain characters need to be escaped?
Q2: How on earth do I use the BLOB methods? In particular setBlob(int parameterIndex, InputStream inputStream) and/or createBlob() and setBlob(int parameterIndex, Blob x)?
Thanks a lot
Brad
Στις Tuesday 07 April 2009 16:31:11 ο/η Brad Milne έγραψε: > Hi all > > I'm a Java dev, new to postgres. I've just migrated an app during > development from Derby to Postgres (due to better indexing features), > but am very confused by the failing BLOB implementations. > > PostgreSQL: 8.3.7 > PostgreSQL-JDBC: 8.3-604 > JDK: 6 > > A lot of what I've seen in searches and even in the google groups page, > seems to be related to older jdbc drivers, where BLOBs were supported, > both for OID and bytea. I'm currently getting 'unimplemented' exceptions > doing anything with BLOBs. > > My application saves sound files as byte arrays to the database. > > Q1: Given that it's sound and not text, would it be safe to use bytea > given that certain characters need to be escaped? I have been storing binary data in bytea for ages with postgresql, with no major problems. Performance might be an issue. It is a good practice to store along with the bytea value, its mime type as well. So in your binary library you could have mp3s,oggs,mpegs, ms docs, jpegs, pdfs, and your servlet would know exactly the content type to serve to the client. Also to answer your question about safeness/escaping, etc... those are taken care by the jdbc driver. > Q2: How on earth do I use the BLOB methods? In particular setBlob(int > parameterIndex, InputStream inputStream) and/or createBlob() and > setBlob(int parameterIndex, Blob x)? > I never did anything with BLOBs so no experience from me here. > Thanks a lot > Brad > -- Achilleas Mantzios
Thanks Achilleas
So how do you save the bytea data? I get:
Method org.postgresql.jdbc4.Jdbc4PreparedStatement.setBinaryStream(int, InputStream) is not yet implemented
Achilleas Mantzios wrote:
So how do you save the bytea data? I get:
Method org.postgresql.jdbc4.Jdbc4PreparedStatement.setBinaryStream(int, InputStream) is not yet implemented
Achilleas Mantzios wrote:
Στις Tuesday 07 April 2009 16:31:11 ο/η Brad Milne έγραψε:Hi all I'm a Java dev, new to postgres. I've just migrated an app during development from Derby to Postgres (due to better indexing features), but am very confused by the failing BLOB implementations. PostgreSQL: 8.3.7 PostgreSQL-JDBC: 8.3-604 JDK: 6 A lot of what I've seen in searches and even in the google groups page, seems to be related to older jdbc drivers, where BLOBs were supported, both for OID and bytea. I'm currently getting 'unimplemented' exceptions doing anything with BLOBs. My application saves sound files as byte arrays to the database. Q1: Given that it's sound and not text, would it be safe to use bytea given that certain characters need to be escaped?I have been storing binary data in bytea for ages with postgresql, with no major problems. Performance might be an issue. It is a good practice to store along with the bytea value, its mime type as well. So in your binary library you could have mp3s,oggs,mpegs, ms docs, jpegs, pdfs, and your servlet would know exactly the content type to serve to the client. Also to answer your question about safeness/escaping, etc... those are taken care by the jdbc driver.Q2: How on earth do I use the BLOB methods? In particular setBlob(int parameterIndex, InputStream inputStream) and/or createBlob() and setBlob(int parameterIndex, Blob x)?I never did anything with BLOBs so no experience from me here.Thanks a lot Brad
Στις Tuesday 07 April 2009 17:04:19 ο/η Brad Milne έγραψε: > Thanks Achilleas > > So how do you save the bytea data? I get: > Method org.postgresql.jdbc4.Jdbc4PreparedStatement.setBinaryStream(int, > InputStream) is not yet implemented > I never worked with JDK 1.6 and JDBC4, from http://jdbc.postgresql.org/download.html we see: "Support for JDBC4 methods is limited. The driver builds, but the majority of new methods are stubbed out." i dont know if that is relevant, i just saw the "jdbc4" part in your org.postgresql.jdbc4.Jdbc4PreparedStatement.setBinaryStream Having said that, i usually call setBytes. > > > Achilleas Mantzios wrote: > > Στις Tuesday 07 April 2009 16:31:11 ο/η Brad Milne έγραψε: > > > >> Hi all > >> > >> I'm a Java dev, new to postgres. I've just migrated an app during > >> development from Derby to Postgres (due to better indexing features), > >> but am very confused by the failing BLOB implementations. > >> > >> PostgreSQL: 8.3.7 > >> PostgreSQL-JDBC: 8.3-604 > >> JDK: 6 > >> > >> A lot of what I've seen in searches and even in the google groups page, > >> seems to be related to older jdbc drivers, where BLOBs were supported, > >> both for OID and bytea. I'm currently getting 'unimplemented' exceptions > >> doing anything with BLOBs. > >> > >> My application saves sound files as byte arrays to the database. > >> > >> Q1: Given that it's sound and not text, would it be safe to use bytea > >> given that certain characters need to be escaped? > >> > > > > I have been storing binary data in bytea for ages with postgresql, > > with no major problems. > > Performance might be an issue. > > It is a good practice to store along with the bytea value, > > its mime type as well. > > So in your binary library you could have mp3s,oggs,mpegs, ms docs, jpegs, pdfs, > > and your servlet would know exactly the content type to serve to the client. > > > > Also to answer your question about safeness/escaping, etc... > > those are taken care by the jdbc driver. > > > > > >> Q2: How on earth do I use the BLOB methods? In particular setBlob(int > >> parameterIndex, InputStream inputStream) and/or createBlob() and > >> setBlob(int parameterIndex, Blob x)? > >> > >> > > > > I never did anything with BLOBs so no experience from me here. > > > > > >> Thanks a lot > >> Brad > >> > >> > > > > > > > > > > -- Achilleas Mantzios
I forgot to ask you which jdbc version do you use. I presume (with Postgresql 8.3.x) you use postgresql-8.3-604.jdbc4.jar -- Achilleas Mantzios
Brad Milne, 07.04.2009 16:04: > So how do you save the bytea data? I get: > Method org.postgresql.jdbc4.Jdbc4PreparedStatement.setBinaryStream(int, > InputStream) is not yet implemented > The following has been working for me since I think 8.1 (and the corresponding JDBC driver): Statement stmt = con.prepareStatement( "INSERT INTO blob_table (id, blob_col) VALUES (?,?)"); stmt.setInt(1, 42); File blobFile = new File("my_picture.jpg"); InputStream in = new FileInputStream(blobFile); stmt.setBinaryStream(2, r, (int)f.length()); stmt.executeUpdate(); con.commit(); Thomas
Thanks to you both This works for me Thomas. For completeness, here is the setter again with a couple of fixes: stmt.setBinaryStream(2,in,in.available()); or optionally (if it is a file): stmt.setBinaryStream(2,in,blobFile.length()); Thomas Kellerer wrote: > Brad Milne, 07.04.2009 16:04: >> So how do you save the bytea data? I get: >> Method >> org.postgresql.jdbc4.Jdbc4PreparedStatement.setBinaryStream(int, >> InputStream) is not yet implemented >> > > The following has been working for me since I think 8.1 (and the > corresponding JDBC driver): > > Statement stmt = con.prepareStatement( > "INSERT INTO blob_table (id, blob_col) VALUES (?,?)"); > stmt.setInt(1, 42); > > File blobFile = new File("my_picture.jpg"); > InputStream in = new FileInputStream(blobFile); > stmt.setBinaryStream(2, r, (int)f.length()); > stmt.executeUpdate(); > con.commit(); > > Thomas > >
Brad Milne wrote on 07.04.2009 23:34: > For completeness, here is the setter again > with a couple of fixes: > > stmt.setBinaryStream(2,in,in.available()); Do not use availabe() This will *not* give you the number of bytes that are "available" in the stream. Read the Javadocs carefully: it will give you the number of bytes that can be /read without blocking/ Most of the time this will not be the length of the stream. Thomas
Thomas Kellerer wrote: > Brad Milne wrote on 07.04.2009 23:34: >> For completeness, here is the setter again with a couple of fixes: >> >> stmt.setBinaryStream(2,in,in.available()); > > Do not use availabe() > > This will *not* give you the number of bytes that are "available" in > the stream. > > Read the Javadocs carefully: it will give you the number of bytes that > can be /read without blocking/ > > Most of the time this will not be the length of the stream. > Good point Thomas, I hear you. I shouldn't have written that hear as others could follow that. In my implementation it is ok as the stream is complete prior to persisting it. I have an AudioInputStream object, but it is always complete. If you think I should be therefore using a different method/db object then I'd be pleased to hear. Thanks Brad
Brad Milne, 07.04.2009 23:55: >> Do not use availabe() >> >> This will *not* give you the number of bytes that are "available" in >> the stream. >> >> Read the Javadocs carefully: it will give you the number of bytes that >> can be /read without blocking/ >> >> Most of the time this will not be the length of the stream. >> > Good point Thomas, I hear you. I shouldn't have written that hear as > others could follow that. In my implementation it is ok as the stream is > complete prior to persisting it. I have an AudioInputStream object, but > it is always complete. If you think I should be therefore using a > different method/db object then I'd be pleased to hear. I have never used AudioInputStream. If that one reports the real size when calling available() then it would be the firstInputStream that I have seen that does that ;) If you do use available() you should verify that the size of your BLOB (bytea) in the database is really what you expected.You have to test with sizes that exceed any potential buffer that takes place in the input stream, and I would inspectthe source code for available(). Thomas