Table of Contents
[new in v8.0]
Valentina DB array fields define a table column as a FIXED-length 1-dimension array, according to SQL Standard. An array can be of any built-in base type. Internally, Array fields use storage very similar to the VARIANT field in Valentina DB.
TODO: Allow arrays of enums, user-defined types, domains, composite types.
Declaration of ARRAY Field
The field is represented at the API level of a Valentina ADK by VArray Class.
dim fldA as VArray fldA = tbl.CreateArrayField( "fldArray", EVFieldType.kLongType, 10 )
At SQL level you can create this field using
-- SQL Standard: CREATE TABLE T1( f1 Long ARRAY );
-- Not SQL Standard: skip ARRAY keyword CREATE TABLE T1( f1 Long );
Array Value Input
VArray.Value allows you to assign the whole array from your programming language into a field then add a record.
tbl.SetBlank() fldA = tbl.ArrayField( "fldArray" ) fld.value = Array(1, 2, 3) // syntax of Xojo, other language should use own syntax to specify array. tbl.AddRecord()
To insert a record with Array value, you should use SQL Standard syntax:
INSERT INTO T1(f1, f2) VALUES( 23, ARRAY[1,2,3,4,NULL,5+1,7*2,8,9,10] )
And of course you can use SQL Binding in both SqlExecute()/SqlSelect() and VSqlStatement. Read those sections for details.
Now we can read from Table.
In API way, you again use VArray.Value, but now to read the whole array from the Array Field of the current record.
After that, you can use your programming language to work with Array items.
In SQL you can access a single array element using operator 
SELECT * FROM T WHERE fa = 545
An array subscript expression will return null if either the array itself or any of the subscript expressions is null. Also, null is returned if a subscript is outside the array bounds (this case does not raise any error).
An array value can be replaced completely:
tbl.RecID = 545 // go to a record with RecID = 545 fldA = tbl.ArrayField( "fldArray" ) fld.value = Array(3, 2, 1) tbl.UpdateRecord()
UPDATE T SET fldA = ARRAY(3,2,1) WHERE fldID = 545
or in SQL you can change only one element:
UPDATE T SET fldA = 1 WHERE fldID = 545
Notice, that although in SQL it looks as a touch of a single element, the engine will read and uncompress the whole array at the background, then change one element, then compress and write the whole array again.
Searching in Arrays
You can search for a single element of ARRAY or even compare two elements:
SELECT * FROM T1 WHERE fldA = 1000
SELECT * FROM T1 WHERE fldA < fld
SELECT * FROM T1 WHERE fldA + fldA < fld
This search happens as a column scan in the ValentinaDB at v8.0.
You can also search for specific values in an array using the ARRAY_POSITION() and ARRAY_POSITIONS functions. The former returns the subscript of the first occurrence of a value in an array; the latter returns an array with the subscripts of all occurrences of the value in the array. For example:
SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon'); => 2
Array Input and Output Syntax
The external text representation of an array value consists of items that are interpreted according to the I/O conversion rules for the array's element type, plus decoration that indicates the array structure.
The decoration consists of square brackets [ and ] around the array value plus delimiter character between items. The delimiter character is a comma “,”.
In the result, you get a string, which you can pass back into SQL.
For example, let you have ARRAYs with values as shown here:
INSERT INTO T1(fldA) VALUES( ARRAY[1,2,3] ); INSERT INTO T2(fldB) VALUES( ARRAY['aaa',NULL,'ccccc'] );
When you will output them into text format, you will get
- [1,2,3] - for the first array.
- ['aaa',NULL,'ccccc'] - for the second.
You may notice that individual element values are written the same way they would be written when not members of an array.