3
Using Oracle8i interMedia Clipboard

This chapter describes how to use the drag-and-drop interface of Oracle8i interMedia Clipboard to retrieve Oracle8i interMedia objects from a database and to store and update Oracle8i interMedia objects in a database. It describes the following:

• Invoking Oracle8i interMedia Clipboard

• Connecting to the database through a database agent

• Understanding the interface of Oracle8i interMedia Clipboard

• Following basic steps to use Oracle8i interMedia Clipboard

• Creating PL/SQL procedures so you can retrieve, store, and update multimedia objects

• Storing multimedia objects into the database

• Updating multimedia objects in the database

• Retrieving multimedia objects from the database and dragging references to them into a Web authoring tool

• Understanding URL formats

• Editing multimedia objects from Oracle8i interMedia Clipboard

• Querying tables

Before you can use the Clipboard, you must add the ORDWEBUTL package to each database you plan to access. The package installs catalog tables, which help the Clipboard keep track of the names of columns containing multimedia objects, primary key columns, PL/SQL procedures, and the types of multimedia objects supported by the Clipboard. For information on adding the package, see the online Read Me file.

3.1 Invoking Oracle8i interMedia Clipboard

You invoke Oracle8i interMedia Clipboard from the Windows NT Program menu. Choose Oracle for Windows NT -> Oracle interMedia -> Oracle interMedia Clipboard.

3.2 Connecting to the Database

When you invoke Oracle8i interMedia Clipboard, the Connect box prompts you for the name of a Web Agent. Enter the following information:

• Host: The domain of the Web server for which Oracle8i interMedia Web Agent is configured.

• Port: The port of the Web server.

• Virtual Path: The virtual path of Oracle8i interMedia Web Agent. Use the virtual path specified in the Web Agent section of the configuration file. The default is /intermedia.

Click Connect.

Oracle8i interMedia Clipboard connects to the Web server and Oracle8i interMedia Web Agent. Then, it prompts you for the user name and password for the agent list. The information you specify must match the agent_list_user and agent_list_password configuration parameters in the Web Agent section of the configuration file. (For information on the configuration file, see Section 2.1.4.) If the information matches, Oracle8i interMedia Clipboard lists the database agents authorized for clipboard access.

3.3 Understanding Oracle8i interMedia Clipboard

Oracle8i interMedia Clipboard helps you easily capture, store, and retrieve multimedia Web objects. The Clipboard has three primary views:

• Navigator Tree, which displays lists of Web servers, database agents, and database tables in a hierarchical view.

• Table View, which displays the rows and columns in the database table.

• Object View, which displays the Oracle8i interMedia object and a property sheet for that object.

In the following figure, the Clipboard displays the expanded list of database agents and tables in the Navigator Tree, the rows and columns of a table named GRAPHICS in the Table View, and an Oracle8i interMedia object (an image) with the GRAPHIC_ID of 10002 in the Object View:

3.4 Using Oracle8i interMedia Clipboard: Basic Steps

To use Oracle8i interMedia Clipboard and Web Agent to insert or update multimedia objects in a database, follow these general steps:

1. Create PL/SQL procedures that can insert and update the multimedia data in the table. See Section 3.5 for information on creating the procedures.

2. Load the new or updated multimedia object into the Object View of Oracle8i interMedia Clipboard. You can use one of several methods to load the multimedia object.

3. If necessary, edit the multimedia object from within Oracle8i interMedia Clipboard, using your favorite editor.

4. Load the new or updated multimedia object into the database.

See Section 3.6 for information about inserting new multimedia objects into the database. See Section 3.7 for information about updating multimedia objects in the database.

To use Oracle8i interMedia Clipboard and Web Agent to retrieve multimedia objects from a database, follow these general steps:

1. Create PL/SQL procedures that can retrieve the multimedia data from the table containing the multimedia objects. See Section 3.5 for information on creating the procedures.

2. Retrieve multimedia objects from the database by dragging them from the Table View of Oracle8i interMedia Clipboard to a Web authoring tool. Oracle8i interMedia Clipboard and Web Agent generate a URL that points to the multimedia object in the database. (Section 3.9 describes the URL format.)

