Case Study 2: Wrapping a C Library in Pascal

Overview

In this case study, we create a Pascal wrapper for SQLite — one of the world's most widely used C libraries. The wrapper demonstrates external function declarations, type mapping between C and Pascal, memory management across language boundaries, and building a clean Pascal API on top of a raw C interface. By the end, we have a reusable SQLiteWrapper unit that PennyWise could use for persistent storage.

Why Wrap a C Library?

SQLite is written in C. It has no Pascal version. But because Free Pascal can call C functions directly, we can use SQLite from Pascal with full performance — no interpreter overhead, no bridge layer. The compiled Pascal code calls SQLite's C functions directly, just as a C program would.

The raw C API is functional but inconvenient: you pass PChar pointers, check integer return codes, and manage opaque handles. A Pascal wrapper provides:

• Type-safe interfaces (Pascal strings, not PChar)
• Exception-based error handling (not return code checking)
• Object-oriented API (methods on a class, not global functions)
• Automatic resource cleanup (destructor closes the database)

Step 1: Declare the C Functions

unit SQLiteWrapper;

{$mode objfpc}{$H+}

interface

uses
  SysUtils, Classes;

const
  {$IFDEF WINDOWS}
  SQLITE_LIB = 'sqlite3.dll';
  {$ENDIF}
  {$IFDEF LINUX}
  SQLITE_LIB = 'libsqlite3.so.0';
  {$ENDIF}
  {$IFDEF DARWIN}
  SQLITE_LIB = 'libsqlite3.dylib';
  {$ENDIF}

  SQLITE_OK    = 0;
  SQLITE_ERROR = 1;
  SQLITE_ROW   = 100;
  SQLITE_DONE  = 101;

type
  PSQLite3 = Pointer;
  PSQLite3Stmt = Pointer;
  PPChar = ^PChar;

{ Core C API declarations }
function sqlite3_open(filename: PChar; var db: PSQLite3): Integer;
  cdecl; external SQLITE_LIB;
function sqlite3_close(db: PSQLite3): Integer;
  cdecl; external SQLITE_LIB;
function sqlite3_errmsg(db: PSQLite3): PChar;
  cdecl; external SQLITE_LIB;
function sqlite3_exec(db: PSQLite3; sql: PChar; callback: Pointer;
  arg: Pointer; var errmsg: PChar): Integer;
  cdecl; external SQLITE_LIB;
procedure sqlite3_free(ptr: Pointer);
  cdecl; external SQLITE_LIB;

{ Prepared statement API }
function sqlite3_prepare_v2(db: PSQLite3; sql: PChar; nByte: Integer;
  var stmt: PSQLite3Stmt; var tail: PChar): Integer;
  cdecl; external SQLITE_LIB;
function sqlite3_step(stmt: PSQLite3Stmt): Integer;
  cdecl; external SQLITE_LIB;
function sqlite3_column_count(stmt: PSQLite3Stmt): Integer;
  cdecl; external SQLITE_LIB;
function sqlite3_column_text(stmt: PSQLite3Stmt; col: Integer): PChar;
  cdecl; external SQLITE_LIB;
function sqlite3_column_name(stmt: PSQLite3Stmt; col: Integer): PChar;
  cdecl; external SQLITE_LIB;
function sqlite3_finalize(stmt: PSQLite3Stmt): Integer;
  cdecl; external SQLITE_LIB;

Each declaration matches the C function signature exactly. The cdecl convention ensures argument passing is compatible. PChar maps to C's char*. Pointer maps to opaque handles.

Step 2: Build the Pascal Wrapper Class

type
  ESQLiteError = class(Exception);

  TQueryResult = class
  private
    FColumns: TStringList;
    FRows: TList;
  public
    constructor Create;
    destructor Destroy; override;
    property Columns: TStringList read FColumns;
    function RowCount: Integer;
    function GetValue(Row, Col: Integer): string;
    function GetValueByName(Row: Integer; const ColName: string): string;
  end;

  TSQLiteDB = class
  private
    FHandle: PSQLite3;
    FFilename: string;
    FOpen: Boolean;
    procedure CheckError(Code: Integer; const Context: string);
  public
    constructor Create(const Filename: string);
    destructor Destroy; override;
    procedure Execute(const SQL: string);
    function Query(const SQL: string): TQueryResult;
    property Filename: string read FFilename;
    property IsOpen: Boolean read FOpen;
  end;

implementation

{ --- TSQLiteDB --- }

constructor TSQLiteDB.Create(const Filename: string);
var
  Code: Integer;
begin
  inherited Create;
  FFilename := Filename;
  Code := sqlite3_open(PChar(Filename), FHandle);
  if Code <> SQLITE_OK then
    raise ESQLiteError.CreateFmt('Cannot open database "%s": %s',
      [Filename, sqlite3_errmsg(FHandle)]);
  FOpen := True;
end;

destructor TSQLiteDB.Destroy;
begin
  if FOpen and (FHandle <> nil) then
    sqlite3_close(FHandle);
  inherited;
