Methods and apparatus for using multimedia data stored in a relational database in web applications6591295Abstract A Web agent software interface between a relational database and Web-based application programs employs stored SQL procedures to store, update and retrieve multimedia objects. Web based applications manipulate the data in the relational database table by using URLs consisting of an alphanumeric designation of the host computer and port, the Web agent program associated with the database, the database agent configuration information associated with the database, specific stored SQL procedure for performing the desired data manipulation, and additional parameter data needed to specify and manipulate a particular multimedia object. A clipboard application program provides a graphical user interface which a user may employ to select and view selected multi-media objects stored in selected relational database tables, and to transfer the Web URLs designating those selected objects to other Web applications, such as HTML authoring tools, using convenient "drag-and-drop" and "cut, copy and paste" operations. The Web agent program retrieves data from the database which are designated by such URLs, and accepts POST requests from HTML forms which are activated by the user of a Web browser to load or update data in the relational database. Claims What is claimed is: Description FIELD OF THE INVENTION
Item Description
domain:port The domain name and port number of the Web server,
separated by a colon (:). As seen in FIG. 2, the
domain:port portion of the URL designates the domain
name, which is resolved into a 32 bit Internet address
by the Internet Domain Name Service (DNS), of a
particular server 211 connected to the Internet
200, and the port number which specifies a
process running on the Web server 211 which
handles incoming HTTP requests for access to
a resource.
intermedia The virtual path representing the Web agent seen at 221.
agent_name The name of the "database agent" and its
associated configuration data 223 which establishes the
connection to the desired database.
request_mode The name of the Web agent request mode: i.e.,
performing a GET operation (225), a PUT
operation (226), obtaining status information in a
fixed format (228) performing a TABLEDUMP
operation (231) to retrieve a result set from
the database, including obtaining metadata and
application data, and executing an SQL statement
to perform such operations as inserting or modifying the
non-multimedia content of a row, deleting a row,
and creating PL/SQL procedures generated by the code
wizard.
proc_name The name of a SQL procedure 228 used to retrieve
or store the data.
path_info Additional path information 229 containing one or
more key values or other data used to perform the
specified operation with respect to a particular
multimedia object.
Special purpose request modes in addition to those depicted in FIG. 2 may by employed to handle error reporting. For example, the clipboard 117 uses a special version of the GET request (CLIPBOARDGET) that returns error information with a reserved MIME type so that the clipboard can distinguish an error condition from the content of a multimedia object. When uploading content, the Clipboard uses a special form of the PUT request (CLIPBOARDPUT) where error conditions are returned as a text/plain response with a standard format, which is also how the TABLEDUMP and EXECUTE requests 231 and 233 work. In contrast, the GET and PUT requests 224 and 225 originating from Web pages or forms return HTTP status codes and/or optional HTML status pages indicating the success or failure of the request. If a GET request fails, the web agent tries to map the error to a suitable HTTP status such as 403 Not Authorized or 404 Not found. All errors that can't be mapped to a specific HTTP error are mapped to 500 Server Error. When a PUT request 225 succeeds, by default, the Web agent 111 builds an HTML page containing a success message. If a PUT request 225 fails, the web agents still builds an HTML page, but this time, the actual error message is included on the page. Note that an application (PL/SQL procedure or Java stored procedure) can modify the default behavior by explicitly directing the Web agent to redirect the client to a static error page or to a Web application that might dynamically build an error page. Finally, a debugging version of the GET request can be used to reports errors in the same way as the default PUT failure mode. Note that, for compatibility, all portions of the URL should contain only those characters which are valid in an HTTP URL. Reserved characters such as spaces, ampersands, plus signs, question marks and percent signs should be escaped using %xx notation where xx is the hexadecimal value of the character (e.g., by convention, a space in the URL should be represented by "%20" and a space in a query string is represented by a "+"). The following example, which retrieves a photograph of an employee, shows a URL that uses the Web agent named "empdb_read", the request mode name "mediaget" specifies the GET mode), specifies using the SQL procedure "GET_EMP_PIC", and specifies a particular employee ID "20013 ":
http://www.orcl.com:8001/intermedia/empdb_read/mediaget/
get_emp_pic/20013
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+t+where+t.emp_id%3d20013
The URL must be on one line but is shown above on multiple lines for readability. Note the clipboard generates additional information in the URL that specifies the properties of the data after it is stored in the database. The Clipboard The clipboard, seen at 117 in FIG. 1, like any other application program, may be launched by name or from an operating system menu. When first invoked, the clipboard displays a dialog box which prompts the user to select the domain name and port of the web server for which the Web agent has been configured, and to further provide the virtual path name (e.g. "intermedia") of the Web agent. The clipboard then prompts the user to enter a user name and password which provides secure access to a listing of database agent names authorized for clipboard access. FIG. 3 illustrates the layout of the screen display produced by the clipboard. In addition to a conventional menu bar at 310, the clipboard display includes a navigator tree seen at 311 which displays lists of Web servers, database agents, and database tables in a hierarchical, collapsible outline view. The display further includes a table view at 315 which displays the rows and columns in the database table highlighted in the navigator tree 311. At 325, the clipboard display provides an object view which displays the multimedia object and a tabbed property sheet for that object. The following steps are performed to use the clipboard and Web Agent to insert or update multimedia objects in a database: 1. Create and store SQL procedures that can insert and update the multimedia data in the table as described below using the code wizard functions described below. 2. Load the new or updated multimedia object into the object view of the clipboard using one of the several methods to be described. 3. If necessary, edit the multimedia object using a suitable editing program for objects of that type. 4. Load the new or updated multimedia object into the database using the previously stored SQL procedures. To use the clipboard and Web agent to retrieve multimedia objects from a database, the user performs the following general steps: 1. Create and store SQL procedures that can retrieve the multimedia data from a designated table containing the desired multimedia objects, as described later. 2. Construct URLs for multimedia objects stored in the database by dragging an object's icon from the table view, seen at 315 in FIG. 3, to a Web authoring tool as seen at 121 in FIG. 1. At this time, the clipboard builds the URL that points to the multimedia object in the database. A Web browser often caches the multimedia content and issues HTTP GET requests with the "If Modified-Since" extension to validate the current contents of the cache. When the Web agent receives such a request, it checks the actual modification date of the object in the database and if more recent, returns the actual content to the browser. If not more recent, the Web agent returns a status indicating that the browser has the most recent content. The clipboard 117 and Web agent 111 employ stored SQL procedures to retrieve, store and update multimedia objects stored in database tables. Relational databases, such as the RDBMS 113 in FIG. 1 include SQL statement interpreters (as well as Java language virtual machines for executing stored Java procedures which execute SQL statements) to perform desired database operations. The clipboard 117 and Web agent 111 facilitate the task of creating and storing SQL procedures needed to retrieve, update, or insert multimedia objects in a database table by using a "code wizard" which prompts for the information needed to form the SQL procedures which are then interpreted by the RDBMS 113 to perform the desired database function. When the Code Wizard is first used to create the SQL procedures for a particular column in a table, the user is prompted for all the information required to create those procedures. However, if the code wizard is subsequently used again on the same table, the user is provided with default values entered by the user from the previous use. If the user chooses new selections, then the code wizard will replace any existing procedures of the same name. Once procedures have been created to access a column in a table, the web agent executes those procedures, supplying information to the procedures as necessary to locate the required data in the database. To call a PL/SQL procedure, the web agent has to dynamically construct an anonymous PL/SQL block that invokes the procedure. This it does once, having looked up the metadata for the procedure to obtain information such as the names and data types of all the arguments in the argument list. Once created, this anonymous SQL block is retained until the web server is stopped or the web agent is instructed to reload the configuration file, at which time it closes any open database connections, releasing information about previously accessed SQL procedures at the same time. To initially create and store the SQL procedures, the clipboard user performs these steps: 1. In the navigator tree 311, expand the list of servers, then expand the name of the particular database agent. The navigation tree 311 the displays the tables to which the database agent has access. 2. Select the desired table from the list of tables in the navigator tree 311. 3. Invoke the code wizard from the clipboard's main menu bar (e.g. Table.fwdarw.Code Wizard). 4. Complete the form presented by the code wizard, selecting a key value of the selected table to be used to specify the desired object. 5. To create an Insert SQL Procedure, which allows the user to insert textual and numeric data into new rows in this table, select the Insert option and then enter a name for the new insert SQL procedure, such as "INSERT_EMP_ROW ". 6. The code wizard displays the columns of the table. Using the control key and the mouse, select the columns for which the user should enter data and which the clipboard stores when a new row is inserted. For example, to store a photograph of an employee in an employee table, it might be necessary to identify an employee by name or number in addition to supplying their home address. 7. To create an Update SQL Procedure, which allows the user to modify textual and numeric data in existing rows in this table, select the Update option and then enter a name for the new update SQL procedure, such as "UPDATE_EMP_ROW ". 8. The code wizard displays the columns of the table. Using the Control key and the mouse, select the columns which the user may modify and which the clipboard updates when an existing row is updated. For example, an employee's address may need to be updated in addition to allowing their photograph to be updated. 9. From a displayed list box, select the object type (e.g. "PICTURE") to indicate that multimedia objects will be stored in the designated column. If multimedia objects are stored in more than one column in the table, additional columns may be specified. 10. To create a Get Object procedure to retrieve multimedia objects from the column, select that option and enter a name for the new Get Object procedure. 11. To create a Put Object procedure to use the clipboard to store multimedia objects in the column, select that option and enter the name of the new Put Object procedure. 12. When a multimedia object is stored in a table, certain attributes of the object may need to be set. Enter the name for the new Set Attribute procedure. If more than one column was specified in Step 9, the clipboard prompts the user for Get Object, Put Object, and Set Object procedures for those columns. When the SQL procedures have been created using the code wizard, the clipboard may be used to easily insert multimedia objects into a database. First, the user uses the navigation tree seen at 311 in FIG. 3 to select a particular table which is then displayed in the table view at 315. To insert a new row of data into this table, the user selects Table and Insert Row from the clipboard menu bar at 310. The clipboard then prompts the user with a series of dialog boxes which accept the values to be entered for the designated columns in the row. The clipboard then displays the object view at 325 for the new row. A multimedia object may be loaded using the object view using one of the following methods: a. From the "Clipboard" tab at 331, click "Paste" to copy an object from the operating system clipboard and paste it into the object view; b. From the "File" tab at 333, click Open to open a file containing a multimedia object and display it in the object view; c. From the "Internet" tab at 335, specify the URL of an object and then click "Fetch" (not shown) to fetch the object data via the Internet and place it in the object view; d. From the "Image Capture" tab at 336, identify and start a particular device, such as an available TWAIN driver for a camera or scanner to capture the object and place it in the object view. With the object loaded into the object view, the user may then select the "Database" tab at 337. The MIME type of the object is displayed and this value is passed via a parameter in the SQL procedure. The object is loaded into the database for persistent storage when user clicks on "Update" in the Database tab. An existing multimedia object may updated in much the same way. The particular table containing the object is selected using the navigation tree 311, and a row and column is selected in that table using the table view 315 so that the object to be updated is displayed in the object view. The table view may be used to display a subset of the records in any table sorted in a desired order using conventional database query and sort procedures selected via the menu at 310. Once a particular object has been identified in a particular row and column location in the table view, that object may then be copied and pasted into an editor, saved as a file and opened in an editor, or replaced by a newly captured image or by a different image fetched via the Internet as described above. A list of one or more editors which may be invoked is presented at the Edit tab 338. When the updated image is present in the object view, the Update button at the Database tab 337 is clicked to update the object. To retrieve an object from the database and use that object in an application that supports "drag-and-drop" operations, the object is first displayed in the object view as described above. The user then specifies Table.fwdarw.Drag Mode.fwdarw.Embed from the menu at 310 to place a reference (URL) to the database object in the target application, such as an HTML editor for creating Web pages. The necessary <img src="url"> tag including the database URL is then automatically inserted into the Web page HTML, and the image is seen in the Web page editor. Alternatively, by selecting "Edit.fwdarw.Drag Mode.fwdarw.Link" a link of the form "<a href="url">anchor text</a>" containing the object's database URL is automatically placed in the Web page HTML. Having selected the drag mode, the icon which represents the object in the table view as seen at 341 may be dragged to and dropped into the target application, such as the Web page authoring tool which constructs the appropriate HTML tag. Storing Data From Web Pages The Web agent illustrated at 111 in FIG. 1 may also be used to store data supplied via a Post request method from an HTML form. When the HTML Web page containing the input form is written, the parameters needed are incorporated into the form markup as illustrated by the following example:
<form
action="http://nedc.us.ora.com:8007/intermedia/emp_write/mediaput/
PUT_EMP_PIC"
method="post" enctype="multipart/form-data">
<input type="hidden" name="ord_post_put_call" value=
"SET_EMP_PIC">
Employee ID: <input type="text" name="ord_procedure_path"
length=5>
Employee Picture File: <input type="file" name="ord_content">
<input type="Submit" value="Store Employee Picture Now">
</form>
FIG. 4 of the drawings illustrates an HTML web page that contains the form specified by the markup above. Note in the HTML above that the "action" specifies the full URL pathname for the SQL procedure PUT_EMP_PIC which has been previously stored for use by the Web agent in storing multimedia objects in a particular database table. In the URL, "emp_write" is the database agent name. The encoding type is specified as "multipart/form-data" and the multimedia object is identified with the variable name "ord_content" which specifies the value entered by the browser user for the particular image file that contains the multimedia object to be loaded. The key value of the table row which is to receive the object is specified by the "ord_procedure_path" variable name, and the "ord_post_put call" variable name is used to set the properties of the data after it is stored in the database. The variable specifies the name ("SET_EMP_PIC") of the SQL procedure that performs post-processing. Using the method illustrated above, multimedia objects may be inserted or updated into a Web server database from any Web browser. Similarly, as noted earlier, multimedia objects may be imbedded in, or linked from, a Web page display using conventional Web page authoring tools in combination with the automatic URL generation and transfer mechanism provided by the clipboard and Web agent. Conclusion It is to be understood that the methods and apparatus which have been described are merely illustrative applications of the principles of the invention. Numerous modifications may be made by those skilled in the art without departing from the true spirit and scope of the invention.
|
Same subclass Same class Consider this |
||||||||||