Release 10.1A: OpenEdge Data Management:
SQL Reference

> ## Unsubscripted array updates and inserts

> OpenEdge SQL applies the reverse of the fetch algorithm for updates to unsubscripted Progress 4GL array columns. An SQL operation accepts an `NVARCHAR` string, complete with separators and any required escape characters. OpenEdge SQL converts the string to the underlying data type and stores it in the current element of the array in the OpenEdge database. SQL repeats this operation for each element of the array until all elements have been inserted.

> This is the SQL syntax for an unsubscripted array update:

> Syntax
>     UPDATE table_name SET array_name = ('char_element') WHERE where_criteria ;

> Example

> The `ARRAYINT` example assumes an integer array named `ARRAYINT`. The result of the `UPDATE` operation is:

> ``ARRAYINT[ 1 ] = 13`` ``ARRAYINT[ 2 ] = 15`` ``ARRAYINT[ 3 ] = 19``

>     UPDATE customer SET arrayint = '13;15;19'WHERE cust_num = 77 ;

> If the number of elements in the `NVARCHAR` string does not match the number of elements in the target column for the update, OpenEdge SQL returns an error, unless there is exactly one element in the `NVARCHAR` string for an `INSERT` operation.

> If there is a single element in the `NVARCHAR` string, you can use the OpenEdge SQL `INSERT` statement to propagate the value to all elements of the array.

> This is the SQL syntax for this short form of an `INSERT` assignment:

> Syntax
>     INSERT INTO table_name ( arr_col_name ) VALUES ( 'one_value' ) ;

> Example

> The following `INSERT` example illustrates how you can assign values to an entire date array from a single value in the `VALUES` clause of an SQL `INSERT` statement:

>     INSERT INTO customer ( begin_quota_date ) VALUES ( '01/01/03' ) ;

> This `INSERT` example assigns the value '`01/01/03`' to every element of the date array '`begin_quota_date`' column in the customer table.

> Note: OpenEdge SQL does not support the assignment of a single value to an entire array using an SQL `UPDATE` statement.

> ##### Updating a single element of an array

> To update a single element of an array, you must construct a string for the entire array, and assign the string to the array using an SQL `UPDATE` statement.

> This is the SQL syntax for updating a single element of an array:

> Syntax
>     UPDATE table_name SET array_col = literal_string ;

> Examples

> To assign a value to the first element of an array of size three, construct a literal string that concatenates these components:

> - Update value for the first element in the array.

> - Semicolon separator.

> - Values for elements two and three in the array.

> Use `PRO_ARR_ESCAPE` to insert any necessary escape characters into the new value. Use `PRO_ELEMENT` to extract the values for elements two and three from the array. This example assigns the value '`aaa`' to the first element of the arraychar array for customer 99, and retains the existing values for elements two and three:

>     UPDATE customer SET arraychar =
>          PROARR_ESCAPE('aaa')
>          || ';'
>          || PRO_ELEMENT(arraychar,2,3)
>     WHERE cust_num = 99 ;

> To assign a value to the second element of an array of size three, construct a string that concatenates these components:

> - Value of the first element in the array.

> - Semicolon separator.

> - Update value for the second element.

> - Semicolon separator.

> - Value of the third element in the array.

> Use `PRO_ARR_ESCAPE` to insert any necessary escape characters into the new value. Use `PRO_ELEMENT` to extract the first and third elements from the array. This example assigns the value '`bbb`' to the second element of the `arraychar` array for customer 99, and retains the existing values for elements one and three in `arraychar`:

>     UPDATE customer SET arraychar =
>          PRO_ELEMENT(arraychar,1,1)
>          || ';'
>          || PROARR_ESCAPE('bbb')
>          || ';'
>          || PRO_ELEMENT(arraychar,3,3)
>     WHERE cust_num = 99 ;

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095