3 Developing Media Upload and Retrieval Applications

CREATE TABLE PHOTOS_UPLOAD(name VARCHAR2(256) UNIQUE NOT NULL,
                           mime_type      VARCHAR2(128),
                           doc_size       NUMBER,
                           dad_charset    VARCHAR2(128),
                           last_updated   DATE,
                           content_type   VARCHAR2(128),
                           blob_content   BLOB );

3.1.1.1 Running the Photo Album Application

After you have completed the setup tasks and have built the photo album application, including creating a database access descriptor (DAD) entry as described in the readme.txt file, you can run the photo album application by entering the following URL in the address field of your Web browser:

<protocol><hostname:port-number>/pls/photo_album_dad

The <protocol> field is http:// and the <hostname:port-number> field is the host name and port number of the system where your HTTP server is running.

When first invoked, the photo album application displays any images that are currently stored in the album. By default, the photo album is empty when first installed. To upload a new photo, select Upload new photo. Enter a description of the photo, the location where the photo was taken, and the name of the image file or browse to its directory location, then click Upload photo. The contents of the photo album are displayed along with a picture of the new photo. Click the thumbnail image to view the full-sized version of the photo. When the photo album application displays the text view image instead of its thumbnail image, the image format that was uploaded was not recognized by interMedia. Click view image to display the full-sized image.

You can now begin to load your photo album application with your favorite photos.

3.1.1.2 Description of the Photo Album Application

The photo album application is composed of a PL/SQL package specification containing 12 modules: 10 procedures, and 2 functions and with the package body implementation. Each module describes tasks that the photo album application performs and information that the photo album application displays on the Web page. These modules are categorized into specific actions and print operations to reduce redundancy, with each action and print operation being assigned to a specific procedure.

Some examples of action operations are the following:

• Display the contents of the photo album (view_album).

• Display an entry in the photo album (view_entry).

• Retrieve the media from the database and deliver it to the Web browser (deliver_media).

• Display the upload form for inserting a new photo in the photo album (view_upload_form).

• Insert a new photo in the photo album (insert_new_photo).

Print operations display some text on the Web page, such as:

• Print the upload form (print_upload_form).

• Print the common page header (print_page_header).

• Print the common page trailer (print_page_trailer).

• Print a heading message (print_heading).

• Print a text link (print_link).

The function get_preferred_format performs a task described later in this section, and returns a value.

Figure 3-1 shows a flow diagram of the names of the procedures and functions, and the order in which each is called to implement a particular operation in the photo album application.

The implementation of the photo album sample application is defined in the PL/SQL package named PHOTO_ALBUM. The package specification includes the following procedures and functions.

VIEW_ALBUM Procedure

Procedure VIEW_ALBUM displays the contents of the photo album on a Web page as a three column table that includes the image description, location, and a thumbnail image to view if the image format is supported by interMedia. See Figure 3-2.

Thumbnail images display as anchor tags that, when selected, are used to display the full-sized image using the following statements:

htp.print( '<td headers="image"><a href="PHOTO_ALBUM.VIEW_ENTRY' ||
         '?entry_id=' || entry.id || '">' );

IF entry.thumb.contentLength > 0 
THEN
    htp.print( '<img src="PHOTO_ALBUM.DELIVER_MEDIA?media=thumb' ||
                 ampersand || 'entry_id=' || entry.id || '"' ||
               ' height=' || entry.thumb.height ||
               ' width=' || entry.thumb.width ||
               ' alt="' || htf.escape_sc( entry.description ) || '"' ||
               ' border=1></a></td>' );
ELSE
    htp.print( '[view image]</a></td>' );
END IF;

The thumbnail images for all entries are displayed by calling the PHOTO_ALBUM.DELIVER_MEDIA procedure using the tag <IMG SRC="PHOTO_ALBUM.DELIVER_MEDIA?media=thumb...> within the preceding IF...THEN...ELSE...END IF statement. Notice that in the query string, ?media=thumb, that the name/value pair indicates that the parameter named media declared in the deliver_media procedure has a value of thumb, which represents the thumbnail image.

In the preceding IF...THEN...ELSE...END IF statement, if the image format is not supported by interMedia, then no thumbnail image would have been created when the image was uploaded. In this special case, a text link of view image is displayed as indicated in the ELSE clause. Selecting view image in the image column, calls the PHOTO_ALBUM.VIEW_ENTRY procedure to display the full-sized image.

At the bottom of the table is Select the thumbnail to view the full-size image, which when selected, calls the PHOTO_ALBUM.VIEW_UPLOAD_FORM procedure. The Web browser also displays a common page header with the page title interMedia PL/SQL Web Toolkit Photo Album Demo by calling the PHOTO_ALBUM.PRINT_PAGE_HEADER procedure. In addition, the Web page displays a common page trailer by calling the PHOTO_ALBUM.PRINT_PAGE_TRAILER procedure. Note that the page trailer is controlled by a Boolean operation, and in this case, is set to false and so the trailer is not displayed. At the bottom of the page is an Upload new photo link to upload a new photo, which when selected, calls the PHOTO_ALBUM.VIEW_UPLOAD_FORM procedure.

VIEW_ENTRY Procedure

Procedure VIEW_ENTRY displays the description, location, and a full-sized version of the image by calling the PHOTO_ALBUM.DELIVER_MEDIA procedure, using the tag <IMG SRC="PHOTO_ALBUM.DELIVER_MEDIA?media=image...> shown in the following statement:

htp.prn( '<tr><td scope= "col" valign="top"><b>Photo:</b></td>
              <td scope="col">' ||
'<img src="PHOTO_ALBUM.DELIVER_MEDIA?media=image' ||
  ampersand || 'entry_id=' || entry_id || '"' );
htp.prn( ' alt="' || htf.escape_sc( entry.description ) || '"' );

Notice that in the query string, ?media=image, that the name/value pair indicates that the parameter named media declared in the DELIVER_MEDIA procedure has a value of image, which represents the full-sized image.

The VIEW_ENTRY procedure is called from the VIEW_ALBUM procedure.

The Web page also displays the common page header (the Web page title) by calling the PHOTO_ALBUM.PRINT_PAGE_HEADER procedure. In addition, the common page trailer is called with the PHOTO_ALBUM.PRINT_PAGE_TRAILER procedure. In this case, the Boolean expression is set to true and displays the Return to photo album link, near the bottom of the Web page. When this link is selected, it calls the PHOTO_ALBUM.VIEW_ALBUM procedure.

DELIVER_MEDIA Procedure

Procedure DELIVER_MEDIA retrieves the image from the database and delivers either a thumbnail or a full-sized image to the Web browser, depending on the value of the field named MEDIA. If the value of the MEDIA field is thumb, a thumbnail image is delivered; otherwise, a full-sized image is delivered.

If the image requested is in the browser cache and the cache is valid, then the image is retrieved from cache; otherwise, the MIME type of the image is set based on the image attribute value in the database, then the image is retrieved from the database and is delivered to the browser, as follows:

IF ordplsgwyutil.cache_is_valid( local_image.getUpdateTime() )
THEN
  owa_util.status_line( ordplsgwyutil.http_status_not_modified );
  RETURN;
END IF;
.
.
.
owa_util.mime_header( local_image.mimeType, FALSE );
ordplsgwyutil.set_last_modified( local_image.getUpdateTime() );
owa_util.http_header_close();

IF owa_util.get_cgi_env( 'REQUEST_METHOD' ) <> 'HEAD' THEN  
  wpg_docload.download_file( local_image.source.localData );
END IF;

Whether the image retrieved is a thumbnail or a full-sized image, two packages of the PL/SQL Web Toolkit are used to do this operation. The first package, owa_util, sets the MIME type of the image, while the second package, wpg_docload, downloads the image from the database to the Web browser.

