Some problems appear when you need to use large arrays in dataset. As IBDAC creates one field for each array item great number of TField objects is created. As a result of it the performance decreases. So IBDAC has the limitation and creates fields for first 1000 items. However, you can access all items with TIBCArray object.
If such table is created
CREATE TABLE IBDAC_ARRAYS (
ID INTEGER NOT NULL,
CHAR_ARRAY CHAR(10) [1:5],
INTEGER_ARRAY INTEGER [1:2,1:5],
FLOAT_ARRAY FLOAT [0:8,0:2]
);
If ComplexArrayFields is False you can access array item using TIBCArrayField
Value := TIBCArrayField(IBCQuery1.FieldByName('CHAR_ARRAY')).AsArray.GetItemAsString([5]);
If ComplexArrayFields is True, to access array items you can call FieldByName method. For example
Value := Query.FieldByName('CHAR_ARRAY[1]').AsString;
If ObjectField property is True this code is right
Value := TADTField(Query.FieldByName('INTEGER_ARRAY[1]')).Fields[0].AsInteger;
Using TIBCDataSet.GetArray you can access to array items through TIBCArray object
Value := Query.GetArray('FLOAT_ARRAY').GetItemAsFloat([5, 2]);
It is possible to control the way Array objects are handled while the application fetches records from the database. Arrays can be fetched either with other fields to the application or on demand.This is determined by DeferredArrayRead option in TCustomIBCDataSet component. Setting DeferredArrayRead to False allows to reduce traffic over the network since arrays are only transferred on demand and to use less memory on the client side because returned record sets do not hold contents of the array fields.
Set TCustomIBCDataSet.Options.CacheArrays to False to access array values on server side without caching arrays on the client side. Only requested portions of data are fetched in that case. Setting CacheArrays to False may bring up the following benefits for time-critical applications: reduced traffic over the network since only required data are fetched, less memory is needed on the client side because array data are not cached on client side.
You can use array type for parameters of SQL statements. You need to assign dtIBCArray to TIBCParam.DataType and use TIBCParam.AsArray property to access array items.
For example:
var
IBCSQL: TIBCSQL;
. . .
IBCSQL.SQL.Text := 'insert into IBDAC_ARRAYS (ID, CHAR_ARRAY) Values(:ID, :CHAR_ARRAY)';
IBCSQL.ParamByName('ID').AsInteger := 50;
with IBCSQL.ParamByName('CHAR_ARRAY').AsArray do begin
TableName := 'IBDAC_ARRAYS';
ColumnName := 'CHAR_ARRAY';
DbHandle := IBCSQL.Connection.Handle;
TrHandle := IBCSQL.Transaction.Handle;
GetArrayInfo;
SetItemAsString([1], 'AA');
SetItemAsString([2], 'BB');
SetItemAsString([3], 'CC');
end;
IBCSQL.Execute;