View Issue Details

IDProjectCategoryView StatusLast Update
0034274FPCDatabasepublic2019-09-10 10:46
Reporterasdf121Assigned ToMichael Van Canneyt 
PrioritynormalSeverityminorReproducibilityalways
Status resolvedResolutionfixed 
Product Version3.0.4Product Build 
Target VersionFixed in Version3.3.1 
Summary0034274: SQLite from SQLdb and Multithreading makes some trouble
DescriptionHey,
I already posted in forum (https://forum.lazarus.freepascal.org/index.php/topic,42212.0.html) but seems it's not the right place for the issue.
It's an issue with sqlite and several transaction to one connection. There is another example in my forum post on page 2 which shows this issues with using transactions for sqlite.
I cannot find anything about this in wiki, so if it's a known restriction it should be mentioned there.

The attached code is based on sqlite and using some threads.
Steps To ReproduceCompile and run the attached code.
Additional InformationSometimes it works without problems but it also fails with one of the following errors:
- EDatabaseError: No such dataset registered : ""
- EAccessViolation: Access violation
- *** Error in `main': realloc(): invalid pointer: 0x000000000262cf98 ***
- Not all threads were executed/prints the writeln line

Even the use of stoUseImplicit does not help and I also tried all kinds of variation between using those transactions.

(I've tested with 3.0.0, 3.0.2 and 3.0.4)
Tagsmultithreading, sqldb, sqlite
Fixed in Revision42972
FPCOldBugId
FPCTarget3.2.0
Attached Files
  • sqlite-multithreading.pas (2,798 bytes)
    program HelloWorld;
     
    {$MODE Delphi}
     
    uses
        cthreads, cmem, SysUtils, Classes, db, sqldb, sqlite3conn;
     
    Var
      C : TSQLConnector;
      T1 : TSQLTransaction;
    //  T1, T2 : TSQLTransaction;
    //  Q1, Q2 : TSQLQuery;
      TH1,TH2,TH3,TH4 : TThread;
     
    procedure CreateConnection();
    begin
            C := TSQLConnector.Create(nil);
            C.ConnectorType := 'SQLite3';
            C.DatabaseName := 'mydatabase.db';
           
            // add a general purpose transaction
            T1 := TSQLTransaction.Create(C);
            T1.Options := [stoUseImplicit];
            C.Transaction  := T1;
           
            C.ExecuteDirect(
            'CREATE TABLE IF NOT EXISTS USERS (U_NAME VARCHAR(255) NOT NULL)'
            );
            C.ExecuteDirect(
                    'INSERT OR IGNORE INTO USERS (U_NAME) VALUES (''hello'');'
                    );
            C.ExecuteDirect(
                    'INSERT OR IGNORE INTO USERS (U_NAME) VALUES (''world'');'
                    );
     
            // open connection/database
            C.Connected := True;
    end;
     
    procedure DoQuery();
    var
      T2 : TSQLTransaction;
      Q1 : TSQLQuery;
    begin
                    // Create a second transaction.
                    T2 := TSQLTransaction.Create(C);
                    T2.Options := [stoUseImplicit];
                    // Point to the database instance
                    T2.Database := C;
     
                    // Create a query to return data
                    Q1 := TSQLQuery.Create(C);
                    // Point to database and transaction.
                    Q1.Database := C;
                    Q1.Transaction := T2;
                    // Set the SQL select statement
                    Q1.SQL.Text := 'SELECT * FROM USERS';
                   
                    try
                            Q1.Open;
                            While not Q1.EOF do
                              begin
                              Writeln(IntTostr(TThread.CurrentThread.ThreadID) + ' -   Username is: ' + Q1.FieldByName('U_NAME').AsString);
                              Q1.Next
                              end;
                            Q1.Close;
                    except on E: Exception do
                            Writeln(E.Message);
                    end;
    end;
     
    begin
            writeln('Start!');
           
            // create SQLite3 connection + set a basic Transaction
            CreateConnection();
        try
           
              TH1:=TThread.ExecuteInThread(@DoQuery, nil);
              TH2:=TThread.ExecuteInThread(@DoQuery, nil);
              TH3:=TThread.ExecuteInThread(@DoQuery, nil);
              TH4:=TThread.ExecuteInThread(@DoQuery, nil);
              Writeln('Main thread done');
              TH1.WaitFor;
              TH2.WaitFor;
              TH3.WaitFor;
              TH4.WaitFor;
           
            finally
                    C.Free;
            end;
       
            writeln('End!');
    end.
  • sqlite-multithreading-with-threading-check.pas (3,096 bytes)
    program HelloWorld;
     
    {$MODE Delphi}
     
    uses
        cthreads, cmem, SysUtils, Classes, db, sqldb, sqlite3conn, sqlite3dyn;
     
    Var
      C : TSQLConnector;
      T1 : TSQLTransaction;
    //  T1, T2 : TSQLTransaction;
    //  Q1, Q2 : TSQLQuery;
      TH1,TH2,TH3,TH4 : TThread;
      threadsafe: integer;
     
    procedure CreateConnection();
    begin
            C := TSQLConnector.Create(nil);
            C.ConnectorType := 'SQLite3';
            C.DatabaseName := 'mydatabase.db';
           
            // add a general purpose transaction
            T1 := TSQLTransaction.Create(C);
            T1.Options := [stoUseImplicit];
            C.Transaction  := T1;
           
            C.ExecuteDirect(
            'CREATE TABLE IF NOT EXISTS USERS (U_NAME VARCHAR(255) NOT NULL)'
            );
            C.ExecuteDirect(
                    'INSERT OR IGNORE INTO USERS (U_NAME) VALUES (''hello'');'
                    );
            C.ExecuteDirect(
                    'INSERT OR IGNORE INTO USERS (U_NAME) VALUES (''world'');'
                    );
     
            // open connection/database
            C.Connected := True;
    end;
     
    procedure DoQuery();
    var
      T2 : TSQLTransaction;
      Q1 : TSQLQuery;
    begin
                    // Create a second transaction.
                    T2 := TSQLTransaction.Create(C);
                    T2.Options := [stoUseImplicit];
                    // Point to the database instance
                    T2.Database := C;
     
                    // Create a query to return data
                    Q1 := TSQLQuery.Create(C);
                    // Point to database and transaction.
                    Q1.Database := C;
                    Q1.Transaction := T2;
                    // Set the SQL select statement
                    Q1.SQL.Text := 'SELECT * FROM USERS';
                   
                    try
                            Q1.Open;
                            While not Q1.EOF do
                              begin
                              Writeln(IntTostr(TThread.CurrentThread.ThreadID) + ' -   Username is: ' + Q1.FieldByName('U_NAME').AsString);
                              Q1.Next
                              end;
                            Q1.Close;
                    except on E: Exception do
                            Writeln(E.Message);
                    end;
    end;
     
    begin
            writeln('Start!');
           
            // create SQLite3 connection + set a basic Transaction
            CreateConnection();
           
    	    threadsafe := sqlite3_threadsafe();
            if threadsafe > 0 then
                writeln('Your SQLite3 does support multithreading with mode: ' + IntToStr(threadsafe))
            else
                writeln('Sorry, no support for multithreading');
     
        try
           
              TH1:=TThread.ExecuteInThread(@DoQuery, nil);
              TH2:=TThread.ExecuteInThread(@DoQuery, nil);
              TH3:=TThread.ExecuteInThread(@DoQuery, nil);
              TH4:=TThread.ExecuteInThread(@DoQuery, nil);
              Writeln('Main thread done');
              TH1.WaitFor;
              TH2.WaitFor;
              TH3.WaitFor;
              TH4.WaitFor;
           
            finally
                    C.Free;
            end;
       
            writeln('End!');
    end.
  • external_sqlite_lib.pas (2,716 bytes)
    program HelloWorld;
     
    {$MODE Delphi}
    
    // comment / uncomment to change mode of DoQuery, uses local var for Psqlite3_stmt if defined
    {$DEFINE LVAR}
    
    uses
        {$IFNDEF MSWINDOWS}cthreads,{$ENDIF} cmem, SysUtils, Classes, slsqlite;
     
    Var
      sqlDB: TslSqliteDB = nil;
      sql_statement: Psqlite3_stmt = nil;
    
      TH1,TH2,TH3,TH4 : TThread;
      //threadsafe: integer;
     
    procedure CreateConnection();
    begin
    	// create db with pragma if wanted
        sqlDB := TslSqliteDB.Create('mydatabase.db', ''); // some other dbs use: synchronous=FULL;synchronous=OFF;
    	
    	sqlDB.ExecSQL(
          'DROP TABLE IF EXISTS USERS'
        );
    	
    	// insert some entries to our database
        sqlDB.ExecSQL(
          'CREATE TABLE IF NOT EXISTS USERS (U_NAME VARCHAR(255) NOT NULL)'
          );
        sqlDB.ExecSQL(
          'INSERT OR IGNORE INTO USERS (U_NAME) VALUES (''hello'');'
          );
    	sqlDB.ExecSQL(
          'INSERT OR IGNORE INTO USERS (U_NAME) VALUES (''world'');'
          );
    	  
    	sql_statement := sqlDB.Open('SELECT * FROM USERS');
    end;
     
    procedure DoQuery();
    {$IFDEF LVAR}
    var
    	s: Psqlite3_stmt;
    {$ENDIF}
    begin
    	{$IFDEF LVAR}
    		s := sqlDB.Open('SELECT * FROM USERS');
    	{$ELSE}
    		sqlDB.Open(sql_statement);
    	{$ENDIF}
    		
    	{$IFDEF LVAR}
    		while sqlDB.Step(s) do
    	{$ELSE}
    		while sqlDB.Step(sql_statement) do
    	{$ENDIF}
        begin
    		{$IFDEF LVAR}
    			Writeln(IntTostr(TThread.CurrentThread.ThreadID) + ' with local variable -   Username is: ' + sqlDB.column_text(s, 0));
    		{$ELSE}
    			Writeln(IntTostr(TThread.CurrentThread.ThreadID) + ' with global -   Username is: ' + sqlDB.column_text(sql_statement, 0));
    		{$ENDIF}
        end;
    end;
     
    begin
        writeln('start!');
           
    	if not slsqlite_inited then
    	begin
    		writeln('SQLite3 not initialised. Error: ' + slsqlite_error);
    		exit;
    	end
    	else
    		writeln('Using SQLite3 version: ' + slSqliteVersion);
    	   
        // create sqlite3 connection and insert some examples
        createconnection();
    
    	// not supported by this pascal lib
        //threadsafe := sqlite3_threadsafe();
        //if threadsafe > 0 then
    	//	writeln('your sqlite3 does support multithreading with mode: ' + inttostr(threadsafe))
        //else
        //    writeln('sorry, no support for multithreading');
     
        try
            th1:=tthread.executeinthread(@doquery, nil);
            th2:=tthread.executeinthread(@doquery, nil);
            th3:=tthread.executeinthread(@doquery, nil);
            th4:=tthread.executeinthread(@doquery, nil);
            writeln('main thread done');
            th1.waitfor;
            th2.waitfor;
            th3.waitfor;
            th4.waitfor;
    	except
    		on E: Exception do
    		begin
    		  writeln('Exception: ' + E.Message);
    		end;
    	end;
       
        writeln('end!');
    end.
    external_sqlite_lib.pas (2,716 bytes)
  • slsqlite.pas (32,434 bytes)
    unit slsqlite;
    
    interface
    
    
    type
    
      sqlite_int64 = int64;
      PPPChar = ^PPAnsiChar;
      Psqlite3  = Pointer;
      PPSqlite3 = ^PSqlite3;
      Psqlite3_context  = Pointer;
      Psqlite3_stmt  = Pointer;
      PPsqlite3_stmt = ^Psqlite3_stmt;
      Psqlite3_value  = Pointer;
      PPsqlite3_value  = ^Psqlite3_value;
    
     sqlite3_callback = function (_para1:pointer; _para2:longint; _para3:PPchar; _para4:PPchar):longint;cdecl;
      busy_handler_func = function (_para1:pointer; _para2:longint):longint;cdecl;
      sqlite3_set_authorizer_func = function (_para1:pointer; _para2:longint; _para3:PAnsiChar; _para4:PAnsiChar; _para5:PAnsiChar; _para6:PAnsiChar):longint;cdecl;
      sqlite3_trace_func = procedure (_para1:pointer; _para2:PAnsiChar);cdecl;
      sqlite3_progress_handler_func = function (_para1:pointer):longint;cdecl;
      sqlite3_commit_hook_func = function (_para1:pointer):longint;cdecl;
      bind_destructor_func = procedure (_para1:pointer);cdecl;
      create_function_step_func = procedure (_para1:Psqlite3_context; _para2:longint; _para3:PPsqlite3_value);cdecl;
      create_function_func_func = procedure (_para1:Psqlite3_context; _para2:longint; _para3:PPsqlite3_value);cdecl;
      create_function_final_func = procedure (_para1:Psqlite3_context);cdecl;
      sqlite3_set_auxdata_func = procedure (_para1:pointer);cdecl;
      sqlite3_result_func = procedure (_para1:pointer);cdecl;
      sqlite3_create_collation_func = function (_para1:pointer; _para2:longint; _para3:pointer; _para4:longint; _para5:pointer):longint;cdecl;
      sqlite3_collation_needed_func = procedure (_para1:pointer; _para2:Psqlite3; eTextRep:longint; _para4:PAnsiChar);cdecl;
    
    
      TslSqliteDB = class
      private
        fSQLite: Pointer;
    
        function Execute(stm: Psqlite3_stmt): boolean;
      public
        function column_count(stm: Psqlite3_stmt): Integer;
        function column_name(stm: Psqlite3_stmt; index: Integer): String;
        function column_int(stm: Psqlite3_stmt; index: Integer): Integer;
        function column_int64(stm: Psqlite3_stmt; index: Integer): Int64;
        function column_double(stm: Psqlite3_stmt; index: Integer): Double;
        function column_text(stm: Psqlite3_stmt; index: Integer): String;
    
        function Step(stm: Psqlite3_stmt): Boolean;
    
        function Open(const sql: String): Psqlite3_stmt; overload;
        function Open(const sql: String; const Args: array of const): Psqlite3_stmt; overload;
        function Open(stm: Psqlite3_stmt): Boolean; overload;
        function Open(stm: Psqlite3_stmt; const Args: array of const): Boolean; overload;
    
        function Reset(stm: PSqlite3_stmt): Boolean;
        function Bind(stm: Psqlite3_stmt; const Args: array of const): Boolean;
    
        function ExecSQL(stm: Psqlite3_stmt): Boolean; overload;
        function ExecSQL(stm: Psqlite3_stmt; const Args: array of const): Boolean; overload;
        function ExecSQL(const sql: String): Boolean; overload;
        function ExecSQL(const sql: String; const Args: array of const): Boolean; overload;
    
        function Close(var re: Psqlite3_stmt): Boolean;
    
        constructor Create(const filename: {$IFDEF UNICODE}RawByteString{$ELSE}String{$ENDIF}; pragma: String);
        destructor Destroy; override;
      end;
    
    Var
      sqlite3_close : function (_para1:Psqlite3):longint;cdecl;
      sqlite3_exec : function (_para1:Psqlite3; sql:PAnsiChar; _para3:sqlite3_callback; _para4:pointer; errmsg:PPchar):longint;cdecl;
      sqlite3_last_insert_rowid : function (_para1:Psqlite3):sqlite_int64;cdecl;
      sqlite3_changes : function (_para1:Psqlite3):longint;cdecl;
      sqlite3_total_changes : function (_para1:Psqlite3):longint;cdecl;
      sqlite3_interrupt : procedure (_para1:Psqlite3);cdecl;
      sqlite3_complete : function (sql:PAnsiChar):longint;cdecl;
      sqlite3_complete16 : function (sql:pointer):longint;cdecl;
      sqlite3_busy_handler : function (_para1:Psqlite3; _para2:busy_handler_func; _para3:pointer):longint;cdecl;
      sqlite3_busy_timeout : function (_para1:Psqlite3; ms:longint):longint;cdecl;
      sqlite3_get_table : function (_para1:Psqlite3; sql:PAnsiChar; resultp:PPPchar; nrow:Plongint; ncolumn:Plongint; errmsg:PPchar):longint;cdecl;
      sqlite3_free_table : procedure (result:PPchar);cdecl;
    // Todo: see how translate sqlite3_mprintf, sqlite3_vmprintf, sqlite3_snprintf
    //   sqlite3_mprintf : function (_para1:Pansichar; args:array of const):Pansichar;cdecl;
      sqlite3_mprintf : function (_para1:PAnsiChar):PAnsiChar;cdecl;
    //  sqlite3_vmprintf : function (_para1:Pansichar; _para2:va_list):Pansichar;cdecl;
      sqlite3_free : procedure (z:PAnsiChar);cdecl;
    //  sqlite3_snprintf : function (_para1:longint; _para2:Pansichar; _para3:Pansichar; args:array of const):Pansichar;cdecl;
      sqlite3_snprintf : function (_para1:longint; _para2:PAnsiChar; _para3:PAnsiChar):PAnsiChar;cdecl;
      sqlite3_set_authorizer : function (_para1:Psqlite3; xAuth:sqlite3_set_authorizer_func; pUserData:pointer):longint;cdecl;
      sqlite3_trace : function (_para1:Psqlite3; xTrace:sqlite3_trace_func; _para3:pointer):pointer;cdecl;
      sqlite3_progress_handler : procedure (_para1:Psqlite3; _para2:longint; _para3:sqlite3_progress_handler_func; _para4:pointer);cdecl;
      sqlite3_commit_hook : function (_para1:Psqlite3; _para2:sqlite3_commit_hook_func; _para3:pointer):pointer;cdecl;
      sqlite3_open : function (filename:PAnsiChar; ppDb:PPsqlite3):longint;cdecl;
      sqlite3_open16 : function (filename:pointer; ppDb:PPsqlite3):longint;cdecl;
      sqlite3_errcode : function (db:Psqlite3):longint;cdecl;
      sqlite3_errmsg : function (_para1:Psqlite3):PAnsiChar;cdecl;
      sqlite3_errmsg16 : function (_para1:Psqlite3):pointer;cdecl;
      sqlite3_prepare : function (db:Psqlite3; zSql:PAnsiChar; nBytes:longint; var ppStmt:Psqlite3_stmt; pzTail:PPchar):longint;cdecl;
      sqlite3_prepare16 : function (db:Psqlite3; zSql:pointer; nBytes:longint; ppStmt:PPsqlite3_stmt; pzTail:Ppointer):longint;cdecl;
      sqlite3_bind_blob : function (_para1:Psqlite3_stmt; _para2:longint; _para3:pointer; n:longint; _para5:bind_destructor_func):longint;cdecl;
      sqlite3_bind_double : function (_para1:Psqlite3_stmt; _para2:longint; _para3:double):longint;cdecl;
      sqlite3_bind_int : function (_para1:Psqlite3_stmt; _para2:longint; _para3:longint):longint;cdecl;
      sqlite3_bind_int64 : function (_para1:Psqlite3_stmt; _para2:longint; _para3:sqlite_int64):longint;cdecl;
      sqlite3_bind_null : function (_para1:Psqlite3_stmt; _para2:longint):longint;cdecl;
      sqlite3_bind_text : function (_para1:Psqlite3_stmt; _para2:longint; _para3:PAnsiChar; n:longint; _para5:bind_destructor_func):longint;cdecl;
      sqlite3_bind_text16 : function (_para1:Psqlite3_stmt; _para2:longint; _para3:pointer; _para4:longint; _para5:bind_destructor_func):longint;cdecl;
    //  sqlite3_bind_value : function (_para1:Psqlite3_stmt; _para2:longint; _para3:Psqlite3_value):longint;cdecl;
    //These overloaded functions were introduced to allow the use of SQLITE_STATIC and SQLITE_TRANSIENT
    //It's the c world man ;-)
      sqlite3_bind_blob1 : function (_para1:Psqlite3_stmt; _para2:longint; _para3:pointer; n:longint; _para5:longint):longint;cdecl;
      sqlite3_bind_text1 : function (_para1:Psqlite3_stmt; _para2:longint; _para3:PAnsiChar; n:longint; _para5:longint):longint;cdecl;
      sqlite3_bind_text161 : function (_para1:Psqlite3_stmt; _para2:longint; _para3:pointer; _para4:longint; _para5:longint):longint;cdecl;
    
      sqlite3_bind_parameter_count : function (_para1:Psqlite3_stmt):longint;cdecl;
      sqlite3_bind_parameter_name : function (_para1:Psqlite3_stmt; _para2:longint):PAnsiChar;cdecl;
      sqlite3_bind_parameter_index : function (_para1:Psqlite3_stmt; zName:PAnsiChar):longint;cdecl;
    //  sqlite3_clear_bindings : function (_para1:Psqlite3_stmt):longint;cdecl;
      sqlite3_column_count : function (pStmt:Psqlite3_stmt):longint;cdecl;
      sqlite3_column_name : function (_para1:Psqlite3_stmt; _para2:longint):PAnsiChar;cdecl;
      sqlite3_column_name16 : function (_para1:Psqlite3_stmt; _para2:longint):pointer;cdecl;
      sqlite3_column_decltype : function (_para1:Psqlite3_stmt; i:longint):PAnsiChar;cdecl;
      sqlite3_column_decltype16 : function (_para1:Psqlite3_stmt; _para2:longint):pointer;cdecl;
      sqlite3_step : function (_para1:Psqlite3_stmt):longint;cdecl;
      sqlite3_data_count : function (pStmt:Psqlite3_stmt):longint;cdecl;
    
      sqlite3_clear_bindings: function (pStmt:Psqlite3_stmt):longint;cdecl;
    
      sqlite3_column_blob : function (_para1:Psqlite3_stmt; iCol:longint):pointer;cdecl;
      sqlite3_column_bytes : function (_para1:Psqlite3_stmt; iCol:longint):longint;cdecl;
      sqlite3_column_bytes16 : function (_para1:Psqlite3_stmt; iCol:longint):longint;cdecl;
      sqlite3_column_double : function (_para1:Psqlite3_stmt; iCol:longint):double;cdecl;
      sqlite3_column_int : function (_para1:Psqlite3_stmt; iCol:longint):longint;cdecl;
      sqlite3_column_int64 : function (_para1:Psqlite3_stmt; iCol:longint):sqlite_int64;cdecl;
      sqlite3_column_text : function (_para1:Psqlite3_stmt; iCol:longint):PAnsiChar;cdecl;
      sqlite3_column_text16 : function (_para1:Psqlite3_stmt; iCol:longint):pointer;cdecl;
      sqlite3_column_type : function (_para1:Psqlite3_stmt; iCol:longint):longint;cdecl;
      sqlite3_finalize : function (pStmt:Psqlite3_stmt):longint;cdecl;
      sqlite3_reset : function (pStmt:Psqlite3_stmt):longint;cdecl;
      sqlite3_create_function : function (_para1:Psqlite3; zFunctionName:PAnsiChar; nArg:longint; eTextRep:longint; _para5:pointer;        xFunc:create_function_func_func; xStep:create_function_step_func; xFinal:create_function_final_func):longint;cdecl;
      sqlite3_create_function16 : function (_para1:Psqlite3; zFunctionName:pointer; nArg:longint; eTextRep:longint; _para5:pointer;            xFunc:create_function_func_func; xStep:create_function_step_func; xFinal:create_function_final_func):longint;cdecl;
      sqlite3_aggregate_count : function (_para1:Psqlite3_context):longint;cdecl;
      sqlite3_value_blob : function (_para1:Psqlite3_value):pointer;cdecl;
      sqlite3_value_bytes : function (_para1:Psqlite3_value):longint;cdecl;
      sqlite3_value_bytes16 : function (_para1:Psqlite3_value):longint;cdecl;
      sqlite3_value_double : function (_para1:Psqlite3_value):double;cdecl;
      sqlite3_value_int : function (_para1:Psqlite3_value):longint;cdecl;
      sqlite3_value_int64 : function (_para1:Psqlite3_value):sqlite_int64;cdecl;
      sqlite3_value_text : function (_para1:Psqlite3_value):PAnsiChar;cdecl;
      sqlite3_value_text16 : function (_para1:Psqlite3_value):pointer;cdecl;
      sqlite3_value_text16le : function (_para1:Psqlite3_value):pointer;cdecl;
      sqlite3_value_text16be : function (_para1:Psqlite3_value):pointer;cdecl;
      sqlite3_value_type : function (_para1:Psqlite3_value):longint;cdecl;
      sqlite3_aggregate_context : function (_para1:Psqlite3_context; nBytes:longint):pointer;cdecl;
      sqlite3_user_data : function (_para1:Psqlite3_context):pointer;cdecl;
      sqlite3_get_auxdata : function (_para1:Psqlite3_context; _para2:longint):pointer;cdecl;
      sqlite3_set_auxdata : procedure (_para1:Psqlite3_context; _para2:longint; _para3:pointer; _para4:sqlite3_set_auxdata_func);cdecl;
      sqlite3_result_blob : procedure (_para1:Psqlite3_context; _para2:pointer; _para3:longint; _para4:sqlite3_result_func);cdecl;
      sqlite3_result_double : procedure (_para1:Psqlite3_context; _para2:double);cdecl;
      sqlite3_result_error : procedure (_para1:Psqlite3_context; _para2:PAnsiChar; _para3:longint);cdecl;
      sqlite3_result_error16 : procedure (_para1:Psqlite3_context; _para2:pointer; _para3:longint);cdecl;
      sqlite3_result_int : procedure (_para1:Psqlite3_context; _para2:longint);cdecl;
      sqlite3_result_int64 : procedure (_para1:Psqlite3_context; _para2:sqlite_int64);cdecl;
      sqlite3_result_null : procedure (_para1:Psqlite3_context);cdecl;
      sqlite3_result_text : procedure (_para1:Psqlite3_context; _para2:PAnsiChar; _para3:longint; _para4:sqlite3_result_func);cdecl;
      sqlite3_result_text16 : procedure (_para1:Psqlite3_context; _para2:pointer; _para3:longint; _para4:sqlite3_result_func);cdecl;
      sqlite3_result_text16le : procedure (_para1:Psqlite3_context; _para2:pointer; _para3:longint; _para4:sqlite3_result_func);cdecl;
      sqlite3_result_text16be : procedure (_para1:Psqlite3_context; _para2:pointer; _para3:longint; _para4:sqlite3_result_func);cdecl;
      sqlite3_result_value : procedure (_para1:Psqlite3_context; _para2:Psqlite3_value);cdecl;
      sqlite3_create_collation : function (_para1:Psqlite3; zName:PAnsiChar; eTextRep:longint; _para4:pointer; xCompare:sqlite3_create_collation_func):longint;cdecl;
      sqlite3_create_collation16 : function (_para1:Psqlite3; zName:PAnsiChar; eTextRep:longint; _para4:pointer; xCompare:sqlite3_create_collation_func):longint;cdecl;
      sqlite3_collation_needed : function (_para1:Psqlite3; _para2:pointer; _para3:sqlite3_collation_needed_func):longint;cdecl;
      sqlite3_collation_needed16 : function (_para1:Psqlite3; _para2:pointer; _para3:sqlite3_collation_needed_func):longint;cdecl;
      sqlite3_libversion: function : PAnsiChar;cdecl;
      sqlite3_initialize: function : Longint;cdecl;
      sqlite3_shutdown: function : Longint;cdecl;
    
    // Not published functions
      sqlite3_libversion_number : Function :longint;cdecl;
    //  sqlite3_key : function (db:Psqlite3; pKey:pointer; nKey:longint):longint;cdecl;
    //  sqlite3_rekey : function (db:Psqlite3; pKey:pointer; nKey:longint):longint;cdecl;
    //  sqlite3_sleep : function (_para1:longint):longint;cdecl;
    //  sqlite3_expired : function (_para1:Psqlite3_stmt):longint;cdecl;
    //function sqlite3_global_recover:longint;cdecl;
    
    function slSqliteVersion: String;
    
    var slsqlite_inited: Boolean = False;
        slsqlite_error: String = '';
    
    implementation
    
    uses
      SysUtils,
      IdGlobal,
    {$IFDEF FPC}
      dynlibs
    {$ELSE}
      {$IFDEF MSWINDOWS}
      Windows
      {$ELSE}
      Libc
      {$ENDIF}
    {$ENDIF}
    ;
    
    const
      {$IFDEF LINUX}
      slSqlite_libsqlite_name         = 'libsqlite3.so.0'; {Do not localize}
      {$ELSE}
      slSqlite_libsqlite_name         = 'sqlite3.dll';  {Do not localize}
      {$ENDIF}
    
    const
    
      //sqlite_exec and sqlite_step return values
      SQLITE_OK         = 0;
      SQLITE_ERROR      = 1;
      SQLITE_INTERNAL   = 2;
      SQLITE_PERM       = 3;
      SQLITE_ABORT      = 4;
      SQLITE_BUSY       = 5;
      SQLITE_LOCKED     = 6;
      SQLITE_NOMEM      = 7;
      SQLITE_READONLY   = 8;
      SQLITE_INTERRUPT  = 9;
      SQLITE_IOERR      = 10;
      SQLITE_CORRUPT    = 11;
      SQLITE_NOTFOUND   = 12;
      SQLITE_FULL       = 13;
      SQLITE_CANTOPEN   = 14;
      SQLITE_PROTOCOL   = 15;
      SQLITE_EMPTY      = 16;
      SQLITE_SCHEMA     = 17;
      SQLITE_TOOBIG     = 18;
      SQLITE_CONSTRAINT = 19;
      SQLITE_MISMATCH   = 20;
      SQLITE_MISUSE     = 21;
      SQLITE_NOLFS      = 22;
      SQLITE_AUTH       = 23;
      SQLITE_FORMAT     = 24;
      SQLITE_RANGE      = 25;
      SQLITE_ROW        = 100;
      SQLITE_DONE       = 101;
    
      // values used in sqlite_set_authorizer to define what operations authorize
      SQLITE_COPY                = 0;
      SQLITE_CREATE_INDEX        = 1;
      SQLITE_CREATE_TABLE        = 2;
      SQLITE_CREATE_TEMP_INDEX   = 3;
      SQLITE_CREATE_TEMP_TABLE   = 4;
      SQLITE_CREATE_TEMP_TRIGGER = 5;
      SQLITE_CREATE_TEMP_VIEW    = 6;
      SQLITE_CREATE_TRIGGER      = 7;
      SQLITE_CREATE_VIEW         = 8;
      SQLITE_DELETE              = 9;
      SQLITE_DROP_INDEX          = 10;
      SQLITE_DROP_TABLE          = 11;
      SQLITE_DROP_TEMP_INDEX     = 12;
      SQLITE_DROP_TEMP_TABLE     = 13;
      SQLITE_DROP_TEMP_TRIGGER   = 14;
      SQLITE_DROP_TEMP_VIEW      = 15;
      SQLITE_DROP_TRIGGER        = 16;
      SQLITE_DROP_VIEW           = 17;
      SQLITE_INSERT              = 18;
      SQLITE_PRAGMA              = 19;
      SQLITE_READ                = 20;
      SQLITE_SELECT              = 21;
      SQLITE_TRANSACTION         = 22;
      SQLITE_UPDATE              =  23;
    
      //Return values of the authorizer function
      SQLITE_DENY                = 1;
      SQLITE_IGNORE              = 2;
    
      SQLITE_NUMERIC = -1;
      SQLITE_TEXT    = -2;
      SQLITE_ARGS    = -3;
    
    Const
      SQLITE_STATIC    =  0;
      SQLITE_TRANSIENT =  -1;
    var
      h_libsqlite    : Integer = 0;
    
    
    function slSqlite_LoadProc(handle: Integer; const fnName: String; var fn: Pointer): Boolean;
    var
      fceName: AnsiString;
    begin
      Result := False;
      fceName := fnName + #0;
      {$IFDEF FPC}
        fn := GetProcAddress(handle, fceName);
      {$ELSE}
        {$IFDEF UNICODE}
          fn := GetProcAddress(handle, PAnsiChar(fceName));
        {$ELSE}
          fn := GetProcAddress(handle, @fceName[1]);
        {$ENDIF}
      {$ENDIF}
    
      if fn = nil then
        slsqlite_error := 'Cannot load ' + fnName
      else
        Result := True;
    end;
    
    procedure slSqliteInit;
    begin
      if slsqlite_inited then exit;
    
    {$IFDEF FPC}
      // Workaround that is requered under Linux
      if h_libsqlite = 0 then h_libsqlite := LoadLibrary(ExtractFilePath(ParamStr(0))+slSqlite_libsqlite_name);
      if h_libsqlite = 0 then h_libsqlite := LoadLibrary(slSqlite_libsqlite_name);
      if h_libsqlite = 0 then
      begin
        slsqlite_error:= 'Couldnt load libsqlite';
        exit;
      end;
    
    {$ELSE}
      {$IFDEF LINUX}
      // Workaround that is requered under Linux
      if h_libsqlite = 0 then h_libsqlite := HMODULE(dlopen(PAnsiChar(ExtractFilePath(ParamStr(0))+slSqlite_libsqlite_name), RTLD_GLOBAL));
      if h_libsqlite = 0 then h_libsqlite := HMODULE(dlopen(slSqlite_libsqlite_name, RTLD_GLOBAL));
      if h_libsqlite = 0 then
      begin
        slsqlite_error:= 'Couldnt load libsqlite';
        exit;
      end;
    
      {$ELSE}
      if h_libsqlite = 0 then
        h_libsqlite := LoadLibrary(slSqlite_libsqlite_name);
      if h_libsqlite = 0 then
      begin
        slsqlite_error:= 'Couldnt load libsqlite: '+SysErrorMessage(GetLastError) ;
        exit;
      end;
    
      {$ENDIF}
    {$ENDIF}
    
    
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_close', @sqlite3_close) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_exec', @sqlite3_exec) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_last_insert_rowid', @sqlite3_last_insert_rowid) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_changes', @sqlite3_changes) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_total_changes', @sqlite3_total_changes) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_interrupt', @sqlite3_interrupt) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_complete', @sqlite3_complete) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_complete16', @sqlite3_complete16) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_busy_handler', @sqlite3_busy_handler) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_busy_timeout', @sqlite3_busy_timeout) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_get_table', @sqlite3_get_table) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_free_table', @sqlite3_free_table) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_mprintf', @sqlite3_mprintf) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_free', @sqlite3_free) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_snprintf', @sqlite3_snprintf) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_set_authorizer', @sqlite3_set_authorizer) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_trace', @sqlite3_trace) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_progress_handler', @sqlite3_progress_handler) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_commit_hook', @sqlite3_commit_hook) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_open', @sqlite3_open) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_open16', @sqlite3_open16) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_errcode', @sqlite3_errcode) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_errmsg', @sqlite3_errmsg) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_errmsg16', @sqlite3_errmsg16) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_prepare', @sqlite3_prepare) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_prepare16', @sqlite3_prepare16) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_bind_blob', @sqlite3_bind_blob) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_bind_double', @sqlite3_bind_double) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_bind_int', @sqlite3_bind_int) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_bind_int64', @sqlite3_bind_int64) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_bind_null', @sqlite3_bind_null) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_bind_text', @sqlite3_bind_text) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_bind_text16', @sqlite3_bind_text16) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_bind_blob', @sqlite3_bind_blob) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_bind_text', @sqlite3_bind_text1) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_bind_text16', @sqlite3_bind_text161) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_bind_parameter_count', @sqlite3_bind_parameter_count) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_bind_parameter_name', @sqlite3_bind_parameter_name) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_bind_parameter_index', @sqlite3_bind_parameter_index) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_column_count', @sqlite3_column_count) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_column_name', @sqlite3_column_name) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_column_name16', @sqlite3_column_name16) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_column_decltype', @sqlite3_column_decltype) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_column_decltype16', @sqlite3_column_decltype16) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_step', @sqlite3_step) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_clear_bindings', @sqlite3_clear_bindings) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_data_count', @sqlite3_data_count) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_column_blob', @sqlite3_column_blob) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_column_bytes', @sqlite3_column_bytes) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_column_bytes16', @sqlite3_column_bytes16) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_column_double', @sqlite3_column_double) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_column_int', @sqlite3_column_int) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_column_int64', @sqlite3_column_int64) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_column_text', @sqlite3_column_text) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_column_text16', @sqlite3_column_text16) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_column_type', @sqlite3_column_type) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_finalize', @sqlite3_finalize) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_reset', @sqlite3_reset) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_create_function', @sqlite3_create_function) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_create_function16', @sqlite3_create_function16) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_aggregate_count', @sqlite3_aggregate_count) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_value_blob', @sqlite3_value_blob) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_value_bytes', @sqlite3_value_bytes) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_value_bytes16', @sqlite3_value_bytes16) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_value_double', @sqlite3_value_double) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_value_int', @sqlite3_value_int) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_value_int64', @sqlite3_value_int64) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_value_text', @sqlite3_value_text) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_value_text16', @sqlite3_value_text16) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_value_text16le', @sqlite3_value_text16le) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_value_text16be', @sqlite3_value_text16be) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_value_type', @sqlite3_value_type) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_aggregate_context', @sqlite3_aggregate_context) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_user_data', @sqlite3_user_data) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_get_auxdata', @sqlite3_get_auxdata) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_set_auxdata', @sqlite3_set_auxdata) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_result_blob', @sqlite3_result_blob) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_result_double', @sqlite3_result_double) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_result_error', @sqlite3_result_error) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_result_error16', @sqlite3_result_error16) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_result_int', @sqlite3_result_int) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_result_int64', @sqlite3_result_int64) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_result_null', @sqlite3_result_null) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_result_text', @sqlite3_result_text) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_result_text16', @sqlite3_result_text16) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_result_text16le', @sqlite3_result_text16le) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_result_text16be', @sqlite3_result_text16be) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_result_value', @sqlite3_result_value) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_create_collation', @sqlite3_create_collation) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_create_collation16', @sqlite3_create_collation16) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_collation_needed', @sqlite3_collation_needed) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_collation_needed16', @sqlite3_collation_needed16) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_libversion', @sqlite3_libversion) then exit;
      if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_libversion_number', @sqlite3_libversion_number) then exit;
      slSqlite_LoadProc(h_libsqlite, 'sqlite3_initialize', @sqlite3_initialize);
      slSqlite_LoadProc(h_libsqlite, 'sqlite3_shutdown', @sqlite3_shutdown);
    
      if ((not Assigned(sqlite3_initialize)) or (0 = sqlite3_initialize())) then
        slsqlite_inited:= True;
    end;
    
    procedure slSqliteUnInit;
    begin
      if not slsqlite_inited then exit;
      if Assigned(sqlite3_shutdown) then
        sqlite3_shutdown();
      if h_libsqlite > 0 then FreeLibrary(h_libsqlite);
      h_libsqlite := 0;
    
      slsqlite_inited:= False;
    end;
    
    function slSqliteVersion: String;
    begin
      Result:= AnsiString(SQLite3_libVersion);
    end;
    
    
    { TslSqliteDB }
    
    function TslSqliteDB.Bind(stm: Psqlite3_stmt; const Args: array of const): Boolean;
    var i: Integer;
        s: String;
        d: Double;
    begin
      Result:= False;
      if 0 <> sqlite3_clear_bindings(stm) then exit;
    
      for I := 0 to High(Args) do
        with Args[I] do
          case VType of
            vtInteger: if 0 <> sqlite3_bind_int(stm, i+1, VInteger) then exit;
            vtInt64:   if 0 <>sqlite3_bind_int64(stm, i+1, VInt64^) then exit;
            vtBoolean: if 0 <> sqlite3_bind_int(stm, i+1, Integer(VBoolean)) then exit;
            vtString:
              begin
                s:= VString^;
                if 0 <> sqlite3_bind_text1(stm, i+1, PAnsiChar(s), length(s), SQLITE_STATIC) then exit;
              end;
            vtAnsiString:
              begin
                s:= AnsiString(VAnsiString);
                if 0 <> sqlite3_bind_text1(stm, i+1, PAnsiChar(s), length(s), SQLITE_STATIC) then exit;
              end;
            vtCurrency:
              begin
                s:= CurrToStr(VCurrency^);
                if 0 <> sqlite3_bind_text1(stm, i+1, PAnsiChar(s), length(s), SQLITE_STATIC) then exit;
              end;
            vtVariant:
              begin
                s:= AnsiString(VVariant^);
                if 0 <> sqlite3_bind_text1(stm, i+1, PAnsiChar(s), length(s), SQLITE_STATIC) then exit;
              end;
            vtChar:
              begin
                s:= VChar;
                if 0 <> sqlite3_bind_text1(stm, i+1, PAnsiChar(s), length(s), SQLITE_STATIC) then exit;
              end;
            vtObject,
            vtClass:
              begin
                s:= VObject.ClassName;
                if 0 <> sqlite3_bind_text1(stm, i+1, PAnsiChar(s), length(s), SQLITE_STATIC) then exit;
              end;
            vtPChar:
                if 0 <> sqlite3_bind_text1(stm, i+1, VPChar, StrLen(VPChar), SQLITE_STATIC) then exit;
            vtPointer:
                if 0 <> sqlite3_bind_null(stm, i+1) then exit;
            vtExtended:
              begin
                d:= VExtended^;
                if 0 <> sqlite3_bind_double(stm, i+1, d) then exit;
              end;
        end;
    
      Result:= True;
    end;
    
    constructor TslSqliteDB.Create(const filename: {$IFDEF UNICODE}RawByteString{$ELSE}String{$ENDIF}; pragma: String);
    var
      ss: String;
    begin
     if 0 <> SQLite3_Open(PAnsiChar(filename), @fSQLite) then
       raise Exception.Create('Cant open sqlite');
    
     pragma:= Trim(pragma);
     while(true)do
     begin
       ss:= Fetch(pragma, ';', True, False);
       if ss= '' then Break;
       ExecSQL('PRAGMA '+ss);
     end;
    end;
    
    destructor TslSqliteDB.Destroy;
    begin
      SQLite3_Close(fSQLite);
    
      inherited;
    end;
    
    
    
    function TslSqliteDB.ExecSQL(stm: Psqlite3_stmt;  const Args: array of const): Boolean;
    begin
    
      Result:= False;
    
      if not Reset(stm) then exit;
      if not Bind(stm, Args) then exit;
    
      if sqlite3_step(stm) <> SQLITE_DONE then exit;
    
      Result:= True;
    end;
    
    function TslSqliteDB.ExecSQL(stm: Psqlite3_stmt): Boolean;
    begin
      Result:= ExecSQL(stm, []);
    end;
    
    function TslSqliteDB.ExecSQL(const sql: String): Boolean;
    begin
      Result:= ExecSQL(sql, []);
    end;
    
    function TslSqliteDB.ExecSQL(const sql: String;
      const Args: array of const): Boolean;
    var re: Psqlite3_stmt;
    begin
      Result:= False;
    
      re:= Open(sql, args);
      if re = nil then exit;
    
      if not execute(re) then
      begin
        Close(re);
        exit;
      end;
    
      Close(re);
    
      Result:= True;
    end;
    
    function TslSqliteDB.Close(var re: Psqlite3_stmt): Boolean;
    begin
      Result:= False;
      if re <> nil then
      begin
        if 0 <> sqlite3_finalize(re) then exit;
        re:= nil;
      end;
      Result:= TRue;
    end;
    
    function TslSqliteDB.Step(stm: Psqlite3_stmt): Boolean;
    begin
      Result:= False;
      if SQLITE_ROW <> sqlite3_step(stm) then exit;
      Result:= TRue;
    end;
    
    
    
    
    function TslSqliteDB.Open(const sql: String;
      const Args: array of const): Psqlite3_stmt;
    var re: Psqlite3_stmt;
    begin
      Result:= nil;
    
      if 0 <> sqlite3_prepare(fSQLite, PAnsiChar(sql), length(sql), re, nil) then exit;
    
      if not Bind(re, Args) then
      begin
        Close(re);
        exit;
      end;
    
      Result:= re;
    end;
    
    function TslSqliteDB.Open(const sql: String): Psqlite3_stmt;
    begin
      Result:= Open(sql, []);
    end;
    
    function TslSqliteDB.column_count(stm: Psqlite3_stmt): Integer;
    begin
      Result:= sqlite3_column_count(stm);
    end;
    
    function TslSqliteDB.column_name(stm: Psqlite3_stmt; index: Integer): String;
    var c: PAnsiChar;
    begin
      Result:= '';
    
      c:= sqlite3_column_name(stm, index);
      Result:= StrPas(c);
    end;
    
    function TslSqliteDB.column_int(stm: Psqlite3_stmt; index: Integer): Integer;
    begin
      Result:= sqlite3_column_int(stm, index);
    end;
    function TslSqliteDB.column_int64(stm: Psqlite3_stmt; index: Integer): Int64;
    begin
      Result:= sqlite3_column_int64(stm, index);
    end;
    
    function TslSqliteDB.column_double(stm: Psqlite3_stmt; index: Integer): Double;
    begin
      Result:= sqlite3_column_double(stm, index);
    end;
    
    function TslSqliteDB.column_text(stm: Psqlite3_stmt; index: Integer): String;
    begin
      Result:= StrPas(sqlite3_column_text(stm, index));
    end;
    
    
    function TslSqliteDB.Execute(stm: Psqlite3_stmt): boolean;
    begin
      Result:= False;
      if sqlite3_step(stm) <> SQLITE_DONE then exit;
      REsult:= true;
    end;
    
    function TslSqliteDB.Reset(stm: PSqlite3_stmt): Boolean;
    begin
      Result:= False;
      if 0 <> sqlite3_reset(stm) then exit;
      Result:= true;
    end;
    
    function TslSqliteDB.Open(stm: Psqlite3_stmt): Boolean;
    begin
      Result:= Open(stm, []);
    end;
    
    function TslSqliteDB.Open(stm: Psqlite3_stmt;
      const Args: array of const): Boolean;
    begin
      Result:= False;
      if not Reset(stm) then exit;
      if not Bind(stm, Args) then exit;
      Result:= True;
    end;
    
    initialization
      slSqliteInit;
    finalization
      slSqliteUnInit;
    end.
    
    slsqlite.pas (32,434 bytes)

Relationships

related to 0034278 resolvedMichael Van Canneyt feature request: support for sqlite3_open_v2() in all sqlite3 components 

Activities

asdf121

2018-09-13 18:37

reporter  

sqlite-multithreading.pas (2,798 bytes)
program HelloWorld;
 
{$MODE Delphi}
 
uses
    cthreads, cmem, SysUtils, Classes, db, sqldb, sqlite3conn;
 
Var
  C : TSQLConnector;
  T1 : TSQLTransaction;
//  T1, T2 : TSQLTransaction;
//  Q1, Q2 : TSQLQuery;
  TH1,TH2,TH3,TH4 : TThread;
 
procedure CreateConnection();
begin
        C := TSQLConnector.Create(nil);
        C.ConnectorType := 'SQLite3';
        C.DatabaseName := 'mydatabase.db';
       
        // add a general purpose transaction
        T1 := TSQLTransaction.Create(C);
        T1.Options := [stoUseImplicit];
        C.Transaction  := T1;
       
        C.ExecuteDirect(
        'CREATE TABLE IF NOT EXISTS USERS (U_NAME VARCHAR(255) NOT NULL)'
        );
        C.ExecuteDirect(
                'INSERT OR IGNORE INTO USERS (U_NAME) VALUES (''hello'');'
                );
        C.ExecuteDirect(
                'INSERT OR IGNORE INTO USERS (U_NAME) VALUES (''world'');'
                );
 
        // open connection/database
        C.Connected := True;
end;
 
procedure DoQuery();
var
  T2 : TSQLTransaction;
  Q1 : TSQLQuery;
begin
                // Create a second transaction.
                T2 := TSQLTransaction.Create(C);
                T2.Options := [stoUseImplicit];
                // Point to the database instance
                T2.Database := C;
 
                // Create a query to return data
                Q1 := TSQLQuery.Create(C);
                // Point to database and transaction.
                Q1.Database := C;
                Q1.Transaction := T2;
                // Set the SQL select statement
                Q1.SQL.Text := 'SELECT * FROM USERS';
               
                try
                        Q1.Open;
                        While not Q1.EOF do
                          begin
                          Writeln(IntTostr(TThread.CurrentThread.ThreadID) + ' -   Username is: ' + Q1.FieldByName('U_NAME').AsString);
                          Q1.Next
                          end;
                        Q1.Close;
                except on E: Exception do
                        Writeln(E.Message);
                end;
end;
 
begin
        writeln('Start!');
       
        // create SQLite3 connection + set a basic Transaction
        CreateConnection();
    try
       
          TH1:=TThread.ExecuteInThread(@DoQuery, nil);
          TH2:=TThread.ExecuteInThread(@DoQuery, nil);
          TH3:=TThread.ExecuteInThread(@DoQuery, nil);
          TH4:=TThread.ExecuteInThread(@DoQuery, nil);
          Writeln('Main thread done');
          TH1.WaitFor;
          TH2.WaitFor;
          TH3.WaitFor;
          TH4.WaitFor;
       
        finally
                C.Free;
        end;
   
        writeln('End!');
end.

Thaddy de Koning

2018-09-14 08:54

reporter   ~0110730

The exception *** Error in `main': realloc(): invalid pointer: 0x000000000262cf98 *** stems from sqlite itself.
You should have followed my advice I gave on the forum. https://sqlite.org/threadsafe.html
Basically you have two options: serialized, or a sqlite built with
 -DSQLITE_THREADSAFE=2
It is very important that you first determine how your sqlite version is built.

This is not a bug in the fcl-db code, as I explained.
Good example code, btw.

Thaddy de Koning

2018-09-14 09:22

reporter   ~0110731

On second thought: The fcl-db standard components use sqlite3_open and not sqlite3_open_v2() which is needed for the multithreading control.
So to mitigate your issue - even if the error stems from sqlite itself - the sqlite components need implementation of sqlite3_open_v2() instead of sqlite3_open to support a sqlite binary compiled with threading support.

Unlike what I wrote above there are two things separate:
1) sqlite compiled for threading. This is not always the case and may differ per platform or even distribution.
2) sqlite components in FPC should use or support sqlite3_open_v2() with the correct flags.

