Firebird Create Table (SOLVED)
lazarus / 16-Databases

----------------------------------------------------------------------
TheLastCayen · 2019-11-10 00:04

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i unit
Unit1; {$mode objfpc}{$H+} interface uses  Classes, SysUtils,
IBConnection, sqldb, db, Forms, Controls, Graphics,  Dialogs,
StdCtrls; type   { TForm1 }   TForm1 = class(TForm)    Button1:
TButton;    Button2: TButton;    procedure Button1Click(Sender:
TObject);    procedure Button2Click(Sender: TObject);    procedure
FormCreate(Sender: TObject);    procedure FormDestroy(Sender:
TObject);    procedure CreateDB;  private   public   end; var  Form1:
TForm1; implementation {$R *.lfm} { TForm1 } var  DBConnection:  
TIBConnection;  SQLTransaction: TSQLTransaction;  SQLQuery:      
TSQLQuery;  DataSource:     TDataSource;  procedure
TForm1.Button1Click(Sender: TObject);begin  CreateDB;end; procedure
TForm1.Button2Click(Sender: TObject);begin  if
FileExists(DBConnection.DatabaseName) then   
DeleteFile(DBConnection.DatabaseName);end; procedure
TForm1.FormCreate(Sender: TObject);begin  DBConnection   :=
TIBConnection.Create(nil);  SQLTransaction :=
TSQLTransaction.Create(nil);  SQLQuery       :=
TSQLQuery.Create(nil);  DataSource     := TDataSource.Create(nil);  
With  DBConnection do    begin      HostName:='';     
DatabaseName:='test1.fdb';      UserName:='SYSDBA';     
Password:='apple';      Charset := 'UTF8';      Dialect := 3;     
Transaction := SQLTransaction;    end;   SQLQuery.DataBase :=
DBConnection;  DataSource.DataSet := SQLQuery; end; procedure
TForm1.FormDestroy(Sender: TObject);begin  DataSource.free; 
SQLQuery.free;  SQLTransaction.free;  DBConnection.free;end; procedure
TForm1.CreateDB;begin  DBConnection.CreateDB;  SQLQuery.Close; 
SQLQuery.SQL.Text :='CREATE TABLE TEST(ID INTEGER NOT NULL, TESTNAME
VARCHAR(800))';  DBConnection.Connected := True; 
SQLTransaction.Active := True;  SQLQuery.Open;end; end.       

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 · 2019-11-10 00:34

Try SQLQuery.ExecSQL instead of SQLQuery.Open.

----------------------------------------------------------------------
TheLastCayen · 2019-11-10 05:29

Thank you JanRoza,

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

----------------------------------------------------------------------
TheLastCayen · 2019-11-10 19:58

For future reference. This is my code to create a table with auto-
indentation.
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.CreateDB;begin  DBConnection.CreateDB;  With SQLQuery do   
begin      SQL.Clear;      SQL.ADD('CREATE TABLE TEST(ID INTEGER NOT
NULL, TESTNAME VARCHAR(800))');      ExecSQL;      SQL.Clear;     
SQL.Add('CREATE GENERATOR "gen_TEST_ID";');      ExecSQL;     
SQL.Clear;      SQL.add('SET GENERATOR "gen_TEST_ID" TO 0;');     
ExecSQL;      SQL.Clear;      SQL.Add('CREATE TRIGGER "SET_TEST_ID"
FOR "TEST" ACTIVE BEFORE INSERT POSITION 0 AS');     
SQL.Add('BEGIN');      SQL.Add('    if (new.ID is null) then');     
SQL.Add('        new.ID = gen_id("gen_TEST_ID", 1);');     
SQL.Add('END');      ExecSQL;   end;  SQLTransaction.Commit;end;     
    
For more robust code, SQLQuery.ExecSQL should be contained in a try.
Something like this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i   try   
ExecSQL;  except    on E:Exception do      raise
Exception.Create('ERROR 4:' + sLineBreak + E.Message);  end;