Обсуждение: 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

Στις 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:

Στις 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