Skip to content

Raw bindings

Jump to bottom
Ivan Semenkov edited this page Oct 24, 2021 · 3 revisions

Table of contents

About

libpassqlite unit contains raw binding for SQLite3 library for FreePascal and Delphi compilers.

Detail information about SQLite3 API presents on SQLite3 C/C++ API page.

Create database

Create new or open exists database file possible by using sqlite3_open_v2 function. Details on SQLite C/C++ API page.

function sqlite3_open_v2(const filename : PAnsiChar; ppDb : ppsqlite3; flags :
  Integer; const zVfs : PAnsiChar) : Integer;

Opened database at end closes by sqlite3_close_v2 function. Details on SQLite C/C++ API page.

function sqlite3_close_v2(handle : psqlite3) : Integer;
Example
uses
  SysUtils, libpassqlite, utils.api.cstring;
  
var
  handle : psqlite3;
  filename : API.PAnsiStringWrapper;
  
begin
  filename := API.CString.Create('database').ToUniquePAnsiChar;
  if sqlite3_open_v2(filename.Value, @Handle,
    SQLITE_OPEN_CREATE or SQLITE_OPEN_READWRITE, nil) <> SQLITE_OK then
    { Oops! Something wrong. }
    
  if sqlite3_close_v2(handle) <> SQLITE_OK then
    { Oops! Something wrong. }
  
  FreeAndNil(filename);
end;

sqlite3_open_v2 function needs c-string parameter for database name. For comfortable working with c-strings in pascal exists c-string wrapper.

Errors processing

Many SQLite3 API functions returns error code. For comfortable working with error codes exists TArrayErrorsStack and TListErrorsStack containers.

Example
uses
  SysUtils, libpassqlite, utils.api.cstring, utils.errorsstack;
 
type
  TSQLiteErrors = {$IFDEF FPC}specialize{$ENDIF} TListErrorsStack<Integer>;
 
var
  errors : TSQLiteErrors;
  handle : psqlite3;
  filename : API.PAnsiStringWrapper;
  
begin
  filename := API.CString.Create('database').ToUniquePAnsiChar;
  errors.Push(sqlite3_open_v2(filename.Value, @Handle,
    SQLITE_OPEN_CREATE or SQLITE_OPEN_READWRITE, nil));
  
  if errors.count > 0 then
    ;
  
  erorrs.Push(sqlite3_close_v2(handle));
  
  if errors.count > 0 then
    ;
    
  FreeAndNil(filename);  
end;

Run query

Run database sql query possible by using sqlite3_prepare_v3, sqlite3_step functions. Details on SQLite C/C++ API page.

function sqlite3_prepare_v3(db : psqlite3; const zSql : PAnsiChar; nByte :
  Integer; prepFlags : Cardinal; ppStmt : ppsqlite3_stmt; const pzTail :
  PAnsiChar) : Integer;

sqlite3_prepare_v3 function needs c-string parameter for query. For comfortable working with c-strings in pascal exists c-string wrapper.

function sqlite3_step(pStmt : psqlite3_stmt) : Integer;

Opened database query statement at end closes by sqlite3_finalize function. Details on SQLite C/C++ API page.

function sqlite3_finalize(pStmt : psqlite3_stmt) : Integer;
Example
uses
  SysUtils, libpassqlite, utils.api.cstring;
  
var
  handle : psqlite3;
  statementHandle : psqlite3_stmt;
  filename : API.PAnsiStringWrapper;
  query : API.PAnsiStringWrapper;
  
begin
  { Create or open database file. }
  filename := API.CString.Create('database').ToUniquePAnsiChar;
  if sqlite3_open_v2(filename.Value, @Handle,
    SQLITE_OPEN_CREATE or SQLITE_OPEN_READWRITE, nil) <> SQLITE_OK then
    { Oops! Something wrong. }
    
  { Prepare sql query. }
  query := API.CString.Create('SELECT * FROM sqlite_master;').ToUniquePAnsiChar;
  if sqlite3_prepare_v3(handle, query.Value, query.Length,
    SQLITE_PREPARE_NORMALIZE, @statementHandle, nil) <> SQLITE_OK then
    { Oops! Something wrong. } 
  
  { Run sql query. }
  if sqlite3_step(statementHandle) <> SQLITE_DONE then
    { Oops! Something wrong. }
  
  { Clear database sql statement. }
  if sqlite3_finalize(statementHandle) <> SQLITE_OK then
    { Oops! Something wrong. }
  
  { Close database. }
  if sqlite3_close_v2(handle) <> SQLITE_OK then
    { Oops! Something wrong. }
    
  FreeAndNil(query);
  FreeAndNil(filename);
end;

Create new table

Example
uses
  SysUtils, libpassqlite, utils.api.cstring;
  
var
  handle : psqlite3;
  statementHandle : psqlite3_stmt;
  filename : API.PAnsiStringWrapper;
  query : API.PAnsiStringWrapper;
  