asdf121

2018-09-14 11:33

reporter  

sqlite-multithreading-with-threading-check.pas (3,096 bytes)
program HelloWorld;
 
{$MODE Delphi}
 
uses
    cthreads, cmem, SysUtils, Classes, db, sqldb, sqlite3conn, sqlite3dyn;
 
Var
  C : TSQLConnector;
  T1 : TSQLTransaction;
//  T1, T2 : TSQLTransaction;
//  Q1, Q2 : TSQLQuery;
  TH1,TH2,TH3,TH4 : TThread;
  threadsafe: integer;
 
procedure CreateConnection();
begin
        C := TSQLConnector.Create(nil);
        C.ConnectorType := 'SQLite3';
        C.DatabaseName := 'mydatabase.db';
       
        // add a general purpose transaction
        T1 := TSQLTransaction.Create(C);
        T1.Options := [stoUseImplicit];
        C.Transaction  := T1;
       
        C.ExecuteDirect(
        'CREATE TABLE IF NOT EXISTS USERS (U_NAME VARCHAR(255) NOT NULL)'
        );
        C.ExecuteDirect(
                'INSERT OR IGNORE INTO USERS (U_NAME) VALUES (''hello'');'
                );
        C.ExecuteDirect(
                'INSERT OR IGNORE INTO USERS (U_NAME) VALUES (''world'');'
                );
 
        // open connection/database
        C.Connected := True;
