Switch to: V10V9V8V7V6V5

Field Type - ARRAY (DRAFT !!!)

Column of this type is able contain ARRAY of values of any basic numeric type supported by Valentina (bool, byte, short, ushort, medium, umedium, long, ulong, llong, ullong, float, double, date, time, datetime ).

Creation of Field ARRAY

To create in a table field of ARRAY type you need specify:

  • Name
  • Item Type
  • Storage Type
  • Optional - Max Size. If Size is not specified and storage type is:
    • VarBinary then is used max size to get 4K total
    • FixedBinary then is used size = 20.
  • Optional - Flags fNullable, fIndexed and fKeepSorted.

API:

tbl.CreateArray( 
       "fldArray",        // filed name
        kTypeShort,       // item type
        kTypeVarBinary,   // storage type   
        2044,             // max number of items
        fNullable + fIndexed + fSorted
);

SQL:

CREATE TABLE T(
       fldSet ARRAY OF SHORT[20] ON VAR BINARY NULLS INDEXED SORTED
)

NOTE: VarBinary can have 4K (4096 bytes) data page maximal. 8 bytes Header, so we have 4088 bytes. Since above example want elements type to be short (2 bytes) then maximal size of this array can be 4088 / 2 = 2044 items. If you will specify bigger size then Valentina will set it back to maximal possible and will write warning into log.

Managain Records

Adding a New Record with ARRAY

You can append one or more items into ARRAY using the folowing API and SQL commands.

API:

tbl.SetBlank(); // STEP 1: clears record buffer
 
    // STEP 2: assign field values 
    fldName = "Peter";
    fldArray.Append( v ); // appends one value to empty array
 
tbl.AddRecord(); // STEP 3: adds a new table record

NOTE: Also can be used such functions as:

    // appends few values from a given array 
    fldArray.Append( ArrayOfValues ); 
 
    // appends few values from a given cursor field
    fldArray.Append( cursor, [cursorFieldName] );

SQL:

INSERT INTO T(f1, f2, fldArray) VALUES( 1, 'abc', 4 );
INSERT INTO T(f1, f2, fldArray) VALUES( 1, 'abc', {4} );    
INSERT INTO T(f1, f2, fldArray) VALUES( 1, 'abc', {4,77,13,8} );
INSERT INTO T(f1, f2, fldArray) VALUES( 1, 'abc', (SELECT b4 FROM T2 WHERE b2>100) );

Deleting Existed Record(s) with ARRAY

Here nothing special, we just delete record of table as we do this usually. ARRAY is deleted also as part of record.

API:

VTable.DeleteRecord();

SQL:

DELETE FROM T WHERE ...

Managain ARRAY Items

Adding Items into ARRAY

You can append one or more items into ARRAY using the folowing API and SQL commands.

API:

VFldArray.Append( v );
VFldArray.Append( ArrayOfValues );
VFldArray.Append( cursor, [cursorFieldName] );

SQL:

INSERT INTO T(f1, f2, fldArray) VALUES( 1, 'abc', 4 );
INSERT INTO T(f1, f2, fldArray) VALUES( 1, 'abc', {4} );    
INSERT INTO T(f1, f2, fldArray) VALUES( 1, 'abc', {4,77,13,77,8} );
INSERT INTO T(f1, f2, fldArray) VALUES( 1, 'abc', (SELECT b4 FROM T2 WHERE b2>100) );

Removing Items from ARRAY

You can remove one or more items from existed ARRAY of existed record using the folowing API and SQL commands.

API:

fldArray.Remove( v );
fldArray.Remove( ArrayOfValues );
fldArray.Remove( cursor, [cursorFieldName] );

To remove items from SET with help of SQL but not remove SET itself we need use UPDATE command in combination with remove() function:

SQL:

UPDATE T 
SET f1 = 1, f2 = 'abc', fldArray = remove(fldArray, 4) 
WHERE recid = 55;
 
UPDATE T 
SET f1 = 1, f2 = 'abc', fldArray = remove(fldArray, 4, 77, 13, 8) 
WHERE recid = 55;
 
UPDATE T 
SET f1 = 1, 
    f2 = 'abc', 
    fldArray = remove(fldArray, (SELECT b4 FROM T2 WHERE b2>100)) 
WHERE recid = 55;

Searching Items in ARRAY

You can do search on ARRAY fields using API and SQL ways, as always.

API:

SQL:

SELECT ...
FROM T
WHERE fldArray[2] < fldArray[3]
SELECT ...
FROM T
WHERE FirstItem(fldArray) < LastItem(fldArray)
SELECT ...
FROM T
WHERE fldArray.FirstItem() < fldArray.LastItem()