Multimedia data must be delivered to a program that is capable of exhibiting it since this data cannot be exhibited in text. The most used technology for computer-based multimedia data is the Web. In this chapter we will introduce various techniques to get your multimedia onto the Web from the database. We will also explain how multimedia data is handled by standard programs over the Web.
Web browsers use HTTP (hyper text transfer protocol) to communicate with Web servers. This protocol is used to deliver text, multimedia data, and other information to and from the browser on request. HTTP is also used in other programs to obtain data, for example, a Java program can use the HTTP protocol to display an image in a thick-client program. Multimedia data that is delivered to browsers are referenced by URLs.
It is important to understand how a browser delivers Web pages with multimedia included on the page. A common misconception is that a Web page with multimedia is delivered as one piece of information. This is not the case. A Web page first gets the text of the page in HTML format. The Web browser then parses the page to see what else besides the text is to be displayed. The Web browser then fetches each multimedia object, using HTTP, until the page is complete.
In many browsers, you can turn off the display of multimedia data like images. In this case, only the text in the HTML is displayed and the bandwidth used for the large multimedia data is never asked for by the browser. In developing multimedia applications, it is useful to know how browsers handle multimedia data.
In the case of an “inline” image, the Web browser will find an <img> tag that describes the image. This tag will have an SRC attribute that indicates the location of the image. This attribute is also in the form of a URL. The URL is a relative URL if there is no path information included, that is an image on the page located at http://webServer.com/aPath/webPage.html might be specified as <img src=“myImage.png” />.
The SRC attribute would be translated by the browser into http://webServer.com/aPath/myImage.png. Since the image is indicated in the form of a URL, the image itself can be located anywhere on the Web, not just on the HTML server machine. If you look closely, you will see that many popular websites will have image servers; that is, the HTML will be delivered from one server and the images from another.
The SRC attribute in traditional applications is often in the form of a reference to a simple filename as in the previous example, but can also specify a program. This program might deliver images from the database where the location of the image in the database is indicated within the URL. Other programs may actually generate images, for example, an up-to-the-minute stock chart might be generated as an image on request.
Since image data is delivered from a URL endpoint, there is great flexibility in who and where the data is obtained or generated.
The browser can handle all multimedia data (and other types of data) by using an <a> (anchor) tag. In the case of an image, the <a> tag can be used to allow a user to click on a thumbnail image and get back a display of a larger image by using the HREF attribute to specify the location of the file. For example:
<a href="http://server.com/bigImage.png"> <img src="http:// smallImage.png"> </a>
It is important to note that the server delivering the data in this case must be configured to indicate the MIME type of the data in the HTTP header. For files, this is typically accomplished by mapping the file extension to a MIME type from a table. In the case of a program, the MIME type should be set in the header before the data is delivered. The anchor tag itself can give a hint of the MIME type of the data using the “type” attribute. For example:
<a href="http://server.com/bigImage.png" type="image/png"> <img src="http://smallImage.png"> </a>
The “type” attribute is an advisory content type that will be overridden by the content type of the data returned in the HTTP response packet. It allows the browser to know what kind of data is being delivered before it requests it.
If the browser can handle, either with a built-in decoder or a plug-in, the MIME type of the multimedia data, the multimedia is displayed or played within the browser. The browser can be configured to use helper applications to display or play multimedia data in the case that it cannot handle the multimedia data returned. In the case that there is no such helper application configured, the browser will typically ask if it should use the operating system default for the data or save the content to a file.
The browser will inspect the MIME type of the data. It will attempt to handle it in the following order:
A built-in decoder
A browser plug-in
A helper application
Allow the user to download data to a file
For all data that uses the <a> tag, the approach is to completely download the data and then display or play it. Download and play can be used for all types of multimedia. This approach, which can be contrasted to streaming for time-based media (video and audio), will be covered later in this chapter.
Browser plug-ins extend the capability of browsers to handle MIME types that are not handled by the browser natively. For multimedia, plug-ins are used to display images, video, and audio that do not have native browser support.
Plug-in tags for browsers use the <embed> or <object> tag. Samples are:
<embed src="myVideo.rpm" width=320 height=240 />
<object data="svgSample.svgz" type="image/svg+xml"
width="240" height="160" name="svgSamp">
</object>Usage of either <embed> or <object> depends on the plug-in used and the kind of browser you are using. Many times the <embed> tag is used within the <object> tag and is picked up by whichever the browser you are using prefers. For example:
<object data="mySample.svgz" type="image/svg+xml"
width="240" height="160">
<embed src="mySample.svgz" type="image/svg+xml"
width="240" height="160" />
</object>Again, the “type” attribute hints at the MIME type being used on the Web page, it does not dictate it.
Perhaps one of the most important performance considerations with delivering media on the Web is to reduce the number of times that media actually has to be delivered. The most efficient media delivery is one that is not done. Caching controls in HTTP are extensive, so this is a simplified description.
Caching can occur anywhere between the browser and the server. The browser itself will cache information, many ISPs will cache information, firewalls cache information, HTTP proxies cache information, and there are some caching servers that front-end applications, like Oracle Web Cache. Some caches will have caching policies that differ from the HTTP standard to increase performance, sometimes at the risk of delivering stale data, for example, a policy that states that server data is fresh for at least 10 minutes.
HTTP caching falls into two categories:
Eliminate the need for a request–response.
Eliminate the need to deliver media.
The first category—to eliminate the need for a request–response—depends on the original content being delivered with an expiration time. This expiration time is used along the HTTP pipeline, in caching servers and firewalls, to store the data until the expiration time. This kind of caching requires the server to set appropriate HTTP headers. This includes the HTTP “Expires” header, which gives an explicit expiration time, when a cache should consider this content stale:
Expires: Fri, 1 Oct 2005 13:29:31 GMT
Another HTTP header that can be used to eliminate unnecessary requests is the “Cache-Control” header. Since computer clocks do sometimes differ from each other, Cache-Control specifies a “max-age.” For example,
Cache-Control: max-age=3600, must-revalidate
Cache-Control also includes many other caching parameters, too many to go over here, but using either, or both, parameter “Cache-Control” or “Expires” HTTP headers can reduce media requests and increase performance.
The other caching mechanism is to eliminate the need to deliver media. This mechanism requires the media server to set information in a media response. The most commonly used is the “Last-Modified” HTTP header on the media HTTP response. For example:
Last-Modified: Fri, 11 Oct 2005 14:37:08 GMT
This parameter tells the client the modification time of the content. The client, any caching mechanism along the path, can then make an HTTP “Conditional Get” to get the media only if it has changed since the last time it was requested. The “Conditional Get” returns the “Last-Modified” date in the “If-Modified-Since” HTTP request field. The presence of “If-Modified-Since” makes an HTTP “Get” an HTTP “Conditional Get.” For example:
If-Modified-Since: Sat, 02 Oct 2005 15:39:20 GMT
Another mechanism for “Conditional Get” was introduced with HTTP 1.1. This is the entity tag. These entity tags, in the “Etag” response header, are server-generated identifiers that change when the server representation of the media does, for example, a version number. For example, on static files, the Apache Server typically generates an Etag based on update time, file size, and filesystem inode. These entity tags can then be returned in an “If-None-Match” request field and the server can see if the resource identifier, for example, version number, has changed or not. These Etags have the advantage that the server can create their own content change data, independent of sometimes messy date-time mismatches.
If, on an HTTP “Conditional Request,” a server finds the content has not changed, an HTTP status 304 (not modified) status response is returned to the request without the media. This indicates that the cached media is still fresh and should be used.
interMedia HTTP classes, interMedia JSP tags, and the interMedia PL/ SQL Gateway Wizard–generated procedures use the “Last-Modified” and “If-Modified-Since” mechanisms using the “Update Time” of the media object in the database (part of the media object metadata) to reduce unnecessary movement of media to increase performance.
Most, if not all, HTTP servers use the “Conditional Get” features by default. This is typically done transparently. For database operations, not using the interMedia-supplied mechanisms, the programmer must set and check the appropriate HTTP header fields for efficiently serving media from the database.
An application may also decide that certain media can have a longer age to avoid any request–response cycle. Setting of the age policies can typically be done by the programmer or by system administration settings. These are application dependent and must be considered on a case-by-case basis.
Servlets can serve as an endpoint for URLs to deliver HTTP data. This data can be of any MIME type. For interMedia, we are mostly concerned with servlets delivering multimedia data. Where this media is stored, or generated, is of no particular concern to the browser as long as it is delivered with the HTTP response.
For serving multimedia from the database, the following generalized steps occur:
HTTP request is received by the servlet
The servlet makes a JDBC request to the database
The servlet creates an HTTP response
The servlet populates the HTTP response header with the MIME type of the multimedia
The servlet sends the HTTP response
There are java classes supplied by interMedia to make the process above easier to accomplish.
interMedia HTTP classes are a convenience to send media data from the database or to store media data sent from the database. These are in addition to the Java proxy classes that represent the database media in a Java program.
The best way to explain how the interMedia HTTP classes work is to give the simplest retrieval of an image from the database. In this example, the URL is of the form http://server/servlets/pictureServlet?id=number where number is the key to the image in the database. Typically the HTML that includes this URL is created dynamically using database information, for example, a list of image names and alongside the image names an <img> tag is used to obtain the picture on the Web page.
The following code is a simple “doGet” method of a standard servlet, the interMedia HTTP class section is in bold.
public void doGet( HttpServletRequest request,
HttpServletResponse response )
throws ServletException, IOException
{
Connection conn = null;
//
// Use a try-block to ensure that JDBC connections are always returned
// to the pool.
//
try
{
//
// Get a JDBC connection from the pool
//
conn = myPooledConnection.getConnection();
//
// Perform a SQL query for the media
//
PreparedStatement stmt =
conn.prepareStatement( "select " + media +
" from photos where id = ?" );
stmt.setString( 1, request.getParameter( "id" ) );
OracleResultSet rset = (OracleResultSet)stmt.executeQuery();
//
// Fetch the row from the result set.
//
if ( rset.next() )
{
//
// Get the OrdImage object from the result set.
//
OrdImage img =
(OrdImage)rset.getORAData(1, OrdImage.getORADataFactory());
//
// Create an OrdHttpResponseHandler object,
// then use it to retrieve
// the image from the database and deliver it to the browser.
//
OrdHttpResponseHandler handler =
new OrdHttpResponseHandler( request, response );
handler.sendImage( img );
}
else
{
//
// Row not found, return a suitable error.
//
response.setStatus( response.SC_NOT_FOUND );
}
}
catch ( SQLException e )
{
//
// Log what went wrong.
//
e.printStackTrace( System.out );
//
// Turn SQL exceptions into ServletExceptions.
//
throw new ServletException( e.toString() );
}
finally
{
//
// If we have a JDBC connection, implicitly return it to the pool.
//
if (conn != null) conn.close();
}
}In general, we get a connection, select the interMedia object into a Java proxy class, create an OrdHttpResponseHandler object, and tell this object to send the image. Quite simple.
Note that in this example we are using an implicit JDBC cached connection managed by Oracle Application Server Containers for J2EE Services (OC4J). We use cached, or pooled, connections for performance reasons. Creating a new JDBC connection every time an image is requested would be expensive. The connection caching is done implicitly through a JDBC DataSource, so the programmer does not need to code differently than a noncached connection. This connection is defined in the OC4J data-sources.xml file and can be managed with an editor or Oracle Enterprise Manager. The entry to this XML file is as follows.
<data-source class="oracle.jdbc.pool.OracleConnectionCacheImpl" name="OracleMediaPoolDS" location="jdbc/cache/OracleMediaCacheDS" connection-driver="oracle.jdbc.driver.OracleDriver" username="scott" password="tiger" url="jdbc:oracle:thin:@lguros-us.us.oracle.com:1521:orcl2" inactivity-timeout="30"> <property name="cacheScheme" value="1" />
The implementation of myGetPooledDataSource() uses this data source defined in data-sources.xml.
/*
* Get a Pooled database connection
*/
private void myGetPooledConnection()
throws SQLException, NamingException
{
javax.naming.InitialContext ic =
new javax.naming.InitialContext();
OracleDataSource ds = (OracleDataSource)
ic.lookup("jdbc/cache/OracleMediaCacheDS");
conn = (OracleConnection)ds.getConnection();
}The interMedia HTTP classes can also be used to upload media data to the database. This case is a bit more complicated, as is the case with uploading a file to the server. The SQL used is similar to the SQL used to load an image from PL/SQL. The following is a simple example of an HTTP servlet’s doPost method:
public void doPost( HttpServletRequest request,
HttpServletResponse response )
throws ServletException, IOException
{
Connection conn = null;
//
// Use a try-block to ensure that JDBC connections are always returned
// to the pool.
//
try
{
//
// Get a JDBC connection from the pool
//
conn = myPooledConnection.getConnection();
//
// Create an OrdHttpUploadFormData object.
//
OrdHttpUploadFormData formData =
new OrdHttpUploadFormData( request );
//
// Nothing to do if this is not an upload request
//
if ( !formData.isUploadRequest() )
{
throw new ServletException( "Not a media upload request" );
conn.close()
return;
}
//
// Parse the multipart/form-data message.
//
formData.parseFormData();
//
// Get the description, location and photo.
//
String description = formData.getParameter( "description" );
String location = formData.getParameter( "location" );
OrdHttpUploadFile photo = formData.getFileParameter( "photo" );
//
// Make sure a valid image file was provided.
//
if ( photo == null ||
photo.getOriginalFileName() == null ||
photo.getOriginalFileName().length() == 0
)
{
// In a real application, go back to the upload form
// with an error msg
throw new ServletException( "A file was not provided." );
return;
}
if ( photo.getContentLength() == 0 )
{
// In a real application, go back to the upload form with
// an error msg
throw new ServletException( "Please supply a valid image file.");
return;
}
//
// Use the file name if there's no description.
//
if ( description == null || description.length() == 0 )
{
description = "Image from file: " + photo.getSimpleFileName() +
".";
description = description.substring(0, 40);
}
//
// We're going to be updating the database and writing to LOBS, so
// make sure auto-commit is disabled.
//
conn.setAutoCommit( false );
//
// Get a value for the ID column of the new row
//
OraclePreparedStatement stmt =
(OraclePreparedStatement)conn.prepareStatement(
"select photos_sequence.nextval from dual" );
OracleResultSet rset = (OracleResultSet)stmt.executeQuery();
if ( !rset.next() )
{
throw new ServletException( "new ID not found" );
}
String id = rset.getString( 1 );
rset.close();
stmt.close();
//
// Prepare and execute a SQL statement to insert the new row.
//
stmt = (OraclePreparedStatement)conn.prepareStatement(
"insert into photos (id,description,location,image,thumb) " +
" values (?,?,?," + EMPTY_IMAGE ")" );
stmt.setString( 1, id );
stmt.setString( 2, description );
stmt.setString( 3, location );
stmt.executeUpdate();
stmt.close();
//
// Prepare and execute a SQL statement to fetch the initialized
// image object from the table.
//
stmt = (OraclePreparedStatement)conn.prepareStatement(
"select image from photos where id = ? for update" );
stmt.setString( 1, id );
rset = (OracleResultSet)stmt.executeQuery();
if ( !rset.next() )
{
throw new ServletException( "new row not found in table" );
}
OrdImage image =
(OrdImage)rset.getORAData( 1, OrdImage.getORADataFactory());
rset.close();
stmt.close();
//
// Load the photo into the database and set the properties.
//
photo.loadImage( image );
}
catch ( SQLException e )
{
//
// Log what went wrong.
//
e.printStackTrace( System.out );
//
// Turn SQL exceptions into ServletExceptions.
//
throw new ServletException( e.toString() );
}
finally
{
//
// If we have a JDBC connection, then return it to the pool.
//
if (conn != null) conn.close();
}
}mod_plsql is an Apache module that is shipped with Oracle Database and Application Server products. This module supports efficient HTTP access to the Oracle Database through PL/SQL stored procedures. This module is installed by default on Oracle’s Application Server and the Apache Server shipped with Oracle Database.
Typically, as far as interMedia data is concerned, you would want to use the interMedia Code Wizard for the PL/SQL Gateway with mod_plsql to write PL/SQL procedures to display or load interMedia data from a Web page. Here, the use of only mod_plsql to deal with interMedia data is described. Again, typically you would not want to write this code when there is a tool that will write it for you, but this section is included to further your understanding of mod_plsql. You may need to modify the code produced by the Code Wizard for the PL/SQL Gateway to customize it for your application, for example, creating thumbnail images when loading an image, but it is easier to insert this code into a procedure that does most of what you need.
mod_plsql funnels HTTP requests to a PL/SQL procedure. These procedures can handle GET, PUT, POST, or other HTTP request messages. Inside the procedure, the request is processed, and the results are passed back in an HTTP response message. mod_plsql procedures require a DAD (database access descriptor) and also a document table must be created if media data is to be uploaded to the database. For interMedia, these procedures will query, insert, or update media. The results of the operation, either the media or status, will be returned to the browser. To illustrate what a mod_plsql procedure does, we will examine two procedures, one to deliver media data and one to insert data.
First, we look at a simple procedure to deliver an image to a browser. These images are assumed to be stored in the database rather than on the file system or on the Web. We don’t check to see if the image in the browser cache is up to date, as the procedures produced by the interMedia PL/SQL wizard do.
CREATE OR REPLACE PROCEDURE GET_IMG ( IMG_ID IN VARCHAR2 )
AS
img ORDSYS.ORDIMAGE;
rid UROWID;
etag VARCHAR2(50);
etag_gen VARCHAR2(50);
b BLOB;
BEGIN
--
-- Retrieve the object from the database.
--
BEGIN
SELECT imgtbl.img,rowid INTO img, rid FROM IMG_TABLE
imgtbl
WHERE imgtbl.ID = IMG_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
owa_util.status_line( 404, 'Not Found', FALSE );
owa_util.mime_header( 'text/html' );
owa_util.http_header_close;
htp.print( '<h1>Not Found</h1> The requested image' ||
'<tt>' ||
owa_util.get_cgi_env( 'SCRIPT_NAME' ) ||
owa_util.get_cgi_env( 'PATH_INFO' ) ||
'?img_id=' || IMG_ID ||
'</tt> was not found.' );
RETURN;
END;
--
-- Check if the browser has the latest media
--
etag_gen := to_char(img.getUpdateTime(),'ssmihh24ddmmyyyy')
|| rid || img.getContentLength();
--
-- NOTE: Getting this value depends on the
-- following entry in
-- dads.conf for the DAD that this routine is used through
--
-- PlsqlCGIEnvironmentList HTTP_IF_NONE_MATCH
--
etag := owa_util.get_cgi_env ('HTTP_IF_NONE_MATCH'),
IF etag IS NOT NULL THEN
IF etag = etag_gen THEN
owa_util.status_line( 304, 'Not Modified', TRUE );
RETURN; -- Don't send image again, requester has
-- latest media.
END IF;
END IF;
--
-- First, set the HTTP header fields.
--
owa_util.mime_header( img.getMimeType(), FALSE );
htp.p('Content-Length: ' || img.getContentLength());
htp.p('ETag: ' || etag_gen);
owa_util.http_header_close();
--
-- Now, deliver the data
--
IF owa_util.get_cgi_env( 'REQUEST_METHOD' ) <> 'HEAD' THEN
b := img.getContent();
wpg_docload.download_file( b );
END IF;
END GET_IMG;This procedure simply does a select on the data using IMG_ID query expression from the URL. The mod_plsql URL would be in the form:
http://<server>:<port>/<mod_plsql_dad>/get_img?IMG_ID=<SELECT_STRING>
Uploading images to the database requires the creation of a document table. This document table receives the input before the insert procedure is called. The interMedia PL/SQL wizard can create a document table for you if you like.
CREATE OR REPLACE PROCEDURE UPLOAD_IMG
( in_ID IN VARCHAR2,
in_IMG IN VARCHAR2 DEFAULT NULL,
in_DESCRIPTION IN VARCHAR2 DEFAULT NULL )
AS
IMG ORDSYS.ORDIMAGE := ORDSYS.ORDIMAGE.init();
ID IMG_TABLE.ID%TYPE := NULL;
upload_size INTEGER;
upload_mimetype VARCHAR2( 128 );
upload_blob BLOB;
BEGIN
--
-- Try an update, in case the row already exists.
-- In that case, we will update it.
--
UPDATE IMG_TABLE imgtbl
SET imgtbl.IMG = IMG,
imgtbl.DESCRIPTION = in_DESCRIPTION
WHERE imgtbl.ID = in_ID
RETURN imgtbl.ID INTO ID;
--
-- If the ID is null, we need to insert a new row.
--
IF ID IS NULL
THEN
--
-- Insert new row into table
--
INSERT INTO IMG_TABLE ( ID, IMG, DESCRIPTION )
VALUES ( in_ID, IMG, in_DESCRIPTION );
END IF;
--
-- Select interMedia object(s) for update
-- (necessary for storing BLOBS)
--
SELECT mtbl.IMG INTO IMG
FROM IMG_TABLE imgtbl WHERE imgtbl.ID = in_ID FOR UPDATE;
--
-- Store media data for column in_IMG, getting data from the
-- document table associated with the DAD that was created.
-- The KEY is passed to this procedure by mod_plsql
-- in in_IMG
--
IF in_PICTURE IS NOT NULL
THEN
SELECT dtbl.doc_size, dtbl.mime_type, dtbl.blob_content
INTO
upload_size, upload_mimetype, upload_blob
FROM DOCUMENT_TABLE dtbl WHERE dtbl.name = in_IMG;
IF upload_size > 0
THEN
dbms_lob.copy( img.source.localData,
upload_blob,
upload_size );
img.setLocal();
BEGIN
img.setProperties();
EXCEPTION
WHEN OTHERS THEN
--
-- setproperties failed! Set the Object to
-- what we know from the web parameters.
--
img.contentLength := upload_size;
img.mimeType := upload_mimetype;
END;
END IF;
--
-- Clean up document table
--
DELETE FROM WPG_DOCUMENT dtbl WHERE dtbl.name =
in_PICTURE;
END IF;
--
-- Update interMedia objects in table
--
UPDATE IMG_TABLE imgtbl
SET imgtbl.PICTURE = img
WHERE imgtbl.ID = in_ID;
--
-- Redirect to main menu
--
owa_util.redirect_url('/myMenu.html'),
END UPLOAD_IMG;As you can see, the upload procedure is quite a bit more complicated. This is due to finding out if we must update or insert the row, and the necessity of inserting a BLOB (within the object) in the database before we can populate it.
interMedia provides a set of JSP tags to make developing JSP pages with media content from the database easier for the JSP developer. The support is provided by the interMedia JSP tag library that includes custom JSP tags for interMedia data. The JSP tag library is best supported through Jdeveloper. The JSP tag library will be covered in Chapter 9.
OracleAS Portal natively supports interMedia in forms and reports. This support can be achieved because Portal recognizes interMedia data types, and thus can handle rich-media columns. For reports, Portal allows you to display media either embedded in the report or by a linked icon.
Portal Forms allow for the upload of interMedia data. To insert interMedia data into the database the following general steps are necessary:
Create a table with an interMedia type.
Create a database provider.
Use the Oracle Portal Forms Wizard to create a media input form.
The Forms Wizard is accessed through the Portal Builder. First select Navigator and then the Providers tab to see a screen similar to the one in Figure 4.1.
Click on Locally Built Providers. At the top of the screen, there will be a Create Database Provider. The first screen will allow for entry of general information about the provider, as shown in Figure 4.2. Once the provider is created, you can click on the provider and select the Forms Wizard.
At this point, you must have a table that includes an interMedia type defined within it. It must be in the schema indicated in the form above, in this case the interMedia table will be in the SCOTT schema. Select the form based on the table or view to enter the Forms Wizard.
The second step in the Forms Wizard prompts for the table to be used for the form, in this case a table with interMedia image types. We select the PHOTOS table (Figure 4.3) and go to the next steps.
From here, we take all the default values until step 5 where we can specify what information we want the form to collect. All the columns are selected for us, and in this case we will remove the thumbnail column from the form since thumbnail creation is not part of form’s functionality. To have thumbnails created, a database job running in the background can create thumbnails where there are IMAGE columns where the THUMBNAIL column is null, or has an update time earlier than the IMAGE column. Figure 4.4 is an example of what the Forms Wizard looks like at this point.
At this point, we can finish to accept the rest of the form’s defaults, and run and test the form. From this form you can now insert images into the database using Oracle Portal and use them in portlets (see Figure 4.5).
You can then insert an image, and if you want to query the table through this portlet, you can see a screen similar to Figure 4.6.
After playing with the form, you can now use the Oracle Portal Report’s wizard to see the images you have placed into the database. From the locations where we chose Create New Form, we can choose to create a new report. To create a Portal Report on the form, you create a new provider from the Provider tab in Portal Navigator. Choose Create New Report. Select Reports from Query Wizard. In this example the preceding form’s examples are used to create the table.
First, enter the report name and properties using the interMedia portal DB provider used for the interMedia portal form. At this point your screen will look like Figure 4.7.
Select the first four columns, including the IMAGE column, into the report so that the screen looks like Figure 4.8.
From here, we select all the defaults by clicking on the Finish button. Note that we can choose to display the image column inline, or by means of a link in subsequent pages when we choose Display Options. The default is to display a link to interMedia content, but it can be changed to embed the image in a report. Running a report results in a portlet that looks like Figure 4.9. Clicking on the picture icon will result in the page showing the image in the table.
In the Windows environment, using Microsoft .NET, media delivery is easily accomplished. This includes media delivery to and from the Web.
The prerequisite for developing ODP.NET applications is to install ODP.NET onto the system where such applications will be developed.
Since these applications require a Web server, a server version of Microsoft Windows will be required. It is also a great convenience to use Visual Studio to develop these applications with the Oracle Developer Tools for Visual Studio .NET add-in.
ODP.NET applications for the Web include an .aspx script component and programmed components. The programmed components can be in C Sharp, a Java or C++-like object-oriented language, or Visual Basic. ODP.NET supports both C Sharp and Visual Basic. In the following examples in this section, the C Sharp language is used.
In ODP.NET, the first component to be defined is the .aspx file. These files are scripting languages that define browser display and define how the application reacts to events. In the case of delivery of binary media data, no text data, like HTML, is used. But in our example, we deliver the media with an .aspx front end. In the case of an error, we can deliver error text in the label. This error message would only be displayed when directly interfacing with the media URL, not embedded URLs, like image or plug-in tags.
If there is no error, we take over the .NET HTTP Response Stream, set the content type to what the media is, and copy the binary media data from the interMedia BLOB to the .NET HTTP Response Stream. The ASP file contains the following code.
<%@ Page language="c#" Codebehind="getMedia.aspx.cs"
AutoEventWireup="false" Inherits="SmilDemo.getMedia" %>
<!DOCTYPE HTML PUBLIC
"-//W3C//DTD HTML 4.0 Transitional//EN" >
<html>
<head>
<title>getMedia</title>
<meta name="GENERATOR"
Content="Microsoft Visual Studio 7.0">
<meta name="CODE_LANGUAGE" Content="C#">
<meta name="vs_defaultClientScript"
content="JavaScript">
<meta name="vs_targetSchema"
content="http://schemas.microsoft.com/intellisense/ie5">
</head>
<body MS_POSITIONING="GridLayout">
<form id="getMedia" method="get" runat="server">
<P>
<asp:Label id="getMediaLabel" runat="server"
Visible="False"></asp:Label>
</P>
</form>
</body>
</html>In the preceding ASPX, any error message would be delivered within the Label tag, otherwise the ASPX will be ignored.
The code to deliver media will use a URL of the form:
http://<SERVER>:<PORT>/<PATH>/ getMedia.aspx?tbl=<TABLE>&col=<COLUMN>&keycol=<KEY COLUMN>&key=<KEY>
For example:
http://myserver.myco.com/getMedia/ getMedia.aspx?tbl=photos&col=thumb&keycol=id&key=1
The code to actually supply the image to the browser from the database follows; it simply does a database query and returns the binary data and MIME type to the response stream of the .NET application (in the normal case):
using System; using System.Collections; using System.ComponentModel; using System.Configuration; using System.Data; using System.Drawing; using System.Web; using System.Web.SessionState; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls; using Oracle.DataAccess.Client; using Oracle.DataAccess.Types; namespace getMedia { /// <summary> /// This code delivers Media from Oracle database interMedia /// objects. /// It takes URL requests of the form: /// http://{SERVER}:{PORT}/getMedia/ /// getMedia.aspx?tbl={TABLE}&col={COLUMN}&keycol={KEY /// COLUMN}&key={KEY} /// where: /// {TABLE} is the datbase table name where the media /// resides /// {COLUMN} is the datbase column name where the media /// resides /// {KEY COLUMN} is the selection key column for the where /// clause /// {KEY} is the selection key to be used in the where /// clause as in /// where {KEY COLUMN} = {KEY} /// for Example: /// http://localhost/getMedia/getMedia.aspx?tbl=photos& /// col=image&keycol=id&key=1 /// /// Database connection parameter is a configuration /// setting in Web.config /// /// This code sets an Etag based upon update time /// and content length. /// The Etag is checked against the If-None-Match /// request HTTP /// header field to only deliver media if necessary. /// </summary> public class getMediaForm : System.Web.UI.Page { protected System.Web.UI.WebControls.Label getMediaLabel; protected static readonly string dbConnectionString = ConfigurationSettings.AppSettings["OracleConnectionString"]; private void Page_Load(object sender, System.EventArgs e) { // Get connection (from pool) OracleConnection dbConn = newOracleConnection(dbConnectionString); dbConn.Open(); string tbl = Request.QueryString["tbl"]; string col = Request.QueryString["col"]; string keycol = Request.QueryString["keycol"]; string key = Request.QueryString["key"]; if (tbl == null || col == null || keycol == null || key == null) { getMediaLabel.Text = "404 - Media not found." + " Missing Request Parameters."; getMediaLabel.Visible = true; Response.StatusCode = 404; return; } try { // Create command OracleCommand cmd = new OracleCommand ("select t."+col+".getContent(), " + " t."+col+".getMimeType(), " + " to_char(t."+col+".getUpdateTime(),|| || 'ssmihh24ddmmyy'), " + " t."+col+".getContentLength() " + "from " + tbl + " t " + "where t." + keycol + " = " + key, dbConn); OracleDataReader reader = cmd.ExecuteReader(); if (!reader.Read() || reader.IsDBNull(0)) { getMediaLabel.Text = "404 - Media not found!"; getMediaLabel.Visible = true; Response.StatusCode = 404; return; } // // If this is a conditional Request, see if media // has changed. // If we can't let requester use cache, set ETag. // if (!reader.IsDBNull(2)) { String myTimeStamp = reader.GetString(2); Decimal len = reader.GetDecimal(3); String Etag = myTimeStamp + ":" + len.ToString(); string ifNoneMatch = Request.Headers["If-None-Match"]; if (ifNoneMatch != null && ifNoneMatch.IndexOf(Etag) != -1) { Response.StatusCode = 304; // Return not-modified return; } // Set the ETAG (like Last-Modified-Time) so // we can cache. Response.Cache.SetETag(Etag); } // Return the data OracleBlob bLob = reader.GetOracleBlob(0); Response.ContentType = reader.GetString(1); Response.AddHeader("Content-Length", || || bLob.Length.ToString()); // Indicate this is not session private media Response.Cache.SetCacheability (HttpCacheability.Public); int bufsiz = bLob.OptimumChunkSize * 8; int readLen; byte[] buf = new byte[bufsiz]; do { readLen = bLob.Read(buf, 0, buf.Length); Response.OutputStream.Write(buf,0,readLen); } while (readLen > 0); Response.Flush(); } catch(OracleException ex) { getMediaLabel.Text = ex.Message; getMediaLabel.Visible = true; } finally { // Close DB connection (return to pool) dbConn.Close(); } } #region Web Form Designer generated code override protected void OnInit(EventArgs e) { // // CODEGEN: This call is required by the ASP.NET Web // Form Designer. // InitializeComponent(); base.OnInit(e); } /// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> private void InitializeComponent() { this.Load += new System.EventHandler(this.Page_Load); } #endregion } }
In the case that it is necessary to deliver media data from a user to the database, this is accomplished in a somewhat similar manner to delivery of data to the browser. The complicating factors are the creation of a database row and the processing of the data from the browser.
The .aspx file will also be a bit more complicated, since it will be used to supply the data for the new media database row. An .aspx file to upload media to the database that was generated by Visual Studio follows.
<%@ Page language="c#" Codebehind="WebForm1.aspx.cs"
AutoEventWireup="false" Inherits="uploadDB.WebForm1" %>
<!DOCTYPE HTML PUBLIC
"-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
<title>WebForm1</title>
<meta content="Microsoft Visual Studio 7.0"
name="GENERATOR">
<meta content="C#" name="CODE_LANGUAGE">
<meta content="JavaScript"
name="vs_defaultClientScript">
<meta content=
"http://schemas.microsoft.com/intellisense/ie3-2nav3-0"
name="vs_targetSchema">
</HEAD>
<body>
<form id="Form1" method="post"
encType="multipart/form-data" runat="server">
<P> </P>
<P>
<TABLE id="Table1" height="119" cellSpacing="1"
cellPadding="1" width="744" border="1">
<TR>
<TD width="141">
<asp:label id="Label1"
runat="server">Description of Picture:
</asp:label>
</TD>
<TD>
<asp:textbox id="Description" runat="server"
Columns="50"></asp:textbox>
<asp:requiredfieldvalidator
id="RequiredFieldValidator1"
runat="server" Width="237px"
ControlToValidate="Description"
ErrorMessage="You must Enter a picture description">
</asp:requiredfieldvalidator>
</TD>
</TR>
<TR></TR>
<TR>
<TD width="141">
<asp:label id="Label2"
runat="server">Location of Picture:
</asp:label>
</TD>
<TD>
<asp:textbox id="Location" runat="server"
Columns="50">
</asp:textbox>
<asp:requiredfieldvalidator
id="RequiredFieldValidator2" runat="server"
ControlToValidate="Location"
ErrorMessage="You must enter a picture Location">
</asp:requiredfieldvalidator>
</TD>
</TR>
<TR>
<TD width="141"><asp:label id="Label3"
runat="server">Picture File</asp:label>
</TD>
<TD>
<INPUT id="mediaFile" type="file" size="50"
runat="server">
<asp:requiredfieldvalidator
id="RequiredFieldValidator3" runat="server"
Width="136px" ControlToValidate="mediaFile"
ErrorMessage="File must be entered"
Height="15px">
</asp:requiredfieldvalidator>
</TD>
</TR>
</TABLE>
</P>
<P>
<asp:button id="Button1" runat="server"
Text="Upload Picture"></asp:button>
</P>
<P>
<asp:label id="StatusLabel" runat="server"
Visible="False"></asp:label></P>
</form>
</body>
</HTML>The code behind this .aspx form inserts a new row into the database, populates the interMedia BLOB, populates the metadata of the interMedia type using setProperties(), and creates a thumbnail if possible (the image is a popular format that interMedia can process).
Also note that this code takes the content type, or MIME type, of the media from the sending Web browser. This value is used in case the image itself is a nonpopular image format, or not even an image. In this case, we will still preserve the content type of the data. We could check to see if the content type began with “image/” to make certain that the data is an image type, at least according to the sending Web browser, but in the following example we accept any file delivered over the Web.
using System; using System.Configuration; using System.IO; using System.Collections; using System.ComponentModel; using System.Data; using System.Drawing; using System.Web; using System.Web.SessionState; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls; using Oracle.DataAccess.Client; using Oracle.DataAccess.Types; namespace uploadDB { /// <summary> /// Summary description for WebForm1. /// </summary> public class WebForm1 : System.Web.UI.Page { protected System.Web.UI.WebControls.TextBox Description; protected System.Web.UI.WebControls.Label Label2; protected System.Web.UI.WebControls.TextBox Location; protected System.Web.UI.WebControls.Label Label3; protected System.Web.UI.HtmlControls.HtmlInputFile mediaFile; protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator1; protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator2; protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator3; protected System.Web.UI.WebControls.Button Button1; protected System.Web.UI.WebControls.Label Label1; protected System.Web.UI.WebControls.Label StatusLabel; protected static readonly string dbConnectionString = ConfigurationSettings.AppSettings["dbConnString"]; private void Page_Load(object sender, System.EventArgs e) { // Put user code to initialize the page here } #region Web Form Designer generated code override protected void OnInit(EventArgs e) { // // CODEGEN: This call is required by the ASP.NET // Web Form Designer. // InitializeComponent(); base.OnInit(e); } /// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> private void InitializeComponent() { this.Button1.Click += new System.EventHandler(this.Button1_Click); this.Load += new System.EventHandler(this.Page_Load); } #endregion private void Button1_Click(object sender, System.EventArgs e) { bool success = false; OracleConnection dbConn = new OracleConnection(dbConnectionString); dbConn.Open(); // Start transaction OracleTransaction trans = dbConn.BeginTransaction(); // Get image characteristics Stream imgStream = mediaFile.PostedFile.InputStream; int imgLength = mediaFile.PostedFile.ContentLength; string imgContentType = mediaFile.PostedFile.ContentType; string imgFileName = mediaFile.PostedFile.FileName; try { OracleCommand cmd = new OracleCommand( " insert into photos t values " + " (photosSeq.nextval, '" + " Description.Text + "', " + " '" + Location.Text + "', " + " ordImage.init(), ordImage.init()) " + " returning rowid, t.image.getContent() " + " into :rwid, :image", dbConn); cmd.Parameters.Add("rwid", OracleDbType.Varchar2, 80).Direction = ParameterDirection.Output; cmd.Parameters.Add("image", OracleDbType.Blob).Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); OracleString rowid = (OracleString)cmd.Parameters["rwid"].Value; OracleBlob imageBlob = (OracleBlob)cmd.Parameters["image"].Value; // // Copy the blob from the net (max 2 gig, due to // .NET interface using int as length) // byte[] buf = new byte[imageBlob.OptimumChunkSize*8]; int num; int total = 0; do { num = imgStream.Read(buf, 0, buf.Length); imageBlob.Write(buf, 0, num); total += num; } while (total < imgLength); OracleCommand procCmd = new OracleCommand( "Declare " + " img ordImage; " + " thumbnail ordImage; " + "Begin " + " Select image, thumb into img, thumbnail " + " from photos where rowid = :rowid " + " for update; "+ " img.setMimeType(:mimetype); " + " Begin " + " img.setProperties(); " + " img.processcopy('fileformat=JFIF " + " maxscale=128 128', thumbnail);" + " Exception " + " when others then thumbnail := null;" + " End; " + " update photos set image=img, " + " thumb=thumbnail where rowid = :rowid; " + "End; ", dbConn); procCmd.Parameters.Add("rowid", OracleDbType.Varchar2,ParameterDirection.Input) .Value=rowid; procCmd.Parameters.Add("mimetype", OracleDbType.Varchar2,ParameterDirection.Input) .Value=imgContentType; procCmd.ExecuteNonQuery(); trans.Commit(); StatusLabel.Text = "Upload sucessful"; StatusLabel.Visible = true; success = true; } catch(OracleException ex) { trans.Rollback(); StatusLabel.Text = "Upload failed. <br>" + ex.Message; StatusLabel.Visible = true; } finally { // Close connection (release connection to pool) dbConn.Close(); } // Make it that the fields are cleared on suceess if (success) { Response.Redirect("webform1.aspx"); } } } }
This example shows how to use an anonymous PL/SQL block, but can also be performed by calling a precompiled PL/SQL procedure, which would be somewhat more efficient.
PHP is a general scripting language suited for use in web servers, especially HTML. It is supported by many web server environments. It is probably used mostly with the Apache web server as an Apache module.
As with the other scripting languages, it is an easy chore to create a PHP script to upload media to the database or deliver media from the database.
The prerequisite for developing PHP applications that will interface with interMedia is to install PHP onto your web server, and to activate the OCI8 PHP extension. This extension is used by PHP to communicate with an Oracle database.
In PHP, as in ODP.NET, there are no proxy objects that represent the database interMedia objects, therefore, the objects must be handled within SQL statements or PL/SQL procedures or blocks.
This script again uses the flexible URL of the form:
http://webserver.co.com/getMedia.php? tbl=<TABLE NAME>&keycol=<KEY COLUMN NAME> &key=<KEY>&col=<MEDIA COLUMN>
Where:
<TABLE NAME> is the table name to query for media (for example: PHOTOS)
<KEY COLUMN NAME> is the name of the column that is used to search for a single entry (for example: ID)
<KEY> is the singleton search key used to locate the appropriate row.
<MEDIA COLUMN> is the column where an interMedia object resides, for example: IMAGE)
An example URL would be:
http://my.svr.com/getMedia.php?tbl=photos& keycol=id&key=1&col=image
The PHP script to accomplish getting media is as follows:
<?php
// Call procedure to get media
getMedia();
function getMedia()
{
// this procedure requires PHP5 and OCI extension
if (!isset($_GET['tbl']) || !isset($_GET['col']) ||
!isset($_GET['keycol']) || !isset($_GET['key'])) {
header("HTTP/1.0 404 Not Found");
return;
}
$tbl = $_GET['tbl'];
$col = $_GET['col'];
$keycol = $_GET['keycol'];
$key = $_GET['key'];
// Connect to the DB. Using EZCONNECT (QuickConnect)
syntax
// (must have EZCONNECT configured)
$conn = oci_connect('scott', 'tiger',
'//my.server.com:1521/orcl2'),
if (!$conn) {
header("HTTP/1.0 500 Internal Error");
echo "500 Internal Error on connection";
return;
}
// Create SQL statement
$query =
"SELECT t." . $col . ".getContent(),
t." . $col . ".getMimeType(),
to_char(t." . $col .
".getUpdateTime(), 'ssmihh24ddmmyyyy'),
t." . $col . ".getContentLength()
FROM " . $tbl . " t
WHERE " . $keycol . " = :key";
$stid = oci_parse($conn, $query);
// Bind key
$r = oci_bind_by_name($stid, ":key", $key, -1);
if (!$r) {
header("HTTP/1.0 500 Internal Error");
echo "500 Internale Error on bind";
return;
}
// Execute SQL statement.
$r = oci_execute($stid, OCI_DEFAULT);
if (!$r) {
header("HTTP/1.0 500 Internal Error");
echo "500 Internal Error on SQL execute";
return;
}
// Fetch row.
$arr = oci_fetch_row($stid);
if (!$arr) {
header("HTTP/1.0 404 Not Found");
echo "Not Found";
return;
}
$lob = $arr[0];
$mime = $arr[1];
$timstamp = $arr[2];
$medialen = $arr[3];
//
// If this is a conditional Request,
// see if media has changed or not.
//
if ($timstamp != '') {
// Create the Etag we will use for caching
$Etag = $timstamp . ":" . $medialen;
$headers = Apache_Request_Headers();
$ifNoneMatch = '';
if (isset($headers["If-None-Match"]))
$ifNoneMatch = $headers["If-None-Match"];
// If this is a conditional request, and the Etag
// matches, we don't have to deliver media.
if ($ifNoneMatch == $Etag){
header("HTTP/1.0 304 Not Modified");
oci_free_statement($stid);
$lob->free();
return;
}
// Set the ETAG (like Last-Modified-Time)
//so we can cache.
header("ETag: " . $Etag);
}
// set media mimetype
header("Content-type: " . $mime);
// write media
while (!$lob->eof()) echo $lob->read(65534);
// Free resources
oci_free_statement($stid);
$lob->free();
}
?>As with our other examples, we set an Etag in the header so that if the browser, or a caching system, has the media cached locally, and the cache is correct (by media size and update time) it will not be delivered. Instead an HTTP response will tell the browser (or caching firewall or caching server) to use the media in it’s cache.
In the case that it is necessary to deliver media data from a user to the database, this is accomplished in a somewhat similar manner to delivery of data to the browser. The complicating factors are the creation of a database row, and the processing of the data from the browser.
This PHP script looks for the necessary inputs to create a new row in the HTTP request. If the HTTP request does not have these fields, a user form is displayed to collect the necessary information. It is done in this manner as an example only, so there is only a single script, most applications will have a form created by one script, and then sent the results to a PHP script to accept the form inputs and put the information into the database.
As in the other examples, inserting a BLOB into the database is a multi step process. The first step is to populate a row and create a BLOB to receive the binary data from the application, the second step is for the application to populate the blob, and the third step is to create a database procedure to process the uploaded image.
<?php
if (!isset($_POST['description']) ||
!isset($_POST['location']) ||
!isset($_FILES['photo'])) {
?>
<HTML>
<BODY>
<FORM name="uploadForm"
method="post"
enctype="multipart/form-data">
<P>
Location? <INPUT type="text"
name="location"/><BR/>
Description? <INPUT type="text"
name="description"/><BR/>
Image File? <INPUT type="file"
name="photo"/><BR/>
<INPUT type="submit" value="Submit" />
</P>
</FORM>
</BODY>
</HTML>
1?php
}
else{
// Connect to the DB. Using EZCONNECT
//(QuickConnect) syntax
// (must have EZCONNECT configured)
$conn = oci_connect('scott', 'tiger',
'//my.srvr.com:1521/orcl2'),
// Call procedure to put photo
$r = db_put_photo($conn, $_POST['description'],
$_POST['location'],
$_FILES['photo']['tmp_name'],
$_FILES['photo']['type']);
$here = $_SERVER['PHP_SELF'];
if ($r) echo "Upload Successful.
<a href="$here"> Again? </a>";
else echo "Upload Failed.
<a href="$here"> Try Again? </a>";
}
//
// Function to put photo into database
//
function db_put_photo($conn, $description,
$location,
$imgfile, $defaultmime)
{
$stmttxt =
"INSERT INTO photos t
(id, description, location, image, thumb)
VALUES(photos_tbl_seq.nextval,:descr,:loc,
ordimage.init(), ordimage.init())
RETURNING t.image.getcontent(), rowid into
:lob, :rid";
$stmt = oci_parse($conn, $stmttxt);
// Fill in text filelds
oci_bind_by_name($stmt, ':descr',
$description, -1);
oci_bind_by_name($stmt, ':loc',
$location, -1);
// Get lob descriptor
$lob = oci_new_descriptor($conn, OCI_D_LOB);
oci_bind_by_name($stmt, ':lob', $lob,
-1, OCI_B_BLOB);
// Get rowid
oci_bind_by_name($stmt, ':rid', $rid, 64);
// Execute the statement
oci_execute($stmt, OCI_DEFAULT);
$handle = fopen($imgfile, "r");
while ( !feof($handle) )
$lob->write(fread($handle, 65534));
// Update the photo and create a
// thumbnail image. If we can't figure
// out the mimetype, use the mimetype
// passed into the webserver.
$stmttxt =
"DECLARE
img ordimage;
th ordimage;
BEGIN
select image, thumb into
img, th from photos
where rowid = :rid;
begin
img.setproperties();
img.processcopy
('fileFormat=JFIF maxScale=128 128',
th);
exception
when others then
img.setMimeType(:mt);
th := null;
end;
update photos set
image=img, thumb=th
where rowid=:rid;
commit;
END;";
$stmt = oci_parse($conn, $stmttxt);
oci_bind_by_name($stmt, ':rid',
$rid, -1);
oci_bind_by_name($stmt, ':mt',
$defaultmime, -1);
oci_execute($stmt, OCI_DEFAULT);
// Free up resources
oci_free_statement($stmt);
$lob->free();
return true;
}
?>This example shows how to use an anonymous PL/SQL block, but can also be performed by calling a pre-compiled PL/SQL procedure, which would be somewhat more efficient.
Media, including video, audio, images, and XML documents, can also be delivered from streaming servers directly from the database. Streaming servers do not generally use HTTP, but use their own streaming protocols. RTSP (real-time streaming protocol) is used by a number of streaming servers, including Helix server and Real server. Microsoft uses both RTSP and Microsoft Media Server (MMS) protocols. These protocols can handle many multimedia types.
Aside from simply serving content from the database, the database can be used to deliver custom media based on a SQL query; for example, the request may be to play the most popular song for the day rather than a particular song.
If the streaming client supports other types of media, like the XML standard SMIL (synchronized multimedia integration language), a more complex presentation may be crafted; for example, play a sample of the top ten most popular songs of the day while displaying their album covers. The XML can be created from SQL queries and delivered to the client, and the client would then request the actual contents of media from the streaming server.
interMedia supports the Real server or the Helix server, which is based on the streaming server from Real Networks through a plug-in. This plug-in can be downloaded through the OTN website at http://www.oracle.com/technology/products/intermedia.
After following the installation procedure, an Oracle Filesystem mount point can be configured from the Helix administration Web page, as shown in Figure 4.10.
Once the mount point has been defined, you can then define a procedure to play media. Note that the Helix or Real server can play or display many kinds of streaming and nonstreaming media (e.g., images), so the procedure may have to be modified for the kind of media displayed. Here is an example of a PL/SQL procedure that plays video on the Helix server.
CREATE OR REPLACE PROCEDURE getVideo(IdIn IN VARCHAR2,
data OUT BLOB,
mimetype OUT VARCHAR2)
AS
mt varchar2(200);
BEGIN
SELECT t.video.getcontent(),
t.video.getmimetype()
INTO data,
mt
FROM videos t
WHERE t.item_id = IdIn;
if (mt like '%real%') then
mt := 'audio/x-pn-realaudio';
end if;
mimetype := mt;
END;Note that the MIME type may have to be modified. This is because the Real/Helix server maps file extensions to one particular MIME type, which may not be an intuitive MIME type, and only uses this MIME type to play the streaming media. In this particular case, the video must be presented to Real/Helix server as an audio MIME type for the server to recognize the media as valid. Other MIME types, such as Quicktime, must be represented as the exact one that Real/Helix expects.
Using a URL of the form RTSP://<HOST>:<PORT>/oracle/5 will retrieve the video in row five within a video player as Figure 4.11 shows.
This video can also be embedded into a Web page. The following HTML is a simple example of how this can be achieved. Note that we use an HTTP protocol to the Helix server to a ramgen mount point. This produces a metafile for the plug-in (so all the video is not downloaded) that references the RTSP streaming protocol.
HTML>
<HEAD>
<TITLE>Very Simple embeded Video page</TITLE>
</HEAD>
<BODY>
<EMBED name="realvideoax"
src="http://server.company.com:1234/ramgen/oracle/5"
type="audio/x-pn-realaudio-plugin">
</EMBED>
</BODY>
</HTML>The above code results in the Web video presentation shown in Figure 4.12.
interMedia offers a data source plug-in for Microsoft Windows Media Streaming Services to obtain its media directly from an Oracle database. The interMedia Plug-in feature is installed in a similar manner to other Microsoft Windows products. Because Microsoft Media Services are only available on Microsoft Windows server, the interMedia Plug-in for Microsoft Windows Media Streaming Services can only be installed on a Windows server machine. One thing to note is that this plug-in must be installed by a Windows user who has administrator privileges. Figure 4.13 shows the Media Services administration screen.
Once the installation wizard is finished, a mount point must be defined, similar to the mount point in Helix server, except the Microsoft mount point is not included as a part of the Media URL.
From the Media Services page, select the server you are managing, then select the Properties tab and click on the box Show All Plug-in Categories. Click on the Data Source category and find the Oracle interMedia custom source plug-in for WMS. Enable this plug-in and double click on the plug-in to display the plug-in properties. On this screen, you will be able to add mount points.
The mount point specifies an Oracle database and PL/SQL procedure to obtain the media. The PL/SQL procedure must define an output parameter named DATA of type BLOB or BFILE. The output parameters must use the names and data types shown in Table 4.1.
Like the Real/Helix server plug-in, the PL/SQL procedure can have zero or more input parameters that are taken from the requesting URL. These parameters are bound, in the order they appear in the URL, to available parameters in the PL/SQL procedure. For example, if you have the procedure:
myGetVid (id IN Number, name IN Varchar2, Data OUT blob, MIMEtype Out varchar2);
the URL could be mms://<SERVER>/<PUBLISHING POINT>/1/ George. In this case, id would be passed the value one and name would be passed the value “George.”
As the procedure that we defined in the Helix server example follows these conventions, we can use the procedure from the preceding section to obtain the media.
Please note that to fully support metadata extraction from Windows Media files, you must install interMedia Support into your database for the Windows Media file format from OTN (Oracle Technology Network). You will want to do this if you deal with Windows Media formats. Figure 4.14 shows a mount point being defined for Microsoft Media Services.
Once the mount point is defined and the plug-in is enabled, you must define a publishing point. This publishing point will define the URL used to access media. Figure 4.15 shows a publishing point being defined.
After the mount point and publishing point have been defined, media can be streamed from the database. The URL is of the form mms://<WINDOWS SERVER>/<ORACLE PUBLISHING POINT>/<SELECTION KEY>. For example, the URL might look like mms://server1.oracle.com/ OracleMedia/8.
Figure 4.16 is an example of a video in the playlist streamed from Oracle.
Oracle Wireless gives diverse user devices the ability to see content that is customized for their device, for example, a laptop using HTML versus a cellular telephone using WML. One of the things necessary for adapting content to specific devices is to supply a device with images that are tailored to it. For instance, a laptop may be able to display JPEG images at a large size where a cell phone may only be able to display WBMP images on a small screen. interMedia provides these adaptation services.
interMedia has a module within OracleAS Wireless that adapts images to a size, color depth, and format that is viewable on the device in question. In developing Oracle Wireless applications, a language called Mobile XML or XHTML/XForms is used. Since XHTML/XForms is currently the recommended language, our samples will be in this language. The Oracle Wireless Multi Channel Server takes XHTML/XForms, or applications that produce XHTML/XForms are used, and converts it into a representation that can be used for the target device.
Images are adapted using the Object tag. The following is an example of XHTML/XForms that will convert an image to a format, color depth, and size that is defined by the device’s profile in the Multi Channel Server.
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<%@ page import="java.util.*" %>
<%@ page session="false" %>
<html xmlns="http://www.w3.org/1999/xhtml"
style="_orcl-aural-props: fetchtimeout(180s)"
profile="http://xmlns.oracle.com/ias/dtds/xhtml+xforms/
0.9.0/1.0">
<head>
<title>Adapting images using the OBJECT tag in XHTML </
title>
</head>
<body>
<h3>Simple Image Adaptation</h3>
<div>
<object id="myid"
data="http://www.oracle.com/admin/images/oralogo.gif"
type="image/gif" />
</div>
</body>
</html>As you can see, to adapt images to the best format your device can handle is easy and automatic. Images that can be used by the requesting device unmodified by image adaptation are simply passed through unchanged. Figure 4.17 is how the screen would look on a standard Web browser.
However, image adaptation typically makes little sense for static images such as a logo. Although adapted images are cached using Oracle Web Cache so the logo would not be adapted for every request, image adaptation is much more useful for images that are dynamically produced from temporal data, such as stock graphs.
For static images that are used frequently, Oracle Wireless provides a command line tool to convert images to particular formats. Following is an example of using the command line tool to convert a color JPEG file to a smaller monochrome GIF.
ImageGenerate -inFile stock_600_450.jpg -outFile stock_240_180.gif -outW 240 -outH 180 -outContent monochrome -outFormat giff
If we want to use these static images produced with the command line tool, we will have to change our above example:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<%@ page import="java.util.*" %>
<%@ page session="false" %>
<%-- Prevent Page Caching --%>
<%
response.setHeader("Cache-Control", "no-store"); // HTTP
1.1
response.setHeader("Pragma", "no-cache"); // HTTP 1.0
%>
<html xmlns="http://www.w3.org/1999/xhtml"
style="_orcl-aural-props: fetchtimeout(180s)"
profile="http://xmlns.oracle.com/ias/dtds/xhtml+xforms/
0.9.0/1.0">
<head>
<title>Adapting images using the OBJECT tag in XHTML </
title>
</head>
<body>
<h3>Simple Image Adaptation with pre-formatted Logos</h3>
<div>
<object id ="myid" data="images/oralogo.gif"
type="image/gif">
<object id="myid2" data="images/logo.wbmp"
type="image/vnd.wap.wbmp"/>
</object>
</div>
</body>
</html>In this case, the nested object tag can deliver a GIF or a WBMP image. Depending on device support, we have left image adaptation on in this example in case a device that does not support these two formats requests this image.
For temporal data, such as stock graphs, we would use tags to prevent the optimization of caching the image before the <html> tag:
<%-- Prevent Page Caching --%>
<%
response.setHeader("Cache-Control", "no-store"); // HTTP
1.1
response.setHeader("Pragma", "no-cache"); // HTTP 1.0
response.setHeader("Expires", "0"); // prevents caching at
the proxy server
%>Another way to access interMedia data is to use Webdav access. Webdav (Web-based distributed authoring and versioning) is an HTTP standard that allows use of a remote Web server like a file system. This technology is also known as Web Folders. Webdav allows for download and upload of files in the server’s file system to the Webdav endpoint that is defined with a URL.
OraDAV (Apache module for Oracle distributed authoring and versioning) is an extension to Apache Webdav. OraDAV extends Webdav access to/ from files in the file system to data within the Oracle database. The data is exposed from a URL that eventually retrieves and/or uploads data from the database. OraDAV ships with OracleAS 10g.
The OraDAV module allows for media to be uploaded and downloaded from OraDAV containers stored in tablespaces on the database server machine. The Oracle interMedia OraDAV driver adds functionality to the OraDAV module to add functionality for multimedia data. This allows the media to be managed by Oracle rather than a file system.
Along with media managed in the OraDAV containers, the Oracle interMedia OraDAV driver can expose interMedia data that resides in exiting tables, and OracleAS 10g must be installed with the Apache module for Oracle distributed authoring and versioning option. You will also need to download the Oracle interMedia OraDAV driver available on OTN.
To make OraDAV expose interMedia media in a table, the following steps must be taken:
Install OracleAS 10g with Apache module for Oracle distributed authoring and versioning.
Download the Oracle interMedia OraDAV Driver from OTN.
Install the OraDAV infrastructure in ORDSYS.
Create an OraDAV container in the database.
Expose the interMedia data in a table through the container.
Define the OraDAV endpoint in the Webdav configuration file.
To install the necessary OraDAV infrastructure within the database, which includes packages, procedures, types, and granting of privileges, the following, which is documented in the INSTALL instructions, is performed:
Find the admin directory within the downloaded and expanded Oracle interMedia OraDAV driver and change to that directory.
Log into SQL/PLUS as a sysdba user.
Load a Java stored procedure to adjust GMT time, for example:
$ loadjava -force -grant PUBLIC -resolve -schema ordsys OraGMTOffset.class
Log in as an SYSDBA privileged user, set the schema to the ordsys schema and install the infrastructure packages, for example:
SQL> connect sys/welcome1 as sysdba SQL> alter session set current_schema = "ORDSYS"; SQL> @orddavin.sql
A container is a set of housekeeping tables that OraDAV uses to find content in an Oracle database as well as a place to store content uploaded to the container from the Web. The SQL file oradavcc.sql from the Oracle interMedia OraDAV driver is used to create a container. The following steps are taken to create a container:
Create a user to contain the OraDAV containers. This user should have CONNECT, RESOURCE, DROP TABLESPACE, CREATE TABLESPACE, and CREATE ANY TRIGGER privileges granted to it. For example:
SQL> create user oradavcontainers identified by oradavcontainers; SQL> grant CONNECT, RESOURCE, CREATE VIEW, DROP TABLESPACE, CREATE TABLESPACE, CREATE ANY TRIGGER to oradavcontainers;Log into a SYSDBA privileged user:
SQL> connect sys/welcome1 as sysdba.Grant access to the media table to the container user, for example,
SQL> grant select, insert on scott.photos to oradavcontainers.Still logged in as a SYSDBA user, set the schema to the container user: SQL> alter session set current_schema = “ORADAVCONTAINERS”.
Invoke orddavcc.sql and enter appropriate values, for example:
SQL> set serveroutput on; -- To see errors SQL> @orddavcc -- Enter a Container Name (<= 20 characters). This will -- serve as a prefix for tables, views, triggers, -- indices, and tablespaces created for the container. -- Default: oradav Container Name: photos -- Enter a Container Size in megabytes (e.g. 100) that -- you want for the two tablespaces created for the -- container. Currently twenty percent of this -- number will be used to create a tablespace -- for the OraDAV housekeeping tables created for the -- container. The other 80 percent will be used for -- the tablespace that owns the default storage -- for BLOBS. -- Default: 1000 Minimum:100 Container Size: 100 -- Enter Y if you want to not execute the create -- container logic but simply only want to generate -- a script (i.e. Logfile which can be later executed -- directly in SQL*Plus. -- Default: n NoExecute : n -- Enter a LogFileDir if you want to log the output of -- all DDL that is generated to create the container. -- Note that the LogFileDir MUST BE a directory -- owned and known -- by the Oracle Server to which you are connected. The -- server must have a UTL_FILE_DIR=<LogFileDir> set -- in its INIT.ORA file. -- Default: LogFileDirectory: -- Enter a LogFile name if LogFileDir is set. -- Default: oradav_schema.sql LogFile : -- Enter Y if you want tracing enabled. This means -- that DDL gets generated to go to serveroutput. -- Note that to enable this option you need to issue -- the following command in SQL*Plus: -- SET SERVEROUTPUT ON -- Default: n Trace Output : -- Enter Y if you would like a small index.html file to -- be added to the container being created. -- This index.htm can later be deleted or -- overwritten with any DAV client. -- It serves as an easy way to visually -- verify successful container creation via a browser. -- Default: y Add index.html : -- Enter Y if you would like the multimedia file -- to be stored in three separate tables of ORDImage, -- ORDAudio, and ORDVideo objects. -- Otherwise, all files will be stored as BLOBS. -- Default: y Use interMedia objects : -- Enter the server directory (including the -- trailing slash if appropriate) where tablespace -- data files for the container should be created. If -- you wish you can just hit enter the -- default location for your database will be used. -- Default: <DATABASE DEFAULT> Tablespace datafile directory : PL/SQL procedure successfully completed.
Expose the media columns through the container.
To expose interMedia content (ORDImage, ORDAudio, ORDVideo, and ORDDoc), a procedure is called when logged in as the container user. The following is an example of how the interMedia data in the PHOTOS table is exposed:
DECLARE BEGIN ORDSYS.DAV_PUBLIC.expose_interMedia_column( ORDSYS.DAV_PUBLIC.Generate_Ctx('PHOTOS'), -- For Container 'photos' 'SCOTT', 'PHOTOS', 'IMAGE', -- Media column 'ID', -- Primary key of table NULL, -- No Prefix NULL, -- No suffix '/image', -- Use image as path. 1, -- Add triggers ORDSYS.DAV_PUBLIC.DUPE_MODE_FAIL); -- Fail if duplicate would result COMMIT; END; /
After the previous steps, an OraDAV container has been built into the database. The next step is to let the Apache Web server, through OraDAV, know about it. The following entry is added to the file moddav.conf in the <AS_HOME>/Apache/oradav/conf/ directory.
<Location /photos> DAV Oracle DAVParam OraService ORCL10g DAVParam ORAUSER oradavcontainers DAVParam ORAPASSWORD oradavcontainers DAVParam ORAContainerName PHOTOS DAVParam ORAPackageName ORDSYS.DAV_API_DRIVER DAVParam OraLockExpirationPad 0 DAVParam OraException NORAISE DAVParam OraTraceLevel 0 DAVDepthInfinity ON DAVMinTimeout 5 Options Indexes </Location>
The Apache Web server must be restarted to reload its configuration files. This can be done with the following command:
opmnctl restartproc ias-component=HTTP_Server
Now you can get a list of images in a Web browser that are stored in the database with a URL of the following form: http://<SERVER>:<PORT>/photos/image.
As you can see from the list, the file names are given the form <PRIMARY KEY>.<FILE EXTENSION FOR MIMETYPE>, for example, http://server:7777/photos/image/1.jpg.
You can also now browse through the images, as well as upload images using Webdav clients such as Windows Explorer. To browse using Windows Explorer, click on Add a Network Place. In the wizard, specify the DAV location you just created (see Figure 4.18).
After this step, specify a name for the network place as shown in Figure 4.19.
At this point, you can browse the container, upload to it, and treat it as a normal WebDAV server, as shown in Figure 4.20.
For media applications, it is important to be able to move the media over the Web. interMedia data can be delivered onto the Web from or to the database in many ways. This chapter described a few of these methods, but by no means is this an exclusive list. Other Web-programming environments and languages can also access and deliver interMedia data, the only requirement is that they are able to perform a SELECT statement and access database BLOBS. For putting data into the database, the ability to call database procedures is required to set the media properties or process the media.