Get Column names with Delphi (Dbexpress)

Question

Hi , I'm using this sql command to get column names :

select COLUMN_NAME from 
INFORMATION_SCHEMA.COLUMNS 
where TABLE_NAME = 'MyTableName'

but i don't know how can i using the executed SQL command results !

for example , this way doesn't work to extract the column names as a string value and i got this error = Operation Not Supported :

  for i := 1 to Qry1.RecordCount do
  begin

  end;

Answer 1

Something like this would work for a TADOQuery (not sure if it's different for dbExpress):

Qry1.Open;
while not Qry1.Eof do begin
    // do whatever with Qry1.Fields[0].AsString here
    Qry1.Next;
end;
Qry1.Close;

Answer 2

From what I understand you are unable to retreive the retults.

Qry1.First;

while not Qry1.Eof do
begin
    X := Qry1.FieldByName('column_name').AsString;

    Qry1.Next;
end;

This is a piece of code which has always worked for me

Or you can read this link which explains why the exception is thrown when calling .RecordCount (http://edn.embarcadero.com/article/28494)

To sum it up it suggests that your query is case-sensitive and you should probably check the table name (MyTableName)

Answer 3

Another way you can do this is to query the table itself to get an empty dataset, and then loop through the fields in that dataset.

A query like this will return the table structure with no records in it:

Qry1.SQL.Text := 'SELECT * FROM MyTableName WHERE 1<>1';
Qry1.Open;

And a loop like this will iterate through each field

for I := 0 to Qry1.FieldCount-1 do
begin
  X := Qry1.Fields[I].FieldName;
  // and do whatever you want with X
end;