Home » Articles » 8i » Here

InterMedia - Import-Export Of Images

Oracle interMedia allows Image, Audio and Video data to be stored in the database. Oracle seem to have focused most of their attention on getting the data in, assuming that all content will be served directly from the database. There is currently no direct support for data export from interMedia, all export routines being merely "stubs" for future development. Instead, the user is required to code custom routines for data export. The following code fragments show how to implement image Import and Export using "Oracle 8.1.5-7, interMedia, Java Stored Procedures and PL/SQL".

This process can be broken down into the following steps:

• Create a Table to store the images.
• Load interMedia Classes into your schema.
• Create a Java Stored Procedure to do the work.
• Create ProcessImages Specification to publish the Java call specification.
• Create ProcessImages Body to allow PL/SQL to access additional interMedia methods.
• Grant Privileges to give JServer access to the filesystem.
• Import an Image and query it's properties.
• Export an Image.
• Possible Modifications.

Assuming you have installed Oracle interMedia on your server, the first thing you will need to do is create a table to store the images.

Create Table

CREATE TABLE images (
  file_name VARCHAR2(100) NOT NULL,
  image     ORDSYS.OrdImage
);

The file_name column could be omitted, making this an object table, as the OrdImage type contains a file name property. The image column is defined using the OrdImage type found in the ORDSYS schema. ORDSYS is the owner of all the interMedia code and default data. The OrdImage type decends from the OrdSource type which contains a Blob that is used to store the image data.

Although imports can be done using the interMedia PL/SQL API, they require directory objects which makes them rather inflexible. A more flexible approach is to code both Import and Export routines using Java Stored Procedures. The Java can be writen and compiled, then loaded into the database, or compiled directly into the database. For simple routines it is easier to load them directly into the database using SQL*Plus, letting Oracle compile them for you. Oracle interMedia comes with a simple Java API, whose classes must be present in the schema where your Java code will reside. This can be done as follows.

Load interMedia Classes

ORACLE_HOME\bin\loadjava -user username/password@service ORACLE_HOME\ord\jlib\ordim.zip

Once these classes are loaded you are ready to code Stored Java Procedures using interMedia. The following Java Stored Procedure is called ImageHandler. It contains two public methods, ImportImage & ExportImage, which require Location and FileName parameters.

ImageHandler Java Stored Procedure

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "ImageHandler" AS
import java.lang.*;
import java.sql.*;
import oracle.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;
import oracle.ord.media.*;

public class ImageHandler
{

  // Import images into the database. The java version frees us from needing
  // to create a directory object every time we import.
  public static void ImportImage(String Location, String FileName) throws Exception
  {
    // Connect to the database
    Connection conn = null;
    OracleDriver ora = new OracleDriver();
    conn = ora.defaultConnection();

    // Check record exists, and create it if it doesn't
    Statement statement = conn.createStatement();
    ResultSet resultSet = statement.executeQuery("SELECT * FROM images WHERE file_name = '" + FileName + "'");
    if (!resultSet.next())
    {
      statement.executeUpdate("INSERT INTO images (file_name, image) VALUES ('" + FileName +
                              "',ORDSYS.ORDImage(ORDSYS.ORDSource(empty_blob(),NULL,NULL,NULL,SYSDATE,0),NULL,NULL,NULL,NULL,NULL,NULL,NULL))");
    }
    resultSet.close();
    statement.close();

    // Bind the image object to the database object
    OrdImage imgObj = new OrdImage(conn);
    imgObj.setBindParams("images", "image", " file_name = '" + FileName + "' ");
    imgObj.refresh(true);

    // Retrieve the data as a blob
    BLOB blobObj = imgObj.getContent();

    // Open streams for the output file and the blob
    File binaryFile = new File(Location + FileName);
    FileInputStream inStream = new FileInputStream(binaryFile);
    OutputStream outStream = blobObj.getBinaryOutputStream();

    // Get the optimum buffer size and use this to create the read/write buffer
    int size = blobObj.getBufferSize();
    byte[] buffer = new byte[size];
    int length = -1;

    // Transfer the data
    while ((length = inStream.read(buffer)) != -1)
    {
      outStream.write(buffer, 0, length);
      outStream.flush();
    }

    // Close everything down
    inStream.close();
    outStream.close();

    //Send alterations back to server
    if (imgObj.loadData(Location + FileName))
      imgObj.setProperties();
    imgObj.flush();

    conn.close();
  }

