Array Datatype

[pwatel]

hi gurus
I am playing with array data type to use with time series

A) given I collect a new data point every day for 20,000 series
what is the most efficient way to update just one point in a serie
I know that I can select the array find the point change it and update the whole array with a update query but it is not very fast
any other method??

B) any way to block copy the TIBCArray into a array of double
or the only way is (again not too fast) pseudo code below
for i:= 1 to High(IBCArray)
myarray := TIBCArray.asfloat

C) any documentation except the demo on arrays??

D) I assume also for performance issue (my array is 8000 big)
I have to set complexarrayfield to false

thanks
Philippe watel

[Plash]

a) You cannot update one point in array. You can update an array only as the whole.

b) The most efficient way is to use the Items property to get values as variant array. Then you can copy the variant array to an array of double in a loop.

c) The IBDAC help contains the description for the TIBCArray class.

[pwatel]

a) You cannot update one point in array. You can update an array only as the whole.
OK MAKES SENSE

b) The most efficient way is to use the Items property to get values as variant array. Then you can copy the variant array to an array of double in a loop.
ANY EXAMPLES ???

c) The IBDAC help contains the description for the TIBCArray class.
OF COURSE I LOOKED AT IT BEFORE BOTHERING YOU BUT IT HAS VERY LITTLE EXAMPLES AND DOES NOT SHOW YOU THE OPTIMAL COMBINATION (AND NOWHERE IT SEEMS) OF ALL THOSE SPECIFIC ARRAY OPTIONS delayedwrite , localcache etc
ONLY THE DEMO HAS STUFF BUT NO COMMENTS SO IT IS OK TO COPY LIKE A PARROT BUT DOES NOT MEAN I UNDERSTOOD ALL SUBTLETIES

HOW ABOUT ANSWER TO D) ????

IT WOULD BE NICE TO HAVE FOR YOUR SUPPORT SOME KIND OF CODE REPOSITORY TO SEE WHAT SHOULD BE DONE FOR SPECIFIC STUFF
LIKE ARRAY LIKE BLOB TEXT UNICODE ETC
THANK YOU
pw

[Plash]

c) There is Arrays demo in IbDacDemo which is located in \Demos\[Win32]\IbDacDemo. You can see how to read and update arrays in this demo.

a) I found a way for updating a part of an array. TIBCQuery has the CacheArrays option and TIBCArray has the Cached property. When the CacheArrays option of TIBCQuery is set to False, non-cached TIBCArray objects are created when fetching data.

If the Cached property of TIBCArray object is False, all its methods read and write data directly to the array in the database (for example, you can use SetItemAsFloat or SetItemsSlice methods).

b)
var
d: array of double;
v: variant;
i, LowB, HighB: integer;
begin
v := IBCArray.Items;
LowB := VarArrayLowBound(v, 1);
HighB := VarArrayHighBound(v, 1);

SetLength(d, HighB - LowB + 1);
for i := LowB to HighB do
d := v;
end;

d) I think that performance can be high enough when working with an array with 8000 elements.

[pwatel]

thanks Plash for your answer
but I have some question on this one

a) I found a way for updating a part of an array. TIBCQuery has the CacheArrays option and TIBCArray has the Cached property. When the CacheArrays option of TIBCQuery is set to False, non-cached TIBCArray objects are created when fetching data.

If the Cached property of TIBCArray object is False, all its methods read and write data directly to the array in the database (for example, you can use SetItemAsFloat or SetItemsSlice methods).

1) I guess you would do that in a select something like that???

var
IBCArray : TIBCArray;

ibcqry1.sql := SELECT myarray from mytable where mykey=keyvalue
ibcqry1.options.CacheArrays := false;
ibcqry1.open;
IBCArray := ibcqry1.GetArray('myarray' );
IBCArray.cached:= false;
IBCArray.SetItemAsFloat([46],365.34);
ibcqry1.close;

2) Although it has a different name I do not get the what is the difference between SetItemAswhatever or SetItemsSlice
the slice stuff is beyond my understanding
anywhere a meaningful description??? the help just repeats itself

Class
TCustomIBCArray
Syntax
procedure SetItemsSlice(const Values: variant);
ParametersValuesHolds the array slice values.
Remarks
Call the SetItemsSlice method to set array slice values.

3) when exactly to use GetArrayInfo all the time
4) CreateTemporaryArray when it is best


Most of the times the order of calling matters so to have code snippets available to us would be nice it would save time on both ends.

the demo is cool but lack comments.
regards thanks again
PW

[Plash]

1) You don't need to set Cached = False because it is done automatically when CacheArrays is set to False.

2) Slice is a part of array (subarray). Please see an example of SetItemSlice in IbDacDemo (button 'Update Record (Slice)').

3, 4) If you use SELECT statement, you can get an array from a field and edit it. If you want to use INSERT or UPDATE statement without having an array, you can create a temporary array, fill this array with data, then execute the statement to write the array to the field in the table.

You can see an example in IbDacDemo (button 'Update Record').

To create a temporary array, you should provide the following: connection handle, transaction handle, table and column name. Then call GetArrayInfo to get information about array bounds from the database.

[pwatel]

hi plash
I cannot make your trick to work

var
aDataset : TIBCQuery;

aDataset.Options.DeferredArrayRead := True;
aDataset.Options.CacheArrays := False;
aDataset.Options.ComplexArrayFields := False;
aDataset.SQL.Text := 'SELECT Ts FROM MyTB where mykey=:mykey;
aDataset.ParamByName('mykey').Asstring := KeyValue;
aDataset.Open;
Arr := aDataset.GetArray('TS');
Arr.Cached := False;
Arr.SetItemAsfloat(, avalue);
adataset.close;

when i read again the data it was not saved
did I forget an option
thanks
PW

[Plash]

After you change an item in an arrays, array ID is changed. That's why you should update your table with the new ID. For example:

Code: Select all

var
  Cmd: TIBCSQL;

...
aDataset.Open; 
Arr := aDataset.GetArray('TS'); 
Arr.Cached := False; 
Arr.SetItemAsFloat([i], avalue); 

Cmd.SQL.Text := 'UPDATE MyTB SET Ts = :Ts WHERE MyKey = :MyKey';
Cmd.ParamByName('Ts').AsArray := Arr;
Cmd.ParamByName('MyKey').AsString := KeyValue;
Cmd.Execute;

aDataset.Close;