When an arrayed embedded variable is specified for the INTO clause of a FETCH statement, it becomes possible to obtain multiple rows of retrieval results at the same time. The FETCH facility using arrays can improve retrieval performance when it is used for accessing a HiRDB system from a HiRDB client to retrieve a large volume of data. Unlike the block transfer facility, the FETCH facility using arrays requires the fact that multiple rows of retrieval results will be obtained to be described in a program. The FETCH facility using arrays is valid only when the embedded variables and indicator variables specified in the INTO clause are all arrayed. For details about the FETCH facility using arrays, see the HiRDB Version 8 UAP Development Guide.

The INSERT facility using arrays makes it possible to execute a single SQL INSERT statement to insert multiple rows of data by specifying an array type variable in which the multiple rows of data are set. By using the INSERT facility using arrays, you can greatly reduce the frequency of communications between the HiRDB client and the HiRDB server. If you are using HiRDB/Parallel Server, you can further reduce the frequency of communications among server applications on the HiRDB server as well. Accordingly, this facility works well for cases in which a HiRDB client accesses a HiRDB server to quickly insert a large amount of data.

How to use the INSERT facility using arrays

Static execution

With the INSERT statement, specify an embedded variable in the FOR clause, and change all embedded variables and indicator variables specified in the VALUES clause to be array type variables. Use the embedded variable specified in the FOR clause to control the number of rows that are inserted at one time.

Dynamic execution

Using the following procedure to execute:

1. Using the PREPARE statement, preprocess the INSERT statement (specify the ? parameter for all values in the VALUES clause that are to be inserted).
2. In the USING clause of the EXECUTE statement, use an array to specify the values to be assigned to the ? parameters entered in the preprocessed INSERT statement, and specify the embedded variable in the BY clause. Use the embedded variable specified in the BY clause to control the numbers of rows that are inserted at one time.

For details about the INSERT facility using arrays, see the HiRDB Version 8 UAP Development Guide.

The UPDATE facility using arrays enables you to execute a single SQL UPDATE statement to update multiple table columns by specifying an array-type variable in which multiple data items are set. Because the frequency of communication between the HiRDB client and the HiRDB server is reduced, this facility works well in cases where a HiRDB client accesses a HiRDB server to perform high-speed updating of a large amount of data.

How to use the UPDATE facility using arrays

Static execution

Specify an embedded variable in the FOR clause of the UPDATE statement, and then change all of the embedded variables and indicator variables specified in the search conditions so that they are array-type variables. Use the embedded variable specified in the FOR statement to control the number of data items that are updated at one time.

Dynamic execution

To perform dynamic execution:

1. Use a PREPARE statement to preprocess the UPDATE statement (specify the ? parameter for the update values or in the search condition).
2. In the USING clause of the EXECUTE statement, use an array to specify the values to be assigned to the ? parameter entered in the preprocessed UPDATE statement, and specify an embedded variable in the BY clause. Use the embedded variable specified in the BY clause to control the number of data items that are updated at one time.

For details about the UPDATE facility using arrays, see the HiRDB Version 8 UAP Development Guide.

The DELETE facility using arrays enables you to execute a single SQL DELETE statement to delete multiple rows by specifying an array-type variable in which multiple data items are set. Because the frequency of communication between HiRDB client and HiRDB server is reduced, this facility works well in cases where a HiRDB client accesses a HiRDB server to perform high-speed deletion of a large amount of data.

How to use the DELETE facility using arrays

Static execution

Specify an embedded variable in the FOR clause of the DELETE statement, and then change all of the embedded variables and indicator variables specified in the search conditions so that they are array-type variables. Use the embedded variable specified in the FOR statement to control the number of data items that are deleted at one time.

Dynamic execution

To perform dynamic execution:

1. Use the PREPARE statement to preprocess the DELETE statement (specify the ? parameter in the search conditions).
2. In the USING clause of the EXECUTE statement, use an array to specify the values to be assigned to the ? parameter entered in the preprocessed DELETE statement, and specify the embedded variable in the BY clause. Use the embedded variable specified in the BY clause to control the number of data items that are deleted at one time.

For details about the DELETE facility using arrays, see the HiRDB Version 8 UAP Development Guide.