Devart Forums

Discussion forums for open issues and questions concerning database tools, data access components and developer tools from Devart
https://forums.devart.com/

Reading and Writing ARRAYS

https://forums.devart.com/viewtopic.php?f=24&t=17182

Page 1 of 2

Reading and Writing ARRAYS

Posted: Mon 22 Feb 2010 17:22
by 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

Posted: Tue 23 Feb 2010 15:05
by 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.

Posted: Tue 23 Feb 2010 15:31
by ravink
Dimon wrote: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

Posted: Wed 24 Feb 2010 11:52
by 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;

Posted: Wed 24 Feb 2010 16:33
by 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

Posted: Thu 25 Feb 2010 08:15
by 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.

Posted: Wed 03 Mar 2010 08:04
by ravink
Dimon wrote: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

Posted: Wed 03 Mar 2010 17:25
by 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;

Posted: Fri 05 Mar 2010 14:58
by Dimon
ravink wrote: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.
ravink wrote: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;

Posted: Fri 05 Mar 2010 15:01
by Dimon
ravink wrote: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.

Posted: Sat 06 Mar 2010 07:29
by 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]
);

Posted: Mon 08 Mar 2010 05:08
by 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;

Posted: Mon 08 Mar 2010 07:14
by 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;

Posted: Tue 09 Mar 2010 02:36
by ravink
Dimon wrote:
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?

Posted: Thu 11 Mar 2010 14:44
by Challenger
Thank you for information. We have reproduced this problem and fixed it. This fix will be included in the next IBDAC build.
All times are UTC
Page 1 of 2
Powered by phpBB® Forum Software © phpBB Limited
https://www.phpbb.com/