Issue Overview: Enabling Extension Loading and DLL Integration in Lazarus Applications on Windows

When integrating SQLite extensions into Lazarus applications on Windows, developers often encounter challenges related to enabling extension loading at the runtime level and properly configuring dynamic-link library (DLL) dependencies. The SQLite engine requires explicit configuration to permit extension loading through its C API, which must be correctly bridged to Pascal code in Lazarus projects. Three critical components interact here: the SQLite engine’s extension loading API, the Lazarus Free Pascal Compiler (FPC) bindings to SQLite, and the Windows-specific handling of DLL paths and dependencies. A failure at any of these layers will prevent extensions from loading, manifesting as silent failures or "access denied" errors even when the extension file exists.

The SQLite C API function sqlite3_enable_load_extension() controls whether a database connection can load extensions. This function must be called with a flag value of 1 (enabled) before attempting to load any extension. However, many Lazarus SQLite wrappers or database components (e.g., TSQLite3Connection) do not expose this setting by default, requiring manual intervention in the application code. Furthermore, Windows imposes strict rules on DLL loading: the extension DLL must reside in a directory that is either in the system’s PATH environment variable, adjacent to the executable, or explicitly specified with an absolute path. Permissions issues, such as restricted access to the DLL file or its dependencies, can also block loading.

Possible Causes: Misconfigured API Bindings, Path Resolution Failures, and Missing Runtime Permissions

1. Incorrect or Missing API Bindings for SQLite Extension Functions in Pascal
Lazarus applications interact with SQLite through Pascal bindings to the SQLite C API. If the bindings for sqlite3_enable_load_extension or sqlite3_load_extension are not declared correctly, the application cannot invoke these functions. For example, a common mistake is omitting the cdecl calling convention or failing to mark the functions as external to link against the SQLite3 library. The following incorrect declaration would fail to link:

procedure sqlite3_enable_load_extension(db: psqlite3; enable: Integer); // Missing 'cdecl' and external linkage

A correct declaration must specify the library name and calling convention:

function sqlite3_enable_load_extension(db: psqlite3; enable: Integer): Integer; cdecl; external 'sqlite3.dll';

2. Extension DLL Path Resolution Failures on Windows
Windows does not implicitly search the application’s directory for DLLs unless it is part of the PATH environment variable. If the extension DLL is placed in a subdirectory (e.g., .\libs) without updating the PATH, the SQLite engine will fail to locate it. Additionally, relative paths in sqlite3_load_extension calls are resolved relative to the process’s current working directory, which may differ from the executable’s directory depending on how the application is launched (e.g., via a shortcut or debugger). Absolute paths are safer but require proper escaping in Pascal strings.

3. Insufficient Permissions to Load DLLs or Access Required System APIs
Windows applications running with limited user privileges may lack permission to load DLLs from certain directories. For example, loading a DLL from C:\Program Files without administrative rights can trigger access denial errors. Furthermore, some SQLite extensions depend on additional system libraries (e.g., OpenSSL for cryptographic functions), which may not be present on the target system. Dependency walkers like Dependency Walker or Process Monitor can identify missing DLLs.

4. SQLite Wrapper Components Suppressing Extension Loading
Lazarus database components such as TSQLite3Connection may internally disable extension loading for security reasons. These components often execute PRAGMA statements like foreign_keys = ON during initialization but do not enable extensions by default. Developers must intercept the database connection workflow to invoke sqlite3_enable_load_extension() immediately after opening the connection but before executing any queries.

Troubleshooting Steps, Solutions & Fixes: Configuring Pascal Bindings, Paths, and Permissions for SQLite Extensions

Step 1: Validate and Correct Pascal Bindings for SQLite Extension APIs
Ensure that the Lazarus project includes accurate declarations for the SQLite C API functions responsible for extension loading. A complete Pascal unit should resemble:

unit SQLite3Ext;

interface

uses
  ctypes;

type
  psqlite3 = type Pointer;

function sqlite3_enable_load_extension(db: psqlite3; enable: cint): cint; cdecl; external 'sqlite3.dll';
function sqlite3_load_extension(
  db: psqlite3;
  zFile: PAnsiChar;
  zProc: PAnsiChar;
  pzErrMsg: PPAnsiChar
): cint; cdecl; external 'sqlite3.dll';

implementation

end.

Compile the application with -k-lsqlite3 to link against sqlite3.dll. Verify that the DLL architecture (32-bit or 64-bit) matches the Lazarus project’s target. Use Process Explorer to confirm that the correct sqlite3.dll is loaded at runtime.

Step 2: Explicitly Enable Extension Loading Before Invoking SQLite APIs
After opening a database connection but before executing any SQL commands, call sqlite3_enable_load_extension() with enable = 1. For TSQLite3Connection users:

uses
  SQLite3Ext, SQLDB;

procedure TForm1.EnableExtensions;
var
  DB: psqlite3;