The DELIVER_MEDIA procedure is called by the PHOTO_ALBUM.VIEW_ALBUM and PHOTO_ALBUM.VIEW_ENTRY procedures.

VIEW_UPLOAD_FORM Procedure

Procedure VIEW_UPLOAD_FORM displays the upload form by calling the PHOTO_ALBUM.PRINT_UPLOAD_FORM procedure. The procedure also displays the common page header by calling the PHOTO_ALBUM.PRINT_PAGE_HEADER procedure, and displays the common page trailer by calling the PHOTO_ALBUM.PRINT_PAGE_TRAILER procedure. The VIEW_UPLOAD_FORM procedure sets the Boolean expression to true to display the common page trailer.

PRINT_UPLOAD_FORM Procedure

Procedure PRINT_UPLOAD_FORM calls the PHOTO_ALBUM.PRINT_HEADING procedure to display the message header Upload a new photo on the Web page, then calls the PHOTO_ALBUM.INSERT_NEW_PHOTO procedure to do the insert operation. In addition, the user input for the image description, image location, and image file name is entered by the user upon request. Clicking Upload photo initiates the upload operation because the input type is submit.

INSERT_NEW_PHOTO Procedure

Procedure INSERT_NEW_PHOTO inserts a new photo into the photo album. This procedure also checks to make sure a file name was entered as input from the previous PHOTO_ALBUM.PRINT_UPLOAD_FORM procedure call, and that the file has content or is not of zero length. If the DESCRIPTION field is blank, the file name is used as the description. Next, the ORDSYS.ORDIMAGE.INIT( ) function is called to initialize both the thumb and image ORDImage object type columns with an empty BLOB for the new row to be stored in the photos table. A SQL SELECT FOR UPDATE statement fetches the newly initialized thumbnail and full-sized image object type columns for update. A DBMS_LOB.COPY operation loads the image from the upload table into the full-sized image ORDImage object type column, then calls the setProperties( ) method to read the image data, get the values of the image object attributes, and store them in the object attributes for the image column.

Because some browsers cannot display some image formats inline, in this sample application, BMP formatted images are converted to either a JPEG image format (for images with greater than 8 bits of color) or a GIFF image format (for images with less than 9 bits of color) by calling the GET_PREFERRED_FORMAT function. A processCopy( ) operation is performed on the full-sized image to create the thumbnail image, then both the full-sized image and the thumbnail image are updated in the database with a SQL UPDATE statement. After the row is updated in the photos table, then the same row containing these new images is deleted from the upload PHOTOS_UPLOAD table. The upload operation returns a message "Photo successfully uploaded into photo album". Finally, the PHOTO_ALBUM.VIEW_ALBUM procedure is called again to refresh the Web page and the PHOTO_ALBUM.PRINT_PAGE_TRAILER procedure is called to display the common page trailer with the Boolean expression set to true to display the trailer.

PRINT_PAGE_HEADER Procedure

Procedure PRINT_PAGE_HEADER displays the common page header, interMedia PL/SQL Web Toolkit Photo Album Demo.

PRINT_PAGE_TRAILER Procedure

Procedure PRINT_PAGE_TRAILER contains a Boolean expression, which when set to true, calls the PRINT_LINK procedure; otherwise when set to false, it displays no text link.

PRINT_HEADING Procedure

Procedure PRINT_HEADING displays a one line message for the specified value of the MESSAGE field when this procedure is called.

PRINT_LINK Procedure

Procedure PRINT_LINK displays Return to photo album at the bottom of the Web page, which when selected, calls the PHOTO_ALBUM.VIEW_ALBUM procedure. The PRINT_LINK procedure is called from the PHOTO_ALBUM.PRINT_PAGE_TRAILER procedure.

For more information about building applications using PL/SQL, see Oracle HTTP Server mod_plsql User's Guide, Oracle Application Server 10g PL/SQL Web Toolkit Reference, Oracle HTTP Server Administrator's Guide, Oracle Database Application Developer's Guide - Fundamentals, PL/SQL User's Guide and Reference, and PL/SQL Packages and Types Reference.

3.1.2.1 Running the Java Servlet Photo Album Application

After you have completed the setup tasks and have built the Java servlet photo album application, you can run the application by entering the following URL in the address field of your Web browser:

• Default installation of Oracle Application Server 10g or Oracle Database

  <protocol><hostname:port-number>/servlet/PhotoAlbumServlet
  
  

• Default installation of Tomcat 3.2 on Windows NT

  <protocol><hostname:port-number>/examples/servlet/PhotoAlbumServlet
  
  

  The <protocol> field is http://, and the <hostname:port-number> field is the host name and port number of the system where your HTTP server is running.

When first invoked, the photo album application displays any images that are currently stored in the album. By default, the photo album is empty when first installed. To upload a new photo, select Upload new photo. Enter a description of the photo, the location where the photo was taken, and the name of the image file or browse to its directory location, then click Upload photo. The contents of the photo album are displayed along with a picture of the new photo. Click the thumbnail image to view the full-sized version of the photo.

When the photo album application displays the text view image instead of its thumbnail image, the image format that was uploaded was not recognized by interMedia. Click view image to display the full-sized image.

You can now begin to load your photo album application with your favorite photos.

3.1.2.2 Description of the interMedia Java Servlet Photo Album Application

The interMedia Java Servlet Photo Album application combines both business logic and the presentation into a single servlet, which when compiled, creates two class files, PhotoAlbumServlet.class and PhotoAlbumRequest.class.

To follow along with the description of tasks, users should refer to a copy of the PhotoAlbumServlet.java file, which can be found in:

<ORACLE_HOME>/ord/http/demo/servlet (on UNIX)

<ORACLE_HOME>\ord\http\demo\servlet (on Windows)

PhotoAlbumServlet Class

The PhotoAlbumServlet class performs the following tasks:

• Extends the HttpServlet and contains the user entered connection information.

  public class PhotoAlbumServlet extends HttpServlet
  
  

• Accepts connection information by allowing you to select the connection method, supply the necessary connection information, and optionally change the user name and password to connect to a schema other than scott/tiger.

  private final static String JDBC_CONNECT_STRING =   
  //  "jdbc:oracle:oci:@<SQL*Net TNS name>";   // 9i JDBC OCI driver
  //  "jdbc:oracle:oci8:@<SQL*Net TNS name>";   // 8i JDBC OCI driver
      "jdbc:oracle:thin:@<host>:<port>:<sid>";  // JDBC Thin driver
  
  private final static String JDBC_USER_NAME = "scott";
  private final static String JDBC_PASSWORD  = "tiger";
  
  

• Instantiates a Java stack used to implement a simple connection-pooling mechanism.

  private static Stack connStack = new Stack();
  
  

• Defines a flag to indicate whether or not the JDBC Thin driver has been loaded.

  private static boolean driverLoaded = false;
  
  

• Defines a servlet initialization method.

  public void init( ServletConfig config ) throws ServletException
  {
      super.init(config);
  }
  
  

• Defines a doGet( ) method to process an HTTP GET request containing an HttpServletRequest object and HttpServletResponse object and instantiates a PhotoAlbumRequest object to process the request to deliver either a full-sized or thumbnail image to the browser, or to display an upload form or the contents of the photo album as thumbnail images.

  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 = getConnection();
  
          //
          // Instantiate a PhotoAlbumRequest object to process the request.
          //
          PhotoAlbumRequest albumRequest =
              new PhotoAlbumRequest( conn, request, response );
  
          //
          // Figure out what to do based on query string parameters.
          //
          String view_media = request.getParameter( "view_media" );
          if ( view_media != null )
          {
              //
              // Deliver a full-sized or thumbnail image to the browser.
              //
              albumRequest.viewMedia( view_media );
              return;
          }
          else if ( request.getParameter( "view_form" ) != null )
          {
              //
              // Display the HTML upload form.
              //
              albumRequest.viewUploadForm();
          }
          else if ( request.getParameter( "view_entry" ) != null )
          {
              //
              // Display full-sized photo image.
              //
              albumRequest.viewPhoto();
          }
          else
          {
              //
              // Display album contents with thumbnail images by default.
              //
              albumRequest.viewAlbum();
          }
      }
      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.
          //
          freeConnection( conn );
      }
  }
  
  

