View Issue Details

IDProjectCategoryView StatusLast Update
0020640FPCDatabasepublic2012-01-23 11:21
ReporterBigChimpAssigned ToJoost van der Sluis 
PrioritynormalSeverityfeatureReproducibilityN/A
Status closedResolutionfixed 
Platformx64OSWindowsOS VersionVista
Product Version2.5.1Product Buildfixes_2_6, r19599 
Target VersionFixed in Version 
Summary0020640: Support for SQLite load_extension (e.g. for Spatialite)
DescriptionThe SQLite dll supports loading extension libraries (dlls/sos).
The attached patch enables this.
Note: you apparently must have an open database connection for this to work; the patch checks for this.

I intend to use this to load the Spatialite GIS extensions to SQlite, allowing me to manipulate GIS data easily without a client/server database server.
Additional InformationPatch tested on Win x86 FPC only

You can load an extension dll/so as an extension to SQLite, with a statement like [3]
select load_extension('libspatialite-4.dll');
However, before that you have to enable the extension load mechanism
using the C API [2]:
int sqlite3_enable_load_extension(sqlite3 *db, int onoff);
(onoff==1 to turn extension loading on)

[2] http://www.sqlite.org/c3ref/enable_load_extension.html
[3] http://www.sqlite.org/lang_corefunc.html#load_extension

Example use within Lazarus:
DBConnection.DatabaseName:='osm.sqlite';
DBConnection.Open;
DBConnection.LoadExtension('libspatialite-4.dll');
//Note: we need an open db before doing this
Tagsdll, library, sqlite
Fixed in Revision20146
FPCOldBugId
FPCTarget
Attached Files
  • 26Fixes_sqlite3conn.pp.diff (1,500 bytes)
    Index: packages/fcl-db/src/sqldb/sqlite/sqlite3conn.pp
    ===================================================================
    --- packages/fcl-db/src/sqldb/sqlite/sqlite3conn.pp	(revision 19599)
    +++ packages/fcl-db/src/sqldb/sqlite/sqlite3conn.pp	(working copy)
    @@ -89,6 +89,7 @@
         constructor Create(AOwner : TComponent); override;
         function GetInsertID: int64;
         procedure GetFieldNames(const TableName : string; List :  TStrings); override;
    +    procedure LoadExtension(LibraryFile: string);
       published
         property Options: TSqliteOptions read FOptions write SetOptions;
       end;
    @@ -883,6 +884,24 @@
       GetDBInfo(stColumns,TableName,'name',List);
     end;
     
    +procedure Tsqlite3connection.LoadExtension(Libraryfile: String);
    +var
    +  LoadResult: integer;
    +begin
    +  CheckConnected; //Apparently we need a connection before we can load extensions.
    +  try
    +    LoadResult:=SQLITE_ERROR; //Default to failed
    +    sqlite3_enable_load_extension(fhandle, 1); //Make sure we are allowed to load
    +    LoadResult:=sqlite3_load_extension(fhandle, PChar(LibraryFile), nil, nil); //Actually load extension
    +    if LoadResult=SQLITE_ERROR then
    +      begin
    +      DatabaseError('LoadExtension: failed to load SQLite extension (SQLite returned an error while loading).',Self);
    +      end;
    +  except
    +    DatabaseError('LoadExtension: failed to load SQLite extension.',Self);
    +  end;
    +end;
    +
     procedure TSQLite3Connection.setoptions(const avalue: tsqliteoptions);
     begin
      if avalue <> foptions then 
    
  • 26Fixes_MoreChecks_sqlite3conn.pp.diff (1,753 bytes)
    Index: packages/fcl-db/src/sqldb/sqlite/sqlite3conn.pp
    ===================================================================
    --- packages/fcl-db/src/sqldb/sqlite/sqlite3conn.pp	(revision 19605)
    +++ packages/fcl-db/src/sqldb/sqlite/sqlite3conn.pp	(working copy)
    @@ -89,6 +89,7 @@
         constructor Create(AOwner : TComponent); override;
         function GetInsertID: int64;
         procedure GetFieldNames(const TableName : string; List :  TStrings); override;
    +    procedure LoadExtension(LibraryFile: string);
       published
         property Options: TSqliteOptions read FOptions write SetOptions;
       end;
    @@ -883,6 +884,31 @@
       GetDBInfo(stColumns,TableName,'name',List);
     end;
     
    +procedure Tsqlite3connection.LoadExtension(Libraryfile: String);
    +var
    +  LoadResult: integer;
    +begin
    +  CheckConnected; //Apparently we need a connection before we can load extensions.
    +  LoadResult:=SQLITE_ERROR; //Default to failed  
    +  try    
    +    LoadResult:=sqlite3_enable_load_extension(fhandle, 1); //Make sure we are allowed to load
    +    if LoadResult=SQLITE_OK then
    +      begin
    +      LoadResult:=sqlite3_load_extension(fhandle, PChar(LibraryFile), nil, nil); //Actually load extension
    +      if LoadResult=SQLITE_ERROR then
    +        begin
    +        DatabaseError('LoadExtension: failed to load SQLite extension (SQLite returned an error while loading).',Self);
    +        end;
    +      end
    +      else
    +      begin
    +        DatabaseError('LoadExtension: failed to load SQLite extension (SQLite returned an error while enabling extensions).',Self);
    +      end;
    +  except
    +    DatabaseError('LoadExtension: failed to load SQLite extension.',Self)
    +  end;
    +end;
    +
     procedure TSQLite3Connection.setoptions(const avalue: tsqliteoptions);
     begin
      if avalue <> foptions then 
    