end;
 
procedure DoQuery();
var
  T2 : TSQLTransaction;
  Q1 : TSQLQuery;
begin
                // Create a second transaction.
                T2 := TSQLTransaction.Create(C);
                T2.Options := [stoUseImplicit];
                // Point to the database instance
                T2.Database := C;
 
                // Create a query to return data
                Q1 := TSQLQuery.Create(C);
                // Point to database and transaction.
                Q1.Database := C;
                Q1.Transaction := T2;
                // Set the SQL select statement
                Q1.SQL.Text := 'SELECT * FROM USERS';
               
                try
                        Q1.Open;
                        While not Q1.EOF do
                          begin
                          Writeln(IntTostr(TThread.CurrentThread.ThreadID) + ' -   Username is: ' + Q1.FieldByName('U_NAME').AsString);
                          Q1.Next
                          end;
                        Q1.Close;
                except on E: Exception do
                        Writeln(E.Message);
                end;
end;
 
begin
        writeln('Start!');
       
        // create SQLite3 connection + set a basic Transaction
        CreateConnection();
       
	    threadsafe := sqlite3_threadsafe();
        if threadsafe > 0 then
            writeln('Your SQLite3 does support multithreading with mode: ' + IntToStr(threadsafe))
        else
            writeln('Sorry, no support for multithreading');
 
    try
       
          TH1:=TThread.ExecuteInThread(@DoQuery, nil);
          TH2:=TThread.ExecuteInThread(@DoQuery, nil);
          TH3:=TThread.ExecuteInThread(@DoQuery, nil);
          TH4:=TThread.ExecuteInThread(@DoQuery, nil);
          Writeln('Main thread done');
          TH1.WaitFor;
          TH2.WaitFor;
          TH3.WaitFor;
          TH4.WaitFor;
       
        finally
                C.Free;
        end;
   
        writeln('End!');