• Defines a doPost( ) method to process an HTTP POST request used to upload a new photo into the album by instantiating a PhotoAlbumRequest object to process the request and then calling the insertNewPhoto( ) 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 = getConnection();
  
          //        // Instantiate a PhotoAlbumRequest object to process the request.
          //
          PhotoAlbumRequest albumRequest =
              new PhotoAlbumRequest( conn, request, response );
  
          //
          // Insert the photo into the album.
          //
          albumRequest.insertNewPhoto();
      }
      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.
          //
          freeConnection( conn );
      }
  }
  
  

• Defines a getConnection( ) method.

  private Connection getConnection()
      throws SQLException
  {
      OracleConnection conn = null;
  
      //
      // Synchronize on the stack object. Load the JDBC driver if not yet
      // done. If there's a free connection on the stack, then pop it off
      // the stack and return it to the caller. Otherwise, create a new
      // connection object and call the version compatibility initialization
      // method.
      //
      synchronized( connStack )
      {
          if ( !driverLoaded )
          {
              DriverManager.registerDriver(
                                  new oracle.jdbc.driver.OracleDriver() );
              driverLoaded = true;
          }
          if ( connStack.empty() )
          {
              conn = (OracleConnection)DriverManager.getConnection
                  ( JDBC_CONNECT_STRING, JDBC_USER_NAME, JDBC_PASSWORD );
          }
          else
          {
              conn = (OracleConnection)connStack.pop();
          }
      }
  
      //
      // Enable auto-commit by default.
      //
      conn.setAutoCommit( true );
  
      return conn;
  }
  
  

• Defines a freeConnection( ) method.

  private void freeConnection( Connection conn )
  {
      //
      // Synchronize on the stack object, then push the connection onto the
      // stack.
      //
      if ( conn != null )
      {
          synchronized( connStack )
          {
              connStack.push( conn );
          }
      }
  }
  
  

In summary, the PhotoAlbumServlet class responds to the HTTP GET and POST requests by allocating a JDBC connection from a connection pool. Each HTTP GET or POST request is assigned its own JDBC connection from the pool to ensure that multiple requests can be serviced concurrently. An HTTP GET request is used to retrieve image data from the photo album and an HTTP POST request is used to upload image data into the photo album. Then, an instance of the PhotoAlbumRequest class is created to execute the request, executes the request, then releases the JDBC connection back to the pool at the end of the request.

PhotoAlbumRequest Class

The PhotoAlbumRequest class does the actual processing of an HTTP GET or POST request and defines the following methods: viewAlbum( ), viewPhoto( ), viewMedia( ), viewUploadForm( ), insertNewPhoto( ), printPageHeader( ), printPageTrailer( ), printMessage( ), printHeading( ), and printLink( ), and the getPreferredFormat( ) function.

In the viewMedia( ) and insertNewPhoto( ) methods, three objects, OrdHttpResponseHandler, OrdHttpUploadFormData, and OrdHttpUploadFile, are instantiated. These objects are used to call the methods of the respective OrdHttpResponseHandler, OrdHttpUploadFormData, OrdHttpUploadFile classes of the Oracle interMedia Java Classes for servlets and JSP. For example, in the viewMedia( ) method, the OrdHttpResponseHandler object is instantiated and used to call the sendImage( ) method as shown in the following code:

OrdHttpResponseHandler handler =
    new OrdHttpResponseHandler( request, response );
handler.sendImage( img );

The viewAlbum( ), viewPhoto( ), viewMedia( ), and insertNewPhoto( ) methods use the ORAData (formerly getCustomDatum) and ORADataFactory (formerly the getFactory) interfaces supplied by Oracle to get the image or thumbnail OrdImage object from the result set to obtain height and width information, to retrieve an image from an OrdImage Java object and deliver it to the browser, and to upload an image in an OrdImage Java object and to also update it in the photos table. For example, the following code excerpt is from the viewAlbum( ) method:

OrdImage img =
    (OrdImage)rset.getORAData( 4, OrdImage.getORADataFactory() );
.
.
.
out.print( "<td headers=\"image\"><a href=\"" + servletUri +
           "?view_entry=yes&id=" + id + "\">" );
if ( img.getContentLength() > 0 )
{
    if (img.getMimeType().startsWith("image/"))
    {
out.print( "<img src=\"" + servletUri +
                           "?view_media=thumb&id=" + id + "\"" +
                           " height=" + img.getHeight() +
                           " width=" + img.getWidth() +
                           " alt=\"" + description + "\"" +
         " border=1>" );
    }
}
else
{
    out.print( "[view image]" );
}
out.println( "</a></td>" );
out.println( "</tr>" );

What follows is a more detailed description of each method and what it does:

• The viewAlbum( ) method does the following:

  • Initializes the row count to zero.

  • Writes a common page header on the HTML page using the printPageHeader( ) function.

  • Executes a SELECT statement to fetch all the thumbnail images in the photo album, order them by description, and display the description and location information for each along with the thumbnail image if it exists, and returns the results in a result set.

  • Displays the thumbnail images in an HTML table with column headers labeled Description, Location, and Image.

  • Within a while block, reads the contents of the result set by reading the first row's contents beginning with the id value, displays the description and location values, then gets the thumbnail OrdImage object and builds the height and width attributes for each thumbnail image.

  • Displays the thumbnail image using an HTML anchor tag that can be used to display the full-sized image. When a user clicks the thumbnail image or view image, the full-sized image is displayed.

  • Displays the contents of the photo album within an HTML anchor tag using the tag <IMG SRC="<servlet-path>?view_media=thumb&id=..."> to display the thumbnail images, where <servlet-path> is the value of servletUri. If the thumbnail image was not created because the image format was not supported by interMedia, the text view image is displayed instead.

  • Increments the row count to see if the photo album is empty; if so, display the message "The photo album is empty".

  • Displays an HTML anchor tag near the bottom of the HTML page using the printLink( ) function with the text Upload new photo.

  • Writes a common trailer at the bottom of the HTML page by calling the printPageHeader( ) function, however, in this case, sets the Boolean argument to false to not display the common page trailer.

  • Closes the result set and the statement.

• The viewPhoto( ) method displays the full-sized version of a photo and does the following:

  • Writes a common page header on the HTML page using the printPageHeader( ) function.

  • Gets the value of the id column for the entry being viewed.

  • Executes a SQL SELECT statement to fetch the entry's description, location, and full-sized image where the value of id in the where clause is a parameter marker and returns the results in a result set.

  • Gets the image OrdImage object from the result set in order to later build the image height and width attributes within the <IMG SRC=...> image tag.

  • Displays the full-sized image in an HTML table beginning with the column names Description and Location, and displays the entry's values for these two columns.

  • Builds the URL to fetch a full-sized image for this entry by using an image tag <IMG SRC="<servlet-path>?view_media=image&id=..."> to display an image in the column labeled Photo, where <servlet-path> is the value of servletUri.

  • Displays the full-sized images height and width by calling the getHeight( ) and getWidth( ) interMedia object methods. If the image format is not recognized by interMedia, height and width values will be zero and will not be displayed.

  • Writes a common page trailer at the bottom of the HTML page by calling the printPageHeader( ) function and setting its Boolean argument to true to display the common page trailer.

  • Closes the result set and the statement.