begin
  // Obtain the raw SQLite3 handle from the connection component
  DB := SQLite3Connection1.Handle;
  if sqlite3_enable_load_extension(DB, 1) <> SQLITE_OK then
    ShowMessage('Failed to enable extensions');
end;

// Call this after opening the connection:
SQLite3Connection1.Open;
EnableExtensions;

Step 3: Resolve DLL Paths Using Absolute Paths or Adjusted Environment Variables
Avoid relying on relative paths for extension DLLs. Instead, construct an absolute path dynamically:

function GetDLLPath: String;
begin
  Result := IncludeTrailingPathDelimiter(ExtractFilePath(ParamStr(0))) + 'extensions\my_extension.dll';
end;

// Load the extension using an absolute path
var
  ErrMsg: PAnsiChar;
begin
  if sqlite3_load_extension(DB, PAnsiChar(GetDLLPath), nil, @ErrMsg) <> SQLITE_OK then
    ShowMessage('Load error: ' + ErrMsg);
end;

If the extension depends on other DLLs, add their directories to the PATH environment variable programmatically:

procedure AddToPath(const Path: String);
var
  NewPath: String;
begin
  NewPath := GetEnvironmentVariable('PATH') + ';' + Path;
  SetEnvironmentVariable('PATH', PChar(NewPath));
end;

// Call before loading the extension:
AddToPath(ExtractFilePath(ParamStr(0)) + 'dependencies');

Step 4: Test Extension Loading in the SQLite Command-Line Interface (CLI)
Isolate the issue by attempting to load the extension via the SQLite CLI:

sqlite3
.open test.db
SELECT load_extension('C:\full\path\to\extension.dll');

If this fails, the problem lies with the extension itself or its dependencies. Use dumpbin /DEPENDENTS extension.dll (Visual Studio) or objdump -p extension.dll (MinGW) to inspect required libraries.

Step 5: Address Windows-Specific Permission and Security Constraints
Ensure the Lazarus application has read/execute permissions for the extension DLL and its dependencies. Right-click the DLL, select "Properties," and check the "Security" tab. For applications requiring elevated privileges, embed a manifest file requesting administrative rights:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<assembly xmlns="urn:schemas-microsoft-com:asm.v1" manifestVersion="1.0">
  <trustInfo xmlns="urn:schemas-microsoft-com:asm.v3">
    <security>
      <requestedPrivileges>
        <requestedExecutionLevel level="requireAdministrator" uiAccess="false"/>
      </requestedPrivileges>
    </security>
  </trustInfo>
</assembly>

Compile the manifest into the executable using mt.exe or Lazarus’ project options.

Step 6: Recompile SQLite with Extension Loading Enabled
Some SQLite distributions, particularly those bundled with Lazarus components, are compiled with -DSQLITE_OMIT_LOAD_EXTENSION to disable extensions. Obtain a SQLite amalgamation source file, compile it with Lazarus, and include the following defines:

fpc -dSQLITE_ENABLE_LOAD_EXTENSION -dSQLITE_CORE sqlite3.c

Link the resulting object file into the Lazarus project to ensure extension support is included.

Step 7: Utilize Lazarus-Specific Workarounds for Database Components
If TSQLite3Connection resists extension loading, bypass it by using direct API calls. Open a database connection using sqlite3_open_v2(), enable extensions, and attach the handle to the component:

var
  DB: psqlite3;
  Filename: String;
begin
  Filename := 'test.db';
  if sqlite3_open_v2(PAnsiChar(Filename), @DB, SQLITE_OPEN_READWRITE or SQLITE_OPEN_CREATE, nil) = SQLITE_OK then
  begin
    sqlite3_enable_load_extension(DB, 1);
    SQLite3Connection1.Handle := DB; // Assign the handle to the component
  end;
end;

Step 8: Debug Extension Loading with SQLite Tracing and Lazarus Logging
Enable SQLite’s error logging to capture detailed diagnostics:

sqlite3_config(SQLITE_CONFIG_LOG, @SQLiteLogCallback, nil);

procedure SQLiteLogCallback(UserData: Pointer; ErrorCode: Integer; Message: PAnsiChar); cdecl;
begin
  WriteLn('SQLite Error ', ErrorCode, ': ', Message);
end;

In Lazarus, enable logging for database components:

SQLite3Connection1.LogEvents := [detCustom, detExecute];
SQLite3Connection1.OnLog := @SQLiteLogEvent;

procedure TForm1.SQLiteLogEvent(Sender: TSQLConnection; EventType: TDBEventType; const Msg: String);
begin
  Memo1.Lines.Add(Msg);
end;

Final Validation
After applying these steps, test extension loading with a simple SQLite extension like sqlite3_math_functions.dll. Execute a function provided by the extension:

SQLQuery1.SQL.Text := 'SELECT sqrt(4)';
SQLQuery1.Open;
if SQLQuery1.Fields[0].AsFloat = 2 then
  ShowMessage('Extension loaded successfully');

If this succeeds, the configuration is correct. If not, re-examine the DLL path, permissions, and API bindings using system-level debugging tools.