When you use a plug-in, you can create a UAP by specifying the facility provided by the plug-in. This makes it possible to manipulate multimedia data such as documents and spatial data.

The following examples use the HiRDB Text Search Plug-in.

(a) Data retrieval

An example of using a Boolean predicate for data retrieval is explained as follows.

Example

In this example, the SELECT statement retrieves medicine IDs that contain the keyword headache in the efficacy section of the operation manual column of the MEDICINE_MANAGEMENT_TABLE (the SQL uses the facility contains in order to extract those documents that match the text search condition provided by the plug-in):

 
SELECT medicine-ID FROM MEDICINE_MANAGEMENT_TABLE
  WHERE contains(operation-manual,'attached-document-data[efficacy{"headache"}]') IS TRUE

(b) Data updating

An example of data updating is explained as follows.

Example

In this example, the UPDATE statement updates the data in the operation manual for those columns in the MEDICINE_MANAGEMENT_TABLE that have medicine 2 as the medicine ID (the SQL uses the SGMLTEXT facility provided by the plug-in):

 
UPDATE MEDICINE_MANAGEMENT_TABLE SET operation-manual = SGMLTEXT(:sgml)
  WHERE medicine-ID = 'medicine-2'
 

The following sgml BLOB type embedded variable must be defined before the UPDATE statement:

 
EXEC SQL BEGIN DECLARE SECTION;                   1
  SQL TYPE IS BLOB(300K)sgml;                     1
EXEC SQL END DECLARE SECTION;                     1
strcpy(sgml.sgml_data,char_ptr_pointing_to_a_sgml_text);   2
sgml.sgml_length =
 strlen(char_ptr_pointing_to_a_sgml_text);                3

Explanation

1. Defines the sgml BLOB type embedded variable.
2. Stores the new data for updating in the sgml embedded variable.
3. Sets the attribute value sgml_length of the created BLOB data to the length of the stored data.

(c) Data deletion

An example of data deletion is explained as follows:

Example

In this example, the DELETE statement deletes from the MEDICINE_MANAGEMENT_TABLE those rows that have medicine 2 in the medicine ID column:

 
DELETE FROM MEDICINE_MANAGEMENT_TABLE
  WHERE medicine-ID = 'medicine-2'

(d) Data insertion

An example of data insertion is explained as follows.

Example

In this example, the INSERT statement inserts into the MEDICINE_MANAGEMENT_TABLE rows that have medicine 25 in the medicine ID column (the SGMLTEXT facility provided by a plug-in is used in the SQL):

 
INSERT INTO MEDICINE_MANAGEMENT_TABLE (medicine-ID,operation-manual)
  VALUES('medicine-25',SGMLTEXT(:sgml))
 

The following sgml BLOB type embedded variable must be defined before the UPDATE statement:

 
EXEC SQL BEGIN DECLARE SECTION;                   1
  SQL TYPE IS BLOB(300K)sgml;                     1
EXEC SQL END DECLARE SECTION;                     1
strcpy(sgml.sgml_data,char_ptr_pointing_to_a_sgml_text);   2
sgml.sgml_length =
strlen(char_ptr_pointing_to_a_sgml_text);                 3

Explanation

1. Defines the sgml BLOB type embedded variable.
2. Stores the new data for updating in the sgml embedded variable.
3. Sets the attribute value sgml_length of the created BLOB data to the length of the stored data.

Creating a UAP by specifying the functions provided by HiRDB XML Extension allows you to manipulate XML data.

(a) Data retrieval

Examples of XML data retrieval follow.

Example 1

In this example, book information containing the book ID 126513592 is extracted from the book control table as a VARCHAR type value. The SQL statement can be written as follows:

SELECT book-ID, XMLSERIALIZE(BOOK INFORMATION AS VARCHAR(32000))
    FROM Book Control Table
    WHERE book-ID = 126513592

Example 2

