Firebird Create Table (SOLVED)

[TheLastCayen]

Hi

I am using:
 - Lazarus 2.0.6
 - FPC 3.0.4


How do you create a table in your code using firebird? The wiki recommends using the tool from lazarus but I want my code to do it.

This is my code so far:

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

1. unit Unit1;
2.  
3. {$mode objfpc}{$H+}
4.  
5. interface
6.  
7. uses
8.   Classes, SysUtils, IBConnection, sqldb, db, Forms, Controls, Graphics,
9.   Dialogs, StdCtrls;
10.  
11. type
12.  
13.   { TForm1 }
14.  
15.   TForm1 = class(TForm)
16.     Button1: TButton;
17.     Button2: TButton;
18.     procedure Button1Click(Sender: TObject);
19.     procedure Button2Click(Sender: TObject);
20.     procedure FormCreate(Sender: TObject);
21.     procedure FormDestroy(Sender: TObject);
22.     procedure CreateDB;
23.   private
24.  
25.   public
26.  
27.   end;
28.  
29. var
30.   Form1: TForm1;
31.  
32. implementation
33.  
34. {$R *.lfm}
35.  
36. { TForm1 }
37.  
38. var
39.   DBConnection:   TIBConnection;
40.   SQLTransaction: TSQLTransaction;
41.   SQLQuery:       TSQLQuery;
42.   DataSource:     TDataSource;
43.  
44.  
45. procedure TForm1.Button1Click(Sender: TObject);
46. begin
47.   CreateDB;
48. end;
49.  
50. procedure TForm1.Button2Click(Sender: TObject);
51. begin
52.   if FileExists(DBConnection.DatabaseName) then
53.     DeleteFile(DBConnection.DatabaseName);
54. end;
55.  
56. procedure TForm1.FormCreate(Sender: TObject);
57. begin
58.   DBConnection   := TIBConnection.Create(nil);
59.   SQLTransaction := TSQLTransaction.Create(nil);
60.   SQLQuery       := TSQLQuery.Create(nil);
61.   DataSource     := TDataSource.Create(nil);
62.  
63.   With  DBConnection do
64.     begin
65.       HostName:='';
66.       DatabaseName:='test1.fdb';
67.       UserName:='SYSDBA';
68.       Password:='apple';
69.       Charset := 'UTF8';
70.       Dialect := 3;
71.       Transaction := SQLTransaction;
72.     end;
73.  
74.   SQLQuery.DataBase := DBConnection;
75.   DataSource.DataSet := SQLQuery;
76.  
77. end;
78.  
79. procedure TForm1.FormDestroy(Sender: TObject);
80. begin
81.   DataSource.free;
82.   SQLQuery.free;
83.   SQLTransaction.free;
84.   DBConnection.free;
85. end;
86.  
87. procedure TForm1.CreateDB;
88. begin
89.   DBConnection.CreateDB;
90.   SQLQuery.Close;
91.   SQLQuery.SQL.Text :='CREATE TABLE TEST(ID INTEGER NOT NULL, TESTNAME VARCHAR(800))';
92.   DBConnection.Connected := True;
93.   SQLTransaction.Active := True;
94.   SQLQuery.Open;
95. end;
96.  
97. end.        
98.  

The procedure CreateDB returns an error because my statement is not a select. So far I only find partial answers on internet or people suggest using a tool to create your table... Does anyone know how to do it from the code?

[JanRoza]

Try SQLQuery.ExecSQL instead of SQLQuery.Open.

[TheLastCayen]

Thank you JanRoza,

SQLQuery.ExecSQL; remove the error message but I also had to add: SQLTransaction.Commit;

[TheLastCayen]

For future reference. This is my code to create a table with auto-indentation.

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

1. procedure TForm1.CreateDB;
2. begin
3.   DBConnection.CreateDB;
4.   With SQLQuery do
5.     begin
6.       SQL.Clear;
7.       SQL.ADD('CREATE TABLE TEST(ID INTEGER NOT NULL, TESTNAME VARCHAR(800))');
8.       ExecSQL;
9.       SQL.Clear;
10.       SQL.Add('CREATE GENERATOR "gen_TEST_ID";');
11.       ExecSQL;
12.       SQL.Clear;
13.       SQL.add('SET GENERATOR "gen_TEST_ID" TO 0;');
14.       ExecSQL;
15.       SQL.Clear;
16.       SQL.Add('CREATE TRIGGER "SET_TEST_ID" FOR "TEST" ACTIVE BEFORE INSERT POSITION 0 AS');
17.       SQL.Add('BEGIN');
18.       SQL.Add('    if (new.ID is null) then');
19.       SQL.Add('        new.ID = gen_id("gen_TEST_ID", 1);');
20.       SQL.Add('END');
21.       ExecSQL;
22.   end;
23.   SQLTransaction.Commit;
24. end;          
25.  
26.  

For more robust code, SQLQuery.ExecSQL should be contained in a try. Something like this:

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

1.   try
2.     ExecSQL;
3.   except
4.     on E:Exception do
5.       raise Exception.Create('ERROR 4:' + sLineBreak + E.Message);
6.   end;    
7.