• The viewMedia( ) method is invoked by both thumbnail and full-sized image URLs to retrieve a thumbnail or full-sized image from the photos table and deliver it to the browser using the OrdHttpResponseHandler class. This method does the following:

  • Executes a SQL SELECT statement to fetch either the thumbnail or full-sized image where the value of id in the where clause is a parameter marker and returns the results in a result set. The SQL SELECT statement is built dynamically with the string media equating to either the thumbnail image column or the full-sized image column.

  • Fetches a row from the result set.

  • Gets the OrdImage object from the result set.

  • Uses the OrdHttpResponseHandler class to create an OrdHttpResponseHandler object to retrieve the image from the OrdImage object and deliver it to the browser using the sendImage( ) method. The sendImage( ) method supports browser content caching by supporting the If-Modified-Since and Last-Modified headers.

  • Closes the result set and the statement.

• The viewUploadForm( ) method displays an HTML form that allows users to upload new photos and does the following:

  • Calls the printPageHeader( ) function to produce the common page header.

  • Defines the form action as a multipart/form-data POST request.

  • Calls the upload_form_fields static string containing the contents of the upload form. The upload form is defined as a table with rows labeled Description and Location, with an input type of text and named description and location respectively, followed by a row labeled File name:, with an input type of file and named photo, and finally a row with no label, an input type of submit, and a value of Upload photo.

  • Calls the printPageTrailer( ) function to produce the common page trailer.

• The insertNewPhoto( ) method does the following:

  • Uses the OrdHttpUploadFormData class to parse a multipart/form-data POST request containing an uploaded photo.

  • Uses the OrdHttpUploadFile class to upload the new photo into the database.

  • Executes a SQL SELECT photos_sequence.nextval statement to get the next value of the id column for the new row to be inserted into the photos table.

  • Executes a SQL INSERT statement to insert a new row in the photos table.

  • Executes a SQL SELECT...FOR UPDATE statement to fetch the initialized full-sized and thumbnail image objects from the photos table.

  • Calls the loadImage( ) method in the OrdHttpUploadFile class to populate an OrdImage object named image with the full-sized image and sets the properties or attribute values of the image object based on the image contents.

  • Checks to see what the image format is and if it is an image format that may not be displayed inline by a browser, such as a BMP image format, then calls the getPreferredFormat( ) method to convert a BMP image format and return the preferred image format.

  • Calls the ProcessCopy( ) method in the OrdImage class to process the full-sized image, create a thumbnail image, and populate an OrdImage object named thumb.

  • Executes a SQL UPDATE statement to update the full-sized and thumbnail images in the database.

  • Displays a photo upload success message and then directs the browser to refresh the page.

• A getPreferredFormat( ) private function, in this sample application, converts a BMP image format and returns the preferred image file format based on the number of colors in the image; returns a MONOCHROME image format if there are no colors, or a JPEG if there are more than 8 colors, or a GIF if there greater than 0 and fewer than 8 colors.

• A printPageHeader( ) private function displays an HTML header that is common to all HTML responses.

• A printPageTrailer( ) private function displays an HTML trailer that is common to all HTML responses.

• A printMessage( ) private function prints a message on the HTML page.

• A printHeading( ) private function prints a header on the HTML page.

• A printLink( ) function produces an HTML anchor tag in a standard format.

3.1.3.1 Running the JSP Photo Album Application

After you have completed the setup tasks and have built the JSP photo album application, you can run the JSP photo album application by entering the following URL in the address field of your Web browser:

• Default installation of Oracle Application Server 10g or Oracle Database

  <protocol><hostname:port-number>/demo/PhotoAlbum.jsp
  
  

• Default installation of Tomcat 3.2 on Windows NT

  <protocol><hostname:port-number>/examples/jsp/PhotoAlbum.jsp
  
  

  The <protocol> field is http://, and the <hostname:port-number> field is the host name and port number of the system where your HTTP server is running.

When first invoked, the photo album application displays any images that are currently stored in the album. By default, the photo album is empty when first installed. To upload a new photo, select Upload new photo. Enter a description of the photo, the location where the photo was taken, and the name of the image file or browse to its directory location, then click Upload photo. The contents of the photo album are displayed along with a picture of the new photo. Click the thumbnail image to view the full-sized version of the photo.

When the photo album application displays the text view image instead of its thumbnail image, the image format that was uploaded was not recognized by interMedia. Click view image to display the full-sized image.

You can now begin to load your photo album application with your favorite photos.

3.1.3.2 Description of the interMedia JSP Photo Album Application

The JSP photo album application separates the business logic from the presentation by having a JavaBean containing methods that are accessed from each of five JSP files. When compiled, the application creates the PhotoAlbumBean.class file, which contains the user-entered connection information and defines the following methods: selectTable( ), selectRowById( ), fetch( ), insertNewPhoto( ), release( ), getConnection( ), freeConnection( ), setId( ), setDescription( ), setLocation( ), getImage( ), and getThumb( ), and the functions: getId( ), getDescription( ), and getLocation( ), and getPreferredFormat( ).

To follow along with the description of tasks, users should refer to a copy of each JSP file, which can be found in:

<ORACLE_HOME>/ord/http/demo/jsp (on UNIX)

<ORACLE_HOME>\ord\http\demo\jsp (on Windows)

In the PhotoAlbumEntryViewer, PhotoAlbumMediaViewer, PhotoAlbum, and PhotoAlbumInsertPhoto JSP files, the jsp:useBean action tag is used to establish an ID and association with the PhotoAlbumBean class and the OrdHttpJspResponseHandler and OrdHttpUploadFormData classes of Oracle interMedia Java Classes for servlets and JSP. For example, the following code appears in the PhotoAlbumInsertPhoto JSP file:

<jsp:useBean id="album" scope="page" class="PhotoAlbumBean"/>
<jsp:useBean id="handler" scope="page"
             class="oracle.ord.im.OrdHttpJspResponseHandler"/>
<jsp:useBean id="formData" scope="page"             class="oracle.ord.im.OrdHttpUploadFormData"/>

This jsp:useBean action tag is used so these objects can be referenced by their respective ID values (album, handler, and formData) to call the methods of these classes.

The OrdHttpUploadFile class of Oracle interMedia Java Classes for servlets and JSP is defined as an object with the name uploadPhoto in the insertNewPhoto( ) method in the PhotoAlbumBean.java file and then used to call its loadImage( ) method to load the photo into the photos table as shown in the following code excerpts:

public void insertNewPhoto( OrdHttpUploadFile uploadPhoto )
        throws SQLException, ServletException, IOException
.
.
.
uploadPhoto.loadImage( image );
.
.
.

The insertNewPhoto( ) method defined in the PhotoAlbumBean.java file, uses the ORAData (formerly getCustomDatum) and ORADataFactory (formerly the getFactory) interfaces supplied by Oracle to upload an image and a thumbnail image in an OrdImage Java object. First, the method executes a SQL SELECT...FOR UPDATE statement to select the row for update, and then, executes a SQL UPDATE statement to update the image and thumb columns for that row in the photos table as shown in the following code excerpts:

stmt = (OraclePreparedStatement)conn.prepareStatement(
            "select image,thumb 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" );
}
image = (OrdImage)rset.getORAData( 1, OrdImage.getORADataFactory());
thumb = (OrdImage)rset.getORAData( 2, OrdImage.getORADataFactory());

rset.close();
stmt.close();
.
.
.
    //
    // Prepare and execute a SQL statement to update the full-sized and
    // thumbnail images in the database.
    //
    stmt = (OraclePreparedStatement)conn.prepareStatement(
               "update photos set image = ?, thumb = ? where id = ?" );
    stmt.setORAData( 1, image );
    stmt.setORAData( 2, thumb );
    stmt.setString( 3, id );
    stmt.execute();
    stmt.close();

    //
    // Commit the changes.
    //
    conn.commit();
}

