Datatype of Fields in sqlite Database Table (SOLVED)

[Dzandaa]

Hi everybody,

Is it a way to retrieve the Datatype of Fields in a Sqlite Database in a TStringList?

Like
PRAGMA table_info('table_name')

or

SELECT sql FROM sqlite_master WHERE name = 'employees' (that works, but it change my TDBGrid and I want to just have the datatype).

Thank you

B->

[cdbc]

Hi
Hmmm, in sqlite3 program, one can use '.schema' to see the structure of the tables within...
I'm wondering if it is possible to do that via your

Code: Pascal  [Select][+][-]

1. SELECT schema FROM sqlite_master WHERE name = 'employees';

Just a thought
Regards Benny

[Hansvb]

Hi,

This code gets the fieldnames and type of a SQLite table.

Code: Pascal  [Select][+][-]

1. unit Unit1;
2.  
3. {$mode objfpc}{$H+}
4.  
5. interface
6.  
7. uses
8.   Classes, SysUtils, SQLite3Conn, Forms, Controls, Graphics, Dialogs, StdCtrls,
9.   SQLDB;
10.  
11. type
12.  
13.   { TForm1 }
14.  
15.   TForm1 = class(TForm)
16.     Button1: TButton;
17.     Memo1: TMemo;
18.     SQLite3Connection1: TSQLite3Connection;
19.     SQLQuery1: TSQLQuery;
20.     procedure Button1Click(Sender: TObject);
21.   private
22.     procedure GetTableFieldsInfo(const DatabaseName, TableName: string);
23.   public
24.  
25.   end;
26.  
27. var
28.   Form1: TForm1;
29.  
30. implementation
31.  
32. {$R *.lfm}
33.  
34. { TForm1 }
35.  
36. procedure TForm1.Button1Click(Sender: TObject);
37. begin
38.   GetTableFieldsInfo('C:\Users\Hans\AppData\Roaming\Test\AppDB.db', 'MAATREGEL');
39. end;
40.  
41. procedure TForm1.GetTableFieldsInfo(const DatabaseName, TableName: string);
42. var
43.   Conn: TSQLite3Connection;
44.   Trans: TSQLTransaction;
45.   Query: TSQLQuery;
46. begin
47.   Conn:= TSQLite3Connection.Create(nil);
48.   Trans:= TSQLTransaction.Create(nil);
49.   Query:= TSQLQuery.Create(nil);
50.   try
51.     Conn.DatabaseName:= DatabaseName;
52.     Conn.Transaction:= Trans;
53.     Query.DataBase:= Conn;
54.  
55.     Conn.Open;
56.     Trans.StartTransaction;
57.  
58.     Query.SQL.Text:= 'PRAGMA table_info("' + TableName + '")';
59.     Query.Open;
60.  
61.     while not Query.EOF do
62.     begin
63.       // send fieldtypes to the memo
64.       Memo1.Lines.Add('Field: ' + Query.FieldByName('name').AsString +
65.                   ' - Type: ' + Query.FieldByName('type').AsString);
66.       Query.Next;
67.     end;
68.  
69.     Query.Close;
70.     Trans.Commit;
71.   finally
72.     Query.Free;
73.     Trans.Free;
74.     Conn.Free;
75.   end;
76. end;
77.  
78. end.
79.  

Edit, i Know, i should a parameter, but this is quick and dirty.

[Zvoni]

I'm wondering if it is possible to do that via your

Code: Pascal  [Select][+][-]

1. SELECT schema FROM sqlite_master WHERE name = 'employees';

Just a thought
Regards Benny

No, not possible. --> "No such Column: schema"

In general: Best bet are the 2 PRAGMA's
table_info
and
table_xinfo (This one returns more info)

It returns what you need.
the "SELECT sql FROM sqlite_master"-approach involves parsing the returned CREATE TABLE-Statement.

Edit, i Know, i should a parameter, but this is quick and dirty.

And no: You can't use parameters in a PRAGMA

[Dzandaa]

Hi everybody,

Thank you for the suggestions.

I's working now.

B->