Activities

2011-11-07 10:57

 

26Fixes_sqlite3conn.pp.diff (1,500 bytes)
Index: packages/fcl-db/src/sqldb/sqlite/sqlite3conn.pp
===================================================================
--- packages/fcl-db/src/sqldb/sqlite/sqlite3conn.pp	(revision 19599)
+++ packages/fcl-db/src/sqldb/sqlite/sqlite3conn.pp	(working copy)
@@ -89,6 +89,7 @@
     constructor Create(AOwner : TComponent); override;
     function GetInsertID: int64;
     procedure GetFieldNames(const TableName : string; List :  TStrings); override;
+    procedure LoadExtension(LibraryFile: string);
   published
     property Options: TSqliteOptions read FOptions write SetOptions;
   end;
@@ -883,6 +884,24 @@
   GetDBInfo(stColumns,TableName,'name',List);
 end;
 
+procedure Tsqlite3connection.LoadExtension(Libraryfile: String);
+var
+  LoadResult: integer;
+begin
+  CheckConnected; //Apparently we need a connection before we can load extensions.
+  try
+    LoadResult:=SQLITE_ERROR; //Default to failed
+    sqlite3_enable_load_extension(fhandle, 1); //Make sure we are allowed to load
+    LoadResult:=sqlite3_load_extension(fhandle, PChar(LibraryFile), nil, nil); //Actually load extension
+    if LoadResult=SQLITE_ERROR then
+      begin
+      DatabaseError('LoadExtension: failed to load SQLite extension (SQLite returned an error while loading).',Self);
+      end;
+  except
+    DatabaseError('LoadExtension: failed to load SQLite extension.',Self);
+  end;
+end;
+
 procedure TSQLite3Connection.setoptions(const avalue: tsqliteoptions);
 begin
  if avalue <> foptions then 

Marco van de Voort

2011-11-07 21:13

manager   ~0053913

Last edited: 2011-11-07 21:14

1) please always base patches on trunk(*). They will be committed to trunk, and only merged back to fixes when considered stable.

2. sqlite3_enable_load_extension has a return value. Please handle it.


(*) I think I'll manage in this case, so no reason to redo it. But keep it in mind for next time

Reinier Olislagers

2011-11-08 12:06

developer   ~0053920

Last edited: 2011-11-08 23:08

