Thread: [SQL] Can I store bitmap graphics in a table?
Hello all,
Can a bitmap graphic file (say a GIF, JPEG, or EPS) be stored
into a PostgreSQL table? If so, how? What field type should I set up
in the table and how do I insert the graphic file?
Thanks.
--Dan
-----------------------------------------------------------------------
Daniel G. Delaney The Louisville Times Chorus
Dionysos@Dionysia.org www.LouisvilleTimes.org
www.Dionysia.org/~dionysos/ Dionysia Design
ICQ Number: 8171285 www.Dionysia.com/design/
-----------------------------------------------------------------------
I doubt, therefore I might be.
I'm relatively new to postgres myself, but I'll take a stab at this.
Anyone can feel free to correct me if I'm wrong.
You can insert the graphic file by declaring the type to be OID and using
lo_import().
For example:
create table graphics
(name text, graphic oid);
insert into graphics values ('pic1', lo_import ('gif1.gif'));
Check out the manpage for large_objects for more info.
BTW, does anyone know, once I create a large object, and it creates the
files xinv... and xinx..., how I can get rid of postgres's references to
these objects when I no longer need them. (vacuum gives a notice that it
can't remove these even if I have physically removed the files from the
database's directory).
-Matt
On Tue, 21 Jul 1998, Dan Delaney wrote:
> Hello all,
> Can a bitmap graphic file (say a GIF, JPEG, or EPS) be stored
> into a PostgreSQL table? If so, how? What field type should I set up
> in the table and how do I insert the graphic file?
> Thanks.
> --Dan
>
> -----------------------------------------------------------------------
> Daniel G. Delaney The Louisville Times Chorus
> Dionysos@Dionysia.org www.LouisvilleTimes.org
> www.Dionysia.org/~dionysos/ Dionysia Design
> ICQ Number: 8171285 www.Dionysia.com/design/
> -----------------------------------------------------------------------
> I doubt, therefore I might be.
>
>
>
>
>
On Tue, 21 Jul 1998, Matt McClure wrote:
> You can insert the graphic file by declaring the type to be OID and using
> lo_import().
That sounds like it might work. I'll give it a try. However, the
graphics I'm going to be inserting are not very large, like 50 to
100k, I'd like to be able to do it without using the large object
interface. Anyone have any ideas?
--Dan
-----------------------------------------------------------------------
Daniel G. Delaney The Louisville Times Chorus
Dionysos@Dionysia.org www.LouisvilleTimes.org
www.Dionysia.org/~dionysos/ Dionysia Design
ICQ Number: 8171285 www.Dionysia.com/design/
-----------------------------------------------------------------------
I doubt, therefore I might be.
At 18:04 +0300 on 21/7/98, Dan Delaney wrote: > That sounds like it might work. I'll give it a try. However, the > graphics I'm going to be inserting are not very large, like 50 to > 100k, I'd like to be able to do it without using the large object > interface. Anyone have any ideas? You can't. There is a limit on the tuple size, restricting it to 8k. If you could guarantee that your images are no more than, say, 1K - I'd say you can uuencode them or translate to hex, and put it in a text column. A byte array is another option. But in the 50k to 100k, it's large objects. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma
> On Tue, 21 Jul 1998, Matt McClure wrote:
> > You can insert the graphic file by declaring the type to be OID and using
> > lo_import().
>
> That sounds like it might work. I'll give it a try. However, the
> graphics I'm going to be inserting are not very large, like 50 to
> 100k, I'd like to be able to do it without using the large object
> interface. Anyone have any ideas?
I'm actually base64 encoding images for my photo album and storing them
in normal tables as char fields. I've currently got 147 images stored in
146464 rows. Seems to work OK.
I had initially thought to use BLOBs, but there were a few things I
didn't like about Postgres BLOBs:
a) Stored in multiple files on the backend in the same directory as
the rest of the data. I would have a lot of directory entries
right now.
b) Couldn't figure out how to delete a BLOB and have it go away (since
you only store the reference to the BLOB).
c) They don't back up with pgdump, which isn't too bad of a problem,
since I don't mind making my own dump program.
Is the BLOB interface being worked on at all? I'd be interested in
making my program do things a little closer to the ``right'' way. :) (or
maybe playing with the BLOB interface myself).
--
SA, software.net My girlfriend asked me which one I like better.
pub 1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin@spy.net>
| Key fingerprint = 87 02 57 08 02 D0 DA D6 C8 0F 3E 65 51 98 D8 BE
L_______________________ I hope the answer won't upset her. ____________
On Tue, 21 Jul 1998, Matt McClure wrote: > I'm relatively new to postgres myself, but I'll take a stab at this. > Anyone can feel free to correct me if I'm wrong. > > You can insert the graphic file by declaring the type to be OID and using > lo_import(). The down side to lo_import is that the file has to be on the server. There are ways of doing this from the client. An example for libpq is in the source tree (I can't remember where exactly), and the ImageViewer and BlobTest examples in the jdbc directory also show this. > On Tue, 21 Jul 1998, Dan Delaney wrote: > > > Hello all, > > Can a bitmap graphic file (say a GIF, JPEG, or EPS) be stored > > into a PostgreSQL table? If so, how? What field type should I set up > > in the table and how do I insert the graphic file? > > Thanks. > > --Dan -- Peter T Mount peter@retep.org.uk or petermount@earthling.net Main Homepage: http://www.retep.org.uk ************ Someday I may rebuild this signature completely ;-) ************ Work Homepage: http://www.maidstone.gov.uk Work EMail: peter@maidstone.gov.uk
On Tue, 21 Jul 1998, Dustin Sallings wrote: > b) Couldn't figure out how to delete a BLOB and have it go away (since > you only store the reference to the BLOB). lo_unlink deletes a blob. > c) They don't back up with pgdump, which isn't too bad of a problem, > since I don't mind making my own dump program. I did have some code that backed up any large objects in a database into a zip file using the JDBC interface. hmmm, I'll see if I can dig it up and add it to the jdbc examples in the distribution. > Is the BLOB interface being worked on at all? I'd be interested in > making my program do things a little closer to the ``right'' way. :) (or > maybe playing with the BLOB interface myself). I'm looking at blob orphaning, which I think I may have solved. Hopefully, I'll have something working by Monday. -- Peter T Mount peter@retep.org.uk or petermount@earthling.net Main Homepage: http://www.retep.org.uk ************ Someday I may rebuild this signature completely ;-) ************ Work Homepage: http://www.maidstone.gov.uk Work EMail: peter@maidstone.gov.uk
On Tue, 21 Jul 1998, Herouth Maoz wrote:
> [... Storing graphics in a postgresql db ...]
> You can't. There is a limit on the tuple size, restricting it to 8k. If you
> could guarantee that your images are no more than, say, 1K - I'd say you
> can uuencode them or translate to hex, and put it in a text column. A byte
> array is another option. But in the 50k to 100k, it's large objects.
Even without having tried it I am rather shure that you are wrong. The 8k
barrier applies AFAIK only to the normal datatypes like text and so. AFAIK
the LOI is capable to store big graphics in the database.
With best regards
Henning Hucke
--
hucke@braintec.de Firma BRAINTEC
Borsigstr. 16, 38446 Wolfsburg, Tel. 0 53 61/ 85 51-0
At 11:06 +0300 on 28/7/98, Henning Hucke wrote: > > > [... Storing graphics in a postgresql db ...] > > You can't. There is a limit on the tuple size, restricting it to 8k. If you > > could guarantee that your images are no more than, say, 1K - I'd say you > > can uuencode them or translate to hex, and put it in a text column. A byte > > array is another option. But in the 50k to 100k, it's large objects. > > Even without having tried it I am rather shure that you are wrong. The 8k > barrier applies AFAIK only to the normal datatypes like text and so. AFAIK > the LOI is capable to store big graphics in the database. Well, he asked for a way to do it as a normal datatype, without using the (awkward) lo interface. Of course you can do it with LOs. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma