Get name of table that TField is from

Question

I'm using a TDataSet where the CommandText property is set to an SQL query. I have also made the following function which creates part of an SQL query based on the fields of TDataSet. It is however incomplete. As you can see I still need to get the name of the table that a TField is from. How do I achieve this?

function GetDataSetFieldsMSSQL(Dataset: TDataSet): String;
var
  I, L: Integer;
  TableName: String;
begin
  Result := '';
  L := Dataset.Fields.Count;
  if (L > 0) then
  begin
    TableName := ... // Name of the table for the Dataset.Fields[0] field.
    Result := '[' + TableName + '].[' + Dataset.Fields[0].FieldName + ']';
    I := 1;
    while (I < L) do
    begin
      TableName := ... // Name of the table for the Dataset.Fields[I] field.
      Result := Result + ',[' + TableName + '].[' + Dataset.Fields[I].FieldName + ']';
      Inc(I);
    end;
  end;
end;

Interesting question. the only thing I can think of is `TField.Origin`, but It's not working for me (maybe it works only for BDE)... what TDataSet desendant are you working with? — kobik, Jun 19 '12 at 12:06
...for a `TADODataSet` with MSSQL you can use (that works for me) `ADODataSet1.Recordset.Fields[i].Properties['BASETABLENAME'].Value` and `ADODataSet1.Recordset.Fields[i].Properties['BASECOLUMNNAME'].Value` — kobik, Jun 19 '12 at 12:13
Thank you for your comments. I am using both a TADODataSet and MSSQL, so your solution will work for me as well. — Petter, Jun 19 '12 at 13:37
Maybe there is no solution at all for a simple TDataSet? It is after all the purpose of TDataSet to effectively hide the underlying datastructure. — Petter, Jun 19 '12 at 13:44
14 years of Delphi programming and I never cared about how to get the table which a field is linked ...... — Fabricio Araujo, Jun 19 '12 at 21:12

score 3 · Answer 1 · edited Apr 07 '14 at 20:02

3

You can use the Delphi Function GetTableNameFromQuery(SQL : String):String; from the DBCommon unit. Just Add The DBCommon on the uses. =)

edited Apr 07 '14 at 20:02

user2009750

3,169
5
35
58

answered Apr 07 '14 at 19:41

Elder

31
2

score 2 · Accepted Answer · edited May 23 '17 at 10:28

Maybe there is no solution at all for a simple TDataSet?

I believe not. Because an TDataset can source its' data not only from RDBMS' tables. It can be:

an RSS feed
An XML file. Example: TCliendataset is an TDataset descendant that can read XML from its' own format or using an XMLTransformProvider.
It can be an SQL for reading an Excel spreadsheet or a text file if you have an ODBC driver for that and configured the datasource.
Sky (and the imagination of Delphi's programmers around the world) is the limit for what a field can represent in an TDataset.

You have some alternatives, since you are using an ADODataset:

Parsing the commandText of ADOCommand
Using the BASETABLENAME property of ADORecordSet (as in kobik's comment)
Guessing by convention ( Abelisto's answer )

score 1 · Answer 3 · answered Jun 19 '12 at 12:01

1

As I know there is no any way to get the name of the table from the SQL query component. However you can give aliases for fields, for example: "select foo_field as foo_dot_foo_field from foo" and then replace them to the correct syntax: "Result := '[' + StringReplace(DataSet.Fields[0].FieldName, 'dot', '].[', [rfReplaceAll]) + ']'"

answered Jun 19 '12 at 12:01

Abelisto

14,826
2
33
41

I am only looking to use TDataSet to figure out the table and field names. Altering the SQL would in this case defeat the purpose of my function. – Petter Jun 19 '12 at 13:46

score 1 · Answer 4 · answered Jun 19 '12 at 20:37

What you are trying to do is impossible if you have no knowledge or control over the SQL used in the query. The query could contain calculated/computed fields or could be returning fields from a view etc. Furthermore the database might have several tables that contain the same field names.

If possible you can query the SQL server view INFORMATION_SCHEMA.COLUMNS and that way try to figure out what table a fieldname is from. However if the field names are not unique this might also prove impossible.

Get name of table that TField is from

4 Answers4