end.

asdf121

2018-09-14 11:40

reporter   ~0110739

Last edited: 2018-09-14 11:44

View 2 revisions

"If neither flag is specified or if sqlite3_open() or sqlite3_open16() are used instead of sqlite3_open_v2(), then the default mode determined by the compile-time and start-time settings is used." (https://www.sqlite.org/threadsafe.html)
and my library is compiled with threading support (some example code attached), so using sqlite3_open is not the issue here. (says that mode is 1)

Thaddy de Koning

2018-09-14 12:29

reporter   ~0110742

The mode should be selected at runtime using SQLITE_OPEN_NOMUTEX or SQLITE_OPEN_FULLMUTEX and use sqlite3_open_v2, possibly replacing all legacy sqlite3_open calls. sqlite3_open_v2() enables that. SQLITE_OPEN_FULLMUTEX is what you should use for your particular example code.
The pascal code can query sqlite for the mode (threaded/not threaded) and can subsequently use either flag.

The reason is that this prevents the FPC code from making any assumptions and set the mode explicitly. In your case the code was possibly running in SQLITE_OPEN_NOMUTEX mode which subsequently led to a fail of sqlite's reallocmem.
If the code could specify SQLITE_OPEN_FULLMUTEX the barrier would have prevented that reallocation inside the sqlite engine to fail..
As per the documentation in the former mode you have to be very careful with the connections, basically do them separately for all threads.

To summarize: the solution is as I described.

asdf121

2018-09-14 13:38

reporter   ~0110744

Last edited: 2018-09-14 13:42

View 2 revisions

I don't think your right:
2. Multi-thread. In this mode, SQLite can be safely used by multiple threads provided that no single database connection is used simultaneously in two or more threads.
3. Serialized. In serialized mode, SQLite can be safely used by multiple threads with no restriction.
The default mode is serialized.
from https://www.sqlite.org/threadsafe.html

I don't see why you should/need set any special flag with MUTEX. It should work out of the box and that's even the case with an older library I used which does directly calls to the sqlite functions without any big wrapper like SQLdb.
There I create one connection and then execute my stuff without any transaction or stuff like that. It even uses the old sqlite3_open() and there I've never had any issues as serialized mode is handling everything.

Thaddy de Koning

2018-09-14 14:37

reporter   ~0110747

Last edited: 2018-09-14 15:01

View 2 revisions

Well: your code also contains a bug:
The CreateConnection is not threadsafe and you can not get at that connection from within the other threads. You should create the connection on a per thread basis. Subsequently you can declare all vars as threadvar.
You use a scenario that is explicitly documented in the SQLITE documentation as a cause for trouble in multithreaded scenario's. The way around it is either create connections on a per thread basis (given SQLITE_OPEN_NOMUTEX) or to open the database with the SQLITE_OPEN_FULLMUTEX flag.
The first option is already possible. The second option needs implementation.
From my tests (on debian) the default mode seems to be SQLITE_OPEN_NOMUTEX which demands that you create *everything* on a per thread basis including the connection. I have tested with a modified version as well QandD using both flags and replacing sqlite3_open with sqlite3_open_v2. In that case your code works flawless with the SQLITE_OPEN_FULLMUTEX flag set on open. It also works flawless when I create the connection on a per thread basis and use threadvars.

Attached the crude example that doesn't crash

asdf121

2018-09-20 17:00

reporter  

external_sqlite_lib.pas (2,716 bytes)
program HelloWorld;
 
{$MODE Delphi}

// comment / uncomment to change mode of DoQuery, uses local var for Psqlite3_stmt if defined
{$DEFINE LVAR}

uses
    {$IFNDEF MSWINDOWS}cthreads,{$ENDIF} cmem, SysUtils, Classes, slsqlite;
 
Var
  sqlDB: TslSqliteDB = nil;
  sql_statement: Psqlite3_stmt = nil;

  TH1,TH2,TH3,TH4 : TThread;
  //threadsafe: integer;
 
procedure CreateConnection();
begin
	// create db with pragma if wanted
    sqlDB := TslSqliteDB.Create('mydatabase.db', ''); // some other dbs use: synchronous=FULL;synchronous=OFF;
	
	sqlDB.ExecSQL(
      'DROP TABLE IF EXISTS USERS'
    );
	
	// insert some entries to our database
    sqlDB.ExecSQL(
      'CREATE TABLE IF NOT EXISTS USERS (U_NAME VARCHAR(255) NOT NULL)'
      );
    sqlDB.ExecSQL(
      'INSERT OR IGNORE INTO USERS (U_NAME) VALUES (''hello'');'
      );
	sqlDB.ExecSQL(
      'INSERT OR IGNORE INTO USERS (U_NAME) VALUES (''world'');'
      );
	  
	sql_statement := sqlDB.Open('SELECT * FROM USERS');
end;
 
procedure DoQuery();
{$IFDEF LVAR}
var
	s: Psqlite3_stmt;
{$ENDIF}
begin
	{$IFDEF LVAR}
		s := sqlDB.Open('SELECT * FROM USERS');
	{$ELSE}
		sqlDB.Open(sql_statement);
	{$ENDIF}
		
	{$IFDEF LVAR}
		while sqlDB.Step(s) do
	{$ELSE}
		while sqlDB.Step(sql_statement) do
	{$ENDIF}
    begin
		{$IFDEF LVAR}
			Writeln(IntTostr(TThread.CurrentThread.ThreadID) + ' with local variable -   Username is: ' + sqlDB.column_text(s, 0));
		{$ELSE}
			Writeln(IntTostr(TThread.CurrentThread.ThreadID) + ' with global -   Username is: ' + sqlDB.column_text(sql_statement, 0));
		{$ENDIF}
    end;
end;
 
begin
    writeln('start!');
       
	if not slsqlite_inited then
	begin
		writeln('SQLite3 not initialised. Error: ' + slsqlite_error);
		exit;
	end
	else
		writeln('Using SQLite3 version: ' + slSqliteVersion);
	   
    // create sqlite3 connection and insert some examples
    createconnection();

	// not supported by this pascal lib
    //threadsafe := sqlite3_threadsafe();
    //if threadsafe > 0 then
	//	writeln('your sqlite3 does support multithreading with mode: ' + inttostr(threadsafe))
    //else
    //    writeln('sorry, no support for multithreading');
 
    try
        th1:=tthread.executeinthread(@doquery, nil);
        th2:=tthread.executeinthread(@doquery, nil);
        th3:=tthread.executeinthread(@doquery, nil);
        th4:=tthread.executeinthread(@doquery, nil);
        writeln('main thread done');
        th1.waitfor;
        th2.waitfor;
        th3.waitfor;
        th4.waitfor;
	except
		on E: Exception do
		begin
		  writeln('Exception: ' + E.Message);
		end;
	end;
   
    writeln('end!');
end.
external_sqlite_lib.pas (2,716 bytes)

asdf121

2018-09-20 17:01

reporter  

slsqlite.pas (32,434 bytes)
unit slsqlite;

interface


type

  sqlite_int64 = int64;
  PPPChar = ^PPAnsiChar;
  Psqlite3  = Pointer;
  PPSqlite3 = ^PSqlite3;
  Psqlite3_context  = Pointer;
  Psqlite3_stmt  = Pointer;
  PPsqlite3_stmt = ^Psqlite3_stmt;
  Psqlite3_value  = Pointer;
  PPsqlite3_value  = ^Psqlite3_value;

 sqlite3_callback = function (_para1:pointer; _para2:longint; _para3:PPchar; _para4:PPchar):longint;cdecl;
  busy_handler_func = function (_para1:pointer; _para2:longint):longint;cdecl;
  sqlite3_set_authorizer_func = function (_para1:pointer; _para2:longint; _para3:PAnsiChar; _para4:PAnsiChar; _para5:PAnsiChar; _para6:PAnsiChar):longint;cdecl;
  sqlite3_trace_func = procedure (_para1:pointer; _para2:PAnsiChar);cdecl;
  sqlite3_progress_handler_func = function (_para1:pointer):longint;cdecl;
  sqlite3_commit_hook_func = function (_para1:pointer):longint;cdecl;
  bind_destructor_func = procedure (_para1:pointer);cdecl;
  create_function_step_func = procedure (_para1:Psqlite3_context; _para2:longint; _para3:PPsqlite3_value);cdecl;
  create_function_func_func = procedure (_para1:Psqlite3_context; _para2:longint; _para3:PPsqlite3_value);cdecl;
  create_function_final_func = procedure (_para1:Psqlite3_context);cdecl;
  sqlite3_set_auxdata_func = procedure (_para1:pointer);cdecl;
  sqlite3_result_func = procedure (_para1:pointer);cdecl;
  sqlite3_create_collation_func = function (_para1:pointer; _para2:longint; _para3:pointer; _para4:longint; _para5:pointer):longint;cdecl;
  sqlite3_collation_needed_func = procedure (_para1:pointer; _para2:Psqlite3; eTextRep:longint; _para4:PAnsiChar);cdecl;


  TslSqliteDB = class
  private
    fSQLite: Pointer;

    function Execute(stm: Psqlite3_stmt): boolean;
  public
    function column_count(stm: Psqlite3_stmt): Integer;
    function column_name(stm: Psqlite3_stmt; index: Integer): String;
    function column_int(stm: Psqlite3_stmt; index: Integer): Integer;
    function column_int64(stm: Psqlite3_stmt; index: Integer): Int64;
    function column_double(stm: Psqlite3_stmt; index: Integer): Double;
    function column_text(stm: Psqlite3_stmt; index: Integer): String;

    function Step(stm: Psqlite3_stmt): Boolean;

    function Open(const sql: String): Psqlite3_stmt; overload;
    function Open(const sql: String; const Args: array of const): Psqlite3_stmt; overload;
    function Open(stm: Psqlite3_stmt): Boolean; overload;
    function Open(stm: Psqlite3_stmt; const Args: array of const): Boolean; overload;

    function Reset(stm: PSqlite3_stmt): Boolean;
    function Bind(stm: Psqlite3_stmt; const Args: array of const): Boolean;

    function ExecSQL(stm: Psqlite3_stmt): Boolean; overload;
    function ExecSQL(stm: Psqlite3_stmt; const Args: array of const): Boolean; overload;
    function ExecSQL(const sql: String): Boolean; overload;
    function ExecSQL(const sql: String; const Args: array of const): Boolean; overload;

    function Close(var re: Psqlite3_stmt): Boolean;

    constructor Create(const filename: {$IFDEF UNICODE}RawByteString{$ELSE}String{$ENDIF}; pragma: String);
    destructor Destroy; override;
  end;

Var
  sqlite3_close : function (_para1:Psqlite3):longint;cdecl;
  sqlite3_exec : function (_para1:Psqlite3; sql:PAnsiChar; _para3:sqlite3_callback; _para4:pointer; errmsg:PPchar):longint;cdecl;
  sqlite3_last_insert_rowid : function (_para1:Psqlite3):sqlite_int64;cdecl;
  sqlite3_changes : function (_para1:Psqlite3):longint;cdecl;
  sqlite3_total_changes : function (_para1:Psqlite3):longint;cdecl;
  sqlite3_interrupt : procedure (_para1:Psqlite3);cdecl;
  sqlite3_complete : function (sql:PAnsiChar):longint;cdecl;
  sqlite3_complete16 : function (sql:pointer):longint;cdecl;
  sqlite3_busy_handler : function (_para1:Psqlite3; _para2:busy_handler_func; _para3:pointer):longint;cdecl;
  sqlite3_busy_timeout : function (_para1:Psqlite3; ms:longint):longint;cdecl;
  sqlite3_get_table : function (_para1:Psqlite3; sql:PAnsiChar; resultp:PPPchar; nrow:Plongint; ncolumn:Plongint; errmsg:PPchar):longint;cdecl;
  sqlite3_free_table : procedure (result:PPchar);cdecl;
// Todo: see how translate sqlite3_mprintf, sqlite3_vmprintf, sqlite3_snprintf
//   sqlite3_mprintf : function (_para1:Pansichar; args:array of const):Pansichar;cdecl;
  sqlite3_mprintf : function (_para1:PAnsiChar):PAnsiChar;cdecl;
//  sqlite3_vmprintf : function (_para1:Pansichar; _para2:va_list):Pansichar;cdecl;
  sqlite3_free : procedure (z:PAnsiChar);cdecl;
//  sqlite3_snprintf : function (_para1:longint; _para2:Pansichar; _para3:Pansichar; args:array of const):Pansichar;cdecl;
  sqlite3_snprintf : function (_para1:longint; _para2:PAnsiChar; _para3:PAnsiChar):PAnsiChar;cdecl;
  sqlite3_set_authorizer : function (_para1:Psqlite3; xAuth:sqlite3_set_authorizer_func; pUserData:pointer):longint;cdecl;
  sqlite3_trace : function (_para1:Psqlite3; xTrace:sqlite3_trace_func; _para3:pointer):pointer;cdecl;
  sqlite3_progress_handler : procedure (_para1:Psqlite3; _para2:longint; _para3:sqlite3_progress_handler_func; _para4:pointer);cdecl;
  sqlite3_commit_hook : function (_para1:Psqlite3; _para2:sqlite3_commit_hook_func; _para3:pointer):pointer;cdecl;
  sqlite3_open : function (filename:PAnsiChar; ppDb:PPsqlite3):longint;cdecl;
  sqlite3_open16 : function (filename:pointer; ppDb:PPsqlite3):longint;cdecl;
  sqlite3_errcode : function (db:Psqlite3):longint;cdecl;
  sqlite3_errmsg : function (_para1:Psqlite3):PAnsiChar;cdecl;
  sqlite3_errmsg16 : function (_para1:Psqlite3):pointer;cdecl;
  sqlite3_prepare : function (db:Psqlite3; zSql:PAnsiChar; nBytes:longint; var ppStmt:Psqlite3_stmt; pzTail:PPchar):longint;cdecl;
  sqlite3_prepare16 : function (db:Psqlite3; zSql:pointer; nBytes:longint; ppStmt:PPsqlite3_stmt; pzTail:Ppointer):longint;cdecl;
  sqlite3_bind_blob : function (_para1:Psqlite3_stmt; _para2:longint; _para3:pointer; n:longint; _para5:bind_destructor_func):longint;cdecl;
  sqlite3_bind_double : function (_para1:Psqlite3_stmt; _para2:longint; _para3:double):longint;cdecl;
  sqlite3_bind_int : function (_para1:Psqlite3_stmt; _para2:longint; _para3:longint):longint;cdecl;
  sqlite3_bind_int64 : function (_para1:Psqlite3_stmt; _para2:longint; _para3:sqlite_int64):longint;cdecl;
  sqlite3_bind_null : function (_para1:Psqlite3_stmt; _para2:longint):longint;cdecl;
  sqlite3_bind_text : function (_para1:Psqlite3_stmt; _para2:longint; _para3:PAnsiChar; n:longint; _para5:bind_destructor_func):longint;cdecl;
  sqlite3_bind_text16 : function (_para1:Psqlite3_stmt; _para2:longint; _para3:pointer; _para4:longint; _para5:bind_destructor_func):longint;cdecl;
//  sqlite3_bind_value : function (_para1:Psqlite3_stmt; _para2:longint; _para3:Psqlite3_value):longint;cdecl;
//These overloaded functions were introduced to allow the use of SQLITE_STATIC and SQLITE_TRANSIENT
//It's the c world man ;-)
  sqlite3_bind_blob1 : function (_para1:Psqlite3_stmt; _para2:longint; _para3:pointer; n:longint; _para5:longint):longint;cdecl;
  sqlite3_bind_text1 : function (_para1:Psqlite3_stmt; _para2:longint; _para3:PAnsiChar; n:longint; _para5:longint):longint;cdecl;
  sqlite3_bind_text161 : function (_para1:Psqlite3_stmt; _para2:longint; _para3:pointer; _para4:longint; _para5:longint):longint;cdecl;

  sqlite3_bind_parameter_count : function (_para1:Psqlite3_stmt):longint;cdecl;
  sqlite3_bind_parameter_name : function (_para1:Psqlite3_stmt; _para2:longint):PAnsiChar;cdecl;
  sqlite3_bind_parameter_index : function (_para1:Psqlite3_stmt; zName:PAnsiChar):longint;cdecl;
//  sqlite3_clear_bindings : function (_para1:Psqlite3_stmt):longint;cdecl;
  sqlite3_column_count : function (pStmt:Psqlite3_stmt):longint;cdecl;
  sqlite3_column_name : function (_para1:Psqlite3_stmt; _para2:longint):PAnsiChar;cdecl;
  sqlite3_column_name16 : function (_para1:Psqlite3_stmt; _para2:longint):pointer;cdecl;
  sqlite3_column_decltype : function (_para1:Psqlite3_stmt; i:longint):PAnsiChar;cdecl;
  sqlite3_column_decltype16 : function (_para1:Psqlite3_stmt; _para2:longint):pointer;cdecl;
  sqlite3_step : function (_para1:Psqlite3_stmt):longint;cdecl;
  sqlite3_data_count : function (pStmt:Psqlite3_stmt):longint;cdecl;

  sqlite3_clear_bindings: function (pStmt:Psqlite3_stmt):longint;cdecl;

  sqlite3_column_blob : function (_para1:Psqlite3_stmt; iCol:longint):pointer;cdecl;
  sqlite3_column_bytes : function (_para1:Psqlite3_stmt; iCol:longint):longint;cdecl;
  sqlite3_column_bytes16 : function (_para1:Psqlite3_stmt; iCol:longint):longint;cdecl;
  sqlite3_column_double : function (_para1:Psqlite3_stmt; iCol:longint):double;cdecl;
  sqlite3_column_int : function (_para1:Psqlite3_stmt; iCol:longint):longint;cdecl;
  sqlite3_column_int64 : function (_para1:Psqlite3_stmt; iCol:longint):sqlite_int64;cdecl;
  sqlite3_column_text : function (_para1:Psqlite3_stmt; iCol:longint):PAnsiChar;cdecl;
  sqlite3_column_text16 : function (_para1:Psqlite3_stmt; iCol:longint):pointer;cdecl;
  sqlite3_column_type : function (_para1:Psqlite3_stmt; iCol:longint):longint;cdecl;
  sqlite3_finalize : function (pStmt:Psqlite3_stmt):longint;cdecl;
  sqlite3_reset : function (pStmt:Psqlite3_stmt):longint;cdecl;
  sqlite3_create_function : function (_para1:Psqlite3; zFunctionName:PAnsiChar; nArg:longint; eTextRep:longint; _para5:pointer;        xFunc:create_function_func_func; xStep:create_function_step_func; xFinal:create_function_final_func):longint;cdecl;
  sqlite3_create_function16 : function (_para1:Psqlite3; zFunctionName:pointer; nArg:longint; eTextRep:longint; _para5:pointer;            xFunc:create_function_func_func; xStep:create_function_step_func; xFinal:create_function_final_func):longint;cdecl;
  sqlite3_aggregate_count : function (_para1:Psqlite3_context):longint;cdecl;
  sqlite3_value_blob : function (_para1:Psqlite3_value):pointer;cdecl;
  sqlite3_value_bytes : function (_para1:Psqlite3_value):longint;cdecl;
  sqlite3_value_bytes16 : function (_para1:Psqlite3_value):longint;cdecl;
  sqlite3_value_double : function (_para1:Psqlite3_value):double;cdecl;
  sqlite3_value_int : function (_para1:Psqlite3_value):longint;cdecl;
  sqlite3_value_int64 : function (_para1:Psqlite3_value):sqlite_int64;cdecl;
  sqlite3_value_text : function (_para1:Psqlite3_value):PAnsiChar;cdecl;
  sqlite3_value_text16 : function (_para1:Psqlite3_value):pointer;cdecl;
  sqlite3_value_text16le : function (_para1:Psqlite3_value):pointer;cdecl;
  sqlite3_value_text16be : function (_para1:Psqlite3_value):pointer;cdecl;
  sqlite3_value_type : function (_para1:Psqlite3_value):longint;cdecl;
  sqlite3_aggregate_context : function (_para1:Psqlite3_context; nBytes:longint):pointer;cdecl;
  sqlite3_user_data : function (_para1:Psqlite3_context):pointer;cdecl;
  sqlite3_get_auxdata : function (_para1:Psqlite3_context; _para2:longint):pointer;cdecl;
  sqlite3_set_auxdata : procedure (_para1:Psqlite3_context; _para2:longint; _para3:pointer; _para4:sqlite3_set_auxdata_func);cdecl;
  sqlite3_result_blob : procedure (_para1:Psqlite3_context; _para2:pointer; _para3:longint; _para4:sqlite3_result_func);cdecl;
  sqlite3_result_double : procedure (_para1:Psqlite3_context; _para2:double);cdecl;
  sqlite3_result_error : procedure (_para1:Psqlite3_context; _para2:PAnsiChar; _para3:longint);cdecl;
  sqlite3_result_error16 : procedure (_para1:Psqlite3_context; _para2:pointer; _para3:longint);cdecl;
  sqlite3_result_int : procedure (_para1:Psqlite3_context; _para2:longint);cdecl;
  sqlite3_result_int64 : procedure (_para1:Psqlite3_context; _para2:sqlite_int64);cdecl;
  sqlite3_result_null : procedure (_para1:Psqlite3_context);cdecl;
  sqlite3_result_text : procedure (_para1:Psqlite3_context; _para2:PAnsiChar; _para3:longint; _para4:sqlite3_result_func);cdecl;
  sqlite3_result_text16 : procedure (_para1:Psqlite3_context; _para2:pointer; _para3:longint; _para4:sqlite3_result_func);cdecl;
  sqlite3_result_text16le : procedure (_para1:Psqlite3_context; _para2:pointer; _para3:longint; _para4:sqlite3_result_func);cdecl;
  sqlite3_result_text16be : procedure (_para1:Psqlite3_context; _para2:pointer; _para3:longint; _para4:sqlite3_result_func);cdecl;
  sqlite3_result_value : procedure (_para1:Psqlite3_context; _para2:Psqlite3_value);cdecl;
  sqlite3_create_collation : function (_para1:Psqlite3; zName:PAnsiChar; eTextRep:longint; _para4:pointer; xCompare:sqlite3_create_collation_func):longint;cdecl;
  sqlite3_create_collation16 : function (_para1:Psqlite3; zName:PAnsiChar; eTextRep:longint; _para4:pointer; xCompare:sqlite3_create_collation_func):longint;cdecl;
  sqlite3_collation_needed : function (_para1:Psqlite3; _para2:pointer; _para3:sqlite3_collation_needed_func):longint;cdecl;
  sqlite3_collation_needed16 : function (_para1:Psqlite3; _para2:pointer; _para3:sqlite3_collation_needed_func):longint;cdecl;
  sqlite3_libversion: function : PAnsiChar;cdecl;
  sqlite3_initialize: function : Longint;cdecl;
  sqlite3_shutdown: function : Longint;cdecl;

// Not published functions
  sqlite3_libversion_number : Function :longint;cdecl;
//  sqlite3_key : function (db:Psqlite3; pKey:pointer; nKey:longint):longint;cdecl;
//  sqlite3_rekey : function (db:Psqlite3; pKey:pointer; nKey:longint):longint;cdecl;
//  sqlite3_sleep : function (_para1:longint):longint;cdecl;
//  sqlite3_expired : function (_para1:Psqlite3_stmt):longint;cdecl;
//function sqlite3_global_recover:longint;cdecl;

function slSqliteVersion: String;

var slsqlite_inited: Boolean = False;
    slsqlite_error: String = '';

implementation

uses
  SysUtils,
  IdGlobal,
{$IFDEF FPC}
  dynlibs
{$ELSE}
  {$IFDEF MSWINDOWS}
  Windows
  {$ELSE}
  Libc
  {$ENDIF}
{$ENDIF}
;

const
  {$IFDEF LINUX}
  slSqlite_libsqlite_name         = 'libsqlite3.so.0'; {Do not localize}
  {$ELSE}
  slSqlite_libsqlite_name         = 'sqlite3.dll';  {Do not localize}
  {$ENDIF}

const

  //sqlite_exec and sqlite_step return values
  SQLITE_OK         = 0;
  SQLITE_ERROR      = 1;
  SQLITE_INTERNAL   = 2;
  SQLITE_PERM       = 3;
  SQLITE_ABORT      = 4;
  SQLITE_BUSY       = 5;
  SQLITE_LOCKED     = 6;
  SQLITE_NOMEM      = 7;
  SQLITE_READONLY   = 8;
  SQLITE_INTERRUPT  = 9;
  SQLITE_IOERR      = 10;
  SQLITE_CORRUPT    = 11;
  SQLITE_NOTFOUND   = 12;
  SQLITE_FULL       = 13;
  SQLITE_CANTOPEN   = 14;
  SQLITE_PROTOCOL   = 15;
  SQLITE_EMPTY      = 16;
  SQLITE_SCHEMA     = 17;
  SQLITE_TOOBIG     = 18;
  SQLITE_CONSTRAINT = 19;
  SQLITE_MISMATCH   = 20;
  SQLITE_MISUSE     = 21;
  SQLITE_NOLFS      = 22;
  SQLITE_AUTH       = 23;
  SQLITE_FORMAT     = 24;
  SQLITE_RANGE      = 25;
  SQLITE_ROW        = 100;
  SQLITE_DONE       = 101;

  // values used in sqlite_set_authorizer to define what operations authorize
  SQLITE_COPY                = 0;
  SQLITE_CREATE_INDEX        = 1;
  SQLITE_CREATE_TABLE        = 2;
  SQLITE_CREATE_TEMP_INDEX   = 3;
  SQLITE_CREATE_TEMP_TABLE   = 4;
  SQLITE_CREATE_TEMP_TRIGGER = 5;
  SQLITE_CREATE_TEMP_VIEW    = 6;
  SQLITE_CREATE_TRIGGER      = 7;
  SQLITE_CREATE_VIEW         = 8;
  SQLITE_DELETE              = 9;
  SQLITE_DROP_INDEX          = 10;
  SQLITE_DROP_TABLE          = 11;
  SQLITE_DROP_TEMP_INDEX     = 12;
  SQLITE_DROP_TEMP_TABLE     = 13;
  SQLITE_DROP_TEMP_TRIGGER   = 14;
  SQLITE_DROP_TEMP_VIEW      = 15;
  SQLITE_DROP_TRIGGER        = 16;
  SQLITE_DROP_VIEW           = 17;
  SQLITE_INSERT              = 18;
  SQLITE_PRAGMA              = 19;
  SQLITE_READ                = 20;
  SQLITE_SELECT              = 21;
  SQLITE_TRANSACTION         = 22;
  SQLITE_UPDATE              =  23;

  //Return values of the authorizer function
  SQLITE_DENY                = 1;
  SQLITE_IGNORE              = 2;

  SQLITE_NUMERIC = -1;
  SQLITE_TEXT    = -2;
  SQLITE_ARGS    = -3;

Const
  SQLITE_STATIC    =  0;
  SQLITE_TRANSIENT =  -1;
var
  h_libsqlite    : Integer = 0;


function slSqlite_LoadProc(handle: Integer; const fnName: String; var fn: Pointer): Boolean;
var
  fceName: AnsiString;
begin
  Result := False;
  fceName := fnName + #0;
  {$IFDEF FPC}
    fn := GetProcAddress(handle, fceName);
  {$ELSE}
    {$IFDEF UNICODE}
      fn := GetProcAddress(handle, PAnsiChar(fceName));
    {$ELSE}
      fn := GetProcAddress(handle, @fceName[1]);
    {$ENDIF}
  {$ENDIF}

  if fn = nil then
    slsqlite_error := 'Cannot load ' + fnName
  else
    Result := True;
end;

procedure slSqliteInit;
begin
  if slsqlite_inited then exit;

{$IFDEF FPC}
  // Workaround that is requered under Linux
  if h_libsqlite = 0 then h_libsqlite := LoadLibrary(ExtractFilePath(ParamStr(0))+slSqlite_libsqlite_name);
  if h_libsqlite = 0 then h_libsqlite := LoadLibrary(slSqlite_libsqlite_name);
  if h_libsqlite = 0 then
  begin
    slsqlite_error:= 'Couldnt load libsqlite';
    exit;
  end;

{$ELSE}
  {$IFDEF LINUX}
  // Workaround that is requered under Linux
  if h_libsqlite = 0 then h_libsqlite := HMODULE(dlopen(PAnsiChar(ExtractFilePath(ParamStr(0))+slSqlite_libsqlite_name), RTLD_GLOBAL));
  if h_libsqlite = 0 then h_libsqlite := HMODULE(dlopen(slSqlite_libsqlite_name, RTLD_GLOBAL));
  if h_libsqlite = 0 then
  begin
    slsqlite_error:= 'Couldnt load libsqlite';
    exit;
  end;

  {$ELSE}
  if h_libsqlite = 0 then
    h_libsqlite := LoadLibrary(slSqlite_libsqlite_name);
  if h_libsqlite = 0 then
  begin
    slsqlite_error:= 'Couldnt load libsqlite: '+SysErrorMessage(GetLastError) ;
    exit;
  end;

  {$ENDIF}
{$ENDIF}


  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_close', @sqlite3_close) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_exec', @sqlite3_exec) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_last_insert_rowid', @sqlite3_last_insert_rowid) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_changes', @sqlite3_changes) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_total_changes', @sqlite3_total_changes) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_interrupt', @sqlite3_interrupt) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_complete', @sqlite3_complete) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_complete16', @sqlite3_complete16) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_busy_handler', @sqlite3_busy_handler) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_busy_timeout', @sqlite3_busy_timeout) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_get_table', @sqlite3_get_table) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_free_table', @sqlite3_free_table) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_mprintf', @sqlite3_mprintf) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_free', @sqlite3_free) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_snprintf', @sqlite3_snprintf) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_set_authorizer', @sqlite3_set_authorizer) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_trace', @sqlite3_trace) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_progress_handler', @sqlite3_progress_handler) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_commit_hook', @sqlite3_commit_hook) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_open', @sqlite3_open) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_open16', @sqlite3_open16) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_errcode', @sqlite3_errcode) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_errmsg', @sqlite3_errmsg) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_errmsg16', @sqlite3_errmsg16) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_prepare', @sqlite3_prepare) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_prepare16', @sqlite3_prepare16) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_bind_blob', @sqlite3_bind_blob) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_bind_double', @sqlite3_bind_double) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_bind_int', @sqlite3_bind_int) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_bind_int64', @sqlite3_bind_int64) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_bind_null', @sqlite3_bind_null) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_bind_text', @sqlite3_bind_text) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_bind_text16', @sqlite3_bind_text16) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_bind_blob', @sqlite3_bind_blob) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_bind_text', @sqlite3_bind_text1) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_bind_text16', @sqlite3_bind_text161) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_bind_parameter_count', @sqlite3_bind_parameter_count) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_bind_parameter_name', @sqlite3_bind_parameter_name) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_bind_parameter_index', @sqlite3_bind_parameter_index) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_column_count', @sqlite3_column_count) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_column_name', @sqlite3_column_name) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_column_name16', @sqlite3_column_name16) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_column_decltype', @sqlite3_column_decltype) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_column_decltype16', @sqlite3_column_decltype16) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_step', @sqlite3_step) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_clear_bindings', @sqlite3_clear_bindings) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_data_count', @sqlite3_data_count) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_column_blob', @sqlite3_column_blob) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_column_bytes', @sqlite3_column_bytes) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_column_bytes16', @sqlite3_column_bytes16) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_column_double', @sqlite3_column_double) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_column_int', @sqlite3_column_int) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_column_int64', @sqlite3_column_int64) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_column_text', @sqlite3_column_text) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_column_text16', @sqlite3_column_text16) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_column_type', @sqlite3_column_type) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_finalize', @sqlite3_finalize) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_reset', @sqlite3_reset) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_create_function', @sqlite3_create_function) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_create_function16', @sqlite3_create_function16) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_aggregate_count', @sqlite3_aggregate_count) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_value_blob', @sqlite3_value_blob) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_value_bytes', @sqlite3_value_bytes) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_value_bytes16', @sqlite3_value_bytes16) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_value_double', @sqlite3_value_double) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_value_int', @sqlite3_value_int) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_value_int64', @sqlite3_value_int64) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_value_text', @sqlite3_value_text) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_value_text16', @sqlite3_value_text16) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_value_text16le', @sqlite3_value_text16le) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_value_text16be', @sqlite3_value_text16be) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_value_type', @sqlite3_value_type) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_aggregate_context', @sqlite3_aggregate_context) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_user_data', @sqlite3_user_data) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_get_auxdata', @sqlite3_get_auxdata) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_set_auxdata', @sqlite3_set_auxdata) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_result_blob', @sqlite3_result_blob) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_result_double', @sqlite3_result_double) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_result_error', @sqlite3_result_error) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_result_error16', @sqlite3_result_error16) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_result_int', @sqlite3_result_int) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_result_int64', @sqlite3_result_int64) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_result_null', @sqlite3_result_null) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_result_text', @sqlite3_result_text) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_result_text16', @sqlite3_result_text16) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_result_text16le', @sqlite3_result_text16le) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_result_text16be', @sqlite3_result_text16be) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_result_value', @sqlite3_result_value) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_create_collation', @sqlite3_create_collation) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_create_collation16', @sqlite3_create_collation16) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_collation_needed', @sqlite3_collation_needed) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_collation_needed16', @sqlite3_collation_needed16) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_libversion', @sqlite3_libversion) then exit;
  if not slSqlite_LoadProc(h_libsqlite, 'sqlite3_libversion_number', @sqlite3_libversion_number) then exit;
  slSqlite_LoadProc(h_libsqlite, 'sqlite3_initialize', @sqlite3_initialize);
  slSqlite_LoadProc(h_libsqlite, 'sqlite3_shutdown', @sqlite3_shutdown);

  if ((not Assigned(sqlite3_initialize)) or (0 = sqlite3_initialize())) then
    slsqlite_inited:= True;
