Database queries

[iondy]

Just getting back into Delphi after about 4 years away, and have a few probs trying to remember a few bits when working with databases.

1) When I return an SQL statement, giving me a single column of x rows, how can I 'export' that as a string array?

2) As 1), but with more than one colum can I 'export' as an array of arrays?

Thanks folks.

 

[ChrisGSchultz]

This is probably not the most elegant solution, but ....

Declare some data types and variables
[tt]
type
tMyType = record
MyField1,
MyField2 : string;
end;
var
aStringData : array of tMyType; // set up an array for your data
[/tt]

In an appropriate control do this....
[tt]
with ADOQuery do begin // assume using ADO - but should work with TQuery
first; // point to first record
SetLength(aStringData, RecordCount); // adjust length of array
while not eof do begin
disablecontrols; // turn off any displays
// recno is a property of TQuery the current record number
// Field1 is a valid fieldname from the table
aStringData[recno].MyField1 := FieldByName('Field1').Value;
aStringData[recno].MyField2 := FieldByName('Field2').Value;
next; // scroll to next record
enablecontrols;
end; // while not eof
end; // with ADOquery
[/tt]

Hope thats a start.....

Chris ;-)

 

[towerbase]

The reply given by Chris doesn't actually answer your question because it doesn't tell you how to populate an array of arrays.

First you need to define your array of arrays.

type
  TRecord = array of string;
  TData = array of TRecord;

var
  Data: TData;

As Chris recommends, use a dynamic array. Dynamic arrays may be new to you if you haven't used Delphi for 4 years. The advantage of dynamic arrays is that you can set the dimensions at run time using the SetLength procedure. Check out the Delphi Help.

In the following code, I'm assuming that the data is being extracted from a dataset called Q. This allows you to use TQuery, TTable and so on as they are all descended from TDataset.

var
  n: integer;  // number of records in dataset
  f: integer;  // number of fields in each record
begin
  Q.Active := true;
  Q.First;
  SetLength ( Data, Q.RecordCount );
  while not Q.Eof do begin
    n := Q.RecNo;
    SetLength ( Data[n-1], Q.FieldCount );
    for f := 0 to Q.FieldCount - 1 do
      Data [ n-1, f ] := Q.Fields[f].AsString;
    Q.Next;
  end;
end;

Note that we need to set the length of each row in Data. Note also that dynamic arrays are zero based but the first record of a dataset has a RecNo of 1.

Andrew
Hampshire, UK

 

[iondy]

That excellent, thanks folks, I will give them a try this afternoon.