begin
  { Create or open database file. }
  filename := API.CString.Create('database').ToUniquePAnsiChar;
  if sqlite3_open_v2(filename.Value, @Handle,
    SQLITE_OPEN_CREATE or SQLITE_OPEN_READWRITE, nil) <> SQLITE_OK then
    { Oops! Something wrong. }
    
  { Prepare sql query. }
  query := API.CString.Create('CREATE TABLE table_name (id INTEGER PRIMARY KEY, ' +
    'value TEXT NOT NULL);').ToUniquePAnsiChar;
  if sqlite3_prepare_v3(handle, query.Value, query.Length,
    SQLITE_PREPARE_NORMALIZE, @statementHandle, nil) <> SQLITE_OK then
    { Oops! Something wrong. } 
  
  { Run sql query. }
  if sqlite3_step(statementHandle) <> SQLITE_DONE then
    { Oops! Something wrong. }
  
  { Clear database sql statement. }
  if sqlite3_finalize(statementHandle) <> SQLITE_OK then
    { Oops! Something wrong. }
  
  { Close database. }
  if sqlite3_close_v2(handle) <> SQLITE_OK then
    { Oops! Something wrong. }
    
  FreeAndNil(filename);
  FreeAndNil(query);
end;

Select data

To read data from database exists sqlite3_column_int, sqlite_column_text, etc functions. Details on SQLite C/C++ API page.

Functions that returns string data return PByte type. For this reason first need change data type to PAnsiString. For comfortable working with c-strings in pascal exists c-string wrapper.

API.CString.Create(PAnsiChar(sqlite3_column_text(StatementHandle, 0)))
  .ToString
Example
uses
  SysUtils, libpassqlite, utils.api.cstring;
  
var
  handle : psqlite3;
  statementHandle : psqlite3_stmt;
  filename : API.PAnsiStringWrapper;
  query : API.PAnsiStringWrapper;
  
begin
  { Create or open database file. }
  filename := API.CString.Create('database').ToUniquePAnsiChar;
  if sqlite3_open_v2(filename.Value, @Handle,
    SQLITE_OPEN_CREATE or SQLITE_OPEN_READWRITE, nil) <> SQLITE_OK then
    { Oops! Something wrong. }
    
  { Prepare sql query. }
  query := API.CString.Create('SELECT * FROM table_name').ToUniquePAnsiChar;
  if sqlite3_prepare_v3(handle, query.Value, query.Length,
    SQLITE_PREPARE_NORMALIZE, @statementHandle, nil) <> SQLITE_OK then
    { Oops! Something wrong. } 
  
  { Run sql query and get row. }
  while sqlite3_step(statementHandle) = SQLITE_ROW do
  begin
    writeln(API.CString.Create(PAnsiChar(sqlite3_column_text(StatementHandle, 1)))
    .ToString);
  end;
  
  { Clear database sql statement. }
  if sqlite3_finalize(statementHandle) <> SQLITE_OK then
    { Oops! Something wrong. }
  
  { Close database. }
  if sqlite3_close_v2(handle) <> SQLITE_OK then
    { Oops! Something wrong. }
    
  FreeAndNil(filename);
  FreeAndNil(query);
end;

Insert data

It is safer to insert new data into the database by data binding instead of set them into sql query. For this using sqlite_bind_int, sqlite_bind_text, etc functions. Details on SQLite C/C++ API page.

For text bindings, it is important to not change or delete the string until run the query. With API.CString object may use ToUniquePAnsiChar method.

sqlite3_bind_text(StatementHandle, 0, 
    API.CString.Create(StrData).ToUniquePAnsiChar, Length(StrData), nil)
Example
uses
  SysUtils, libpassqlite, utils.api.cstring;
  
var
  handle : psqlite3;
  statementHandle : psqlite3_stmt;
  filename : API.PAnsiStringWrapper;
  query : API.PAnsiStringWrapper;
  str : API.PAnsiStringWrapper;
  
begin
  { Create or open database file. }
  filename := API.CString.Create('database').ToUniquePAnsiChar;
  if sqlite3_open_v2(filename.Value, @Handle,
    SQLITE_OPEN_CREATE or SQLITE_OPEN_READWRITE, nil) <> SQLITE_OK then
    { Oops! Something wrong. }
    
  { Prepare sql query. }
  query := API.CString.Create('INSERT INTO table_name (value) VALUES (?);').ToUniquePAnsiChar;
  if sqlite3_prepare_v3(handle, query.Value, query.Length,
    SQLITE_PREPARE_NORMALIZE, @statementHandle, nil) <> SQLITE_OK then
    { Oops! Something wrong. } 
  
  { Bind text data. }
  str := API.CString.Create('string').ToUniquePAnsiChar;
  if sqlite3_bind_text(statementHandle, 0, str.Value,
    str.Length, nil) <> SQLITE_OK then
    { Oops! Something wrong. }
    
  { Run sql query. }
  if sqlite3_step(statementHandle) <> SQLITE_DONE then
    { Oops! Something wrong. }
  
  { Clear database sql statement. }
  if sqlite3_finalize(statementHandle) <> SQLITE_OK then
    { Oops! Something wrong. }
  
  { Close database. }
  if sqlite3_close_v2(handle) <> SQLITE_OK then
    { Oops! Something wrong. }
    
  FreeAndNil(filename);
  FreeAndNil(query);
  FreeAndNil(str);
end;

Object wrapper

For comfortable works with database possible use TSQLite3Database object.

Clone this wiki locally