Thread: Streaming blob to db
Hello, I got a web application that use jdbc and that will need to stream binary data to the database. My application need an outputstream to send the data to the db, and an inputstream to read the data from the db. I seeked the web but I did not found any working example. I use postgresql 8.0.1 with bytea field. The data size will be around: <1mb 50% 1 - 100mb 30% >100mb 20% Any idea of the best approach? Regards Kuon
Attachment
Hi Kuon Use the standard jdbc method on a PreparedStatement, called setBinaryStream(..) (using version 8.0.x of the driver). The bad thing is that getting the data out from database using getBinaryStream(..) will use a lot of memory relative to the filesize - there seems to be no way to avoid this. Look elsewhere in the archives for an explanation by Oliver on this issue... Regards Jesper 時期 精霊 wrote: > Hello, > > I got a web application that use jdbc and that will need to stream > binary data to the database. > > My application need an outputstream to send the data to the db, and > an inputstream to read the data from the db. > > I seeked the web but I did not found any working example. > > I use postgresql 8.0.1 with bytea field. > > The data size will be around: > > <1mb 50% > 1 - 100mb 30% > >100mb 20% > > Any idea of the best approach? > > Regards > > Kuon --
On Fri, 1 Jul 2005, [UTF-8] �^Y^B�^\^_ 精�^\� wrote: > > I got a web application that use jdbc and that will need to stream > binary data to the database. > > My application need an outputstream to send the data to the db, and > an inputstream to read the data from the db. > > I use postgresql 8.0.1 with bytea field. > > >100mb 20% > If you are really dealing with 100+Mb files you should probably look into using large objects instead of bytea storage. This will provide the streaming interfaces you are looking for and avoid excessive memory usage. http://jdbc.postgresql.org/documentation/80/binary-data.html Kris Jurka
How can I trigger the deletion of the large object when the row containing the oid is deleted? I mean I know that: DELETE FROM pg_largeobject WHERE loid = nnn But is there in jdbc, an automatic way to handle that? Regards Kuon On Jul 4, 2005, at 11:33 AM, Kris Jurka wrote: > > > On Fri, 1 Jul 2005, [UTF-8] æ^Y^Bæ^\^_ ç²¾é^\Š wrote: > >> >> I got a web application that use jdbc and that will need to stream >> binary data to the database. >> >> My application need an outputstream to send the data to the db, and >> an inputstream to read the data from the db. >> >> I use postgresql 8.0.1 with bytea field. >> >> >>> 100mb 20% >>> >> >> > > If you are really dealing with 100+Mb files you should probably > look into > using large objects instead of bytea storage. This will provide the > streaming interfaces you are looking for and avoid excessive memory > usage. > > http://jdbc.postgresql.org/documentation/80/binary-data.html > > Kris Jurka >
時期 精霊 schrieb: > How can I trigger the deletion of the large object when the row > containing the oid is deleted? > > I mean I know that: > DELETE FROM pg_largeobject WHERE loid = nnn > > But is there in jdbc, an automatic way to handle that? > There is a vacuumlo program in the contrib directory of the database source. If the contributed programs are installed you should have it. The readme for it is usually installed in the directory doc/contrib in the directory of the database installation. -- Roland Walter phone: +49 (0) 22 25 / 88 2-41 1 MOSAIC SOFTWARE AG fax: +49 (0) 22 25 / 88 2-20 1 Am Pannacker 3 mailto: rwa (at) mosaic-ag (dot) com D-53340 Meckenheim http://www.mosaic-ag.com ------- L E G A L D I S C L A I M E R --------- Die Informationen in dieser Nachricht sind vertraulich und ausschliesslich fuer den Adressaten bestimmt. Kenntnisnahme durch Dritte ist unzulaessig. Die Erstellung von Kopien oder das Weiterleiten an weitere, nicht originaere und benannte Adressaten ist nicht vorgesehen und kann ungesetzlich sein. Die Meinungen in dieser Nachricht stellen lediglich die Meinungen des Senders dar. Falls Sie vermuten, dass diese Nachricht veraendert wurde, setzen Sie sich mit dem Absender in Verbindung. Der Absender uebernimmt ohne weitere Ueberpruefung keine Verantwortung fuer die Richtigkeit und Vollstaendigkeit des Inhalts. Unbefugte Empfaenger werden gebeten, die Vertraulichkeit der Nachricht zu wahren und den Absender sofort ueber einen Uebertragungsfehler zu informieren. ------------------------------------------------------
Roland Walter <rwa@mosaic-ag.com> writes:
> 時期 精霊 schrieb:
>> How can I trigger the deletion of the large object when the row
>> containing the oid is deleted?
>>
>> I mean I know that:
>> DELETE FROM pg_largeobject WHERE loid = nnn
>>
>> But is there in jdbc, an automatic way to handle that?
> There is a vacuumlo program in the contrib directory of the
> database source.
Also see contrib/lo for a trigger you can install to delete blobs
immediately when the reference is deleted.
regards, tom lane