1. Will do, thanks.
2. The reason I don't explicitly test for sqlite3_enable_load_extension result is that it may be run before in which case doing it again probably has no influence on failure of the next statements.
If the sqlite3_load_extension call subsequently fails, it will either give an SQLITE_ERROR result, which is handled, or trigger an exception, which is handled as well.
I've attached another patch in case you don't agree with me ;) (Tested on Win32)

2011-11-08 12:06

 

26Fixes_MoreChecks_sqlite3conn.pp.diff (1,753 bytes)
Index: packages/fcl-db/src/sqldb/sqlite/sqlite3conn.pp
===================================================================
--- packages/fcl-db/src/sqldb/sqlite/sqlite3conn.pp	(revision 19605)
+++ packages/fcl-db/src/sqldb/sqlite/sqlite3conn.pp	(working copy)
@@ -89,6 +89,7 @@
     constructor Create(AOwner : TComponent); override;
     function GetInsertID: int64;
     procedure GetFieldNames(const TableName : string; List :  TStrings); override;
+    procedure LoadExtension(LibraryFile: string);
   published
     property Options: TSqliteOptions read FOptions write SetOptions;
   end;
@@ -883,6 +884,31 @@
   GetDBInfo(stColumns,TableName,'name',List);
 end;
 
+procedure Tsqlite3connection.LoadExtension(Libraryfile: String);
+var
+  LoadResult: integer;
+begin
+  CheckConnected; //Apparently we need a connection before we can load extensions.
+  LoadResult:=SQLITE_ERROR; //Default to failed  
+  try    
+    LoadResult:=sqlite3_enable_load_extension(fhandle, 1); //Make sure we are allowed to load
+    if LoadResult=SQLITE_OK then
+      begin
+      LoadResult:=sqlite3_load_extension(fhandle, PChar(LibraryFile), nil, nil); //Actually load extension
+      if LoadResult=SQLITE_ERROR then
+        begin
+        DatabaseError('LoadExtension: failed to load SQLite extension (SQLite returned an error while loading).',Self);
+        end;
+      end
+      else
+      begin
+        DatabaseError('LoadExtension: failed to load SQLite extension (SQLite returned an error while enabling extensions).',Self);
+      end;
+  except
+    DatabaseError('LoadExtension: failed to load SQLite extension.',Self)
+  end;
+end;
+
 procedure TSQLite3Connection.setoptions(const avalue: tsqliteoptions);
 begin
  if avalue <> foptions then 

Marco van de Voort

2012-01-21 22:46

manager   ~0055932

Committed, thanks.

Issue History

Date Modified Username Field Change
2011-11-07 10:57 Reinier Olislagers New Issue
2011-11-07 10:57 Reinier Olislagers Status new => assigned
2011-11-07 10:57 Reinier Olislagers Assigned To => Joost van der Sluis
2011-11-07 10:57 Reinier Olislagers File Added: 26Fixes_sqlite3conn.pp.diff
2011-11-07 21:13 Marco van de Voort Note Added: 0053913
2011-11-07 21:14 Marco van de Voort Note Edited: 0053913
2011-11-07 21:14 Marco van de Voort Note Edited: 0053913
2011-11-08 12:06 Reinier Olislagers Note Added: 0053920
2011-11-08 12:06 Reinier Olislagers File Added: 26Fixes_MoreChecks_sqlite3conn.pp.diff
2011-11-08 12:06 Reinier Olislagers Tag Attached: dll
2011-11-08 12:06 Reinier Olislagers Tag Attached: sqlite
2011-11-08 12:06 Reinier Olislagers Tag Attached: library
2011-11-08 23:08 Reinier Olislagers Note Edited: 0053920
2012-01-21 22:46 Marco van de Voort Fixed in Revision => 20146
2012-01-21 22:46 Marco van de Voort Status assigned => resolved
2012-01-21 22:46 Marco van de Voort Resolution open => fixed
2012-01-21 22:46 Marco van de Voort Note Added: 0055932
2012-01-23 11:21 Reinier Olislagers Status resolved => closed