Using Progress 4GL/SQL-89 ARRAY fields with SQL-92 drivers

Quoted from Progress On-Line documentation

C.2 Support for the ARRAY Data Type

For compatibility with earlier Progress databases, Progress SQL–92 provides limited support for the ARRAY data type.

C.2.1 Overview

Array fields are created using the Progress 4GL Dictionary. Progress SQL–92 provides a mechanism for referencing and updating these arrays. Subscripted references are not supported. Progress SQL–92 manages the arrays as NVARCHAR strings, and the string representation is the concatenation of each array element, with a semicolon ( ; ) separating each element.

To escape an instance of a semicolon in the data of a Progress CHARACTER array, use the escape character tilde before the semicolon ( ~; ). An unquoted question mark represents a NULL element. To distinguish a NULL element from a question mark, use a tilde as an escape character for a question mark in the data ( ~? ). A tilde is also the escape character for a tilde ( ~~ ).

Progress SQL–92 supplies three built–in functions for extracting an element, and for adding escape characters to and removing escape characters from a single element of a character array. The PRO_ELEMENT, PRO_ARR_ESCAPE, and PRO_ARR_DESCAPE functions provide full internationalization support. A description of each function follows.

C.2.2 PRO_ELEMENT Function

Extracts one or more elements from an array column and returns the NVARCHAR or VARCHAR string between the specified positions, including any internal separator characters and any internal escape characters.

SYNTAX

PRO_ELEMENT ( 'array_style_expression', start_position, end_position ) ;

array_style_expression

A string of datatype VARCHAR or CHAR, with a semi-colon ( ; ) separating each element of the array.

start_position

The position in the string marking the beginning of the element PRO_ELEMENT is to extract.

end_position

The position in the string marking the end of the element to be extracted.

EXAMPLES

The following example returns the string 'bb':

PRO_ELEMENT('aa;bb;cc', 2, 2) ;

The next example returns the string 'aa;bb':

PRO_ELEMENT('aa;bb;cc', 1, 2) ;

The third example returns the string 'aa~;aa':

PRO_ELEMENT('aa~;aa;bb;cc', 1, 1) ;

NOTES

• The array_style_expression must be data type NVARCHAR, VARCHAR, or CHAR.

• The returned string does not include the leading separator of the first element, or the trailing separator ( ; ) of the last element.

• Even if you are extracting only one element, the escape characters are included in the result.

• You must invoke PRO_ARR_DESCAPE to remove any escape characters.

C.2.3 PRO_ARR_ESCAPE Function

Adds required escape characters to a single element of a character array.

PRO_ARR_ESCAPE scans the char_element looking for the separator character ( ; ) or an escape character ( ~ ). The function inserts an additional escape character, when it finds any of these constructs:

• Escape character followed by a separator character ( ~; )

• Escape character followed by another escape character ( ~~ )

• Escape character followed by a NULL terminator ( ~\0 )

SYNTAX

PRO_ARR_ESCAPE( 'char_element' ) ;

char_element

The character representation of an array element, without any leading or trailing separators. Must be data type NVARCHAR or VARCHAR or CHAR.

EXAMPLES

The following example returns the string 'aa~;aa':

PRO_ARR_ESCAPE('aa;aa') ;

The following example returns the string 'aa~aa'. There is no change, since another special character does not follow the escape character:

PRO_ARR_ESCAPE('aa~aa') ;

The third example returns the string 'aa~~;aa':

PRO_ARR_ESCAPE('aa~;aa') ;

NOTES

• char_element must be data type NVARCHAR, VARCHAR, or CHAR.

• char_element must not be the name of an array column, since the column contains true separators which would be destroyed by this function.

C.2.4 PRO_ARR_DESCAPE Function

Removes escape characters from a single element of a character array. PRO_ARR_DESCAPE scans the char_element looking for the separator character ( ; ) or an escape character ( ~ ). The function removes an escape character, when it finds any of these constructs:

• Escape character followed by a separator character ( ~; )

• Escape character followed by another escape character ( ~~ )

• Escape character followed by a NULL terminator ( ~\0 )

  SYNTAX

  PRO_ARR_DESCAPE( 'char_element' ) ;

char_element

The character representation of an array element, without any leading or trailing separators. Must be data type NVARCHAR or VARCHAR or CHAR.

EXAMPLES

The following example returns the string 'aa;aa':

PRO_ARR_DESCAPE('aa~;aa') ;

The following example returns the string 'aa~aa'. There is no change, since another special character does not follow the escape character:

PRO_ARR_DESCAPE('aa~aa') ;

The third example returns the string 'aa~;aa':

PRO_ARR_DESCAPE('aa~~;aa') ;

NOTE: char_element should not be the name of an array column, since the column contains true separators which would be destroyed by this function.

C.2.5 Unsubscripted Array References

When there is a reference to an unsubscripted Progress array column, Progress SQL–92 performs these operations:

• Extracts each element from the Progress 4GL array

• Converts the element to a DT_VARCHAR data type

• Passes the element to the PRO_ARR_ESCAPE function for the addition of any necessary escape characters

• Concatenates the result to a composite with the separator character ( ; ) between this element and the next element

This results in dual data types for array fields. The fetch type is TPE_DT_NVARCHAR for the unsubscripted references. The underlying Progress 4GL data type is the native type.

EXAMPLES

The first example assumes a character array named ARRAYCHAR containing three elements in a row in the customer table where the cust_num column is equal to 88, and with values:

ARRAYCHAR[ 1 ] = 'aa'

ARRAYCHAR[ 2 ] = 'bb'

ARRAYCHAR[ 3 ] = 'cc'

The ARRAYCHAR example returns the value 'aa;bb;cc'.

SELECT arraychar FROM customer WHERE cust_num = 88 ; ARRAYCHAR ––––––––– aa;bb;cc 1 record selected

To retrieve an individual element from an array with any escape characters removed, use the PRO_ELEMENT and PRO_ARR_DESCAPE functions. For example, ARRAYTYPE[ 2 ] contains the value 'aa;bb'. The PRO_ELEMENT function in the ARRAYTYPE example returns 'aa~;bb' and PRO_ARR_DESCAPE('aa~;bb') removes the escape character ( ~ ), returning the element value 'aa;bb'.

select pro_arr_descape(pro_element(arraytype,2,2)) from customer ; ARRAYTYPE ––––––––– aa;bb 1 record selected

C.2.6 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:

• 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 ;