LiteDAC

Creating Database Objects

This tutorial describes how to create tables, stored procedures and other objects in SQLite Database.

Requirements

In order to create database objects, you have to connect to SQLite DB. This process is described in details in the tutorial Connecting To SQLite Database.

General information

Database objects are created using Data Definition Language (DDL), which is a part of SQL. There are two ways to create database objects. You can build DDL statements manually and execute them using the component like TLiteSQL. Another way is to use console utility sqlite3.exe This topic covers the first way - using components.

There are two ways of executing DDL statements in components like TLiteSQL, in design-time and in run-time. Both these ways are described below.

Note: the following assumes that you have the IDE running, you are currently focused on the form designer, and you have already set up the TLiteConnection on the form.

Creating tables

To create tables, the TLiteSQL component is used here.

Design time creation

it is named LiteSQL1. Note that the LiteSQL1.Connection property is already set to existent (on the form) connection.

CREATE TABLE dept (
  deptno INTEGER PRIMARY KEY,
  dname VARCHAR2(14),
  loc VARCHAR2(13)
);

CREATE TABLE emp (
  empno INTEGER PRIMARY KEY,
  ename VARCHAR2(10),
  job VARCHAR2(9),
  mgr INTEGER,
  hiredate DATE,
  sal FLOAT,
  comm FLOAT,
  deptno INTEGER
);

Run time creation

The same operations performed in runtime look as follows:

[Delphi]

var
  LiteSQL: TLiteSQL;
begin
  LiteSQL:= TLiteSQL.Create(nil);
  try
    // LiteConnection is either TLiteConnection already set up
    LiteSQL.Connection := LiteConnection; 

    // set SQL script for creating tables
    LiteSQL.SQL.Clear;
    LiteSQL.SQL.Add('CREATE TABLE dept (');
    LiteSQL.SQL.Add('  deptno INTEGER,');
    LiteSQL.SQL.Add('  dname VARCHAR2(14),');
    LiteSQL.SQL.Add('  loc VARCHAR2(13)');
    LiteSQL.SQL.Add(');');

    LiteSQL.SQL.Add('CREATE TABLE emp (');
    LiteSQL.SQL.Add('  empno INTEGER PRIMARY KEY,');
    LiteSQL.SQL.Add('  ename VARCHAR2(10),');
    LiteSQL.SQL.Add('  job VARCHAR2(9),');
    LiteSQL.SQL.Add('  mgr INTEGER,');
    LiteSQL.SQL.Add('  hiredate DATE,');
    LiteSQL.SQL.Add('  sal FLOAT,');
    LiteSQL.SQL.Add('  comm FLOAT,');
    LiteSQL.SQL.Add('  deptno INTEGER');
    LiteSQL.SQL.Add(');');

    // execute script
    LiteSQL.Execute;
  finally
    LiteSQL.Free;
  end;
end;

[C++Builder]

{
  TLiteSQL* LiteSQL= new TLiteSQL(NULL);
  try
  {
    // LiteConnection is either TLiteConnection already set up
    LiteSQL->Connection = LiteConnection;

    // set SQL script for creating tables
    LiteSQL->SQL->Clear();
    LiteSQL->SQL->Add("CREATE TABLE dept (");
    LiteSQL->SQL->Add("  deptno INTEGER PRIMARY KEY,");
    LiteSQL->SQL->Add("  dname VARCHAR2(14),");
    LiteSQL->SQL->Add("  loc VARCHAR2(13)");
    LiteSQL->SQL->Add(");");

    LiteSQL->SQL->Add("CREATE TABLE emp (");
    LiteSQL->SQL->Add("  empno INTEGER PRIMARY KEY,");
    LiteSQL->SQL->Add("  ename VARCHAR2(10),");
    LiteSQL->SQL->Add("  job VARCHAR2(9),");
    LiteSQL->SQL->Add("  mgr INTEGER,");
    LiteSQL->SQL->Add("  hiredate DATE,");
    LiteSQL->SQL->Add("  sal FLOAT,");
    LiteSQL->SQL->Add("  comm FLOAT,");
    LiteSQL->SQL->Add("  deptno INTEGER");
    LiteSQL->SQL->Add(");");

    // execute script
    LiteSQL->Execute();
  }
  __finally
  {
    LiteSQL->Free();
  }
}

Additional Information

Actually, there are lots of ways to create database objects on server. Any tool or component that is capable of running a SQL query, can be used to manage database objects. For example, TLiteSQL suits fine for creating objects one by one, while TLiteScript is designed for executing series of DDL/DML statements. For information on DDL statements syntax refer to SQLite documentation.

© 1997-2024 Devart. All Rights Reserved. Request Support DAC Forum Provide Feedback