  // This is required because the is no native support for the export function
  // in interMedia.
  public static void ExportImage(String Location, String FileName) throws Exception
  {
    // Connect to the database
    Connection conn = null;
    OracleDriver ora = new OracleDriver();
    conn = ora.defaultConnection();

    // Bind the image object to the database object
    OrdImage imgObj = new OrdImage(conn);
    imgObj.setBindParams("images", "image", " file_name = '" + FileName + "' ");
    imgObj.refresh(true);

    // Retrieve the data as a blob
    BLOB blobObj = imgObj.getContent();

    // Open streams for the output file and the blob
    File binaryFile = new File(Location + FileName);
    FileOutputStream outStream = new FileOutputStream(binaryFile);
    InputStream inStream = blobObj.getBinaryStream();

    // Get the optimum buffer size and use this to create the read/write buffer
    int size = blobObj.getBufferSize();
    byte[] buffer = new byte[size];
    int length = -1;

    // Transfer the data
    while ((length = inStream.read(buffer)) != -1)
    {
      outStream.write(buffer, 0, length);
      outStream.flush();
    }

    // Close everything down
    inStream.close();
    outStream.close();
    conn.close();
  }

};
/

show errors java source "ImageHandler"

Once this Java class is compiled into the database you will need to publish call specifications for it's methods. This involves writing PL/SQL "Wrappers" to allow the methods to be called from PL/SQL. The following two code samples create a package specification and body called ProcessImages which publishes the ImageHandler methods and some of the ORDSYS.ORDIMAGE methods.

ProcessImages Specification

CREATE OR REPLACE PACKAGE ProcessImages AS

-- -----------------------------------------------------------------------------
-- The interMedia classes do not seem to be loaded into the database by default.
-- This means references to them cannot be resolved. Do the following from DOS:
--
-- loadjava -user username/password@service ORACLE_HOMEordjlibordim.zip
--
-- File system permission must be granted to allow the JVM to write files:
--
-- EXEC Dbms_Java.Grant_Permission('schema-name', 'java.io.FilePermission', '<>', 'read ,write, execute, delete');
-- -----------------------------------------------------------------------------
PROCEDURE ImportImage (p_location   IN  VARCHAR2,
                       p_file_name  IN  VARCHAR2)
AS LANGUAGE JAVA
NAME 'ImageHandler.ImportImage(java.lang.String, java.lang.String)';

PROCEDURE ExportImage (p_location   IN  VARCHAR2,
                       p_file_name  IN  VARCHAR2)
AS LANGUAGE JAVA
NAME 'ImageHandler.ExportImage(java.lang.String, java.lang.String)';

FUNCTION GetHeight(p_image  IN  ORDSYS.ORDImage) RETURN NUMBER;

FUNCTION GetWidth(p_image  IN  ORDSYS.ORDImage) RETURN NUMBER;

FUNCTION GetFileFormat(p_image  IN  ORDSYS.ORDImage) RETURN VARCHAR2;

FUNCTION GetContentFormat(p_image  IN  ORDSYS.ORDImage) RETURN VARCHAR2;

FUNCTION GetCompressionFormat(p_image  IN  ORDSYS.ORDImage) RETURN VARCHAR2;

FUNCTION GetUpdateTime(p_image  IN  ORDSYS.ORDImage) RETURN DATE;

FUNCTION GetMimeType(p_image  IN  ORDSYS.ORDImage) RETURN VARCHAR2;

FUNCTION GetContentLength(p_image  IN  ORDSYS.ORDImage) RETURN NUMBER;

FUNCTION GetSource(p_image  IN  ORDSYS.ORDImage) RETURN VARCHAR2;

FUNCTION GetSourceType(p_image  IN  ORDSYS.ORDImage) RETURN VARCHAR2;

FUNCTION GetSourceLocation(p_image  IN  ORDSYS.ORDImage) RETURN VARCHAR2;

FUNCTION GetSourceName(p_image  IN  ORDSYS.ORDImage) RETURN VARCHAR2;

FUNCTION GetContent(p_image  IN  ORDSYS.ORDImage) RETURN BLOB;

END ProcessImages;
/

With the advent of Java in the database Oracle have relaxed the restrictions on using Packaged Functions within SQL, hence the lack of PRAGMA RESTRICT_REFERENCES in the ProcessImages package specification.

ProcessImages Body

CREATE OR REPLACE PACKAGE BODY ProcessImages AS

