Re: BLOB help - yes I've read around!

Lists: pgsql-jdbc
From: Brad Milne <mail_4brad(at)yahoo(dot)co(dot)uk>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: BLOB help - yes I've read around!
Date: 2009-04-07 13:31:11
Message-ID: 49DB559F.3030805@yahoo.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-jdbc

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


From: Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Cc: Brad Milne <mail_4brad(at)yahoo(dot)co(dot)uk>
Subject: Re: BLOB help - yes I've read around!
Date: 2009-04-07 13:57:07
Message-ID: 200904071657.08065.achill@matrix.gatewaynet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-jdbc

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


From: Brad Milne <mail_4brad(at)yahoo(dot)co(dot)uk>
To: pgsql-jdbc(at)postgresql(dot)org
Cc: Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com>
Subject: Re: BLOB help - yes I've read around!
Date: 2009-04-07 14:04:19
Message-ID: 49DB5D63.5040103@yahoo.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-jdbc

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
>>
>>
>
>
>
>


From: Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Cc: Brad Milne <mail_4brad(at)yahoo(dot)co(dot)uk>
Subject: Re: BLOB help - yes I've read around!
Date: 2009-04-07 14:21:28
Message-ID: 200904071721.28357.achill@matrix.gatewaynet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-jdbc

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


From: Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Cc: Brad Milne <mail_4brad(at)yahoo(dot)co(dot)uk>
Subject: Re: BLOB help - yes I've read around!
Date: 2009-04-07 14:29:44
Message-ID: 200904071729.45205.achill@matrix.gatewaynet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-jdbc

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


From: Thomas Kellerer <spam_eater(at)gmx(dot)net>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: BLOB help - yes I've read around!
Date: 2009-04-07 14:39:06
Message-ID: grfoia$t7m$1@ger.gmane.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-jdbc

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


From: Brad Milne <mail_4brad(at)yahoo(dot)co(dot)uk>
To: pgsql-jdbc(at)postgresql(dot)org
Cc: Thomas Kellerer <spam_eater(at)gmx(dot)net>, Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com>
Subject: Re: BLOB help - yes I've read around!
Date: 2009-04-07 21:34:20
Message-ID: 49DBC6DC.5030609@yahoo.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-jdbc

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
>
>


From: Thomas Kellerer <spam_eater(at)gmx(dot)net>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: BLOB help - yes I've read around!
Date: 2009-04-07 21:41:53
Message-ID: grgham$q84$1@ger.gmane.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-jdbc

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


From: Brad Milne <mail_4brad(at)yahoo(dot)co(dot)uk>
To: pgsql-jdbc(at)postgresql(dot)org
Cc: Thomas Kellerer <spam_eater(at)gmx(dot)net>
Subject: Re: BLOB help - yes I've read around!
Date: 2009-04-07 21:55:14
Message-ID: 49DBCBC2.6080402@yahoo.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-jdbc

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


From: Thomas Kellerer <spam_eater(at)gmx(dot)net>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: BLOB help - yes I've read around!
Date: 2009-04-08 06:38:46
Message-ID: grhgpj$vqr$1@ger.gmane.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-jdbc

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 first InputStream 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 inspect the source code for available().

Thomas