Page 1 of 1
obtaining field types of columns in sqlite database
Posted: Tue 06 Nov 2012 12:12
by 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
Re: obtaining field types of columns in sqlite database
Posted: Wed 07 Nov 2012 09:28
by 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;