The fetch( ) method defined in the PhotoAlbumBean.java file or the PhotoAlbumBean JavaBean, fetches the next row from the result set using the ORAData and ORADataFactory interfaces to retrieve the image and the thumbnail image from an OrdImage Java object, and delivers each to the browser, as shown in the following example:

public boolean fetch()
    throws SQLException
{
    if ( rset.next() )
    {
        id = rset.getString( 1 );
        description = rset.getString( 2 );
        location = rset.getString( 3 );
        image = (OrdImage)rset.getORAData( 4, OrdImage.getORADataFactory() );
        thumb = (OrdImage)rset.getORAData( 5, OrdImage.getORADataFactory() );
        return true;
    }
    else
    {
        rset.close();
        stmt.close();
        return false;
    }
}

What follows is a more detailed description of each JSP file.

PhotoAlbum.jsp

This JSP file is the entry point to the JSP photo album application and does the following:

• Uses the PhotoAlbumBean JavaBean to access the contents of the photos table.

• Uses the OrdHttpJspResponseHandler class to facilitate the retrieval of image data from the photos table and its delivery to a browser or other HTTP client from a Java servlet.

• Displays the title of the page in the HTML header and in the common page header.

• Displays the thumbnail images in a table using column headers labeled, Description, Location, and Image.

• Uses a try/catch block to ensure the JDBC connection is released.

• Calls the selectTable( ) method to select all the rows in the photos table.

• Initializes the row count to zero.

• Displays an entry in the photo album by calling the getDescription( ) method, then the getLocation( ) method, and then printing the values in the appropriate columns. If the location information is blank, print a space in the Location column.

• Displays the contents of the photo album as thumbnail images using an HTML anchor tag to call the PhotoAlbumEntryViewer.jsp file to get the ID value by calling the getID( ) function.

• Calls the getThumb( ) method to get the thumbnail image and calls the getContentLength( ) method to determine the image length.

• Tests to see if the value returned for the image length is greater than 0, and if so uses an image tag of the form <IMG SRC="PhotoAlbumMediaViewer.jsp?media=thumb&...> to display the thumbnail image; otherwise, prints the link view image in the column header labeled Image, which, when clicked, retrieves the full-sized image.

• Displays a message "The photo album is empty" if the photo album is empty. If the photo album is not empty, the following message is displayed "Select the thumbnail to view the full-sized image".

• Ends the try/catch block with a finally clause and releases the JDBC connection by calling the release( ) method.

• Displays a link to the upload form with the text Upload new photo at the bottom of the page that calls the PhotoAlbumUploadForm.jsp file.

PhotoAlbumEntryViewer.jsp

This JSP file is called by the PhotoAlbum.jsp file that displays one full-sized version of a photo in the album. This JSP file does the following:

• Uses the PhotoAlbumBean JavaBean to access the contents of the photos table.

• Uses the OrdHttpJspResponseHandler class to facilitate the retrieval of image data from the photos table and its delivery to a browser or other HTTP client from a Java servlet.

• Displays the title of the page in the HTML header and in the common page header.

• Defines a string named id that calls the getParameter( ) method to get the id value.

• Displays a message "Malformed URL, no id parameter" in the event the value of the id string is null.

• Uses a try/catch block to ensure the JDBC connection is released.

• Calls the selectRowById( ) method with the value of id to select the entry to be displayed. If the next row to be fetched for that id value is not found, display a message "Entry not found: <id value>".

• Displays the entry in the album by calling the getDescription( ) method and displaying its value under the header Description, calling the getLocation( ) method and displaying its value under the Location header.

• Displays one full-sized version of a photo in the album using an image tag in the form <IMG SRC="PhotoAlbumMediaViewer.jsp?media=image&..."> under the Photo header.

• Displays the full-sized images height and width by calling the getHeight( ) and getWidth( ) methods. If the image format is not recognized by interMedia, height and width values will be zero and will not be displayed.

• Displays a link at the bottom of the page Return to photo album that calls the PhotoAlbum.jsp file.

• Ends the try/catch block, and with a finally clause, releases the JDBC connection by calling the release( ) method.

PhotoAlbumMediaViewer.jsp

This JSP file is called by the PhotoAlbum.jsp and PhotoAlbumEntryViewer.jsp files and retrieves a single thumbnail or full-sized image from the photos table using the PhotoAlbumBean JavaBean and delivers it to the browser using the OrdHttpResponseHandler class. This JSP file does the following:

• Uses the PhotoAlbumBean JavaBean to access the contents of the photos table.

• Uses the OrdHttpJspResponseHandler class to facilitate the retrieval of image data from the photos table and its delivery to a browser or other HTTP client from a Java servlet.

• Defines a string named id that calls the getParameter( ) method to get the id value.

• Defines a string named media that calls the getParameter( ) method to get the media value.

• Sets a condition to proceed as long as the value of the string id and the value of the string media is not null.

• Uses a try/catch block to ensure the JDBC connection is released.

• Calls the selectRowById( ) method to select a specific row from the photos table for the value of id.

• Delivers the full-sized or thumbnail image by first calling the setPageContext( ) method of the OrdHttpJspResponseHandler class to specify the page context object; then, calling the getImage( ) method to return the image to the OrdImage object; then, calling the sendImage( ) method of the OrdHttpResponseHandler class to retrieve the image from the OrdImage object and deliver it to the browser. If the value of media is image, an image is delivered to the browser; if the value of media is thumb, a thumbnail image is delivered to the browser. The sendImage( ) method supports browser content caching by supporting the If-Modified-Since and Last-Modified headers.

• Ends the try/catch block with a finally clause and releases the JDBC connection by calling the release( ) method.

• Displays the following message in the event the request is not understood "PhotoAlbumMediaViewer.jsp - malformed URL".

PhotoAlbumUploadForm.jsp

This JSP file is called by the PhotoAlbum.jsp file that displays an HTML form to allow users to upload new photos into the album. This JSP file does the following:

• Displays the title of the page in the HTML header and in its common page header.

• Displays any error message under the header "Error message" from a previous attempt to upload an image to determine whether or not the value of a string is not null after calling the getParameter( ) method with an argument of error.

• Displays a header with the text Upload a new photo.

• Defines the form action specifying the PhotoAlbumInsertPhoto.jsp file to process the upload request as a multipart/form-data POST request.

• Displays the upload form with rows labeled Description, Location, and File name:.

• Displays the contents of the upload form defined as a table with rows labeled Description and Location, both with an input type of text and named description and location respectively, followed by a row labeled File name: with an input type of file and named photo, and finally followed by a row with no label and an input type of submit and a value of Upload photo.

• Displays a link at the bottom of the page Return to photo album that calls the PhotoAlbum.jsp file.

PhotoAlbumInsertPhoto.jsp

This JSP file is called by the PhotoAlbumUploadForm.jsp file that uses the OrdHttpUploadFormData class to parse the POST data in a POST request containing the uploaded photo. This JSP file does the following:

• Uses the PhotoAlbumBean JavaBean to access the contents of the photos table.

• Uses the OrdHttpJspResponseHandler class to facilitate the retrieval of image data from the photos table and its delivery to a browser or other HTTP client from a JSP file.

• Uses the OrdHttpUploadFormData class to facilitate the processing of POST requests by parsing the POST data containing the multipart/form-data encoding, and making the contents of regular form fields and uploaded files readily accessible to a JSP file.

• Sets the value of the strings description and location to null and the OrdHttpUploadFile object uploadPhoto to null.

• Uses a try/catch block to ensure the JDBC connection is released.

• Passes an OrdHttpUploadFile object to the PhotoAlbumBean class to store the photo into the database.

• Calls the setServletRequest( ) method of the OrdHttpUploadFormData class to specify the ServletRequest object for the request.

• Tests to see if the request is encoded using the multipart/form-data encoding by calling the isUploadRequest( ) method of the OrdHttpUploadFormData class.