The object types ORDImage, ORDAudio, ORDVideo, and ORDVir contain an attribute for the last modification date of the object. Before displaying an object, Oracle8i interMedia Clipboard reads the attribute. If the last modification date of the object is more recent than the date of the object stored in the Web browser cache, Oracle8i interMedia Clipboard retrieves the contents of the multimedia object from the database. If the last modification date of the object is earlier than the date of the object stored in cache, Oracle8i interMedia Clipboard displays the object stored in cache.

If you are using the ORDImgB object type, the Oracle8i interMedia Clipboard Code Wizard asks you to name a column as the last-modification-date column. If you do not specify such a column, Oracle8i interMedia Clipboard always retrieves the contents of the multimedia object from the database. (Note that browsers use the last-modification-date column or attribute in a similar way.)

See Section 3.8 for more information about retrieving multimedia objects.

For the exercises in this chapter, assume that you have a database agent named EMP_CLIP. The configuration file defines it as having a request class of clipboard and a retrieve_database_agent named empdb_read. The database agent EMP_CLIP can access the database table named EMPLOYEE_PIC, which contains the following columns:

• PICTURE holds the employee picture as an Oracle8i interMedia image with a data type of ORDSYS.ORDImage.

• EMP_ID is a primary key which contains the employee ID. It has a data type of NUMBER.

• EMP_NAME contains the employee name and has a data type of VARCHAR2.

• MGR_NAME contains the name of the employee's manager and has a data type of VARCHAR2.

The following figure shows the Clipboard as it displays the EMPLOYEE_PIC table, which currently contains no rows:

3.5 Creating PL/SQL Procedures with Oracle8i interMedia Clipboard

Before you can use Oracle8i interMedia Clipboard to retrieve, insert, or update multimedia objects in a table, you must create PL/SQL procedures for that table using the Code Wizard.

The procedures specify the information needed to process the request, including the content type, content length, and HTTP status. For more information about the PL/SQL procedures and their parameters, see Section 4.2.

To create the procedures, take these steps:

1. In the Navigator Tree, expand the list of servers, then expand the EMP_CLIP agent. Oracle8i interMedia Clipboard displays the tables to which the database agent has access.

2. Select the EMPLOYEE_PIC table from the list of tables in the Navigator Tree.

3. To invoke the Code Wizard, choose Table -> Code Wizard.

4. From the drop-down list on the first page of the Code Wizard, select EMP_ID to represent the key value. This column must contain unique values. Click Next.

5. To create an Insert Procedure, which lets you use Oracle8i interMedia Clipboard to insert rows of data into this table, click Yes. Then, click Next.

6. For the name of the new Insert Procedure, enter INSERT_EMP_ROW.

7. Using the Control key and the mouse, select the following columns to indicate that the Clipboard user will populate these columns:
   • EMP_ID

   • EMP_NAME

   • MGR_NAME

     
     

8. Click Next.

9. To create an Update Procedure, which lets you use Oracle8i interMedia Clipboard to update rows of data in this table, click Yes. Then, click Next.

10. For the name of the new Update Procedure, enter UPDATE_EMP_ROW.

11. Using the Control key and the mouse, select the following columns to indicate that the user will update these columns:
    • EMP_NAME

    • MGR_NAME

12. Click Next.

13. From the list box, select PICTURE to indicate that multimedia objects will be stored in this column. Click Next.

    If multimedia objects are stored in more than one column in the table, you can select more than one column.

14. To create a Get Object Procedure to retrieve multimedia objects from the column, click Yes. Then, click Next.

15. For the name of the new Get Object Procedure, enter GET_EMP_PIC. Click Next.

16. To create a Put Object Procedure to use the Clipboard to store multimedia objects in the column, click Yes. Then, click Next.

17. For the name of the new Put Object Procedure, enter PUT_EMP_PIC. Click Next.

18. When you store a multimedia object in a table, you may need to set certain attributes of the objects. To create a Set Object Attribute Procedure to do so, click Yes. Then, click Next.

19. For the name of the new Set Object Attribute Procedure, enter SET_EMP_PIC. Click Next.

    If you specified that multimedia objects are to be stored in more than one column in Step 13, the Clipboard prompts you for Get Object, Put Object, and Set Object procedures for those columns.

