Reading and Writing ARRAYS

[ravink]

Hi

I am new to Firebird and am having problems using arrays. I have spent hours searching the forum and looking at the example, but still get errors.

I just need sample code to load and save the arrays.

This is the TABLE :

CREATE TABLE DAILY (
EMP_NO VARCHAR(10),
DYEAR CHAR(4),
DMONTH CHAR(2),
DLOC CHAR(12) [1:31],
HOURS SMALLINT [1:31],
RATE DECIMAL(6,2) [1:31],
MULTI DECIMAL(2,1) [1:31]
);


I need to

1. Load the DLOC, HOURS and RATE into local arrays. (I actually use a stringgrid.)

2. Save the updated local array to the database.

There are 3 data types here; string, integer and float.

An example code or link to a good example will be greatly appreciated.


Thank you.

Regards

Ravi K.
________
Nassau

[Dimon]

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.

[ravink]

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.

As I had stated in my post I have already checked it and it is not of much help. It would have been easier if the code had been commented. There is almost no documentation on this topic.
________
OG KUSH

[Dimon]

The example of a code for arrays processing:

Code: Select all

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[i] := v[i]; 
end; 

[ravink]

The read array is quite easy.

But from the examples I have seen, there are various ways to write the arrays.

What would be the easiest way to write / commit the arrays for this example preferably without parameters or SQL code.
________
Home made vaporizer

[Dimon]

The TDBGrid component usage is the easiest way to update arrays values.
Also please see the btUpdateRecordClick method of Arrays demo in IbDacDemo as a sample of updating using parameters and without using TDBGrid.

[ravink]

The TDBGrid component usage is the easiest way to update arrays values.
Also please see the btUpdateRecordClick method of Arrays demo in IbDacDemo as a sample of updating using parameters and without using TDBGrid.

Your very short answers made it quite obvious that you are very reluctant to give a proper detailed reply and so i left the arrays for a while and went on to do other work. I decided to check and test your reply in more detail and realised that you still did not answer my question.

1. There is some cryptic remark about using dbgrid. I cannot find any references or examples for this.
2. I asked for a solution without using parameters and there is none given. Is there a way do this or is this not possible?
3. There is a big difference between the ADD and the UPDATE example in the Demo. Can the "ADD Method" that does not use parameters be used for update?

Hope I can get some answers to the above.

Regards

Ravi K

[ravink]

I have spent several hours on this and still am not successful. It is extremely frustrating that Devart support cannot supply a few lines of code to help out.

As noted earlier there are good examples of read, but almost no explanation on write. In desperation I decided to just modify the example for the DEMO program. Still I cannot get it working.


I even added an ID field :

CREATE TABLE DAILY (
EMP_NO CHAR(10),
DYEAR CHAR(4),
DMONTH CHAR(2),
DLOC CHAR(12) [1:31],
HOURS SMALLINT [1:31],
RATE DECIMAL(6,2) [1:31],
MULTI DECIMAL(2,1) [1:31],
ID INTEGER NOT NULL
);


Following is the code :

var
Arr: TIBCArray;
Low1, High1, i: integer;
begin
Arr := TIBCArray.Create(IBCSQL.Connection.Handle, IBCSQL.Transaction.Handle,
'DAILY', 'HOURS');
try
Arr.Cached := cbCacheArrays.Checked; //Controls applied changes behaviour.
Arr.GetArrayInfo; //Here we read array dimension and type info
Arr.CreateTemporaryArray;
Low1 := Arr.ArrayLowBound[0];
High1 := Arr.ArrayHighBound[0];
for i := Low1 to High1 do
Arr.SetItemAsInteger(, 100 + i);
IBCSQL.ParamByName('ID').AsInteger := IBCQuery.FieldByName('ID').AsInteger;
IBCSQL.ParamByName('HOURS').AsArray := Arr;
finally
Arr.Free;
end;

Arr := IBCSQL.ParamByName('DLOC').AsArray;
Arr.DbHandle := IBCSQL.Connection.Handle;
Arr.TrHandle := IBCSQL.Transaction.Handle;
Arr.TableName := 'DAILY';
Arr.ColumnName := 'DLOC';
Arr.GetArrayInfo;
Arr.CreateTemporaryArray;
Low1 := Arr.ArrayLowBound[0];
High1 := Arr.ArrayHighBound[0];
for i := Low1 to High1 do
Arr.SetItemAsString(, 'STR'+INTTOSTR(100 + i));
IBCSQL.ParamByName('DLOC').AsArray := Arr;

IBCSQL.Execute;
IBCQuery.RefreshRecord;

[Dimon]

1. There is some cryptic remark about using dbgrid. I cannot find any references or examples for this.