• Forwards the request to the PhotoAlbumUploadForm.jsp file if the call to the isUploadRequest( ) method returns a Boolean expression of not false.

• Parses the form data by calling the parseFormData( ) method of the OrdHttpUploadFormData class.

• Gets the form field values for description and location by calling the getParameter( ) method of the OrdHttpUploadFormData class, and also gets the name of the file to be uploaded by calling the getFileParameter( ) method of the same class.

• Tests to make sure the file name is not null from the getFileParameter( ) method call of the OrdHttpUploadFormData class, then calls the getOriginalFileName( ) method of the OrdHttpUploadFile class to ensure that the original file name as provided by the browser is not null, or that the content length of the file is empty by calling the getContentLength( ) method of the OrdHttpUploadFile class.

• Forwards the request to the PhotoAlbumUploadForm.jsp file if there is a valid image file.

• If the description is null or empty, uses the file name as the description by calling the getSimpleFileName( ) method of the OrdHttpUploadFile class.

• Inserts the new entry into the photos table by calling the setDescription( ), setLocation( ), and insertNewPhoto( ) methods in the PhotoAlbumBean.java JavaBean.

• Ends the try/catch block with a finally clause and releases the JDBC connection by calling the release( ) method and releases all resources held by the OrdHttpUploadFormData object by calling its release( ) method.

• Displays the updated photo album by displaying the title of the page in the HTML header and in its common page header, directing the browser to the main page by calling the PhotoAlbum.jsp file, then displays the header "Photo successfully uploaded into photo album" and the instruction, "Please click on link below or wait for the browser to refresh the page".

• Displays a link at the bottom of the main page Return to photo album that calls the PhotoAlbum.jsp file.

PhotoAlbumBean.java

This is a JavaBean used by the JSP files to access the database.

The first call to the JavaBean for a request causes it to allocate a JDBC connection from a connection pool. Subsequent calls by the same request reuse the same connection. At the end of a request, each JSP file is responsible for calling the JavaBean to release the JDBC connection back to the pool. Each HTTP GET or POST request is assigned its own JDBC connection from the pool to ensure that multiple requests can be serviced concurrently.

The following methods are defined:

• The selectTable( ) method selects all the rows in the photos table, orders them by location, and returns the results in a result set.

• The selectRowById( ) method selects a specific row from the photos table where the value of id in the where clause is a parameter marker and returns the results in a result set.

• The fetch( ) method fetches the next row from the result set.

• The insertNewPhoto( ) method does the following:

  • Uses the OrdHttpUploadFile class to upload the new photo into the database.

  • Disables auto-commit by calling the setAutoCommit( ) method with an argument of false.

  • Executes a SQL SELECT photos_sequence.nextval statement to get the next value for the value of the id column for the new row to be inserted into the photos table.

  • Executes a SQL INSERT statement to insert a new row in the photos table.

  • Executes a SQL SELECT...FOR UPDATE statement to fetch the initialized full-sized and thumbnail image objects from the photos table.

  • Loads the image by calling the loadImage( ) method in the OrdHttpUploadFile class to populate an OrdImage object named image with the full-sized image, and sets the properties or attribute values of the image object based on the image contents.

  • Gets the image file format by calling the getContentFormat( ) method and if it is not null, and if the MIME type is BMP, then tries to process the image by calling the process( ) method and calling the getPreferredFormat( ) method to convert it to a MONOCHROME, GIF, or JPEG image format, based on the number of colors in the image.

  • Tries to copy the full-sized image and process it to create the thumbnail image by calling the processCopy( ) method in the OrdImage class and populate the OrdImage object named thumb.

  • Executes a SQL UPDATE statement to update the full-sized and thumbnail images in the database.

  • Commits the changes.

• A release( ) method to release the result set and statement objects, and places the JDBC connection back on the free list or stack.

• Get methods (getId( ), getDescription( ), getLocation( ), getImage( ), and getThumb( )) and the set methods (setId( ), setDescription( ), and setLocation( )) are used to get or set attributes for all attributes or columns.

• A getConnection( ) private function implements a simple JDBC connection pool.

• A freeConnection( ) private function releases the JDBC connection back to the pool at the end of the request.

• A getPreferredFormat( ) private function returns the preferred image file format based on the number of bits of color in the BMP image; returns a MONOCHROME image if there are no bits of color, returns JPEG if there are more than 8 bits of color, or returns GIF if there are between 1 and 8 bits of color.

3.1.4.1 Running the ASP/VBScript Photo Album Application

After you have installed and configured this photo album application in Microsoft IIS and configured the application connection parameters, you are ready to run the photo album application.

To use it, enter the photo album URL into the location bar of your Web browser, for example:

http://<hostname:port>/photoAlbum

When first invoked, the application displays any images that are currently stored in the album. By default, the photo album is empty when first installed. To upload a new photo, click Upload new photo. Enter a description of the photo, the location where the photo was taken, and the name of the image file or browse to its directory location, then click Upload new photo. The contents of the photo album are then displayed along with a picture of the new photo. Click the thumbnail image to view the full-sized version of the photo.

When the photo album application displays the text view image instead of its thumbnail image, the image format that was uploaded was not recognized by interMedia. Click view image to display the full-sized image.

You can now begin to load your photo album application with your favorite photos.

3.1.4.2 Description of the ASP/VBScript Photo Album Application

The top-level files that implement the photo album application are: viewAlbum.asp, viewEntry.asp, and uploadPhoto.asp. In addition, the getPhoto.asp file retrieves the images from the database and the insertPhoto.asp file inserts a new image into the database.

viewAlbum.asp

The viewAlbum page displays the contents of the photo album in a tabular format with columns labeled Description, Location, and Image.

Thumbnail images are ordered by description in the SQL SELECT statement and displayed with an anchor tag that is used to display the full-sized image, using the tag <img src="getPhoto.asp?entry_id=..."> as follows:

<A href="viewEntry.asp?entry_id=<%=strId%>">
       <% If objThumb.contentlength > 0 Then %> 
         <IMG border = 1
              height="<%=objThumb.height%>"
              width="<%=objThumb.width%>"
              alt="<%=strDescription%>"
              src="getPhoto.asp?media=thumb&entry_id=<%=strId%>" 
         >

If interMedia does not support the image format, then a thumbnail image would not have been created and stored in the database. In this case, the text view image is displayed instead of the thumbnail image in the Image column header of the table.

Text is displayed on the page stating Select the thumbnail to view the full-size image. A link appearing at the bottom of the page Upload new photo, calls the uploadPhoto.asp file to present an upload form (uploadForm.inc) to assist in uploading a new photo into the database.

viewEntry.asp

The viewEntry page, which displays the full-sized version of a photo, also uses the tag <img src="getPhoto.asp?entry_id..."> to display an image, as follows:

<TD vAlign=top scope="col"><B>Photo:</B></TD>
    <TD scope="col">
       <IMG border=1
            alt="<%=strDescription%>"
            src="getPhoto.asp?media=image&entry_id=<%=strId%>"
       <% If objImage.height > 0 And objImage.width > 0 Then %>
            height="<%=objImage.height%>"
            width="<%=objImage.width%>"
       <% End If %>
       >
    </TD>

Both URLs will retrieve an image from the database and deliver it to the browser using Oracle Objects for OLE to communicate with the database.

A link appears at the bottom of the page Return to photo album that calls the viewAlbum.asp file to present the photo album table and its set of thumbnail images to view.

uploadPhoto.asp

The uploadPhoto page displays an HTML form (uploadForm.inc) that allows a user to upload a new photo into the database by entering description and location information for the new photo, and its image file name. The form invokes the insertPhoto page as follows:

<FORM action="insertPhoto.asp" method="post" encType="multipart/form-data">

insertPhoto.asp

The insertPhoto page performs the work of loading the image into the photos table and automatically generating a thumbnail version of the image.

