(a) Example retrieval with the SGMLTEXT type (1)

Figure 2-47 shows an example of data retrieval with the SGMLTEXT type. This example searches for medicines that are indicated for relief of headaches. The SQL statement for retrieving the data can be specified as follows:

SELECT MEDICINE_ID FROM MEDICINE_MGMT_TABLE
  WHERE contains (OPERATION_MANUAL, 'attached text data
[indications {"headaches"}]')
  IS TRUE

This example uses the contains abstract data type function to retrieve medicines that include the character string headaches in the indications structure section of the OPERATION_MANUAL column.

Figure 2-47 Example of retrieval with the SGMLTEXT type (1)

(b) Example retrieval with the SGMLTEXT type (2)

Figure 2-48 shows another example of retrieval with the SGMLTEXT type. This example retrieves the medicine ID and inventory quantity of medicines that are indicated for food poisoning. The SQL statement for retrieving the data can be specified as follows:

SELECT MEDICINE_MGMT_TABLE.MEDICINE_ID,SQUANTITY
  FROM MEDICINE_MGMT_TABLE LEFT OUTER JOIN STOCK
    ON MEDICINE_MGMT_TABLE.MEDICINE_ID=STOCK.MEDICINE_ID
    WHERE contains (OPERATION_MANUAL, 'attached text data
[indications {"food poisoning"}]')
    IS TRUE

In this example, a medicine management table and a stock table are outer joined and searched. The example uses the contains abstract data type function to retrieve medicine IDs that include the character string food poisoning in the indications structure section of the OPERATION_MANUAL column and find out the stock quantity for those medicine IDs.

Figure 2-48 Example of retrieval with the SGMLTEXT type (2)

Figure 2-49 shows an example of updating with the SGMLTEXT type. This example updates the operation manual for MEDICINE 2. The SQL statement for updating the table can be specified as follows:

UPDATE MEDICINE_MGMT_TABLE SET OPERATION_MANUAL = SGMLTEXT(:sgml AS BLOB(1M))
WHERE MEDICINE_ID = 'MEDICINE 2'

This example uses the SGMLTEXT abstract data type function to update the operation manual data for MEDICINE 2.

The sgml BLOB-type embedded variable must be defined beforehand in front of 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

1. Define the sgml BLOB-type embedded variable.
2. Store the new update data in the sgml embedded variable.
3. Set the sgml_length attribute value for the BLOB data that was created to the length of the stored data.
   

   Figure 2-49 Example of updating with the SGMLTEXT type

   

Figure 2-50 shows an example of row deletion with the SGMLTEXT type. This example deletes the row for MEDICINE 2. The SQL statement for deleting the row can be specified as follows:

DELETE FROM MEDICINE_MGMT_TABLE
  WHERE MEDICINE_ID = 'MEDICINE 2'

This examples deletes the row for MEDICINE 2 from the medicine management table.

Figure 2-50 Example of deletion with the SGMLTEXT type

Figure 2-51 shows an example of row insertion with the SGMLTEXT type. This example inserts a row for MEDICINE 25. The SQL statement for inserting the row can be specified as follows:

INSERT INTO MEDICINE_MGMT_TABLE(MEDICINE_ID,OPERATION_MANUAL)
  VALUES(MEDICINE 25,SGMLTEXT(:sgml AS BLOB(1M)))

This example uses the SGMLTEXT abstract data type function to add a row for MEDICINE 25 to the medicine management table.

The sgml BLOB-type embedded variable must be defined beforehand in front of the INSERT 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

1. Define the sgml BLOB-type embedded variable.
2. Store the insertion data in the sgml embedded variable.
3. Set the sgml_length attribute value for the BLOB data that was created to the length of the stored data.
   

   Figure 2-51 Example of insertion with the SGMLTEXT type