How do I insert pictures into the database

[Patros]

Basically the database is as follows
Name Null? Type
----------------------------------------- -------- ------------
ISBN NOT NULL NUMBER(10)
NAME VARCHAR2(30)
GENRE VARCHAR2(10)
WARNING VARCHAR2(3)
RELEASEDATE DATE
PRICE NOT NULL NUMBER(10)
GAME_PIC BLOB
My question is what is the correct insert statement to include the image currently stored as c:/temp/GameCover.jpeg
Insert into gameDetails VALUES ('1122345','Tom', 'Horror', '+15', '12/03/03', 20, ??????);
Thanks in advance for any help you guys can give,
Patros

 

[cliousa]

It's kind of long story to explain in details, but take a look here:

http://www.dbaclick.com/forums/archive/29/2095.html

Hope that helps,

clio_usa - OCP DBA
------------------

http://www.dbaclick.com

 

[topher0303]

Recomendation #1 would be to leave the picture in an OS file and just save the reference to the file (i.e. directory location).

If you must store it in the database...
BLOBs cannot be updated through inserts. Depending on where you are updating a blob from depends on the method to use. Oracle documentation would be the starting place:

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96591/toc.htm

Chris.

 

[Patros]

Thanks

 

[Uli32]

with this little Procedure i loaded many files into the Database it works for 8.1.6 for 9i i don'n know
i hope this procedure works on 9i too.

from sql plus you can start the procedure with
an execute load_file('1','2','test.jpg')

CREATE OR REPLACE PROCEDURE load_file (lenr IN VARCHAR2,pidnr IN VARCHAR2, fname IN VARCHAR2) IS

-- lenr = first id nr from file
-- pidnr = second id from file
-- fname = file name
-- create a Directory as SYS
-- b.s.p. CREATE OR REPLACE DIRECTORY ctemp AS '/tmp;
-- GRANT READ ON DIRECTORY ctemp TO dev;


src_file BFILE;
dst_file BLOB;
lgh_file BINARY_INTEGER;

BEGIN
src_file := bfilename('CTEMP', fname);

-- blob reserve
INSERT INTO a_dktab
(dk_name, dk_file)
VALUES
(trim(fname),EMPTY_BLOB())
RETURNING dk_file INTO dst_file;

-- lock blob for input
SELECT dk_file
INTO dst_file
FROM a_dktab
WHERE dk_name = fname
FOR UPDATE;

-- file open
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);

-- length of file
lgh_file := dbms_lob.getlength(src_file);

-- input file
dbms_lob.loadfromfile(dst_file, src_file, lgh_file);

-- update table
UPDATE a_dktab
SET dk_file = dst_file,
dk_user = (Select user from dual),
dk_date = SYSDATE,
dk_ftyp = substr(dk_name,-3,3),
dk_lenr = lenr,
dk_pidnr = pidnr
WHERE dk_name = fname;

-- Alle Files close
dbms_lob.filecloseall;
-- dbms_lob.fileclose(src_file);


COMMIT;
END load_file;
/