To edit array fields in TDBGrid you should only connect it to IBCQuery via DataSource and open the table. In this case the table should have a primary key. Or you can specify the unique key fields using the KeyFields property.

2. I asked for a solution without using parameters and there is none given. Is there a way do this or is this not possible?

Yes, you can update record without using parameters. This way is developed in the btUpdateSliceClick method of IbDacDemo. In your case you can use the following code:

Code: Select all

  IBCQuery.Edit;
  Arr := IBCQuery.GetArray('HOURS');
  VarArr := Arr.GetItemsSlice([1, 31]);
  for i := 1 to 31 do
      VarArr[i] := 100 + i;
  Arr.SetItemsSlice(VarArr);
  IBCQuery.Post;

[Dimon]

I have spent several hours on this and still am not successful. It is extremely frustrating that Devart support cannot supply a few lines of code to help out.
As noted earlier there are good examples of read, but almost no explanation on write. In desperation I decided to just modify the example for the DEMO program. Still I cannot get it working.

To solve the problem you should use the following code:

Code: Select all

var
  Arr: TIBCArray;
  Low1, High1, i: integer;
begin
  Arr := TIBCArray.Create(IBCSQL.Connection.Handle, IBCSQL.Transaction.Handle,
    'DAILY', 'HOURS');
  try
    Arr.Cached := cbCacheArrays.Checked; //Controls applied changes behaviour.
    Arr.GetArrayInfo; //Here we read array dimension and type info
    Arr.CreateTemporaryArray;
    Low1 := Arr.ArrayLowBound[0];
    High1 := Arr.ArrayHighBound[0];
    for i := Low1 to High1 do
      Arr.SetItemAsSmallInt([i], 30 + i); // NOT SetItemAsInteger

    IBCSQL.ParamByName('ID').AsInteger := IBCQuery.FieldByName('ID').AsInteger;
    IBCSQL.ParamByName('HOURS').AsArray := Arr;
  finally
    Arr.Free;
  end;

  Arr := IBCSQL.ParamByName('DLOC').AsArray;
  Arr.DbHandle := IBCSQL.Connection.Handle;
  Arr.TrHandle := IBCSQL.Transaction.Handle;
  Arr.TableName := 'DAILY';
  Arr.ColumnName := 'DLOC';
  Arr.GetArrayInfo;
  Arr.CreateTemporaryArray;
  Low1 := Arr.ArrayLowBound[0];
  High1 := Arr.ArrayHighBound[0];
  for i := Low1 to High1 do
    Arr.SetItemAsString([i], 'STR'+INTTOSTR(20 + i));

  // don't use this:  IBCSQL.ParamByName('DLOC').AsArray := Arr;

  IBCSQL.Execute;
  IBCQuery.RefreshRecord;

And make sure that the table have a primary key or specify the unique key fields using the KeyFields property.

[ravink]

Hi

Thanks for the detailed answer. In the meantime I managed to use a different code that seems to be working. Unfortunately I just discovered that it is not storing floats properly. I have 3 questions.

Firstly, considering how simple the code is I am worried that I may be doing something wrong. Is the code OK?

Secondly, why is there a problem with the floats. The values are all being truncated to integer values. Is it because of the method I am using or something else.

Thirdly, considering there seems to be so many ways to save arrays, I hope you can give some information on the pro's and con's of the different methods.

Thank you.


var i: Integer;
begin

with DM.DAILY do
begin
edit;
for i := 1 to 15 do
begin
FieldByName('DTOT[' + inttostr(i) + ']').AsVariant := strtofloat(otGrid.cells[2, i]);
{ or FieldByName('DTOT[' + inttostr(i) + ']').AsFloat := strtofloat(otGrid.cells[2, i]); }
end;

Post;
Transaction.CommitRetaining;
RefreshRecord;

CREATE TABLE DAILY (
EMP_NO VARCHAR(10) NOT NULL,
DMONTH CHAR(2) NOT NULL,
DYEAR CHAR(4),
DTYPE CHAR(1) [1:31],
DSITE CHAR(12) [1:31],
DHOURS DECIMAL(3,1) [1:31],
DTOT DECIMAL(6,2) [1:31],
D10 DECIMAL(6,2) [1:31],
D15 DECIMAL(6,2) [1:31],
D20 DECIMAL(6,2) [1:31],
D30 DECIMAL(6,2) [1:31],
DFOOD DECIMAL(6,2) [1:31]
);

[ravink]

Hi

I tested using your code. Float is rounded to integer.

Tried dbgrid also. Dbgrid does not accept decimal point.

Also noticed that the demo does not include an example for float.

Is float supported?


