This tutorial describes how to create tables, stored procedures and other objects in SQLite Database.
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.
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.
To create tables, the TLiteSQL component is used here.
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
);
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();
}
}
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.