end;

procedure slSqliteUnInit;
begin
  if not slsqlite_inited then exit;
  if Assigned(sqlite3_shutdown) then
    sqlite3_shutdown();
  if h_libsqlite > 0 then FreeLibrary(h_libsqlite);
  h_libsqlite := 0;

  slsqlite_inited:= False;
end;

function slSqliteVersion: String;
begin
  Result:= AnsiString(SQLite3_libVersion);
end;


{ TslSqliteDB }

function TslSqliteDB.Bind(stm: Psqlite3_stmt; const Args: array of const): Boolean;
var i: Integer;
    s: String;
    d: Double;
begin
  Result:= False;
  if 0 <> sqlite3_clear_bindings(stm) then exit;

  for I := 0 to High(Args) do
    with Args[I] do
      case VType of
        vtInteger: if 0 <> sqlite3_bind_int(stm, i+1, VInteger) then exit;
        vtInt64:   if 0 <>sqlite3_bind_int64(stm, i+1, VInt64^) then exit;
        vtBoolean: if 0 <> sqlite3_bind_int(stm, i+1, Integer(VBoolean)) then exit;
        vtString:
          begin
            s:= VString^;
            if 0 <> sqlite3_bind_text1(stm, i+1, PAnsiChar(s), length(s), SQLITE_STATIC) then exit;
          end;
        vtAnsiString:
          begin
            s:= AnsiString(VAnsiString);
            if 0 <> sqlite3_bind_text1(stm, i+1, PAnsiChar(s), length(s), SQLITE_STATIC) then exit;
          end;
        vtCurrency:
          begin
            s:= CurrToStr(VCurrency^);
            if 0 <> sqlite3_bind_text1(stm, i+1, PAnsiChar(s), length(s), SQLITE_STATIC) then exit;
          end;
        vtVariant:
          begin
            s:= AnsiString(VVariant^);
            if 0 <> sqlite3_bind_text1(stm, i+1, PAnsiChar(s), length(s), SQLITE_STATIC) then exit;
          end;
        vtChar:
          begin
            s:= VChar;
            if 0 <> sqlite3_bind_text1(stm, i+1, PAnsiChar(s), length(s), SQLITE_STATIC) then exit;
          end;
        vtObject,
        vtClass:
          begin
            s:= VObject.ClassName;
            if 0 <> sqlite3_bind_text1(stm, i+1, PAnsiChar(s), length(s), SQLITE_STATIC) then exit;
          end;
        vtPChar:
            if 0 <> sqlite3_bind_text1(stm, i+1, VPChar, StrLen(VPChar), SQLITE_STATIC) then exit;
        vtPointer:
            if 0 <> sqlite3_bind_null(stm, i+1) then exit;
        vtExtended:
          begin
            d:= VExtended^;
            if 0 <> sqlite3_bind_double(stm, i+1, d) then exit;
          end;
    end;

  Result:= True;