20. To create the procedures, click Execute.

    The Code Wizard creates the procedures and displays information about success of its actions.

21. Click Finish to exit the Code Wizard.

If the procedures already exist, you can use the Code Wizard to replace them with new versions, delete them, or leave them as they are.

If you are using the ORDImgB object type, the Code Wizard asks you to select the last-modification-date column. (If your table does not contain such a column, leave this blank.) If you use such a column, note that you should not select it in the steps that create the insert and update procedures. The procedure created by Oracle8i interMedia Clipboard populates this column.

3.6 Storing Oracle8i interMedia Data in a Database

Using Oracle8i interMedia Clipboard, you can store Oracle8i interMedia objects in a database.

If you have not generated PL/SQL procedures for the table, you must use the Code Wizard to do so. See Section 3.5 for step-by-step directions.

For this exercise, assume that you want to use Oracle8i interMedia Clipboard to insert a new row into the EMPLOYEE_PIC table and to store the employee's picture into the new row. The picture currently resides in a file.

To store a new row, take these steps:

1. In the Navigator Tree, expand the list of servers, then expand the list of agents. Expand the EMP_CLIP agent to see the list of tables. Double-click the EMPLOYEE_PIC table. Oracle8i interMedia Clipboard displays the Table View. Currently, it contains no rows.

2. To insert a new row into the database table, choose Table -> Insert Row. Oracle8i interMedia Clipboard displays a series of Insert dialog boxes, which prompt you for the values to be entered in the columns in the row. The column names are prefixed with "IN_".

3. In the Insert dialog box, enter a value, 20012, for the EMP_ID column and click OK.

4. In the next Insert dialog box, enter a value, diamon, for the EMP_NAME column and click OK.

5. In the next Insert dialog box, enter a value, rubin, for the MGR_NAME column and click OK.

6. Click OK to dismiss the informational message.

Oracle8i interMedia Clipboard displays the Object View for the row you inserted.

You can insert more than one row at a time by choosing Table -> Insert Multiple Rows.

To store the Oracle8i interMedia object into the row, take these steps:

1. If the Object View is not displayed, select the row and double-click the object icon. Oracle8i interMedia Clipboard displays the Object View.

   
   

   You can load the Oracle8i interMedia object into the Object View using one of the following methods:

   • From the Clipboard tab, click Paste to copy an Oracle8i interMedia object from the Windows NT clipboard and paste it into the Object View.

   • From the File tab, click Open to open a file containing a multimedia object. Oracle8i interMedia Clipboard displays the file in the Object View.

   • From the Internet tab, specify a URL for an Oracle8i interMedia object and click Fetch to fetch the image. Oracle8i interMedia Clipboard displays the file in the Object View.

   • From the Capture tab, click Start to start the device, such as a TWAIN driver, to capture the object.

2. For this exercise, assume that the employee picture is stored in a file in GIF format. From the File tab, click Open and select the file containing the employee's picture, for example, ddiam.gif. Click Open. Oracle8i interMedia Clipboard displays the picture in the Object View.

   Note that the object is not loaded into the database at this point.

   If you wish, you can edit the object by selecting the Edit tab. (See Section 3.10 for more information.)

3. To load the object into the database, select the Database tab. The MIME type is displayed in the box and the value is passed through the ORD_CONTENT_TYPE parameter in a PL/SQL procedure.

4. Click Update. Oracle8i interMedia Clipboard loads the object into the database.

The Table View now looks like this:

3.7 Updating Oracle8i interMedia Data in a Database

Using Oracle8i interMedia Clipboard, you can update Oracle8i interMedia objects in a database.

If you have not generated PL/SQL procedures for the table, you must use the Code Wizard to do so. See Section 3.5 for step-by-step directions.

In this exercise, you update the employee picture that you stored in the previous section. Take these steps:

1. In the Table View, from the row identified with the EMP_ID of 20012, double-click the multimedia icon in the PICTURE column. Oracle8i interMedia Clipboard displays the Object View.

   Update the Oracle8i interMedia object in the Object View of Oracle8i interMedia Clipboard. You can update the Oracle8i interMedia object in several ways:

   • From the Clipboard tab, click Paste to copy an Oracle8i interMedia object from the Windows NT clipboard and paste it into the Object View.

   • From the File tab, click Open to open a file containing an image. Oracle8i interMedia Clipboard displays the file in the Object View.

   • From the Internet tab, specify a URL for an Oracle8i interMedia object and click Fetch to fetch the image into the Object View.

   • From the Capture tab, click Start to start the device, such as a TWAIN driver, to capture the object.

2. For this exercise, assume that the updated employee picture is stored in a file in GIF format. From the File tab, click Open and select the employee's picture, for example, ddiam2.gif. Oracle8i interMedia Clipboard displays the picture in the Object View.

   Note that the object is not loaded into the database at this point.

3. Select the Database tab. The MIME type is displayed in the box and the value is passed to the Insert Procedure through the ORD_CONTENT_TYPE parameter.

4. Click Update. Oracle8i interMedia Clipboard loads the object into the database.

3.8 Retrieving Oracle8i interMedia Data from a Database

Using Oracle8i interMedia Clipboard, you can retrieve Oracle8i interMedia objects from a database and drag the objects into a Web authoring tool. Oracle8i interMedia Clipboard generates a URL for the object and adds it to the page in the Web authoring tool.

If you have not generated PL/SQL procedures for the table, you must use the Code Wizard to do so. See Section 3.5 for step-by-step directions.

In this exercise, you retrieve a multimedia object from the database and drag the object into a Web authoring tool. Take these steps:

1. Display the Table View if it is not already displayed. (In the Navigator Tree, expand the list of servers, then expand the list of agents. Expand the EMP_CLIP agent to see a list of tables. Double-click the EMPLOYEE_PIC table. Oracle8i interMedia Clipboard displays the Table View.)

2. To specify the drag mode, choose Edit -> Drag Mode -> Embed. The choices for Drag Mode are:
   • Embed: Puts a reference to the object into the Web authoring page so that the image is displayed.

     For example, if you drag an image in Embed mode, Oracle8i interMedia Clipboard passes the <IMG SRC> tag to the HTML code. The <IMG SRC> tag contains a URL that points to the object in the database. When you view the page in a browser or Web authoring tool, Oracle8i interMedia Web Agent decodes the URL, retrieves the image from the database, and displays the image.

   • Link: Puts a link to the object into the Web authoring page, so that a link is displayed.

     For example, if you drag an image in Link mode, Oracle8i interMedia Clipboard passes the <A HREF> tag to the HTML code. The <A HREF> tag contains a URL that points to the object in the database and the text that is displayed as the link. You can edit the link text in the Web authoring tool. When you view the page in a browser or Web authoring tool, the browser or Web authoring tool displays the link text. When you click the link, Oracle8i interMedia Web Agent decodes the URL, retrieves the image from the database, and displays the image.

3. In the Table View, select an icon from the EMPLOYEE_PIC column. The icon represents a multimedia image object. Drag the object into the Web authoring tool.

   Oracle8i interMedia Clipboard passes the <IMG SRC> tag to the Web authoring tool. The <IMG SRC> tag contains a URL that points to the object in the database. For example, if you select the object from a row with the EMP_ID of 20012, Oracle8i interMedia Clipboard generates the following HTML tag:

   <img src="http://imedia.ora.com:8007/intermedia/empdb_read/mediaget/
   GET_EMP_PIC/20012" width="250" height="300">
   
   

   The parts of the URL that follow intermedia indicate the following:

   • empdb_read is the database agent name.

   • mediaget indicates that Oracle8i interMedia Web Agent should retrieve the object.

   • GET_EMP_PIC is the name of the PL/SQL procedure that is used to retrieve the object.

   • 20012 is the EMP_ID, a key value.

For more information about the URL format, see Section 3.9.

Now, you can continue to author your Web page using your Web authoring tool.

When you view the page in a browser or Web authoring tool, Oracle8i interMedia Web Agent decodes the URL, retrieves the image from the database, and displays the image.

3.9 Understanding URL Formats

