Overview --------The following ASP code demonstrates how to use an Active Server Page (ASP) with Oracle Objects for OLE (OO4O) to retrieve images stored as BLOB data inside an Oracle database and render them via Internet Information Server (IIS). Program Notes -------------oThe sample has been tested using Microsoft Windows 2000 with InternetInformation Server and 8.1.6 Oracle Software.oIt was tested against Microsoft Data Access Components (MDAC) 2.6oComplete the following steps to setup the sample: 1.Enter the following SQL statements via SQL*Plus to create a table.
Make sure you log into Oracle as a user appropriate for running this
example (i.e. SCOTT/TIGER):
CREATE TABLE lob_table (
filename VARCHAR2(50),
blobdata BLOB); 1a. The user will require the CREATE ANY DIRECTORY privilege.If you do
not have this privilege then contact your DBA and have him issue the
following statement:
GRANT CREATE ANY DIRECTORY TO scott; 2.Now enter the following SQL statement which specifies the path to the
location of your BFILE in Sqlplus:
CREATE OR REPLACE DIRECTORY bfile_loc AS 'C:\';
Note: The c:\ directory that is specified above is on the
server where the database resides at. Make sure you change
this to a directory where you have access to put the file. 3.Create the following stored procedure in Sqlplus which will insert a BFILE, in our case your image file (i.e. jpeg, gif, etc), into Oracle as a BLOB datatype to be later rendered by IIS:
CREATE OR REPLACE PROCEDURE LoadBLOBFromBFILE (myfilename IN VARCHAR2) IS
Dest_Loc BLOB;
Src_LocBFILE:= BFILENAME('BFILE_LOC', myfilename);
Amount INTEGER;
BEGIN
/* Get the size of the image */
DBMS_LOB.FILEOPEN(Src_Loc, DBMS_LOB.FILE_READONLY);
Amount:=DBMS_LOB.GETLENGTH(Src_Loc);
DBMS_LOB.FILECLOSE(Src_Loc);
/* Insert a new record into the table containing the */
/* filename you have specified and a LOB LOCATOR.*/
/* Return the LOB LOCATOR and assign it to Dest_Loc. */
INSERT INTO lob_table VALUES (myfilename,
EMPTY_BLOB()) RETURNING blobdata INTO Dest_Loc;
/* Load the image into the database as a BLOB */
DBMS_LOB.OPEN(Src_Loc, DBMS_LOB.LOB_READONLY);
DBMS_LOB.OPEN(Dest_Loc, DBMS_LOB.LOB_READWRITE);
DBMS_LOB.LOADFROMFILE(Dest_Loc, Src_Loc, Amount);
DBMS_LOB.CLOSE(Dest_Loc);
DBMS_LOB.CLOSE(Src_Loc);
COMMIT;
END; 4.Finally, insert your image into the database by calling the stored
procedure with the filename of the image:
EXEC LoadBLOBFromBFILE('myimage.jpg') Note: If you do not have a JPG file called myimage.jpg, change
the name of an image file in the above statement to match your
file. Also make sure that you have placed the file into the directory you have specified above in step 2. You should see following message:
"PL/SQL procedure successfully completed." 5.Create a file named 'GLOBAL.ASA' containing the first part of the
sample code listed below.Modify the username, password and database
name for the database you will be using.
6.In the following connect string: OraSession.CreateDatabasePool 1, 40, 200, "mydatabase", "scott/tiger", 0
a. Change "mydatabase" to match your sqlnet alias/connection string. b. Change "scott/tiger" to match userid and password for your database.
7.Create a file named 'RenderImage.asp' containing the second part of
the sample code listed below.8.Create a Virtual Directory inside of IIS called 'ImageExample' that
contains the GLOBAL.ASA and RenderImage.asp files. Make sure the directory is readable and executable. 9.Open a web browser and run the RenderImage.asp page:
http:///ImageExample/RenderImage.aspReferences ----------@ Information in this article was taken from Oracle Sample Code Repository @ Entry# 1413. Caution -------The sample program in this article is provided for educational purposes only and is NOT supported by Oracle Support Services.It has been testedinternally, however, and works as documented.We do not guarantee that it will work for you, so be sure to test it in your environment before relyingon it. Program ------- - - - - - - - - - - - Part 1 of the Sample Code Begins Here - - - - - - - - - -' ******************** ' ***GLOBAL.ASA*** ' ******************** - - - - - - - - - - - Part 2 of the Sample Code Begins Here - - - - - - - - - - - - - - - - - - - - - - - - - -Code ends here- - - - - - - - - - - - - - - -
|