In this example, the titles of books that fall in the database category are extracted from the book control table. To extract evaluation results of an XQuery expression, the XMLQUERY function is used. Furthermore, in order not to output rows in which the evaluation result of the XQuery expression is an empty sequence, the XMLEXISTS predicate is used. The SQL statement can be written as follows:

  SELECT book-ID,
         XMLSERIALIZE(
           XMLQUERY('/Book Information/Title'
                     PASSING BY VALUE BOOK INFORMATION
                     RETURNING SEQUENCE EMPTY ON EMPTY)
           AS VARCHAR(32000))
    FROM Book Control Table
    WHERE XMLEXISTS('/Book Information[category="database"]'
                     PASSING BY VALUE BOOK Information)

Example 3

In this example, the titles of books that fall in the database category are combined and extracted from the book control table. To output the XML value from each row as a single XML type value, the XMLAGG set function is used. The SQL statement can be written as follows:

SELECT XMLSERIALIZE(
         XMLAGG(
           XMLQUERY('/Book Information/Title'
                      PASSING BY VALUEBOOK INFORMATION
                      RETURNING SEQUENCE EMPTY ON EMPTY)
         )
         AS VARCHAR(32000))
       FROM Book Control Table
       WHERE XMLEXISTS('/Book Information[category="database"]'
                       PASSING BY VALUE BOOK INFORMATION)

Example 4

In this example, information on books whose title is the Complete SQL Guide and the titles of books that fall in the same category are extracted from the book control table. In order to use the XML value from each row as a single XML type value and evaluate the XQuery expression on this value, the XMLAGG set function has been specified as the argument of the XML query of the XMLQUERY function. The SQL statement can be written as follows:

SELECT
  XMLSERIALIZE(
    XMLQUERY(
      '$BOOKS/Book information [category=$BOOKS/book information[title="Complete SQL Guide"]/category]'
      PASSING BY VALUE XMLAGG(BOOK INFORMATION) AS BOOKS
      RETURNING SEQUENCE EMPTY ON EMPTY)
    AS VARCHAR(32000))
  FROM Book Control Table

A routine or a component specification is used to manipulate data of a table containing a user-defined abstract data type. A component specification is used to manipulate the attribute of a column that comprises an abstract data type. The following examples manipulate the data of a table containing a user-defined abstract data type.

(a) Retrieving an abstract data type column

An example of retrieving a column of a table containing a user-defined abstract data type is explained as follows.

Example

In this example, the SELECT statement uses the user-defined facility service-years to retrieve from STAFF_TABLE the employee numbers of employees whose service years are 20 years or more:

 
SELECT employee-no
  FROM STAFF_TABLE
  WHERE service-years (employee)>=20

(b) Updating an abstract data type column

An example of updating a column of a table containing a user-defined abstract data type is explained as follows.

Example

In this example, the UPDATE statement updates to CHIEF the position employee attribute of the employee whose EMPLOYEE_NO column in STAFF_TABLE is 9001230; this UPDATE statement uses the employee..position component specification for this updating:

 
UPDATE STAFF_TABLE
SET employee.position ='CHIEF'
WHERE employee_no = '900123'
 

(c) Deleting an abstract data type column

An example of deleting a column of a table containing a user-defined abstract data type is explained as follows:

Example

In this example, the DELETE statement uses the employee..position component specification to delete the data in which the position attribute of the employee column in STAFF_TABLE is GENERAL:

 
DELETE FROM STAFF_TABLE
  WHERE employee..position='general'

(d) Data insertion

An example of insertion of data into a table containing a user-defined abstract data type is explained as follows.

Example

In this example, the INSERT statement uses the t_employee constructor facility to insert a row whose EMPLOYEE_NO column is 990070 into STAFF_TABLE (:xidphoto is a BLOB type embedded variable in which the ID_photo image has been set):

 
INSERT INTO STAFF_TABLE
  VALUES ('990070',t_employee('Mary Moore',
                              'F',
                              'GENERAL',
                              '1999-04-01',
                              :xidphoto AS BLOB,
                              140000
                              ))