Re: how to insert jpg images in a field of a database?

What are the downsides to storing them in the database vs. on disk as files? Are their performance or Size issues?

On Mon, 14 Oct 2002 15:37:54 GMT, Daniel Morgan <dmorgan_at_exesolutions.com> wrote:

>Arun wrote:
>
>> i want to know : Hoe can i store jpg images in a database who's other
>> fields are of text form .? plz provide me with the query .?
>> Does oracle 9i works on windows XP professional edition ?
>
>Use a BLOB data type in your table and use the DBMS_LOB package to read
>the files and load them. I posted an example to this group about six
>months back but since it has been awhile ... here it is again.
>
>
>-- define the directory inside Oracle when logged on as SYS
>CREATE OR REPLACE DIRECTORY ctemp AS 'c:\temp\';
>
>-- grant read on the directory to the Dev schema
>GRANT READ ON DIRECTORY ctemp TO dev;
>
>-- connect as dev
>CONN dev/dev_at_oracle
>
>-- the storage table for the image file
>CREATE TABLE lob_demo (
>file_name VARCHAR2(30),
>image BLOB)
>TABLESPACE ?????;
>
>-- create the procedure to load the file
>CREATE OR REPLACE PROCEDURE load_file (fname IN VARCHAR2) IS
>
>src_file BFILE;
>dst_file BLOB;
>lgh_file BINARY_INTEGER;
>
>BEGIN
> src_file := bfilename('CTEMP', fname);
>
> -- insert a NULL record to lock
> INSERT INTO lob_demo
> (file_name, image)
> VALUES
> (fname, EMPTY_BLOB())
> RETURNING image INTO dst_file;
>
> -- lock record
> SELECT image
> INTO dst_file
> FROM lob_demo
> WHERE file_name = fname
> FOR UPDATE;
>
> -- open the file
> dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
>
> -- determine length
> lgh_file := dbms_lob.getlength(src_file);
>
> -- read the file
> dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
>
> -- update the blob field
> UPDATE lob_demo
> SET image = dst_file
> WHERE file_name = fname;
>
> -- close file
> dbms_lob.fileclose(src_file);
>
> COMMIT;
>END load_file;
>/
>
>Daniel Morgan
>
Received on Wed Oct 16 2002 - 20:04:10 CDT