end;

constructor TslSqliteDB.Create(const filename: {$IFDEF UNICODE}RawByteString{$ELSE}String{$ENDIF}; pragma: String);
var
  ss: String;
begin
 if 0 <> SQLite3_Open(PAnsiChar(filename), @fSQLite) then
   raise Exception.Create('Cant open sqlite');

 pragma:= Trim(pragma);
 while(true)do
 begin
   ss:= Fetch(pragma, ';', True, False);
   if ss= '' then Break;
   ExecSQL('PRAGMA '+ss);
 end;
end;

destructor TslSqliteDB.Destroy;
begin
  SQLite3_Close(fSQLite);

  inherited;
end;



function TslSqliteDB.ExecSQL(stm: Psqlite3_stmt;  const Args: array of const): Boolean;
begin

  Result:= False;

  if not Reset(stm) then exit;
  if not Bind(stm, Args) then exit;

  if sqlite3_step(stm) <> SQLITE_DONE then exit;

  Result:= True;
end;

function TslSqliteDB.ExecSQL(stm: Psqlite3_stmt): Boolean;
begin
  Result:= ExecSQL(stm, []);
end;

function TslSqliteDB.ExecSQL(const sql: String): Boolean;
begin
  Result:= ExecSQL(sql, []);
end;

function TslSqliteDB.ExecSQL(const sql: String;
  const Args: array of const): Boolean;
