Nonstop Database, HiRDB Version 9 UAP Development Guide
This section describes examples that use the HiRDB Text Search Plug-in. The HiRDB Text Search Plug-in provides the abstract data type functions shown in the following table. For details about the abstract data type functions provided by the plug-in, refer to the individual plug-in manuals.
Table 2-1 Descriptions of abstract data type functions provided by the HiRDB Text Search Plug-in
Function name | Description |
---|---|
SGML TEXT | SGML text registration |
Contains | Structure specification retrieval |
contains_with_score, score | Score retrieval |
This section also explains examples that use SGML text to manage an operation manual for medicines. The examples use tables that were defined in the database creation section (for tables that include abstract data types provided by the plug-in) of the HiRDB Version 9 Installation and Design Guide.
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)
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
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
Figure 2-51 Example of insertion with the SGMLTEXT type
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.