When you use Oracle8i interMedia Clipboard to retrieve Oracle8i interMedia objects from a database and drag the objects into a Web authoring tool, the Clipboard constructs a URL. In the same way, when you use Oracle8i interMedia Clipboard to store or update Oracle8i interMedia objects in a database, the Clipboard constructs a URL.

Oracle8i interMedia Clipboard constructs the URLs using the following format:

http://domain:port/intermedia/agent_name/request_mode/proc_name/path_info

You use the same format when you use a Web application to retrieve, store, or update multimedia data.

Table 3-1 shows the meaning of the parts of the URL format.

Table 3-1  URL Format

Item	Description
domain:port	The domain name and port number of the Web server, separated by a colon (:).
intermedia	The virtual path representing Oracle8i interMedia Web Agent. The name must match the virtual path listed in the interMedia Web Agent section of the configuration file. This value is case sensitive.
agent_name	The name of the database agent representing the connection to the database.
request_mode	The name of the Oracle8i interMedia Web Agent request mode. The valid modes are: mediaget: Retrieves multimedia data from a database. mediaput: Stores multimedia data in a database. ~mediaget: Provides more information about error conditions. Useful for debugging mediaget requests. appmediaput: Provides completion status information in a fixed format. Useful for developing custom applications that store and update multimedia data.
proc_name	The name of a PL/SQL procedure to retrieve or store the data. You can express the name of the procedure using one of the following forms: procedure-name package-name.procedure-name synonym-name.procedure-name schema-name.procedure-name schema-name.package-name.procedure-name The Web Agent evaluates the PL/SQL procedure name in the order listed. If the authorized_sql_procedures configuration parameter for the database agent specifies particular PL/SQL procedures, you must use the same form in the URL as is listed for the configuration parameter.
path_info	Additional path information, which contains one or more key values used to locate the Oracle8i interMedia object. The key values should contain only characters that are valid in an HTTP URL. Reserved characters such as spaces, ampersands (&), plus signs (+), question marks (?), or percent signs (%) should be written using the %xx escape notation. For example, a space is written as %20 (not using a plus sign (+) as in a query string) and a colon as %3A. Note that rowids may contain these reserved characters.

The following example, which retrieves a photograph of an employee, shows a URL that uses the agent empdb_read, the PL/SQL procedure GET_EMP_PIC, and the employee ID 20013.

http://www.orcl.com:8001/intermedia/empdb_read/mediaget/get_emp_pic/20013

In addition to using a PL/SQL procedure, you can use an anonymous PL/SQL block or a SQL statement passed as a parameter to the request mode. You use the ord_sql variable name to pass the statement. However, note that PL/SQL anonymous blocks and SQL statements may not be as efficient as PL/SQL procedures.

The following example shows a URL that uses a SQL statement to retrieve an image:

http://www.orcl.com:8001/intermedia/empdb_read/mediaget?ord_sql=
select+t.picture.GetContent()+as+ord_content_blob,+
t.picture.GetMimeType()+as+ord_content_type,+
t.picture.GetContentLength()+as+ord_content_length+
from+employee_pic+twhere+t.emp_id%3d20013

The URL must be on one line. It is shown here on multiple lines for readability.

You cannot specify a SQL statement as a component of the path in a URL. You can specify one only as a query string or POST data variable. For more information about POST data variables, see Section 4.2.1.

Note that when you store data using Oracle8i interMedia Clipboard, it generates additional information in the URL that sets the properties of the data after it is stored in the database. For information about the URL format and setting the properties when you store data using an application, such as a Web form, rather than Oracle8i interMedia Clipboard, see Section 4.1.

3.10 Editing Oracle8i interMedia Objects Through the Clipboard

You can edit Oracle8i interMedia objects through Oracle8i interMedia Clipboard. The Object View contains an Edit tab from which you can specify the editing software you want to use and from which you can invoke the software.

For example, you can select an image from a database, and, from the Object View, invoke image editing software. After making the changes to the image, you can load the modified image into the database.

The following steps describe how to edit an image:

1. In the Navigator Tree, expand the list of Web servers, then expand the list of agents. Expand the EMP_CLIP agent to see a list of tables. Double-click the EMPLOYEE_PIC table. Oracle8i interMedia Clipboard displays the Table View.

