In this chapter we will introduce interMedia APIs with simple examples. We will use the various APIs to load an image, convert that image to a smaller size image and store the thumbnail in the database, and retrieve the image and store it in a local file.
To prepare the database for interMedia file operations, the user schema must be assigned the privileges to access files and create files. Before a file can be accessed, a database directory object must be created, and access to that directory object must be granted to the database user. From a system administrator account, the following PL/SQL steps are used to prepare the user Scott for file input and output operations:
Create or replace directory IMAGEDIR as ‘c:Images’
Allow Scott to import images from IMAGEDIR
Grant READ on directory IMAGEDIR to Scott
Allow Scott to export images to IMAGEDIR
Grant WRITE on directory IMAGEDIR to Scott
Grant Java file permission to ORDSYS (only needed for Oracle9); call dbms_java.grant_permission (’ORDSYS’,’java.io.FilePermission’, ‘c:plsql*’,’write’)
Grant to user Java file permission to ORDSYS; call dbms_java.grant_permission(’SCOTT’,’java.io.FilePermission’, ‘c: plsql*’,’write’);
Please note that directory object names are case sensitive. The database, by default, uppercases a directory name unless the name is encased in double quotes. If you tried to specify the directory object as ‘imagedir,’, rather than ‘IMAGEDIR,’ the directory would not be found.
After we have prepared the database security environment, we can log into the database and create a PL/SQL procedure to upload images into the database, as follows.
create or replace procedure upload_image(file_name varchar2,
prikey_id number,
descr varchar2,
loc varchar2)
as
imgobj ORDSYS.ORDImage;
ctx raw(64):=null;
thisrowid urowid;
BEGIN
insert into photos (id, description, location, image, thumb)
values (prikey_id, descr, loc,
ORDSYS.ORDImage.init(), ORDSYS.ORDImage.init())
returning rowid, t.image into thisrowid, imgobj;
imgobj.importFrom(ctx, 'FILE', 'IMAGEDIR', file_name);
update photos t set t.image=imgobj where rowid = thisrowid;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('EXCEPTION caught '||sqlcode||
' '||sqlerrm);
END;
/There are three important things to note:
An empty media object must be inserted into the database before the media can be imported into it. This is necessary to create an empty BLOB to receive the raw binary data.
An object-relational method called Import or ImportFrom is used to load the image into the database. By default, this method will also populate the image metadata in the image object.
After importation, the uploaded image object column must be updated.
This procedure does not fully commit the image into the database; we are saving that operation for after we create the thumbnail for efficiency.
Now that the original image is in the database, we can create a thumbnail. Typically, most applications that use thumbnails avoid the costly nature of repeatedly creating the thumbnail by creating a thumbnail once and storing it. The size of the thumbnail is typically much smaller than the original, much more than the reduction of the width and height. The following procedure creates the thumbnail.
create or replace procedure make_thumbnail(prikey_id number)
as
imgobj ORDSYS.ORDImage;
th Ordsys.OrdImage;
thisrowid urowid;
BEGIN
select t.image, t.thumb, rowid into imgobj, th, thisrowid from
photos t
where id = prikey_id for update;
imgobj.processCopy('fileFormat=JFIF maxScale=128 128', th);
update photos t set t.thumb=th where rowid = thisrowid;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('EXCEPTION caught '||sqlcode||
' '||sqlerrm);
END;
/The important things to note are:
Both the original and thumbnail objects to be populated must be selected from the database.
Image processing is done on the original image with the thumbnail image object specified as a target.
The thumbnail must be updated into the database.
Note that again we are incomplete without a commit. We will do this outside the procedures.
After the thumbnail is created, we may want to see the results. Or, we may be using the database as an image-processing engine. The following procedure exports the thumbnail into a file on the file system.
create or replace procedure download_thumbnail(prikey_id
number)
as
th Ordsys.OrdImage;
thisrowid urowid;
ctx raw(64):=null;
BEGIN
select t.thumb, rowid into th, thisrowid from photos t
where id = prikey_id;
th.export(ctx, 'file', 'IMAGEDIR', prikey_id ||
'_thumb.jpg'),
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('EXCEPTION caught '||sqlcode||
' '||sqlerrm);
END;
/The important things to note are:
The image to be exported must be selected.
The image is exported using an object method. The database directory object is specified as well as the file name, which in this procedure would be of the form <PRIMARY_KEY>_thumb.jpg.
The following PL/SQL code uses the above procedures to import an image, create a thumbnail of the image using database image processing, and export the thumbnail into the file system. Note that we commit the changes after the processing step to make the new row permanent.
BEGIN
upload_image('t.img', 1, 'A sample Picture',
'Could be anywhere'),
make_thumbnail(1);
commit;
download_thumbnail(1);
END;
/This section shows how to develop a JDBC Java application that stores and uses images in an Oracle database table.
Java programmers are intimately familiar with Java objects but are often unaware that Oracle Database is an object-relational database, and as such, supports storage and retrieval of objects. Oracle interMedia provides the database type ORDImage, which is used to store images in a database table just like any other relational data. Some interMedia functionality (such as thumbnail generation) may also be used if images are stored in BLOB (binary large object) columns, but Oracle Corporation recommends storing images in ORDImage columns. An example of an ORDImage object in a database table is illustrated in Figure 5.1.
Even though the JDBC specification does not support object-relational databases directly, Oracle interMedia database objects can be used in JDBC programs by means of the interMedia Java Client.
The interMedia Java Client contains high-performance proxy Java objects that allow for quick object property retrieval and convenient upload/download. The proxies forward any requests for computations back to the database server for the ORDImage object to execute. These client objects are in the oracle.ord.media.im package (found in the $ORACLE_HOME/ord/jlib/ordim.jar file).
A schematic diagram of how a database ORDSYS.ORDImage object is related to the Java ORDImage object is shown in Figure 5.2. It can’t be stressed enough that ORDImage Java objects are merely proxies for database objects—they must be created from a database ORDImage object. These proxies are the same for the other media types such as OrdVideo, OrdAudio, and OrdDoc.
To use the OrdImage, OrdAudio, OrdVideo, and OrdDoc classes in your Java programs, the following import statement must be present.
import oracle.ord.im.OrdImage; import oracle.ord.im.OrdAudio; import oracle.ord.im.OrdVideo; import oracle.ord.im.OrdDoc;
Note that the examples in this article also make use of several standard JDBC classes in the java.sql package and the Oracle JDBC extension classes OracleResultSet and OraclePreparedStatement that are included using the following import statements.
import java.sql.Connection; import java.sql.SQLException; import java.sql.DriverManager; import java.sql.Statement; import java.sql.PreparedStatement; import java.sql.ResultSet; import oracle.jdbc.OracleResultSet; import oracle.jdbc.OraclePreparedStatement;
To connect to the database and use OrdImage objects, the following jar files must be in your CLASSPATH:
Setting the CLASSPATH on the command line
Windows (assumes %ORACLE_HOME% has been set):
setCLASSPATH=%ORACLE_HOME%jdbclib ojdbc14.jar;%ORACLE_HOME%ordjlib ordim.jar;%ORACLE_HOME%sqljlib untime12.jar;
UNIX (assumes that $ORACLE_HOME has been set):
setenv CLASSPATH ${ORACLE_HOME}/jdbc/lib/ ojdbc14.jar:$ORACLE_HOME/ord/jlib/ ordim.jar:$ORACLE_HOME/sqlj/lib/runtime12.jar:
Setting the CLASSPATH using JDeveloper 10g:
Go to the project properties (right click on your project and choose properties).
Navigate to the Profiles/Development/Libraries option and choose the three libraries shown below, and in Figure 5.3.
Oracle JDBC
Oracle interMedia
SQLJ Runtime
The documentation (in Javadoc format) for the interMedia Java classes can be found from the main list of Oracle Documentation Books (under the heading of “interMedia Java Classes Reference”). The Oracle documentation is included with the Database install, and it can be found on OTN at http://www.oracle.com/pls/db10g/db10g.homepage (click on the Books tab).
Oracle interMedia can be used with both Oracle JDBC drivers (thin or oci) and the Connection object is created in the normal way, except that the AutoCommit flag must be set to false. For example:
// register the oracle jdbc driver with the JDBC // driver manager DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); Connection conn = DriverManager.getConnection(connectString, username, password); // Note: it is CRITICAL to set the autocommit to false so that // two-phase select-commit of BLOBS can occur. conn.setAutoCommit(false); // create a JDBC Statement object to execute SQL // in the database Statement stmt = conn.createStatement();
As shown above, the Connection that is established must have the Auto-Commit flag set to false because interMedia uses BLOB columns internally to store data. Since BLOB updates in Oracle Database require a two-stage select-commit process, if the AutoCommit flag is set to true (the default) then BLOB operations will fail with the exception: java.sql.SQLException: ORA-01002: fetch out of sequence.
This section shows how to upload images that are stored in disk files into the photos table we used in previous chapters.
Insert a new row into the table with ID set to 1 and image initialized to a new ORDImage object.
// insert a row into photos String rowInsertSQL = ("insert into photos (id, description, location, image, thumb) values (1,'Dogs', 'At Home',ordsys.ordimage.init(),ordsys.ordimage.init())"); stmt.execute(rowInsertSQL);Get a proxy for the ORDImage database object in row 1 in the OrdImage Java proxy object imageProxy (note that since we will be uploading data into the ORDImage’s underlying BLOB column, the row must be selected with the FOR UPDATE clause).
// select the new ORDImage into a java proxy OrdImage object (imageProxy) String rowSelectSQL = "select image from photos where id = 1 for update"; OracleResultSet rset = (OracleResultSet)stmt.executeQuery(rowSelectSQL); rset.next(); OrdImage imageProxy = (OrdImage)rset.getORAData("image", OrdImage.getORADataFactory()); rset.close();Load the image data from the dogs.gif file into the ORDImage object (and by extension into the database) by calling the loadDataFromFile method on the Java proxy object.
imageProxy.loadDataFromFile("dogs.gif");Automatically detect the image’s height, width, file format, and so on by calling setProperties() on the proxy object. Calling setProperties() on the proxy object forwards the request to the database to execute ORDImage.setProperties() on the server.
imageProxy.setProperties();
Update photos to reflect the changes we have made to the ORDImage object (uploaded data and filled in properties).
String updateSQL = "update photos set image=? where id=1"; OraclePreparedStatement opstmt = (OraclePreparedStatement)conn.prepareStatement(updateSQL); opstmt.setORAData(1, imageProxy); opstmt.execute(); opstmt.close();
Note
Note: If you call ORDimage.setProperties() on an image that is not one of interMedia’s supported formats, a java.sql.SQLException that encapsulates an IMG-00705 error such as the following is thrown.
java.sql.SQLException: ORA-29400: data cartridge error IMG-00705: unsupported or corrupted input format
Once images are in Oracle Database, you can access image metadata using either standard SQL queries or the Java proxy accessor methods. In the following examples, we demonstrate how to use the Java proxy accessor methods to access the properties of the goats.gif file that we uploaded in the previous section. Note that the properties that may be selected are height; width; fileFormat (JPEG, GIFF, and so on); contentFormat (monochrome and so on); contentLength (number of bytes of image data); and mimeType.
One may access an image’s height and width by calling the accessors getHeight() and getWidth() on the interMedia Java proxy objects. To do this, first the image is selected into a proxy object (imageProxy), and then the getHeight() and getWidth() methods are called.
String rowSelectSQL = "select image from photos where id = 1";
OracleResultSet rset =
(OracleResultSet)stmt.executeQuery(rowSelectSQL);
rset.next();
OrdImage imageProxy = (OrdImage)rset.getORAData("image",
OrdImage.getORADataFactory());
rset.close();
int height = imageProxy.getHeight();
int width = imageProxy.getWidth();The above code results in height = 375 and width = 500 when using the example dogs.gif file.
We now illustrate how to create an ORDImage object that contains a thumbnail of an existing ORDImage object using the processCopy() method. To use the processCopy() method, the programmer describes the desired properties of the output image and provides the input image. For example, the following description generates a JPEG thumbnail image of size 75 × 100 pixels: 'fileformat=jfif fixedscale=75 100'.
The following example shows how to insert a new ORDImage object into a second row of photos, and then shows how to generate a JPEG thumbnail of the goats.gif image in the new row with the 'maxscale=100 100 fileformat=jfif' processCopy command.
// get the source and Destination ORDImage objects
String srcSelectSQL = "select image, thumb from photos where
id=1";
OracleResultSet rset =
(OracleResultSet)stmt.executeQuery(srcSelectSQL);
rset.next();
OrdImage srcImageProxy = (OrdImage)rset.getORAData("image",
OrdImage.getORADataFactory());
OrdImage dstImageProxy = (OrdImage)rset.getORAData("thumb",
OrdImage.getORADataFactory());
rset.close();
// call the processCopy method (processing occurs on the
SERVER)
srcImageProxy.processCopy("maxscale=100 100 fileformat=jfif",
dstImageProxy);
// update the destination image in the second row
String dstUpdateSQL = "update photos set thumb=? where id=1";
OraclePreparedStatement opstmt =
(OraclePreparedStatement)conn.prepareStatement(dstUpdateSQL);
opstmt.setORAData(1, dstImageProxy);
opstmt.execute();
opstmt.close();An ORDImage database object can be downloaded into a local disk file with the following steps.
Select the ORDImage object from the database into an OrdImage Java proxy.
Call the getDataInFile() method on the OrdImage Java proxy to download the image data into a file.
An example of these two steps to download the image in row 2 to “row2.jpg” is shown below.
// export the data in row 1
String exportSelectSQL = "select thumb from photos where id =
1";
OracleResultSet rset =
(OracleResultSet)stmt.executeQuery(exportSelectSQL);
// get the proxy for the image in row 1
rset.next();
OrdImage imageProxy = (OrdImage)rset.getORAData("thumb",
OrdImage.getORADataFactory());
rset.close();
// call the getDataInFile method to write the ORDImage in row
// 1 to disk
imageProxy.getDataInFile("thumb_1.jpg");Media data in Oracle can be inserted, accessed, and manipulated in any language that can perform PL/SQL operations. Oracle interMedia does supply convenience proxy classes for Java, but these are not necessary to access the full functionality of interMedia. In this section, the Oracle C++ Call Interface (OCCI) is used to perform the same processing as is performed by Java. The same interMedia processing can also be performed using Oracle Callable Interface (OCI).
The following includes and namespaces are used with this example. The file occi.h is found in <ORACLE_HOME>/rdbms/public/occi.h.
#include <iostream> #include <fstream> #include <occi.h> #include <unistd.h> #include <string> using namespace oracle::occi; using namespace std; #define BUFSIZE 16384
The connection is created, as you would in any OCCI program, with the following code.
Environment *env = Environment::createEnvironment
(Environment::DEFAULT);
Connection *conn = env->createConnection
(username, password, url);For the population of the image row, OCCI must have AutoCommit set to false. Unlike JDBC, by default, OCCI sets AutoCommit off by default. Also, the AutoCommit settings are a member of the Statement class, not the Connection class.
The following OCCI method inserts an image into the database, populates the image raw data using the OCCI Blob and Stream classes, and then uses an anonymous PL/SQL block to set the properties of the image.
void insertImageRow (Connection *conn, unsigned int id,
string descrip, string location, string fName)
throw (SQLException)
{
//
// Open input file to populate blob
//
ifstream inFile;
inFile.open(fName.data(), ios::in | ios::binary );
if (!inFile)
{
cout << fName; cout << " file not found
";
return;
}
//
// Insert row returning ID and blob to be populated
//Insert a new row into the table with ID set to 1 and image and thumb initialized to a new ORDImage object.
Statement *stmt = conn->createStatement ( "Insert into Photos t values (:v1, :v2, :v3, Ordimage.Init(), OrdImage.Init()) returning ROWID, t.image.getContent() into :v4, :v5"); stmt->setInt(1, id); stmt->setString(2, descrip); stmt->setString(3, location); stmt->registerOutParam(4, OCCISTRING, 50); stmt->registerOutParam(5, OCCIBLOB);Execute the statement and retrieve the unique row ID and BLOB from ORDImage object.
stmt->executeUpdate(); string rid = stmt->getString(4); Blob blob = stmt->getBlob(5); // // Populate interMedia blob from file using Stream // interface // unsigned int bufsize=BUFSIZE; char *buffer = new char[bufsize];Copy data from file into the BLOB stream. The method writeLastBuffer is required to end the BLOB stream writes.
Stream *strm = blob.getStream(); while(inFile) { inFile.read((char *)buffer, bufsize); strm->writeBuffer(buffer,inFile.gcount()); } strm->writeLastBuffer(buffer, 0); blob.closeStream(strm); inFile.close(); delete[] buffer; // // In the same transaction, Set the image properties // and create a thumbnail //
Create a PL/SQL block of code to retrieve interMedia objects from the row that was just inserted, set a default MIME type, and attempt to populate metadata in the object from the image binary data using setProperties(). If we are successful in getting image metadata into the image object, a thumbnail of the image is created and copied into the thumb object.
stmt->setSQL ("DECLARE img ORDIMAGE; thumbnail ORDIMAGE; BEGIN select image, thumb into img, thumbnail from Photos where rowid = :v1 for update; img.setMimetype(:v2); 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 = :v1; END;"); stmt->setString(1, rid); // set default mime type based upon file Extension in // case of processing failure (image/<FILE_EXTENSION>) stmt->setString(2, "image/" + fName.substr(fName.find_last_of(".") + 1)); stmt->executeUpdate(); conn->commit(); conn->terminateStatement (stmt); cout << "Populating and initializing the Image - Success" << endl; return; }
The extraction of an image from the database into the file is simply done with a select statement, obtaining the BLOB stream, and copying the stream data from the database into the file.
void writeThumbnailFile (Connection *conn, unsigned int id,
string fprefix)
throw (SQLException)
{
cout << "createStatement
";Select the binary data into and Mimetype of the image from the row with the passed ID.
Statement *stmt = conn->createStatement ("Select t.thumb.getContent(), t.thumb.getMimetype() from Photos t where t.id= :v1"); stmt->setInt(1, id); ResultSet *rset = stmt->executeQuery (); if (rset->next ()) {Get the binary data into an OCCI BLOB object and the MIME type into a string.
Blob blob = rset->getBlob (1); string mimeType = rset->getString(2); string fName = fprefix + "." + mimeType.substr(mimeType.find_last_of("/") + 1); cout << "Output file name is " << fName << endl; ofstream outFile; outFile.open(fName.data() , ios::out | ios::binary); if (!outFile) { cout << fName; cout << " file could not be created "; conn->terminateStatement (stmt); return; } unsigned int bufsize=BUFSIZE; char *buffer = new char[bufsize];
After creating the output file, using part of the mimetype as the file extension (for example, image/jpeg would create a .jpeg file), copy the contents of the Blob into the
Stream *inStream = blob.getStream (1,0); while (1) { int bytesRead = inStream->readBuffer(buffer, bufsize); if (bytesRead < 0) break; outFile.write(buffer, bytesRead); } blob.closeStream(inStream); outFile.close(); delete[] buffer; } conn->commit(); conn->terminateStatement (stmt); return; }
Media data stored with interMedia data can easily be accessed and handled in many Microsoft environments. These environments include C# and Visual Basic by using the Oracle Data Provider for .NET (ODP.NET). The example given in the previous sections of this chapter will be repeated in this section to illustrate the steps taken to use interMedia with .NET.
ODP.NET allows for access to the Oracle database. This runtime component provides the programming interface to execute database functionality.
A programmer using .NET will typically be using Microsoft’s Visual Studio .Net. In this case, they would probably want to also install Oracle Developer Tools for Visual Studio .NET.
To access the Oracle database, it is necessary that ODP.NET is installed. To effectively use Visual Studio with Oracle databases, it is best to install Oracle Developer Tools for Visual Studio .NET. Both of these products are installed by default when the Oracle client is installed on the Windows system.
To set up your Visual Studio C# project to make use of ODP.NET, the project should make a reference to Oracle.DataAccess. This reference will then be used by Visual Studio for object reference information.
The following namespaces are defined to be used in this program. The “using” directive allows us to reference types without having to fully qualify the namespace they are part of.
using System; using System.Data; using System.IO; using Oracle.DataAccess.Types; Using Oracle.DataAccess.Client;
The database connection is created with the following code.
//
// get connection to database
//
OracleConnection dbConn =
new OracleConnection("User ID=scott;" +
"Password=tiger;" +
"Data Source=ORCL10g");
dbConn.Open();In this program, to increase the efficiency somewhat in the case of multiple inserts, the database commands are created once and then reused to create new rows in the database. The following code is used to create these commands.
private static OracleCommand insertCommand;
private static OracleParameter paramDesc;
private static OracleParameter paramID;
private static OracleParameter paramLoc;
private static OracleParameter paramBlob;
private static OracleParameter paramRowid;
private static OracleCommand processCommand;
private static OracleParameter paramRowidIn;
private static OracleParameter paramMime;
private static void setupCommands(OracleConnection conn)
{
//
// Initilze insert command for reuse as well as params
//Create the command that will insert a new row into the database returning the row ID and BLOB to be populated with the contents of the file.
insertCommand = new OracleCommand ("Insert into Photos t values" + "(:1, :2, :3, Ordimage.Init(), OrdImage.Init())" + " returning " + "t.image.getContent(), ROWID into:4, :5", conn); paramID = new OracleParameter("id", OracleDbType.Int32, ParameterDirection.Input); insertCommand.Parameters.Add(paramID); paramDesc = new OracleParameter("desc", OracleDbType.Varchar2, ParameterDirection.Input); insertCommand.Parameters.Add(paramDesc); paramLoc = new OracleParameter("loc", OracleDbType.Varchar2, ParameterDirection.Input); insertCommand.Parameters.Add(paramLoc); paramBlob = new OracleParameter("imgblob", OracleDbType.Blob, ParameterDirection.Output); insertCommand.Parameters.Add(paramBlob); paramRowid = new OracleParameter("rowid", OracleDbType.Varchar2, ParameterDirection.Output); paramRowid.Size = 50; insertCommand.Parameters.Add(paramRowid); // // Initialize setproperties and thumbnail creation // command and parameters //
Create the command that will set a default MIME type and attempt to set the properties of the image. If successful, a thumbnail is created.
processCommand = new OracleCommand ("DECLARE " + " img ORDIMAGE; "+ " thumbnailORDIMAGE; " + "BEGIN " + " select image, thumb into" + " img, thumbnail fromPhotos " + " where rowid = :1 for update; " + " img.setMimetype(:2); "+ " 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= :1; " + "END;", conn); paramRowidIn = new OracleParameter("id", OracleDbType.Varchar2, ParameterDirection.Input); processCommand.Parameters.Add(paramRowidIn); // set default mime type based upon file Extension in // case of setProperties failure(image/<FILE_EXTENSION>) paramMime = new OracleParameter("mime", OracleDbType.Varchar2, ParameterDirection.Input); processCommand.Parameters.Add(paramMime); }
For the population of the image row, ODP.NET must have AutoCommit set to false, which it is by default.
The following C# method inserts an image into the database, populates the image raw data using the BLOB class, and then sets the properties of the image. This is accomplished with the commands created in the previous section, only the parameter values for these commands need to be set.
//
//Insert image in row and create thumbnail
//
private static void insertImageRow (OracleConnection conn,
uint id,
string descrip,
string location,
string fName)
{
//
// Open input file to populate blob
//
BinaryReader inStream =
new BinaryReader(
File.Open(fName,System.IO.FileMode.Open));
//
// Start transaction
//Start a transaction for the two commands.
OracleTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted); // // Insert row into DB returning ID and blob to be // populated //
Insert the row into the database, setting the necessary input parameters.
paramID.Value = System.Convert.ToUInt32(id); paramDesc.Value = descrip; paramLoc.Value = location; paramRowid.Size = 50; insertCommand.ExecuteNonQuery(); // // Obtain the rowid and media blob for transaction use //
Get the returned parameters, the row ID, and the BLOB to populate.
OracleBlob blob = (OracleBlob)insertCommand.Parameters["imgblob"].Value; OracleString rid = (OracleString)insertCommand.Parameters["rowid"].Value; // // Populate interMedia blob from file using stream // interface //
long written = 0; int bufsiz = blob.OptimumChunkSize * 8; while(written < inStream.BaseStream.Length) { byte [] buf = inStream.ReadBytes(bufsiz); blob.Write(buf, 0, buf.Length); written += buf.Length; } blob.Close(); // // In the same transaction, Set the image properties // and create a thumbnail //
Set the input parameters and process the BLOB in the database.
paramRowidIn.Value = paramRowid.Value; // set default mime type based upon file Extension in // case of setProperties failure(image/<FILE_EXTENSION>) paramMime.Value = "image/" + fName.Substring(fName.LastIndexOf(".") + 1); processCommand.ExecuteNonQuery();
Commit the transaction; after this command, the row is available in the database.
trans.Commit(); return; }
The extraction of an image from the database into the file is simply done with a select command, obtaining the BLOB stream, and copying the stream data from the database into the file. The MIME type is also obtained and used to provide a file extension.
//
// Get thumbnail from the Photos table and put it in a file
//
private static void writeThumbnailFile
(OracleConnection conn, uint id, string fprefix)
{
//
// Create select command to get thumnail image using ID
//Prepare and execute the SQL command to obtain the MIME type and binary thumbnail image.
OracleCommand cmd = new OracleCommand ("Select t.thumb.getContent(), " + " t.thumb.getMimetype() from Photos t " + "where t.id= :v1", conn); OracleParameter paramID = new OracleParameter("id", OracleDbType.Int32, System.Convert.ToUInt32(id), ParameterDirection.Input); cmd.Parameters.Add(paramID); // // Execute the command as a reader commmand // OracleDataReader reader = cmd.ExecuteReader(); if (!reader.Read()) { Console.WriteLine("No data found for {0}", cmd.CommandText); return; } // // Obtain the binary data and mimetype from the SELECT //
Get the query results into local data types.
OracleBlob blob = reader.GetOracleBlob(0); string mimeType = reader.GetString(1); // // Use the mimetype to create a file extension // string fName = fprefix + "." + mimeType.Substring(mimeType.LastIndexOf("/")+ 1); // // Create output file // BinaryWriter outStream = new BinaryWriter (File.Open(fName,System.IO.FileMode.CreateNew)); // // Write binary contents from database into file //
Copy from the thumbnail BLOB to the output file.
byte [] buf = new byte[blob.OptimumChunkSize * 8]; long bytesWritten = 0; while (bytesWritten < blob.Length) { int len = blob.Read(buf, 0, buf.Length); outStream.Write(buf, 0, len); bytesWritten += len; } // Clean up outStream.Close(); cmd.Dispose(); return; }
It can be the case that the processing capabilities of interMedia do not include what is needed for a particular application. Perhaps it is necessary to merge two images, sharpen edges, or do other image-processing functions not available in interMedia.
Being the case that users may need additional image-processing capabilities, interMedia provides convenience classes so that Java Advanced Imaging (JAI) can interact with data stored in interMedia.
Some of the details are not covered here since they are covered in the previous chapters (e.g., creating the database connection).
The interMedia JAI I/O classes used in this demo are:
oracle.ord.media.jai.io.BlobInputStream
oracle.ord.media.jai.io.BlobOutputStream
There are other interMedia JAI I/O classes as well that are not directly used in this example, which are:
oracle.ord.media.jai.io.BfileInputStream
oracle.ord.media.jai.io.ByteArraySeekableOutputStream
oracle.ord.media.jai.io.FileSeekableOutputStream
oracle.ord.media.jai.io.MemoryCacheSeekableOutputStream
oracle.ord.media.jai.io.SeekableOutputStream
All of the JAI-related interMedia classes are included in the Oracle inter-Media jar file ordim.jar.
The example in the following section will do the following steps:
Load the image into JAI from the file system.
Create a row in the database for the image and thumbnail.
Use JAI to encode the image as JPEG and store it into the database.
Use JAI to create a JAI PlanarImage from the database for processing.
Use JAI to scale the image to a half-size thumbnail image.
Use JAI to encode and store this image into the database as a PNG image.
Commit the row update.
Reread the thumbnail image from the database into a JAI PlanarImage.
Use Java image processing to paint a logo into the thumbnail in the lower right corner of the image.
Use JAI to write the thumbnail with the logo into the file system.
To prepare for processing, the following JAI classes are included. These classes include the classes provided by Oracle interMedia that allow JAI clients to access media stored in the database.
import javax.media.jai.*; import javax.media.jai.operator.*; import com.sun.media.jai.widget.DisplayJAI; import com.sun.media.jai.codec.*; import oracle.ord.media.jai.io.BlobInputStream; import oracle.ord.media.jai.io.BlobOutputStream;
The database row is created, as in the previous Java example, on a JDBC connection with AutoCommit turned off.
/**
* Play with the image using Oracle and JAI
*/
void insertImageRow(int id, String desc,
String loc, String fileName,
Connection conn ) throws Exception
{
//
// Create JAI PlanarImage from file. We will
// Stream this image, using JAI, into the
// database using a java OutputStream.
//
//
PlanarImage src = JAI.create("fileload", fileName);
//
// Create new row with image
//Create a statement to insert the row returning the ROWID and image objects.
CallableStatement cstmt = conn.prepareCall ( "begin " + "insert into photos t (id, description, " + " location, image, thumb) " + " values (?, ?, ?, ORDImage.init(), " + " ORDImage.init())" + " returning rowid, t.image, t.thumb into " + " ?, ?, ? ; " + "end;"); // Register input parameters cstmt.setInt(1, id); cstmt.setString(2, desc); cstmt.setString(3, loc); // Register Output Parameters // RowID, Image and Thumbnail image cstmt.registerOutParameter(4, Types.VARCHAR); cstmt.registerOutParameter(5, Types.STRUCT, "ORDIMAGE"); cstmt.registerOutParameter(6, Types.STRUCT, "ORDIMAGE");
Execute the statement and obtain the ROWID and image objects.
int rowsUpdated = cstmt.executeUpdate(); String rowid = cstmt.getString(4); OrdImage imgObj = (OrdImage) ((OracleCallableStatement)cstmt).getORAData(5, OrdImage.getORADataFactory()); OrdImage thumbObj = (OrdImage) ((OracleCallableStatement)cstmt).getORAData(6, OrdImage.getORADataFactory()); cstmt.close(); // // Populate image BLOB with JAI image using JAI // to convert the image to jpg // And using the interMedia BlobOutputStream for JAI //
Use interMedia to create JAI-usable output stream and JAI to encode images as JPEGs and place them into database BLOB.
BlobOutputStream bos = new BlobOutputStream((java.sql.Blob) imgObj.getContent()); JAI.create("Encode", src, bos, "JPEG" , null); bos.close(); // // Set the image properties //
Tell the database to set the image properties.
imgObj.setProperties(); // // re-Create JAI image from database Blob based // stream using the interMedia BlobInputStream class //As an exercise, use the newly created image in the database as input to JAI.
BlobInputStream bis =$ new BlobInputStream((java.sql.Blob)$ imgObj.getContent());$ src = JAI.create("Stream", bis);$ // // Use JAI to make thumbnail here // ParameterBlock pb = new ParameterBlock(); pb.addSource(src); // The source image pb.add(0.5F); // The xScale pb.add(0.5F); // The yScale pb.add(0.0F); // The x translation pb.add(0.0F); // The y translation pb.add(new InterpolationNearest());//Scale algorithm // scale the image using JAI
Use JAI to scale the image from the database.
PlanarImage thumb = JAI.create("Scale", pb, null); // // Save thumbnail image into database as PNG format //As we have done with the image, use JAI and interMedia JAI classes to store the scaled image as a PNG image. Use interMedia to set the properties of the image.
bos = new BlobOutputStream((java.sql.Blob) thumbObj.getContent()); JAI.create("Encode", thumb, bos, "PNG" , null); bos.close(); thumbObj.setProperties(); // // Update database row with JAI created thumbnail //
Update the row with the image and thumbnail and commit the transaction.
OraclePreparedStatement updateImages = (OraclePreparedStatement)conn.prepareStatement( "Update photos set image= ?, thumb = ? " + " where rowid = ?" ); updateImages.setORAData(1, imgObj); updateImages.setORAData(2, thumbObj); updateImages.setString(3, rowid); updateImages.execute(); updateImages.close(); // // Commit the transaction // conn.commit(); // // Display original and thumbnail image // add(new DisplayJAI(src), BorderLayout.NORTH); add(new DisplayJAI(thumb), BorderLayout.WEST); }
Now that the image is in the database, we can query the database to obtain the thumbnail. Unlike what we have done in the past, a logo will be painted into the thumbnail. This additional functionality is here to demonstrate the ease of processing images from the database. In a real application, we would probably put the logo on the thumbnail when the row was inserted into the database.
void writeThumbnailFile(int id, Connection conn)
throws Exception
{
//
// Select the thumbnail
//Obtain the thumbnail image from the database.
OraclePreparedStatement selectThumb = (OraclePreparedStatement)conn.prepareStatement( "select thumb from photos where id = ?" ); selectThumb.setInt(1, id); OracleResultSet rset = (OracleResultSet)selectThumb.executeQuery(); rset.next(); OrdImage thumbObj = (OrdImage)rset.getORAData(1, OrdImage.getORADataFactory()); selectThumb.close(); // // Get the thumbnail into a JAI image //
Use JAI and interMedia JAI classes to load an image into JAI PlanarImage
BlobInputStream bis = new BlobInputStream((java.sql.Blob) thumbObj.getContent()); PlanarImage thumbnail = JAI.create("Stream", bis); // // Get the logo to place into lower right //
Load a logo image from a prepopulated logos table. Use the first logo in the table.
OraclePreparedStatement selectLogo = (OraclePreparedStatement)conn.prepareStatement( "select logo from logos where id = 1" ); rset = (OracleResultSet)selectLogo.executeQuery(); rset.next(); OrdImage logoObj = (OrdImage)rset.getORAData(1, OrdImage.getORADataFactory()); selectLogo.close(); bis = new BlobInputStream ((java.sql.Blob)logoObj.getContent()); PlanarImage logo = JAI.create("Stream", bis); // // Now that we have the thumbnail and logo, // draw logo into lower right corner. // BufferedImage thumbBi = thumbnail.getAsBufferedImage();
Paint the logo into the lower right corner of the thumbnail.
Graphics2D g2d = thumbBi.createGraphics(); g2d.drawImage(logo.getAsBufferedImage(), null, thumbnail.getWidth()-logo.getWidth(), thumbnail.getHeight()-logo.getHeight()); // // Save the thumbnail with logo into a file //
Create a file in PNG format with the thumbnail with the logo file.
JAI.create("filestore", thumbBi, "thumbLogo" + id + ".png", "PNG", null); // // Display the image with logo // add(new DisplayJAI(thumbBi), BorderLayout.EAST); addWindowListener(new WindowAdapter() { public void windowClosing(WindowEvent e) { System.exit(0); } }); pack(); show(); }
To run the sample, the methods in the previous two sections are included into a class with the following signature.
public class jaiIm extends Frame {
public static void main(String[] args) throws Exception {
if (args.length != 4) {
System.out.println("usage: java jaiIm " +
"connectionString username password inputFile");
System.out.println("e.g. java laiIm " +
"jdbc:oracle:oci:@inst1scotttiger"+
"myImage.jpg");
System.exit(-1);
}
// register the oracle jdbc driver with the JDBC
//driver managerConnect to the database, and make sure to set AutoCommit to false.
DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver()); Connection conn = DriverManager.getConnection(args[0], args[1], args[2]); // Note: it is CRITICAL to set the autocommit to // false so that two-phase select-commit of BLOBS // can occur. conn.setAutoCommit(false); jaiIm jaiim = new jaiIm();
Perform the processing in the previous two sections.
jaiim.insertImageRow(17, "jai Description", "jai location", args[3], conn); jaiim.writeThumbnailFile(17, conn); }
The execution of the example given above will result in the display shown in Figure 5.4, as well as a new database row and thumbnail with logo file. The top image is the original image, the lower left image is the thumbnail image, and the lower right is the thumbnail with logo image. Note that the logo has some transparent pixels so the image appears to wrap the text.
The examples in this section are just quick examples of how to use interMedia from some APIs. More complete and intensive data storage and manipulation are possible.
To use interMedia from an Oracle database requires an API that can perform SQL operations, operate on binary database BLOBS, and make use of PL/SQL procedures. Since these are the only requirements, the examples given in this section are only a subset of the APIs that can make use of interMedia. The processing using these other APIs will be similar to those examples that do not have interMedia convenience classes.
In addition to these requirements, interMedia provides convenience APIs for some interfaces. These include the Java and JAI environments.