var re: Psqlite3_stmt;
begin
  Result:= False;

  re:= Open(sql, args);
  if re = nil then exit;

  if not execute(re) then
  begin
    Close(re);
    exit;
  end;

  Close(re);

  Result:= True;
end;

function TslSqliteDB.Close(var re: Psqlite3_stmt): Boolean;
begin
  Result:= False;
  if re <> nil then
  begin
    if 0 <> sqlite3_finalize(re) then exit;
    re:= nil;
  end;
  Result:= TRue;
end;

function TslSqliteDB.Step(stm: Psqlite3_stmt): Boolean;
begin
  Result:= False;
  if SQLITE_ROW <> sqlite3_step(stm) then exit;
  Result:= TRue;
end;




function TslSqliteDB.Open(const sql: String;
  const Args: array of const): Psqlite3_stmt;
var re: Psqlite3_stmt;
begin
  Result:= nil;

  if 0 <> sqlite3_prepare(fSQLite, PAnsiChar(sql), length(sql), re, nil) then exit;

  if not Bind(re, Args) then
  begin
    Close(re);
    exit;
  end;

  Result:= re;
end;

function TslSqliteDB.Open(const sql: String): Psqlite3_stmt;
begin
  Result:= Open(sql, []);
end;

function TslSqliteDB.column_count(stm: Psqlite3_stmt): Integer;
begin
  Result:= sqlite3_column_count(stm);