Clicking Upload photo near the bottom of the uploadPhoto page executes the submit input type form action, as follows:

<TD colSpan=2><INPUT type=submit value="Upload photo"></TD>

getPhoto.asp

The getPhoto page retrieves the image, either a thumbnail or full-sized image, based on its photo column indicator value (thumb or image), from the database and returns it to the browser. If the image requested is in the browser cache and the cache is valid, then it retrieves the image from cache; otherwise, it sets the MIME type of the image based on its attribute value in the database, then gets the image from the database and delivers it to the browser, as follows:

If CacheIsValid( setPhotos(1).value ) Then
  Response.Status = HTTP_STATUS_NOT_MODIFIED
Else
  ' Set the mime type header and deliver the image to the browser.
  SetLastModified( setPhotos(1).value )
  Response.ContentType = objMedia.mimetype
  ReadBlob objMedia.source.localData
End If

3.2.1.1 Creating a New DAD or Choosing an Existing DAD

To create media upload or retrieval procedures, you must select one or more DADs for use with the Code Wizard. To prevent the unauthorized browsing of schema tables and to prevent the unauthorized creation of media access procedures, you must authorize each DAD using the Code Wizard's administration function. Depending on your database and application security requirements, you may choose to create and authorize one or more new DADs specifically for use with the Code Wizard, or you may choose to authorize the use of one or more existing DADs.

Oracle recommends that any DAD authorized for use with the Code Wizard should use some form of user authentication mechanism. The simplest approach is to create or use a DAD that uses database authentication. To use this approach, select Basic Authentication Mode and omit the Password in the DAD specification. Alternatively, you may choose to use a DAD that specifies an existing application-specific authentication mechanism. For more information on configuring DADs, see Oracle HTTP Server mod_plsql User's Guide.The following example describes how to create a DAD to create and test media upload and retrieval procedures in the SCOTT schema.

1. Enter the URL of the Gateway Configuration Menu page into your browser's location bar, for example:

   http://<host-name>:<port-number>/pls/admin_/gateway.htm
   
   

2. Select Gateway Database Access Descriptor Settings.

3. Select Add Default (blank configuration).

4. Enter the following information into the specified sections of the Create DAD Entry form:

   Add Database Access Descriptor
     Database Access Descriptor Name:      SCOTTCW
     Schema name:                          [leave blank]
   Database Connectivity Information
     Oracle User Name:                     SCOTT
     Oracle Password:                      [leave blank]
     Oracle Connect String:                [leave blank or enter TNS name]
   Authentication Mode
     Authentication Mode:                  Basic
   Session Cookie
     Session Cookie Name:                  [leave blank]
   Package/Session Management State
     Package/Session Management Type       Stateless (Reset Package State)
   Connection Pool Parameters
     Enable Connection Pooling?            Yes
   Default (Home) Page
     Default (Home) Page:                  ORDCWPKG.MENU
   Document Access Information
     Document Table:                       MEDIA_UPLOAD_TABLE
     Document Access Path:                 [leave blank]
     Document Access Procedure:            [leave blank]
     Extensions uploaded as Long Raw:      [leave blank]
   Path Aliasing
     Path Alias:                           [leave blank]
     Path Alias Procedure:                 [leave blank]
   
   

3.2.1.2 Authorizing a DAD

To authorize a DAD for use with the Code Wizard, do the following steps:

1. Enter the Code Wizard's administration URL into your browser's location bar, for example:

   http://<host-name>:<port-number>/pls/ordcwadmin
   
   

2. Enter the ORDSYS user name and password when prompted by the browser.

3. Select DAD authorization from the Main menu as shown in Figure 3-3, then click Next.

   Figure 3-3 Main Menu for the interMedia Code Wizard for the PL/SQL Gateway

   
   Description of the illustration imwizpls.gif
   
   

4. Enter the name of the DAD you wish to authorize together with the user name, as shown in Figure 3-4, then click Apply.

   Figure 3-4 Authorizing the SCOTTCW DAD

   
   Description of the illustration authdad.gif
   
   
   
   

   Note: Duplicate DADs are not allowed, and each authorized DAD must indicate which database schema the user is authorized to access with the Code Wizard, using the DAD. Use this same page to delete the authorization for any existing DADs that no longer need to use the Code Wizard.

   
   

5. Review the updated list of DADs that are authorized to use the interMedia Code Wizard, as shown in Figure 3-5, then click Next.

   Figure 3-5 List of Authorized DADs

   
   Description of the illustration listdads.gif
   
   

6. To log out (clear HTTP authentication information), select Logout from the Main menu, then click Next. The log out operation redirects the request to the PL/SQL Gateway's built-in logmeoff function. For more information, see Oracle HTTP Server mod_plsql User's Guide.

3.2.1.3 Creating and Testing Media Upload and Retrieval Procedures

To start the Code Wizard, enter the appropriate URL into your browser's location bar, for example:

http://<hostname>:<port-number>/pls/scottcw

or

http://<hostname>:<port-number>/pls/mediadad/ordcwpkg.menu

Then, enter the user name and password when prompted by the browser. The Main menu page of the interMedia Code Wizard for the PL/SQL Gateway is displayed as shown in Figure 3-6.

If the DAD is configured specifically for use with the Code Wizard, simply enter the DAD name. Alternatively, to use another DAD, enter the DAD name together with the Code Wizard package name and Main menu procedure name, ORDCWPKG.MENU after the DAD name.

Once you have logged in, you can log out (clear HTTP authentication information) at any time by selecting Logout from the Main menu, then clicking Next. The logout operation redirects the request to the PL/SQL Gateway's built-in logmeoff function. For more information, see Oracle HTTP Server mod_plsql User's Guide.

To create a media upload procedure (see Section 3.2.1.4) or a media retrieval procedure (see Section 3.2.1.5), select the appropriate option from the Main menu, then click Next. The Code Wizard then guides you through a series of self-explanatory steps to create the procedure.

If you create a standalone media upload or retrieval procedure, you will have the opportunity to view the contents of the procedure as well as to test it.The image and multimedia sample sessions described in Section 3.2.2 and Section 3.2.3 respectively, illustrate how to create and test a media upload procedure and a media retrieval procedure.

3.2.1.4 Creating a Media Upload Procedure

To create a media upload procedure using the interMedia Code Wizard for the PL/SQL Gateway, do the following steps:

1. At the Main menu, select Create media upload procedure as shown in Figure 3-7. Click Next.

   Figure 3-7 Create a Media Upload Procedure

   
   Description of the illustration medupld.gif
   
   

2. At Step 1: Select database table and procedure type, select CW_IMAGES_TABLE and Standalone procedure as shown in Figure 3-8. Click Next.

   Figure 3-8 Step 1: Select Database Table and Procedure Type

   
   Description of the illustration imgupld1.gif
   
   

3. At Step 2: Select PL/SQL Gateway document upload table, select Use existing document table and select MEDIA_UPLOAD_TABLE because the SCOTTCW DAD is configured to use this document table as shown in Figure 3-9 and Figure 3-10. Click Next.

   Figure 3-9 Step 2: Select PL/SQL Gateway Document Upload Table (Part 1)

   
   Description of the illustration imgupl2a.gif
   
   

   Figure 3-10 Step 2: Select PL/SQL Gateway Document Upload Table (Part 2)

   
   Description of the illustration imgupl2b.gif
   
   

4. At Step 3: Select data access and media column(s), ensure IMAGE (ORDIMAGE) is checkmarked, that ID (Primary key) is selected, and select Conditional insert or update as shown in Figure 3-11. Click Next.

   Figure 3-11 Step 3: Select Data Access and Media Column(s)

   
   Description of the illustration imgupld3.gif
   
   

