Re: Storing images in SQL database?

Andy Hassall <andy_at_andyh.co.uk> wrote in message news:<kmq5m0lfhli4b9vdiaiv6dqgq3i24t0vjt_at_4ax.com>...
> On 5 Oct 2004 13:38:04 -0500, Galen Boyer <galenboyer_at_hotpop.com> wrote:
>
> >On Tue, 05 Oct 2004, recneps.w.divad_at_elcaro.moc wrote:
> >>
> >> Galen Boyer wrote:
> >>> On Tue, 05 Oct 2004, andy_at_andyh.co.uk wrote:
> >>>
> >>>> As for it being harder to use in a web environment,
> >>> I'd like to hear the explanation of this.
> >>
> >> Compare:
> >>
> >> [img src="/srv/www/htdocs/image.png"]
> >>
> >> with
> >>
> >> [img src="SELECT IMAGE FROM IMAGE_TABLE"]
> >>
> >> One of them probably won't work.
> >
> >But, you already have access to the database, so use that layer
> >and issue:
> >
> > select picture from tbl where id = ?;
> >
> >where the table is:
> >
> > create table image_tbl(
> > id number
> > ,picture blob);
> >
> >What's the issue?
>
> The slight awkwardness is that is has to be in a separate request; you output
> an <img> tag in the HTML output pointing to the resource for the image, you
> don't output the image data in the page.
>
> So it has to be something like
>
> <img src="/thingy/that/serves/image?id=whatever">
>
> ... and that other page then serves up the image. So it's in a different
> session and transaction. Not much of an issue, though.

Hi,

if you use Apache with mod_psql and the OWA -(creation of the web pages in the Oracle database) and the dbms_lob package.

Requires how to set up mod_plsql and the connection to the database (seek for
mod_plsql).

dbms_lob will retrieve the BLOB from the database and htp package to create the mime header and the page containing the picture.

Create procedure showgif(p_id number)
as

    p_lob blob;
    p_off number:= 1;
    p_raw  raw(32767);
    p_amt number :=32767;

begin

• select gif into blob

select picture into p_lob from tbl where id = p_id

• build up the page information

owa_util.mime_header('image/gif');

    begin
      loop

• read complete blob and display over htp.prn

        dbms_lob.read(p_lob, p_amt, p_off, p_raw);

        htp.prn(utl_raw.cast_to_varchar2(p_raw));

        p_off := p_off + p_amt;

      end loop;

    exception

      when no_data_found then

        null;
    end;

end;
/   

• in your browser you call

http://localhost/pls/myconnection/showgif?p_id=1 Received on Thu Oct 07 2004 - 07:50:35 CDT