FUNCTION GetHeight(p_image  IN  ORDSYS.ORDImage) RETURN NUMBER IS
BEGIN
  RETURN p_image.GetHeight;
END;

FUNCTION GetWidth(p_image  IN  ORDSYS.ORDImage) RETURN NUMBER IS
BEGIN
  RETURN p_image.GetWidth;
END;

FUNCTION GetFileFormat(p_image  IN  ORDSYS.ORDImage) RETURN VARCHAR2 IS
BEGIN
  RETURN p_image.GetFileFormat;
END;

FUNCTION GetContentFormat(p_image  IN  ORDSYS.ORDImage) RETURN VARCHAR2 IS
BEGIN
  RETURN p_image.GetContentFormat;
END;

FUNCTION GetCompressionFormat(p_image  IN  ORDSYS.ORDImage) RETURN VARCHAR2 IS
BEGIN
  RETURN p_image.GetCompressionFormat;
END;

FUNCTION GetUpdateTime(p_image  IN  ORDSYS.ORDImage) RETURN DATE IS
BEGIN
  RETURN p_image.GetUpdateTime;
END;

FUNCTION GetMimeType(p_image  IN  ORDSYS.ORDImage) RETURN VARCHAR2 IS
BEGIN
  RETURN p_image.GetMimeType;
END;

FUNCTION GetContentLength(p_image  IN  ORDSYS.ORDImage) RETURN NUMBER IS
BEGIN
  RETURN p_image.GetContentLength;
END;

FUNCTION GetSource(p_image  IN  ORDSYS.ORDImage) RETURN VARCHAR2 IS
BEGIN
  RETURN p_image.GetSource;
END;

FUNCTION GetSourceType(p_image  IN  ORDSYS.ORDImage) RETURN VARCHAR2 IS
BEGIN
  RETURN p_image.GetSourceType;
END;

FUNCTION GetSourceLocation(p_image  IN  ORDSYS.ORDImage) RETURN VARCHAR2 IS
BEGIN
  RETURN p_image.GetSourceLocation;
END;

FUNCTION GetSourceName(p_image  IN  ORDSYS.ORDImage) RETURN VARCHAR2 IS
BEGIN
  RETURN p_image.GetSourceName;
END;

FUNCTION GetContent(p_image  IN  ORDSYS.ORDImage) RETURN BLOB IS
BEGIN
  RETURN p_image.GetContent;
END;

END ProcessImages;
/

By default, JServer (Oracle's Java Virtual Machine) does not allow access to the file system. The DBMS_JAVA package can be used to grant assorted privileges to JServer.

Grant Privileges

EXEC Dbms_Java.Grant_Permission('SCHEMA-NAME', 'java.io.FilePermission', '<<ALL FILES>>', 'read ,write, execute, delete');

It is up to the individual to decide on the level of access that is required. Depending on the documentation used, you may be told to add the directories you intend to access to your UTL_FILE_DIR parameter in the Init.ora file. I have not found this to be necessary when using the Java approach.

Assuming you have made it this far without errors, you should now be ready to load images into the database. Calling the ProcessImages.ImportImage procedure with a valid Location and FileName will import the specified image into the Images table. Once there, you can use the ProcessImages functions to retrieve information about the image.

Import Image

EXEC ProcessImages.ImportImage('C:\Images\', 'MyImage.gif');

SELECT ProcessImages.GetHeight(image) height,
       ProcessImages.GetWidth(image) width,
       ProcessImages.GetContentLength(image) length
FROM   images
WHERE  file_name = 'MyImage.gif';

If you subsequently need to export the images, you simply call ProcessImages.ExportImage with a valid Location and FileName.

Export Image

EXEC ProcessImages.ExportImage('C:\Images\Export\', 'MyImage.gif');

PossibleModifications

With minor modifications the same code will run outside the database allowing import of images from filesystems not present on the Oracle Server.

• Add ordim.zip to you classpath.
• Use Oracle's Thin JDBC Driver instead of the default driver used in Java Stored Procedures to connect to the database.

Import of Audio and Video data follows the same format, being stored in Blobs within the OrdAudio and OrdVideo types which are both decended from the OrdSource type. Early documentation implies that Oracle 8.1.7 may fully support interMedia export, but this may still require the presence of directory objects.

Hope this helps. Regards Tim...

Back to the Top.

Created: 2005-05-14  Updated: 2015-06-27

Contact Us

Home | Articles | Scripts | Blog | Certification | Videos | Misc | About

About Tim Hall
Copyright & Disclaimer