end;

function TslSqliteDB.column_name(stm: Psqlite3_stmt; index: Integer): String;
var c: PAnsiChar;
begin
  Result:= '';

  c:= sqlite3_column_name(stm, index);
  Result:= StrPas(c);
end;

function TslSqliteDB.column_int(stm: Psqlite3_stmt; index: Integer): Integer;
begin
  Result:= sqlite3_column_int(stm, index);
end;
function TslSqliteDB.column_int64(stm: Psqlite3_stmt; index: Integer): Int64;
begin
  Result:= sqlite3_column_int64(stm, index);
end;

function TslSqliteDB.column_double(stm: Psqlite3_stmt; index: Integer): Double;
begin
  Result:= sqlite3_column_double(stm, index);
end;

function TslSqliteDB.column_text(stm: Psqlite3_stmt; index: Integer): String;
begin
  Result:= StrPas(sqlite3_column_text(stm, index));
end;


function TslSqliteDB.Execute(stm: Psqlite3_stmt): boolean;
begin
  Result:= False;
  if sqlite3_step(stm) <> SQLITE_DONE then exit;
  REsult:= true;
end;

function TslSqliteDB.Reset(stm: PSqlite3_stmt): Boolean;
begin
  Result:= False;
  if 0 <> sqlite3_reset(stm) then exit;
  Result:= true;
end;

function TslSqliteDB.Open(stm: Psqlite3_stmt): Boolean;
begin
  Result:= Open(stm, []);
end;

function TslSqliteDB.Open(stm: Psqlite3_stmt;
  const Args: array of const): Boolean;
begin
  Result:= False;
  if not Reset(stm) then exit;
  if not Bind(stm, Args) then exit;
  Result:= True;
end;

initialization
  slSqliteInit;
finalization
  slSqliteUnInit;
end.
slsqlite.pas (32,434 bytes)

asdf121

2018-09-20 17:07

reporter   ~0110901

I've added another library (slsqlite) and some example code (external_sqlite_lib).
The lib does not use any of these _v2 functions from sqlite and does even not use any lockings inside it's class but it's working flawless for my tests.

To compile it you need Indy10:
fpc -MDelphi -O3 -FuIndy10\System external_sqlite_lib.pas

Michael Van Canneyt

2019-09-10 10:46

administrator   ~0118016

The problem has little to do with sqlite itself. (although there may be sqlite-specific thread-safety issues as well)

The link between database, dataset and transaction is not thread safe.
Till now, you had to use a TConnection per thread.

This is a long-known shortcoming, which I have now fixed.

Issue History

Date Modified Username Field Change
2018-09-13 18:37 asdf121 New Issue
2018-09-13 18:37 asdf121 File Added: sqlite-multithreading.pas
2018-09-13 18:38 asdf121 Tag Attached: sqldb
2018-09-13 18:38 asdf121 Tag Attached: sqlite
2018-09-13 18:38 asdf121 Tag Attached: multithreading
2018-09-14 08:54 Thaddy de Koning Note Added: 0110730
2018-09-14 09:22 Thaddy de Koning Note Added: 0110731
2018-09-14 11:33 asdf121 File Added: sqlite-multithreading-with-threading-check.pas
2018-09-14 11:40 asdf121 Note Added: 0110739
2018-09-14 11:44 asdf121 Note Edited: 0110739 View Revisions
2018-09-14 12:29 Thaddy de Koning Note Added: 0110742
2018-09-14 13:38 asdf121 Note Added: 0110744
2018-09-14 13:42 asdf121 Note Edited: 0110744 View Revisions
2018-09-14 14:37 Thaddy de Koning Note Added: 0110747
2018-09-14 15:01 Thaddy de Koning Note Edited: 0110747 View Revisions
2018-09-19 11:06 Marco van de Voort Relationship added related to 0034278
2018-09-20 17:00 asdf121 File Added: external_sqlite_lib.pas
2018-09-20 17:01 asdf121 File Added: slsqlite.pas
2018-09-20 17:07 asdf121 Note Added: 0110901
2018-10-28 15:28 Michael Van Canneyt Assigned To => Michael Van Canneyt
2018-10-28 15:28 Michael Van Canneyt Status new => assigned
2019-09-10 10:46 Michael Van Canneyt Status assigned => resolved
2019-09-10 10:46 Michael Van Canneyt Resolution open => fixed
2019-09-10 10:46 Michael Van Canneyt Fixed in Version => 3.3.1
2019-09-10 10:46 Michael Van Canneyt Fixed in Revision => 42972
2019-09-10 10:46 Michael Van Canneyt FPCTarget => 3.2.0
2019-09-10 10:46 Michael Van Canneyt Note Added: 0118016