obtaining field types of columns in sqlite database

[sandy771]

Is there a function within sqlite that will allow me to iterate through the fields of an unknown sqlite database and return the field name and field type?

If the answer is TUniMetadata - is there a demo or some decent documentation as the help leaves a little to be desired?

Thanks

[AlexP]

Hello,

You can retrieve the information about table fields by running the PRAGMA table_info(table-name) command, the following example demonstrates retrieving all fields from all tables:

Code: Select all

program Project1;

{$APPTYPE CONSOLE}

uses
  SysUtils, Classes, Uni, SQLiteUniProvider;

var
  UniConnection: TUniConnection;
  UniQuery: TUniQuery;
  TblLst: TStringList;
  i: integer;
begin
  UniConnection := TUniConnection.Create(nil);
  try
    UniConnection.ProviderName := 'SQLite';
    UniConnection.Database := 'd:\temp1.db3';
    UniConnection.Connect;
    TblLst := TStringList.Create;
    try
      UniConnection.GetTableNames(TblLst);
      for i := 0 to TblLst.Count - 1 do
      begin
        UniQuery := TUniQuery.Create(nil);
        try
          UniQuery.Connection := UniConnection;
          UniQuery.SQL.Text := Format('PRAGMA table_info(%s)',[TblLst[i]]);
          UniQuery.Open;
          while not UniQuery.Eof do
          begin
            Writeln(Format('Table name: %s | FieldName: %s | Fied Type: %s',[TblLst[i], UniQuery.FieldByName('name').AsString, UniQuery.FieldByName('type').AsString]));
            UniQuery.Next;
          end;
            Writeln('--------------------------------------------------------------------------');
        finally
          UniQuery.Free;
        end;
      end;
    finally
      TblLst.Free;
    end;
  finally
    UniConnection.Free;
    readln;
  end;

end.

You can also retrieve the information about the fields using the TUniMetaData component as shown below:

Code: Select all

  UniMetaData.MetaDataKind := 'Columns';
  UniMetaData.Restrictions.Values['Table_Name'] := 'Dept';
  UniMetaData.Open;
  While Not UniMetaData.Eof do
  begin
    WriteLn(Format('Table name: %s | FieldName: %s | Fied Type: %s',[UniMetaData.FieldByName('TABLE_NAME').AsString, UniMetaData.FieldByName('COLUMN_NAME').AsString, UniMetaData.FieldByName('DATA_TYPE').AsString]));
    UniMetaData.Next;
  end;