Progress
SQL-92
Guide and Reference


Unsubscripted Array Updates and Inserts

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

This is the SQL-92 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, Progress SQL-92 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 Progress SQL-92 INSERT statement to propagate the value to all elements of the array.

This is the SQL-92 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-92 INSERT statement:

INSERT INTO customer ( begin_quota_date ) VALUES ( ’01/01/00’ ) ; 

This INSERT example assigns the value ’01/01/00’ to every element of the date array ’begin_quota_date’ column in the customer table.

NOTE: Progress SQL-92 does not support the assignment of a single value to an entire array using an SQL-92 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-92 UPDATE statement.

This is the SQL-92 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:

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:

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 © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095