Figure 2-47 shows an example of data retrieval with a plug-in. 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 a plug-in (1)

Figure 2-48 shows another example of retrieval with a plug-in. 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 a plug-in (2)

Figure 2-49 shows an example of updating with a plug-in. 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 a plug-in

   

Figure 2-50 shows an example of row deletion with a plug-in. The 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 a plug-in

Figure 2-51 shows an example of row insertion with a plug-in. This example inserts a row for MEDICINE 25 into the medicine management table. 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 a plug-in