var
rr: double;
Arr: TIBCArray;
VarArr: variant;
i: integer;
begin
rr := 12.5;
with DM.DAILY do
begin
edit;
Arr := GetArray('DHOURS');
VarArr := Arr.GetItemsSlice([1, 31]);
for i := 1 to 31 do
VarArr := rr;

Arr.SetItemsSlice(VarArr);
Post;
Transaction.CommitRetaining;
RefreshRecord;
end;

[ravink]

Hi

Tried this also. Float is stored as "0". The 2nd part with string works, but not the float.


Arr := TIBCArray.Create(dm.IBCSQL.Connection.Handle,dm.IBCSQL.Transaction.Handle,
'DAILY', 'DHOURS');
try
Arr.Cached := true; //cbCacheArrays.Checked; //Controls applied changes behaviour.
Arr.GetArrayInfo; //Here we read array dimension and type info
Arr.CreateTemporaryArray;
Low1 := Arr.ArrayLowBound[0];
High1 := Arr.ArrayHighBound[0];
for i := Low1 to High1 do
Arr.SetItemAsFloat(, 1.5); // NOT SetItemAsInteger

dm.IBCSQL.ParamByName('DHOURS').AsArray := Arr;
dm.IBCSQL.ParamByName('EMP_NO').Asstring := dm.Daily.FieldByName('EMP_NO').AsString;
dm.IBCSQL.ParamByName('DMONTH').Asstring := dm.Daily.FieldByName('DMONTH').AsString;

finally
Arr.Free;
end;

Arr := dm.IBCSQL.ParamByName('DSITE').AsArray;
Arr.DbHandle := dm.IBCSQL.Connection.Handle;
Arr.TrHandle := dm.IBCSQL.Transaction.Handle;
Arr.TableName := 'DAILY';
Arr.ColumnName := 'DSITE';
Arr.GetArrayInfo;
Arr.CreateTemporaryArray;
Low1 := Arr.ArrayLowBound[0];
High1 := Arr.ArrayHighBound[0];
for i := Low1 to High1 do
Arr.SetItemAsString(, 'STR'+INTTOSTR(20 + i));

dm.IBCSQL.Execute;
dm.IBCSQL.Transaction.CommitRetaining;
dm.daily.RefreshRecord;

[ravink]

To solve the problem you should use the following code:

Code: Select all

var
  Arr: TIBCArray;
  Low1, High1, i: integer;
begin
  Arr := TIBCArray.Create(IBCSQL.Connection.Handle, IBCSQL.Transaction.Handle,
    'DAILY', 'HOURS');
  try
    Arr.Cached := cbCacheArrays.Checked; //Controls applied changes behaviour.
    Arr.GetArrayInfo; //Here we read array dimension and type info
    Arr.CreateTemporaryArray;
    Low1 := Arr.ArrayLowBound[0];
    High1 := Arr.ArrayHighBound[0];
    for i := Low1 to High1 do
      Arr.SetItemAsSmallInt([i], 30 + i); // NOT SetItemAsInteger

    IBCSQL.ParamByName('ID').AsInteger := IBCQuery.FieldByName('ID').AsInteger;
    IBCSQL.ParamByName('HOURS').AsArray := Arr;
  finally
    Arr.Free;
  end;

  Arr := IBCSQL.ParamByName('DLOC').AsArray;
  Arr.DbHandle := IBCSQL.Connection.Handle;
  Arr.TrHandle := IBCSQL.Transaction.Handle;
  Arr.TableName := 'DAILY';
  Arr.ColumnName := 'DLOC';
  Arr.GetArrayInfo;
  Arr.CreateTemporaryArray;
  Low1 := Arr.ArrayLowBound[0];
  High1 := Arr.ArrayHighBound[0];
  for i := Low1 to High1 do
    Arr.SetItemAsString([i], 'STR'+INTTOSTR(20 + i));

  // don't use this:  IBCSQL.ParamByName('DLOC').AsArray := Arr;

  IBCSQL.Execute;
  IBCQuery.RefreshRecord;

And make sure that the table have a primary key or specify the unique key fields using the KeyFields property.

for i := Low1 to High1 do
Arr.SetItemAsSmallInt(, 30 + i); // NOT SetItemAsInteger
IBCSQL.ParamByName('HOURS').AsArray := Arr;

VERSUS :

for i := Low1 to High1 do
Arr.SetItemAsString(, 'STR'+INTTOSTR(20 + i));

// don't use this: IBCSQL.ParamByName('DLOC').AsArray := Arr;

Why are these two arrays updated differently. One with ":= Arr" and the other without?

Is there also a difference for floats?

[Challenger]

Thank you for information. We have reproduced this problem and fixed it. This fix will be included in the next IBDAC build.