end;

procedure TSQLiteDB.CheckError(Code: Integer; const Context: string);
begin
  if Code <> SQLITE_OK then
    raise ESQLiteError.CreateFmt('%s: %s', [Context, sqlite3_errmsg(FHandle)]);
end;

procedure TSQLiteDB.Execute(const SQL: string);
var
  ErrMsg: PChar;
  Code: Integer;
begin
  ErrMsg := nil;
  Code := sqlite3_exec(FHandle, PChar(SQL), nil, nil, ErrMsg);
  if Code <> SQLITE_OK then
  begin
    try
      raise ESQLiteError.CreateFmt('SQL error: %s', [ErrMsg]);
    finally
      if ErrMsg <> nil then
        sqlite3_free(ErrMsg);
    end;
  end;
end;

function TSQLiteDB.Query(const SQL: string): TQueryResult;
var
  Stmt: PSQLite3Stmt;
  Tail: PChar;
  Code, I, ColCount: Integer;
  Row: TStringList;
begin
  Result := TQueryResult.Create;
  Stmt := nil;

  Code := sqlite3_prepare_v2(FHandle, PChar(SQL), -1, Stmt, Tail);
  CheckError(Code, 'Prepare');

  try
    ColCount := sqlite3_column_count(Stmt);

    { Get column names }
    for I := 0 to ColCount - 1 do
      Result.Columns.Add(sqlite3_column_name(Stmt, I));

    { Fetch rows }
    while sqlite3_step(Stmt) = SQLITE_ROW do
    begin
      Row := TStringList.Create;
      for I := 0 to ColCount - 1 do
        Row.Add(sqlite3_column_text(Stmt, I));
      Result.FRows.Add(Row);
    end;
  finally
    sqlite3_finalize(Stmt);
  end;
end;

Step 3: Usage Example

program TestSQLite;

{$mode objfpc}{$H+}

uses
  SysUtils, SQLiteWrapper;

var
  DB: TSQLiteDB;
  QR: TQueryResult;
  I: Integer;
begin
  DB := TSQLiteDB.Create('test.db');
  try
    { Create table }
    DB.Execute('CREATE TABLE IF NOT EXISTS expenses (' +
      'id INTEGER PRIMARY KEY AUTOINCREMENT,' +
      'description TEXT NOT NULL,' +
      'amount REAL NOT NULL,' +
      'category TEXT)');

    { Insert data }
    DB.Execute('INSERT INTO expenses (description, amount, category) ' +
      'VALUES ("Groceries", 85.50, "food")');
    DB.Execute('INSERT INTO expenses (description, amount, category) ' +
      'VALUES ("Bus pass", 45.00, "transport")');
    DB.Execute('INSERT INTO expenses (description, amount, category) ' +
      'VALUES ("Electric bill", 95.30, "utilities")');

    { Query }
    QR := DB.Query('SELECT * FROM expenses ORDER BY amount DESC');
    try
      WriteLn('Expenses (', QR.RowCount, ' rows):');
      for I := 0 to QR.RowCount - 1 do
        WriteLn(Format('  %s: $%s (%s)', [
          QR.GetValueByName(I, 'description'),
          QR.GetValueByName(I, 'amount'),
          QR.GetValueByName(I, 'category')
        ]));
    finally
      QR.Free;
    end;
  finally
    DB.Free;
  end;
end.

Key Design Decisions

1. Exceptions Instead of Return Codes

The C API returns integer error codes (SQLITE_OK, SQLITE_ERROR, etc.). The Pascal wrapper converts these to exceptions. This is more natural in Pascal and ensures errors cannot be silently ignored.

2. Pascal Strings at the Interface

The wrapper accepts and returns Pascal string values, converting to/from PChar internally. Users of the wrapper never deal with PChar pointers.

3. RAII-Style Resource Management

The constructor opens the database; the destructor closes it. Combined with try..finally, this guarantees the database is closed even if an exception occurs. No manual cleanup is needed.

4. Platform-Specific Library Names

The SQLITE_LIB constant uses conditional compilation to set the correct library name for each platform. The rest of the code is platform-independent.

Lessons Learned

1. C libraries are accessible. Any C library with a documented API can be called from Pascal. The type mapping (PChar for char, Pointer for void, Integer for int) is straightforward.

2. Wrappers improve ergonomics. The raw C API is usable but error-prone. A Pascal wrapper provides type safety, exceptions, and automatic cleanup.

3. Memory ownership must be clear. When C allocates memory (sqlite3_errmsg), Pascal must use the C library's free function (sqlite3_free), not Pascal's FreeMem. Mixing allocators causes crashes.

4. Test error paths. It is easy to test the happy path (everything works). Test what happens when the database file is locked, when SQL is malformed, when the library is missing.

5. This pattern applies universally. The same wrapping technique works for any C library: OpenSSL, libcurl, zlib, image processing libraries, and more.