When a plug-in is used, creation of a UAP by specifying the facility provided by the plug-in makes it possible to quickly and easily manipulate multimedia data such as documents and images.

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.

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
                              ))