IBDAC

ARRAY Data Type

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;

See Also

© 1997-2022 Devart. All Rights Reserved. Request Support DAC Forum Provide Feedback