5. At Step 4: Select additional columns and procedure name, ensure DESCRIPTION is checkmarked, accept the default procedure name, UPLOAD_CW_IMAGES_TABLE_IMAGE, and select Create procedure in the database as shown in Figure 3-12. Click Next.

   Figure 3-12 Step 4: Select Additional Columns and Procedure Name

   
   Description of the illustration imgupld4.gif
   
   

6. At Step 5: Review selected options, review the options you have selected as shown in Figure 3-13. If the options selected are correct, click Finish.

   Figure 3-13 Step 5: Review Selected Options

   
   Description of the illustration imgupld5.gif
   
   

7. At the Compile procedure and review generated source window note the message, Procedure created successfully: UPLOAD_CW_IMAGES_TABLE_IMAGE as shown in Figure 3-14. To review the compiled PL/SQL source code in another window, click View (see Step 5, substep 6g in Section 3.2.2 for a copy of the generated upload procedure). Assuming you have configured the SCOTTCW DAD and specified MEDIA_UPLOAD_TABLE as the document table, in the DAD: field, the DAD name scottcw is displayed by default. To test the PL/SQL procedure created, click Test.

   Figure 3-14 Compile Procedure and Review Generated Source

   
   Description of the illustration imgupld6.gif
   
   

8. At the interMedia Code Wizard: Template Upload Form window, enter the value 1 in the ID field, browse for and select the image you want to upload in the IMAGE field, and enter a brief description of the image to be uploaded in the DESCRIPTION field as shown in Figure 3-15. Click Upload media.

   Figure 3-15 Template Upload Form

   
   Description of the illustration imgupld7.gif
   
   

9. The image is uploaded into the table row and a message is displayed, as shown in Figure 3-16.

   Figure 3-16 Template Upload Procedure -- Media Uploaded Successfully Message

   
   Description of the illustration imgupld8.gif
   
   

10. Return to the Compile procedure and review generated source window. If you are finished testing, click Done to return to the Main menu.

3.2.1.5 Creating a Media Retrieval Procedure

To create a media retrieval procedure using the interMedia Code Wizard for the PL/SQL Gateway, do the following steps:

1. At the Main menu, select Create media retrieval procedure as shown in Figure 3-17. Click Next.

   Figure 3-17 Create a Media Retrieval Procedure

   
   Description of the illustration medrt.gif
   
   

2. At Step 1: Select database table and procedure type, select CW_IMAGES_TABLE and select Standalone procedure as shown in Figure 3-18. Click Next.

   Figure 3-18 Step 1: Select Database Table and Procedure Type

   
   Description of the illustration imgrt1.gif
   
   

3. At Step 2: Select media column and key column, ensure IMAGE (ORDIMAGE) and ID (Primary key) are selected as shown in Figure 3-19. Click Next.

   Figure 3-19 Step 2: Select Media Column and Key Column

   
   Description of the illustration imgrt2.gif
   
   

4. At Step 3: Select procedure name and parameter name, accept the default procedure name, GET_CW_IMAGES_TABLE_IMAGES, accept the default parameter name, MEDIA_ID, and accept Create procedure in the database as shown in Figure 3-20. Click Next.

   Figure 3-20 Step 3: Select Procedure Name and Parameter Name

   
   Description of the illustration imgrt3.gif
   
   

5. At Step 4: Review Selected Options, review the options you have selected as shown in Figure 3-21. If the options selected are correct, click Finish.

   Figure 3-21 Step 4: Review Selected Options

   
   Description of the illustration imgrt4.gif
   
   

6. At the Compile procedure and review generated source window note the message, Procedure created successfully: GET_CW_IMAGES_TABLE_IMAGE as shown in Figure 3-22. To review the compiled PL/SQL source code in another window, click View (see Step 6, substep 5e in Section 3.2.2 for a copy of the generated retrieval procedure). To test the PL/SQL procedure created, assuming you have an image already loaded in the database with an ID value of 1, enter the value 1 for the Key parameter (MEDIA_ID), then click Test. The image is retrieved from the table row and is displayed as shown in Figure 3-23. Click Done to return to the Main menu.

   Figure 3-22 Compile Procedure and Review Generated Source

   
   Description of the illustration imgrt5.gif
   
   

   Figure 3-23 The Displayed Image 1981

   
   Description of the illustration imgupld9.gif
   
   

3.2.1.6 Using the PL/SQL Gateway Document Table

All files uploaded using the PL/SQL Gateway are stored in a document table. Media upload procedures created by the Code Wizard automatically move uploaded media from the specified document table to the application's table. To avoid transient files from appearing temporarily in a document table used by another application component, use a document table that is not being used to store documents permanently.

Be sure to specify the selected document table in the application's database access descriptor (DAD). If the DAD already specifies a different document table, create a new DAD for media upload procedures. If you choose to create a new document table, the Code Wizard will create a table with the following format:

CREATE TABLE document-table-name
  ( name           VARCHAR2(256) UNIQUE NOT NULL,
    mime_type      VARCHAR2(128),
    doc_size       NUMBER,
    dad_charset    VARCHAR2(128),
    last_updated   DATE,
    content_type   VARCHAR2(128),
    blob_content   BLOB );

For more information on file upload and document tables, see Oracle HTTP Server mod_plsql User's Guide.

3.2.1.7 How Time Zone Information Is Used to Support Browser Caching

User response times are improved and network traffic is reduced if a browser can cache resources received from a Web server and subsequently use those cached resources to satisfy future requests. This section describes at a very high level, how the browser caching mechanism works and how the Code Wizard utility package is used to support that mechanism. When reading this discussion, note that all HTTP date and time stamps are expressed in Coordinated Universal Time (UTC).

All HTTP responses include a Date header, which indicates the date and time when the response was generated. When a Web server sends a resource in response to a request from a browser, it can also include the Last-Modified HTTP response header, which indicates the date and time when the requested resource was last modified. It is important to note that the Last-Modified header must not be later than the Date header.

After receiving and caching a resource, if a browser needs to retrieve the same resource again, it sends a request to the Web server with the If-Modified-Since request header specified as the value of the Last-Modified date, which was returned by the application server when the resource was previously retrieved and cached. When the Web server receives the request, it compares the date in the If-Modified-Since request header with the last update time of the resource. Assuming the resource still exists, if the resource has not changed since it was cached by the browser, the Web server responds with an HTTP 304 Not Modified status with no response body, which indicates that the browser can use the resource currently stored in its cache. Assuming once again the resource still exists, if the request does not include an If-Modified-Since header or if the resource has been updated since it was cached by the browser, the Web server responds with an HTTP 200 OK status and sends the resource to the browser. See the HTTP specification (http://www.w3.org/Protocols/) for more information.

The ORDImage, ORDAudio, ORDVideo, and ORDDoc objects all possess an updateTime attribute stored as a DATE in the embedded ORDSource object. Although the DATE data type has no support for time zones or daylight savings time, the Oracle9i and later database versions do support time zones and also provide functions for converting a DATE value stored in a database to UTC. See Oracle Database Administrator's Guide for more information on how to set a time zone for a database. See Oracle Database SQL Reference for more information on date and time functions.

When a response is first returned to a browser, a media retrieval procedure sets the Last-Modified HTTP response header based on the updateTime attribute. If a request for media data includes an If-Modified-Since header, the media retrieval procedure compares the value with the updateTime attribute and returns an appropriate response. If the resource in the browser's cache is still valid, an HTTP 304 Not Modified status is returned with no response body. If the resource has been updated since it was cached by the browser, then an HTTP 200 OK status is returned with the media resource as the response body.Media retrieval procedures created by the Code Wizard call the utility package to convert a DATE value stored in the database to UTC. The utility package uses the time zone information stored with an Oracle9i or later database and the date and time functions to convert database date and time stamps to UTC. To ensure the resulting date conforms to the rule for the Last-Modified date described previously, the time zone information must be specified correctly. See Oracle Database Administrator's Guide for more information on how to set a time zone for a database.