Get text [] from sql table using FireDAC in Delphi

Question

In pgAdmin with a simple query “select * from data” I received only one record containing field type text [] with the value "{" 1 "," 2 "," 3 "}. The following simplified code returns only the" 1 "part of the value :

function GetData: string; var q: TFDQuery; s: string; i: integer; av: array of variant; as: array of string; begin result:=''; q:=TFDQuery.Create(nil); try q.Connection:=FDConnection; q.SQL.Text:='select * from data'; try q.Open; while not q.Eof do begin //s:=q.FieldByName('data_array').AsString; //s = '1' //as:=q.FieldByName('data_array').AsVariant; //as length = 1; as[0] = '1' av:=q.FieldByName('data_array').AsVariant; for i:=0 to Length(av)-1 do s:=s+av[i]; //av length = 1; s = '1' q.Next; end; result:=q.RecordCount; except result:=-2; end; finally q.Free; sl.Free; end; end; 

What is the way to get all the data?

Answer 1

Although the Embarcadero documentation says you should use TArrayField casting (it works for Interbase):

 procedure TFrmMain.Button1Click(Sender: TObject); var F: TArrayField; V: Variant; begin F := TArrayField(q.FieldByName('data_array')); V := F.FieldValues[0]; ShowMessage(VarToStr(V)); end; 

PostgreSQL doesn't seem to work correctly (at least with C ++ builder XE6 I only get the first element of the array). Firedac treats the fields of the PostgreSQL array as nested data sets, so if the above does not work for you, in C ++ you can use the PG array as a regular DataSet, access the elements by moving the cursor, for example:

 TDataSetField * TT = (TDataSetField*)q->FieldByName("data_array"); TT->NestedDataSet->RecNo=2; // or while(!q->NestedDataSet->eof) etc. ShowMessage(TT->NestedDataSet->Fields->Fields[0]->AsString); 

which translates to delphi in your case will look (maybe with an error):

 ... var TT: TDataSetField; ... begin TT:= TDataSetField(q.FieldByName('data_array')); while not TT.NestedDataSet.Eof do begin s:= s+ TT.NestedDataSet.Fields.Fields[0].AsString; //0 - only single dimension supported TT.NestedDataSet.Next; end; end; 

Yours faithfully