Scripting examples

Each example below shows the same task written in both Lua and Pascal, so you can compare the two APIs. They use the bundled dbdemos database, which the installer places under the shared application data folder:

C:\ProgramData\SQLite Expert\<edition> 6 - <bitness>\Data\dbdemos.db3

where <edition> is Professional or Personal and <bitness> is 32bit or 64bit — for example Professional 6 - 64bit. The examples use the 64-bit Professional path; adjust it to match your own installation.

Example 1: display data

Run a query and show its result on the Data tab.

database = sqlitedatabase:new() 
database:filename("C:\\ProgramData\\SQLite Expert\\Professional 6 - 64bit\\Data\\dbdemos.db3") 
database:connected(true) 
query = sqlitequery:new() 
query:database(database) 
query:sql("select * from customer") 
query:active(true) 
displaydata(query) 
query:free() 
database:free() 

The same script in Pascal:

var 
  Database: TSQLiteDatabase; 
  Query: TSQLiteQuery; 
begin 
  Database := TSQLiteDatabase.Create(nil); 
  Database.FileName := 'C:\ProgramData\SQLite Expert\Professional 6 - 64bit\Data\dbdemos.db3'; 
  Database.Connected := True; 
  Query := TSQLiteQuery.Create(nil); 
  Query.Database := Database; 
  Query.SQL := 'select * from customer'; 
  Query.Open; 
  DisplayData(Query); 
  Query.Free; 
  Database.Free; 
end. 

Example 2: list all table names

Print the name of every table in the database.

database = sqlitedatabase:new() 
database:filename("C:\\ProgramData\\SQLite Expert\\Professional 6 - 64bit\\Data\\dbdemos.db3") 
database:open() 
query = sqlitequery:new() 
query:database(database) 
query:sql("select * from sqlite_master where type = \"table\"") 
query:open() 
query:first() 
while (query:eof() == false) do 
  print(query:getfield("name"):value()) 
  query:next() 
end 
query:free() 
database:free() 

The same example in Pascal:

var 
  Database: TSQLiteDatabase; 
  Query: TSQLiteQuery; 
begin 
  Database := TSQLiteDatabase.Create(nil); 
  Database.FileName := 'C:\ProgramData\SQLite Expert\Professional 6 - 64bit\Data\dbdemos.db3'; 
  Database.Connected := True; 
  Query := TSQLiteQuery.Create(nil); 
  Query.Database := Database; 
  Query.SQL := 'select * from sqlite_master where type = "table"'; 
  Query.Open; 
  while not Query.Eof do 
  begin 
    Writeln(Query.FieldByName('name').AsString); 
    Query.Next; 
  end; 
  Query.Free; 
  Database.Free; 
end. 

Example 3: update a field

Iterate through the employee table and increase every salary by 10%.

database = sqlitedatabase:new() 
database:filename("C:\\ProgramData\\SQLite Expert\\Professional 6 - 64bit\\Data\\dbdemos.db3") 
database:connected(true) 
query = sqlitequery:new() 
query:database(database) 
query:sql("select * from employee") 
query:active(true) 
query:first() 
while (query:eof() == false) do 
  query:edit() 
  query:getfield('Salary'):asfloat(query:getfield('Salary'):asfloat() * 1.1) 
  query:post() 
  query:next() 
end 
displaydata(query) 
query:free() 
database:free() 

The same example in Pascal:

var 
  Database: TSQLiteDatabase; 
  Query: TSQLiteQuery; 
begin 
  Database := TSQLiteDatabase.Create(nil); 
  Database.FileName := 'C:\ProgramData\SQLite Expert\Professional 6 - 64bit\Data\dbdemos.db3'; 
  Database.Connected := True; 
  Query := TSQLiteQuery.Create(nil); 
  Query.Database := Database; 
  Query.SQL := 'select * from employee'; 
  Query.Open; 
  while not Query.Eof do 
  begin 
    Query.Edit; 
    Query.FieldByName('Salary').AsFloat := Query.FieldByName('Salary').AsFloat * 1.1; 
    Query.Post; 
    Query.Next; 
  end; 
  DisplayData(Query); 
  Query.Free; 
  Database.Free; 
end.

See also: Lua scripting API · Pascal scripting API