2. In the Table View, from the row identified with the EMP_ID of 20012, double-click the multimedia icon from the PICTURE column. Oracle8i interMedia Clipboard displays the Object View.

3. Select the Edit tab.

4. Click Editor and browse the file system to find the executable image for the image editing software you prefer. Double-click the executable image. Once you select the image editing software, the Clipboard stores the information; you do not need to select it again unless you want to change to different software.

5. Click Edit. Oracle8i interMedia Clipboard invokes the image editor.

6. Make the necessary changes to the image and save the file.

7. From the Edit tab of the Object View, click Reload to load the modified image into the Object View.

8. Select the Database tab. The MIME type is displayed in the box and the value is passed through the ORD_CONTENT_TYPE parameter.

9. Click Update. Oracle8i interMedia Clipboard updates the image in the database.

3.11 Defining Queries and Sort Order for a Table

To refine the result set that is shown in the Table View, you can specify a query for the table using the Query and Sort dialog box. It displays the columns in the table on which you can base a condition or sort order. This is especially useful when the table contains a great many rows.

Note, because you cannot base a condition or sort order on an Oracle8i interMedia object, the Oracle8i interMedia column does not appear in this dialog box.

To specify a query and sort order, take these steps:

1. From the Navigator Tree, select the table you want to query.

2. Choose Table -> Enter Query. Oracle8i interMedia Clipboard displays the Query and Sort dialog box.

3. From the Column Name column, highlight the first column you want to include in the query and select Yes from the drop-down list. Press the Enter key.

   (After each selection, press the Enter key to register the value.)

4. If you want a condition to be based on this column, select an operator from the Operator drop-down list. Then, in the Value column, enter a value and press the Enter key.

   For example, if the table contains the column ID with a numeric data type, you can specify the greater than or equal to operator (>=) and a value of 2000 for the ID column. Oracle8i interMedia Clipboard generates a SQL query with the condition WHERE ID >= 2000.

   You can enter more than one condition for a particular column by clicking More and then specifying another operator and another value.

   For information about the listed operators, see Oracle8i SQL Reference.

5. To specify that the column is used to sort the results of the query, select a number from the Sort drop-down list and press the Enter key. The number specifies the precedence the column takes in the sort order. You can sort the query on multiple columns.

6. If the column is one on which you want to sort the results, select ASC (ascending) or DES (descending) from the Direction drop-down list and press the Enter key.

7. Repeat Steps 2 through 5 for each column to be included in the query.

8. Click OK.

3.11.1 Adding More Than One Condition

You can enter more than one condition for a particular column by clicking More and then specifying another operator and another value. Oracle8i interMedia Clipboard combines the conditions with an OR operator.

You can enter conditions for more than one column. Oracle8i interMedia Clipboard combines the conditions with an AND operator.

When you use more than one condition for a column, Oracle8i interMedia Clipboard uses separate pages for each condition. It groups the first condition for each column on the same page. You can display the previous page by clicking Previous. You can display the next page by clicking Next.

For example, assume you have two columns, ID and JOB_CODE, both numeric columns. You want a query that gives you rows where the ID is less than 2000 or greater than 5000 and the JOB_CODE is greater than 670 (to represent managers). You enter the conditions in the following way:

1. For column ID, select the less than operator (<) and enter a value of 2000.

2. For JOB_CODE, select the greater than operator (>) and enter a value of 670. You now have the first page of conditions. Oracle8i interMedia Clipboard constructs the condition WHERE ID < 2000 AND JOB_CODE > 670.

3. Click More to add a second page of conditions.

4. For column ID, select the greater than operator (>) and enter a value of 5000.

5. For JOB_CODE, select the greater than or equal to operator (>=) and enter a value of 670.

   You now have the second page of conditions. Oracle8i interMedia Clipboard constructs the condition WHERE ID > 5000 AND JOB_CODE >= 670.

   You can view the previous page by clicking Previous.

   Oracle8i interMedia Clipboard puts the two pages of conditions together and constructs a query with the following conditions:

   WHERE (ID < 2000 AND JOB_CODE > 670)  OR (ID > 5000 AND JOB_CODE > 670) 
   
   

6. Click OK to execute the query.