Introduction to SQLite

Introduction

SQLite is an open-source stand-alone (ie. embedded) and very compact SQL engine. Since results are returned as a pointer to an array of strings (standard C stuff...), some wrappers have been written to ease use from VB (here's a list of wrappers for different languages):

Compiling SQLite

Windows

BloodShed Dev-C++

BloodShed contains an IDE and the open-source MinGW compiler

 

 

  1. Download and unzip the amalgamation version of the SQLite source code (everything in a single C file)
  2. Create a new project
  3. Add sqlite3.c and sqlite3.h into the project
  4. Add #include "sqlite3.h"
  5. In main(), add this:

    sqlite3 *db;
    sqlite3_open(":memory:", &db);
     
  6. See if it compiles OK

 

MS VC++ 5

For C newbies like me, here's how to setup the MS VC++ 5 compiler and compile SQLite (it seems like no one succeeded in compiling SQLite with Borland C++; Don't know if the VC++ 6 compiler has a very different IDE):

  1. Install the free Visual C++ 2005 Express, its SP, and the Platform SDK for Visual C++ Express

 

  1. File | New | Projects : Choose Win32 DLL, and give a name to your new project. It will be saved in its own subdirectory under "\Program Files\DevStudio\MyProjects\
  2. Unzip the SQLite source code into this directory (Caution: I understand that there are two versions of the source code. One is the most generic, ie. not meant for a particular OS, while one source package has been massaged, ready to be compiled by MS VC++. Choose the latter)
  3. Project | Add to project | Files, navigate to the folder where your project lives and now contains the SQLite source files, and select all its C files to have them added to your project (Note: To remove a file from a project, select it once with the mouse, and hit the DEL key. There is no option in the menu, either the application's or the pop-up's)
  4. If you have no use for TCL (a scripting language), remove tclsqlite.c
  5. Also remove shell.c (no idea why)
  6. Build | Set Active Configuration, and select "My project - Win32 Release" so we generate a plain DLL, with no debug infos
  7. If you wish to add version information so you get a Version tab when right-clicking on the DLL in Windows Explorer, select Project | Add to project | New : Resource Script. A clear-text res.rc is added to the project that you can edit to include version information (I haven't found how to have the version be incremented automatically.)
    Hit Build | Build mysqlite.dll, and right-click on the output DLL: You should have a Version tab. Note that this version info embedded by VC++ is independent from the version info returned by sqlite_libversion(), ie. don't worry about the fact that the former is a four-digit number, while the latter uses three
  8. Build | Rebuild All. You'll see plenty of warnings, but if all goes well, you should now have a DLL in a Release/ subdirectory in your project directory

If you wish to use Steve O'Hara's free VB wrapper ("VB Wrapper", ex-psvbutls32 in the Files section of the now dead SQLite mailing list over at Yahoo), just add its source file pssql.c and its export file pssql.def to your project, edit the DEF file so that the LIBRARY line matches the name of your DLL (otherwise, you'll get a warning), and build the DLL.

Note that pssql.c assumes that the SQLite source code is located in a sqlite/ subdirectory, so either create a subdirectory to host the SQLite source files, or edit this file to have all the C files in the same directory. If the DEF file is missing, you'll get ""Runtime Error 453 Can't find DLL entry point PSVBUTLS_VersionDB in mysqlite.dll" when calling any of the wrapper's exported functions.

FWIW:

Linux

  1. Download sqlite-3.5.4.tar.gz (don't know what sqlite-source-3_5_4.zip and sqlite-amalgamation-3_5_4.zip are for: Windows?)
  2. tar xzvf ./sqlite-3.5.4.tar.gz
  3. cd sqlite-3.5.4.tar.gz
  4. ./configure --disable-tcl
  5. make (The libraries will be in ./.libs/)
  6. make install (The libraries will be copied into /usr/local/lib)
  7. ldconfig -p |grep sq

Calling SQLite from C

Here's a example:

#include <stdio.h>
#include <stdlib.h>
#include <syslog.h>
#include <string.h>
#include <sqlite3.h>
 
int main(int argc, char *argv[])
{
        char line[80];
        int i;
        sqlite3 *db;
        char *zErr;
        int rc;
        char *sql;
 
        setlinebuf(stdout);
        setlinebuf(stderr);
 
        rc = sqlite3_open("/tmp/test.db", &db);
        if(rc) {
            fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
            sqlite3_close(db);
            exit(1);
        }
 
        sql = "create table episodes(id int, name text)";
        rc = sqlite3_exec(db, sql, NULL, NULL, &zErr);
        if(rc != SQLITE_OK) {
                if (zErr != NULL) {
                        fprintf(stderr, "SQL error: %s\n", zErr);
                        sqlite3_free(zErr);
                }
        }
 
        sqlite3_close(db);
        puts("SET CALLERID \"Derf <123>\"");
 
        return(EXIT_SUCCESS);
}

Here's to compile a C program with GCC:

  1. cd /tmp
  2. vim dummy.c (http://sqlite.org/quickstart.html)
  3. gcc -Wall dummy.c -o dummy -lsqlite3
  4. gcc -Wall dummy.c -o dummy -lsqlite3 -mtune=i386

SQLite and Python

See here.

SQLite and Delphi

As of May 2007, here are the widgets available to use SQLite in Delphi that under active development:

Aducom open-source Delphi/BCC SQLite components

Components

Notes

Compiling the dynamic version

This requires providing the SQLite DLL along with your Delphi application:

  1. Create a directory in which the unzip the Aducom source file
  2. Add this directory in the Delphi IDE's Library Path
  3. File > Open, and compile the runtime package asqlite3pkgD10.dpk
  4. File > Close All
  5. File > Open, and compile/install the designtime package asqlite3D100.dpk
  6. Close All
  7. Create a new project to test the Aducom components

Compiling the static version

This compiles the SQLite source code into your Delphi application, so you can ship a self-containted EXE:

  1. Download and install the free Borland's C++Builder Compiler 5.5 (if you don't want to bother creating an account, eMule)
  2. Download the SQLite source code, eg. sqlite-source-3_3_17.zip sqlite-source-3_4_1.zip no longer contains sqlite3.c. You must download eg. sqlite-amalgamation-3_5_4.zip instead. Unzip anywhere you want to compile
  3. Create the OBJ file: c:\borland\bcc55\bin\bcc32 -pc -RT- -O -w- -6 -Ic:\borland\bcc55\include -c sqlite3.c . You can rename the object file as SQLite3_3_17.obj to match the file below
  4. Download and unzip the latest ASGSQLite component, eg. "Components for SQLite3 RELEASE 2007.04.A" (free registration required)
  5. In the directory where you unzipped the ASGSQLite package, create a \OBJ sub-directory, and move the OBJ file that you compiled above into this sub-directory
  6. Edit ASGSQLite3.pas, and remove the leading dot in {.$DEFINE SQLite_Static}
  7. Next, if needed, apply the fix in ASGSQLite3.pas so that it compiles OK as a static component:

    {$IFDEF SQLite_Static}
    Var
    __HandlerPtr:Pointer;

    {$L 'OBJ\SQLite3_3_17.obj'}
    {$L 'OBJ\streams.obj'}

    //Make sure streams.obj is added a second time:
    {$L 'OBJ\initcvt.obj'}
    {$L 'OBJ\streams.obj'}
    {$L 'OBJ\scantod.obj'}

    //Add those lines, if not there already
    function _sqlite3_column_text16(hstatement: pointer; iCol: integer): PWideChar; cdecl; external;
    function _sqlite3_bind_text16(hstatement: pointer; iCol: integer; buf: pointer; n: integer; DestroyPtr: Pointer): integer; cdecl; external;
    function _sqlite3_bind_parameter_count(hstatement: pointer): integer; cdecl; external;
    {$ENDIF}
    // GPA - Static Link End


    //Edit this function to add the lines in the relevant IF section
    function TASQLite3DB.LoadLibs: boolean;
        {$IFNDEF SQLite_Static}
        ...
        {$ELSE}
        ...
        @SQLite3_Column_text16 := @_SQLite3_Column_text16;
        @SQLite3_Bind_Text16 := @_SQLite3_Bind_Text16;
        @sqlite3_bind_parameter_count := @_sqlite3_bind_parameter_count;
        Result := true;
        {$ENDIF}
  8. In the sub-directory \OBJ, unzip the required object files, eg. sqlite_3_5_2_full.zip. Apparently, it doesn't matter if they don't match the version of the SQLite OBJ file that we compiled above
  9. In the Delphi7 IDE, compile and install the design-time package asqlite3.dpk, which will also compile the runtime-package asqlite3pkg.dpk. Ignore the tons of warnings.
  10. Through the Environment Options, add this directory to the Library path, File > Close All

Update 25 July 2007: When trying to compile SQLite 3.4.1, I had the following problems:

Playing with ASQLite3

Documentation here. "The components for SQLite are quite similar to the TDatabase, TTable and TQuery components you are used to with the BDE. Only the BDE is not used now, so you don't have to deploy it."

  1. Create a new application, click on the new "Aducom SQLite3" tab, and add a ASQLite3DB icon
  2. Add a push button, and the following code:

    procedure TForm1.Button1Click(Sender: TObject);
    begin

      with ASQLite3DB1 do begin
        Database := 'mydb.db';
        DefaultDir := ExtractFileDir(Application.ExeName);
        Open;
        SQLite3_ExecSQL('create table IF NOT EXISTS products (id integer primary key, name varchar(255))');
        Close;
      end;

    end;

Here's how to perform a SELECT with just a DB and Query components:

with ASQLite3Query1 do begin
    SQL.text := 'select * from products';
    Open;
    while not eof do begin
        MyInternalVar := FieldByName('myfield').AsSomeType;
        next;
    end;
    Close;
end;

Here's a function to send a SELECT, and get results back into a dynamic array:

var
    type TDynamicStringArray = array of array of string;
 
[...]
 
//Used to run SELECT
//Note: Use var to pass the array by reference instead of by copy
function RunDB(db : string; SQLCmd : string; var OutputArray : TDynamicStringArray): Boolean; Overload;
var
  SQLiteDB : TASQLite3DB;
  SQLiteQuery : TASQLite3Query;
  I,J : Integer;
begin
  Result := False;
 
  SQLiteDB := TASQLite3DB.Create(nil);
  SQLiteQuery := TASQLite3Query.Create(nil);
  try
    SQLiteDB.Database := db;
    SQLiteDB.DefaultDir := ExtractFileDir(Application.ExeName);
    SQLiteDB.Open;
    with SQLiteQuery do begin
        Connection := SQLiteDB;
 
        SQL.text := SQLCmd;
        Open;
 
        SetLength(OutputArray,SQLiteQuery.RecordCount,SQLiteQuery.FieldCount);
 
        I := 0;
        While Not Eof do begin
          For J:= 0 to FieldCount - 1 do begin
            OutputArray[I,J] := Fields[J].AsString;
            If OutputArray[I,J] = '' then begin
              OutputArray[I,J] := '(empty)';
            end;
          end;
          Inc(I);
          next;
        end;
        Close;
    end;
    SQLiteDB.Close;
    Result := True;
  finally
    SQLiteQuery.Free;
    SQLiteDB.Free;
  end;
 
end;
 
//Used to run INPUT, UPDATE, DELETE
function RunDB(db : string; SQLCmd : string): Boolean; Overload;
[...]
end;

There are two ways to check how many fields a table contains:

with SQLiteQuery do begin
    Connection := SQLiteDB;
    SQL.text := 'select count(*) from ' + table ;
    Open;
 
    NbrOfRecs := Fields[0].AsInteger;
    //or SQLiteQuery.FieldCount;
end;

Here's how to retrieve the fieldnames of a table:

var
  FieldNames : TStringList;
  MyItem : String;
 
begin
  FieldNames := TStringList.Create;
 
  with ASQLite3DB1 do begin
    DefaultDir := ExtractFileDir(Application.ExeName);
    Database := 'db.sqlite';
    Open;
    GetFieldNames('products',FieldNames);
  end;
 
  for MyItem in FieldNames do begin
    ShowMessage(MyItem);
  end;
 
  FreeAndNil(FieldNames);

Another way to get the list of column names:

ASQLite3Query1.SQL.Text := 'SELECT * FROM mytable LIMIT 1';
ASQLite3Query1.Open;
for index := 0 to ASQLite3Query1.Fields.Count - 1 do begin
  ShowMessage(ASQLite3Query1.Fields[index].DisplayName);
end;

Here's how to add records, and retrieve/update existing records:

  With ASQLite3DB1 do begin
    DefaultDir := ExtractFileDir(Application.ExeName);
    Database := 'test.sqlite';
    CharacterEncoding := 'STANDARD';
    Open;
    SQLite3_ExecSQL('CREATE TABLE IF NOT EXISTS books (id INTEGER PRIMARY KEY, isbn VARCHAR, author VARCHAR)');
  end;
 
  with ASQLite3UpdateSQL1 do begin
    InsertSQL.Text := 'INSERT INTO books *';
    UpdateSQL.Text := 'UPDATE books * WHERE id=:id';
  end;
 
  With ASQLite3Query1 do begin
    Connection := ASQLite3DB1;
    UpdateSQL := ASQLite3UpdateSQL1;
 
    SQL.Text := 'SELECT * FROM books';
    Open;
 
    try
      Append;
      FieldByName('isbn').AsString := '123';
      FieldByName('author').AsString := 'Some author';
      Post;
 
      //Must close a dataset after making changes before reSELECTing data
      Close;
      SQL.Text := 'SELECT * FROM books WHERE id=7';
 
      SQL.Text := 'SELECT * FROM books WHERE isbn="3308720030695"';
      Open;
      ShowMessage(IntToStr(RecordCount));
 
      //If more than one record, must use First/Next to update all records;
      //Otherwise, only first row is updated
      First;
      while not Eof do begin
        Edit;
        FieldByName('author').AsString := 'Test';
        Post;
      end;
      Next;
 
      Close;
    except
      ShowMessage('Bad...');
    end;
 
    ASQLite3DB1.Close;

Here's some basic tasks to be done with an SQLite database using Aducom's SQLite connector for Delphi:

  1. Create a new VCL project, and add the following controls: ASQLite3DB, ASQLite3Query, ASQLite3UpdateSQL, DataSource, DBGrid
  2. Add the following code:

    procedure TForm1.FormCreate(Sender: TObject);
    3. begin
      With ASQLite3DB1 do begin
          DefaultDir := ExtractFileDir(Application.ExeName);
          Database := 'test.sqlite';
          CharacterEncoding := 'STANDARD';
          Open;
          SQLite3_ExecSQL('CREATE TABLE IF NOT EXISTS mytable (id INTEGER PRIMARY KEY, label VARCHAR)');
      end;
     
      With ASQLite3UpdateSQL1 do begin
        InsertSQL.Text := 'INSERT INTO mytable *';
        UpdateSQL.Text := 'UPDATE mytable SET label=:label WHERE id=:id';
      end;
     
      With ASQLite3Query1 do begin
        ASQLite3Query1.Connection := ASQLite3DB1;
        UpdateSQL := ASQLite3UpdateSQL1;
     
        SQL.Text := 'SELECT * FROM mytable';
        Open;
      end;
     
      DataSource1.DataSet := ASQLite3Query1;
      DBGrid1.DataSource := DataSource1;
    end;
     
    procedure TForm1.Button1Click(Sender: TObject);
    begin
      With ASQLite3Query1 do begin
        //Let's edit the currently-selected row in the DBGrid object
        Edit;
        FieldByName('label').AsString := 'dummy label';
     
        //Let's add a new row to the dataset
        Append;
        FieldByName('label').AsString := 'some new label';
     
        Post;
      end;
    end;
     
    procedure TForm1.FormDestroy(Sender: TObject);
    begin
      ASQLite3Query1.Close;
      ASQLite3DB1.Close;
    end;

Here's how to connect the statically-compiled Aducom's SQLite component to a DBGrid (from www.szutils.net):

  1. Create a new project, and add the following components on the form:

    ASQLite3DB1: Database=test.db, DriveDLL=(empty)
    ASQLite3Table1: Connection=ASQLite3DB1, TableName = <name of a table in the SQLite DB file>
    DataSource1: DataSet = ASQLite3Table1, DataSet.Active = True
    DBGrid1: DataSource=DataSource1
    DBNavigator1: DataSource=DataSource1
     
  2. Create two listboxes and three pushbuttons, and add the following code to list the database, the tables it contains, and the data in the selected table:

    procedure TForm1.Button1Click(Sender: TObject);
    begin
      ASQLite3DB1.ShowDatabases(ListBox1.Items);
    end;

    procedure TForm1.Button2Click(Sender: TObject);
    begin
      ASQLite3DB1.Database := ListBox1.Items[ListBox1.ItemIndex];
      ASQLite3DB1.GetTableNames(ListBox2.Items, true);
    end;

    procedure TForm1.Button3Click(Sender: TObject);
    begin
      ASQLite3Table1.Close;
      ASQLite3Table1.TableName:= ListBox2.Items[ListBox2.ItemIndex];
      ASQLite3Table1.Open
    end; 

DISQLite3

Installing DISQLite

  1. Tools > Options > Library Win32 : add \DISQLite3_Install\Source\ and \DISQLite3_Install\D11\
  2. Install the design-time components by opening \DISQLite3_Install\Source\DISQLite3_D???.dpk

After Delphi compiled and installed the package, the DISQLite3 icons will be be visible on the "Delphi Inspiration" components palette.

Upgrading DISQLite

When DISQLite is already installed, the Install icon shown when opening and compiling a package is disabled. To upgrade, either extract the new DISQLite3 files into the same folder as the previous version and replace all file and recompile, or first remove the old version through Component > Install Packages > Remove.

Not that removing icons from the Component Palette does not uninstall their packages, it just makes them invisible (have their Page property set to Hidden in Tools > Env't Options > Palette > [All] page at the very bottom). To make the icons (and the section) reappear in the Palette, select a hidden icon, and click on Show.

If you are updating DISQLite3 to a newer version and have the components already intalled, you need to recompile the package. In this case, just press "Compile". There should be no need to press "Install" next, but it does not harm either.

To connect a DB-aware control to a DISQLiteDatabase control, you must also include a TDISqlite3UniDirQuery, a TDataSetProvider, and a TClientDataSet: "The behavior of DISQLite3 is quite similar to the dbExpress TSQLQuery component. TClientDataSet is used as a local memory buffer, TDataSetProvider to resolve update from the client dataset." Check Demos\DISQLite3_World\DISQLite3_World_ClientDataSet.dpr.

TDISQLite3DataSetImporter is used to simplify data transfer from any TDataSet to DISQLite3, eg. import data received from other sources into your DISQLite3 application database (for example CVS data via a TCvsDataSet), transfer an existing application's data to DISQLite3, read flat-file data into DISQLite3 for SQL data analysis.

Information

Simple programs can still make do with only 3 functions: sqlite3_open, sqlite3_exec, and sqlite3_close.

More control over the execution of the database engine is provided using sqlite3_prepare to compile an SQLite statement into byte code and sqlite3_step to execute that bytecode.

A family of routines with names beginning with sqlite3_column... is used to extract information about the result set of a query. Many interface functions come in pairs, with both a UTF-8 and UTF-16 version. And there is a collection of routines used to implement user-defined SQL functions and user-defined text collating sequences.

DISQLite3 offers three interface layers to access the database engine:

Additionally, TDISQLite3DatasetImporter helps to transfer existing databases to DISQLite3.

Playing

To compile the samples, compile, run, and read the \DISQLite3_Install\Demos\DISQLite3_World demos.

TDISQLite3Database can be used to connect to a database, execute or prepare SQL commands.

The non-visual TDISQLite3Statement is used to actuality execute SQL commands that have been prepared with TDISQLite3Database, and display the result.

TDISQLite3UniDirQuery descents from TDataset, and as the name implies, is a unidirectional connector. If you need to connect it to DB-aware controls, you'll have to provide a DataSetProvider and a ClientDataSet. The behavior of DISQLite3 is quite similar to the dbExpress TSQLQuery component. TClientDataSet is used as a local memory buffer, TDataSetProvider to resolve update from the client dataset.

TDISQLite3DatasetImporter is used to import data from a TDataset object into a DISQLite3 database, eg. import data received from other sources into your DISQLite3 application database (for example CVS data via a TCvsDataSet), transfer an existing application's data to DISQLite3, or read flat-file data into DISQLite3 for SQL data analysis. Typical DISQLite3 applications which just work with their own, native data will have no need for TDISQLite3DataSetImporter.

Here's how to create a database, fill it with data, and read them back using the DISQLite wrapper:

Devart Unidac

ZeosLib

Here's how to install the latest ZeosLib:

  1. Copy the DB-specific required DLL's in eg. SYSTEM32
  2. Unzip the package in directory, preferable version-neutral to make upgrading easier
  3. Edit src\Zeos.inc file
  4. Launch the Delphi IDE, head for the Library path setting (eg. Tools > Options > Env't Options > Delphi Options > Library - Win32 > Library Path), and add the Zeos directory to the Library path along with the packages\<Delphi version>\build\ subdirectory
  5. File > Open, open packages\<Delphi version>\zeosdbo.groupproj, and choose Project > Compile all projects. This will compile the following components: ZCore, ZParseSQL, ZPlain, ZDbc, and ZComponent
  6. Select and compile/install ZComponentDesign which is part of zeosdbo.groupproj
  7. File > Close All without saving, create a new project, and add some ZeosLib components

Here's how to connect to an SQLite database, and read data using SELECT:

Here's how to add a record:

Here's how to update a record:

ExplainThat Delphi SQLite Wrapper

http://www.explainth.at/en/delphi/sqlite.shtml

SQLite Quick

SQLite With PHP

SQLite with Classic VB

Using Pivotal Solutions' SQLHelper

This samples lets you display the return set into either a regular ListBox, or ComponentOne's VSFlexGrid. The stand-alone VB executable (with MSVBVM50.DLL, VB5FR.DLL, PSVBUTLS32.DLL, and the VSFlexGrid ActiveX control compiled into the EXE using PEBundle) is available here).

(Update oct 2003) Steve O'Hara as compiled a new version of SQLHelper to solve the tiny bug (Err 380) when copying the result set from a SELECT into the VSFlexGrid object. Off-by-one bug? It seems like no row is returned when ignoring headers and SELECT should only return one row

Module

Option Explicit
 
Public Declare Function PSVBUTLS_OpenDB& Lib "PSVBUTLS32.DLL" (ByVal sFileName$, ByVal iMode&, ByRef sError$)
Public Declare Sub PSVBUTLS_CloseDB Lib "PSVBUTLS32.DLL" (ByVal lHandle&)
Public Declare Function PSVBUTLS_VersionDB$ Lib "PSVBUTLS32.DLL" ()
 
'OLD version, causing Err 380 when used with ComponentOne's VSFlexGrid object
Public Declare Function PSVBUTLS_ExecuteDB% Lib "PSVBUTLS32.DLL" (ByVal lHandle&, ByVal sSQL$, ByRef lRows&, ByRef lCols&, ByRef vResults As Variant, ByRef sError$)
'New version, which lets you ignore headers in a SELECT
Public Declare Function PSVBUTLS_ExecuteDB% Lib "PSVBUTLS32.DLL" (ByVal lHandle&, ByVal sSQL$, ByRef lRows&, ByRef lCols&, ByRef vResults As Variant, ByRef sError$, Optional ByVal bIgnoreFirstRow)
 
Public Declare Function PSVBUTLS_GetItem$ Lib "PSVBUTLS32.DLL" (ByVal iItem%, ByVal sSeparator$, ByVal sSource$)
Public Declare Function PSVBUTLS_SetItem$ Lib "PSVBUTLS32.DLL" (ByVal iItem%, ByVal sSeparator$, ByVal sSource$, ByVal sValue$)
Public Declare Function PSVBUTLS_Substitute$ Lib "PSVBUTLS32.DLL" (ByVal sReplace$, ByVal sWith$, ByVal sSource$, Optional ByVal vCaseSensitive As Variant)
Public Declare Function PSVBUTLS_EncryptString$ Lib "PSVBUTLS32.DLL" (ByVal sValue$)
Public Declare Function PSVBUTLS_Decryp<$ Lib "PSVBUTLS32.DLL" (ByVal sValue$)
Public Declare Function PSVBUTLS_GetDirPart$ Lib "PSVBUTLS32.DLL" (ByVal sValue$)
Public Declare Function PSVBUTLS_GetFilePart$ Lib "PSVBUTLS32.DLL" (ByVal sValue$)
Public Declare Function PSVBUTLS_RemoveAlphas$ Lib "PSVBUTLS32.DLL" (ByVal sValue$)
Public Declare Function PSVBUTLS_RemoveNonChars$ Lib "PSVBUTLS32.DLL" (ByVal sValue$)
Public Declare Function PSVBUTLS_ReplaceNonChars$ Lib "PSVBUTLS32.DLL" (ByVal sValue$, Optional ByVal vReplaceChar As Variant)
Public Declare Function PSVBUTLS_RaggedRight$ Lib "PSVBUTLS32.DLL" (ByVal sValue$, ByVal iWidth%)
Public Declare Function PSVBUTLS_GetHtmlFromRtf$ Lib "PSVBUTLS32.DLL" (ByVal sRTF$, Optional ByRef bInLine, Optional ByRef bDump, Optional ByRef bDebug)
Public Declare Function PSVBUTLS_GetSoundExCode$ Lib "PSVBUTLS32.DLL" (ByVal sWord$)
Public Declare Function PSVBUTLS_GetMetaPhoneCode$ Lib "PSVBUTLS32.DLL" (ByVal sWord$, ByRef sSecondary$)
Public Declare Function PSVBUTLS_GetEditDistance% Lib "PSVBUTLS32.DLL" (ByVal sFirst$, ByVal sSecond$)
Public Declare Function PSVBUTLS_Base64Encode$ Lib "PSVBUTLS32.DLL" (ByVal sSource$)
Public Declare Function PSVBUTLS_Base64Decode$ Lib "PSVBUTLS32.DLL" (ByVal sSource$)
Public Declare Function PSVBUTLS_QuotedEncode$ Lib "PSVBUTLS32.DLL" (ByVal sSource$)
Public Declare Function PSVBUTLS_QuotedDecode$ Lib "PSVBUTLS32.DLL" (ByVal sSource$)

Form

Option Explicit
'#Const listbox = True
 
Private Sub Command1_Click()
    'Uses Label1 to display messages, and either List1 or VSFlexGrid1 to display result set (depending on the listbox constant)
    
    Dim sBase As String
    Dim sFullBase As String
    Dim sErr As String * 256
    Dim lDB As Long
    Dim iReturn As Integer
    Dim lRows As Long
    Dim lCols As Long
    Dim lRowsTemp As Long
    Dim lColsTemp As Long
    Dim vResults() As Variant
    Dim lCounter As Long
    Dim sRow As String
    Dim vTemp As Variant
    
    'Should be in Form.Load but let's keep everything in one sample...
    VSFlexGrid1.FixedCols = 0
    
    'Display SQLite version in title bar
    Me.Caption = PSVBUTLS_VersionDB$
    
    sBase = "test.db"
    sFullBase = "c:\" & sBase
    'Get rid of current DB, if any
    If (Dir(sFullBase, 0) = sBase) Then
        Kill sFullBase
    End If
    
    'If running multiple times, empty caption beforehand
    Label1.Caption = ""
    
    Label1.Caption = Label1.Caption & "Open @ " & Time$ & vbCrLf
    lDB = PSVBUTLS_OpenDB&(sFullBase, 0&, sErr)
    
    Label1.Caption = Label1.Caption & "Create table @ " & Time$ & vbCrLf
    iReturn = PSVBUTLS_ExecuteDB%(lDB, "create table tbl1(one varchar(10), two smallint);", lRows, lCols, vResults, sErr)
 
    Label1.Caption = Label1.Caption & "Insert into @ " & Time$ & vbCrLf
    iReturn = PSVBUTLS_ExecuteDB%(lDB, "BEGIN;", lRows, lCols, vResults, sErr)
    For lCounter = 1 To 10000
        iReturn = PSVBUTLS_ExecuteDB%(lDB, "insert into tbl1 values('user " & Str$(lCounter) & "',10);", lRows, lCols, vResults, sErr)
    Next lCounter
    iReturn = PSVBUTLS_ExecuteDB%(lDB, "COMMIT;", lRows, lCols, vResults, sErr)
    
    Label1.Caption = Label1.Caption & "Select @ " & Time$ & vbCrLf
    'TRUE = Ignore headers
    If PSVBUTLS_ExecuteDB%(lDB, "select * from tbl1;", lRows, lCols, vResults, sErr,TRUE) then
        MsgBox sErr, , "SSQLite Error"
    End If
    
    Label1.Caption = Label1.Caption & "Reading set @ " & Time$ & vbCrLf
 
#If ListBox Then
    '-------------------- LISTBOX ----------------------------------
    For lRowsTemp = 0 To (UBound(vResults)-1)
        sRow = ""
        For lColsTemp = 0 To (lCols - 1)
            sRow = sRow & " | " & vResults(lRowsTemp, lColsTemp)
        Next lColsTemp
        List1.AddItem sRow
    Next lRowsTemp
    '-------------------- LISTBOX ----------------------------------
#Else
    '-------------------- VSFLEXGRID ----------------------------------
    'Warning: Forced to use a temporary variant() due to Err 380 when feeding
    'vResults directly into VSFlexGrid object
    ReDim vTemp(lRows, lCols - 1) As Variant
    
    'Headers
    For lColsTemp = 0 To (lCols - 1)
        VSFlexGrid1.TextMatrix(0, lColsTemp) = vResults(0, lColsTemp)
    Next lColsTemp
    For lRowsTemp = 1 To (UBound(vTemp))
        For lColsTemp = 0 To (lCols - 1)
            vTemp(lRowsTemp - 1, lColsTemp) = vResults(lRowsTemp, lColsTemp)
        Next lColsTemp
    Next lRowsTemp
 
    'Note: the above code is no longer needed, as SQLHelper author Steve O'Hara corrected a bug
    'A new version of this DLL should be available in the Files section of the SQLite forum on Yahoo
 
    VSFlexGrid1.BindToArray vTemp
    VSFlexGrid1.LoadArray vTemp, 0, 1
    '-------------------- VSFLEXGRID ----------------------------------
#End If
    
    Label1.Caption = Label1.Caption & "Close @ " & Time$ & vbCrLf
    PSVBUTLS_CloseDB (lDB)
 
End Sub

Checking for errors

You can run PSVBUTLS_ExecuteDB(), and loop until you get SQLITE_OK, or the user clicks on Cancel to stop trying. Useful if more than one host needs to write data into a database located on a shared network drive:

Add the following constants and function into a form's Declaration section:

Const SQLITE_OK = 0            'Successful result
Const SQLITE_ERROR = 1         ' SQL error or missing database
Const SQLITE_INTERNAL = 2      ' An internal logic error in SQLite
Const SQLITE_PERM = 3          ' Access permission denied
Const SQLITE_ABORT = 4         ' Callback routine requested an abort
Const SQLITE_BUSY = 5          ' The database file is locked
Const SQLITE_LOCKED = 6        ' A table in the database is locked
Const SQLITE_NOMEM = 7         ' A malloc() failed
Const SQLITE_READONLY = 8      ' Attempt to write a readonly database
Const SQLITE_INTERRUPT = 9     ' Operation terminated by sqlite_interrupt()
Const SQLITE_IOERR = 10        ' Some kind of disk I/O error occurred
Const SQLITE_CORRUPT = 11      ' The database disk image is malformed
Const SQLITE_NOTFOUND = 12     ' (Internal Only) Table or record not found
Const SQLITE_FULL = 13         ' Insertion failed because database is full
Const SQLITE_CANTOPEN = 14     ' Unable to open the database file
Const SQLITE_PROTOCOL = 15     ' Database lock protocol error
Const SQLITE_EMPTY = 16        ' (Internal Only) Database table is empty
Const SQLITE_SCHEMA = 17       ' The database schema changed
Const SQLITE_TOOBIG = 18       ' Too much data for one row of a table
Const SQLITE_CONSTRAINT = 19   ' Abort due to contraint violation
Const SQLITE_MISMATCH = 20     ' Data type mismatch
Const SQLITE_MISUSE = 21       ' Library used incorrectly
Const SQLITE_NOLFS = 22        ' Uses OS features not supported on host
Const SQLITE_AUTH = 23         ' Authorization denied
Const SQLITE_ROW = 100         ' sqlite_step() has another row ready
Const SQLITE_DONE = 101        ' sqlite_step() has finished executing
 
Private Function Execute(lDB As Long, sCommand As String, vResults As Variant) As Boolean
    Dim lRows As Long, lCols As Long
    Dim sErr As String
    Dim iReturn As Integer
    
    'Default return value. If we exit due to error, function returns 0/False
    Execute = True
    
    Do
        iReturn = PSVBUTLS_ExecuteDB%(lDB, sCommmand, lRows, lCols, vResults, sErr)
        Select Case iReturn
            Case SQLITE_OK ' Successful result
                Debug.Print "SQLITE_OK"
                Exit Do
            Case SQLITE_DONE ' sqlite_step() has finished executing
                Debug.Print "SQLITE_DONE"
                Exit Do
            Case SQLITE_ERROR ' SQL error or missing database
                iReturn = MsgBox("SQLITE_ERROR: Try again?", , vbOKCancel)
                If iReturn = vbCancel Then
                    PSVBUTLS_CloseDB (lDB)
                    Exit Function
                End If
            Case SQLITE_PERM ' Access permission denied
                iReturn = MsgBox("SQLITE_PERM: Try again?", , vbOKCancel)
                If iReturn = vbCancel Then
                    PSVBUTLS_CloseDB (lDB)
                    Exit Function
                End If
            Case SQLITE_BUSY ' The database file is locked
                iReturn = MsgBox("SQLITE_BUSY: Try again?", , vbOKCancel)
                If iReturn = vbCancel Then
                    PSVBUTLS_CloseDB (lDB)
                    Exit Function
                End If
            Case SQLITE_LOCKED ' A table in the database is locked
                iReturn = MsgBox("SQLITE_LOCKED: Try again?", , vbOKCancel)
                If iReturn = vbCancel Then
                    PSVBUTLS_CloseDB (lDB)
                    Exit Function
                End If
            Case SQLITE_IOERR ' Some kind of disk I/O error occurred
                iReturn = MsgBox("SQLITE_IOERR: Try again?", , vbOKCancel)
                If iReturn = vbCancel Then
                    PSVBUTLS_CloseDB (lDB)
                    Exit Function
                End If
            Case SQLITE_CORRUPT ' The database disk image is malformed
                iReturn = MsgBox("SQLITE_CORRUPT: Try again?", , vbOKCancel)
                If iReturn = vbCancel Then
                    PSVBUTLS_CloseDB (lDB)
                    Exit Function
                End If
            Case SQLITE_CANTOPEN ' Unable to open the database file
                iReturn = MsgBox("SQLITE_CANTOPEN: Try again?", , vbOKCancel)
                If iReturn = vbCancel Then
                    PSVBUTLS_CloseDB (lDB)
                    Exit Function
                End If
            Case SQLITE_MISMATCH ' Data type mismatch
                iReturn = MsgBox("SQLITE_MISMATCH: Try again?", , vbOKCancel)
                If iReturn = vbCancel Then
                    PSVBUTLS_CloseDB (lDB)
                    Exit Function
                End If
            Case SQLITE_AUTH ' Authorization denied
                iReturn = MsgBox("SQLITE_AUTH: Try again?", , vbOKCancel)
                If iReturn = vbCancel Then
                    PSVBUTLS_CloseDB (lDB)
                    Exit Function
                End If
        End Select
    Loop While True
End Function

Add a call to this function in your form:

sCommand = "create table mytable (id INTEGER PRIMARY KEY," name VARCHAR(50));"
            
'If returns False, either error or user clicked on Cancel in dialog box
If Not Execute(lDB, sCommand, vResults) Then
    Exit Sub
End If

Calling SQLite directly

The first difficulty, is that the SQLite DLL that you can download from the site has been compiled using the cdecl format, which is the standard C method to pass parameters before caller and called functions. Unfortunately, while PowerBasic lets you tell choose, VB only supports making calls with the stdcall method.

  1. So, the first task is to recompile the DLL with the stdcall format, so as to get rid of the unfamous error 49 ("Incorrect DLL calling convention")
  2. Next, the main difficulty is that SQLite calls a function to handle the output of sqlite_exec(), and expects to be provided with the pointer to this routine.

Here's some none-working code :-)

Occasionnaly, this code would GPF (OK in form, bad in module or when compiled as EXE; Maybe due to _cdecl_ instead of _stdcall_), and may present some memory leaks, so look at the alternatives above.

SQLite COM Controls

As of July 2007, the only easy-to-deploy, well-maintained COM interface to SQLite is SQLite Plus. More information on the SQLite site.

SQLite in client/server mode

SQLite was built for local use, not over a network, so that performance degrades as the database gets bigger.

Here's an idea for a basic SQLite server:

Here are the client/server solutions I checked:

SQLiting

REAL SQL Server

SQL4Sockets

uSQLiteServer

DatenHaus dhRPCServer + dhSQLite COM client

SQLite Server

SQLite Server is a free database server that is the central data store for Pro Track Source Connected. It comes with your purchase of Pro Track Source Connected, but we also offer it here for a free download in case you have lost it.

SQL Relay

"SQL Relay is a persistent database connection pooling, proxying and load balancing system for Unix and Linux."

SQLiteDBMS

SQLite_on_Sockets

TerraInformatica SQLiteDBServer

Using SELECT

https://www.sqlite.org/lang_select.html

Tips & Tricks

Importing a MySQL/MariaDB dump

On Linux, just download and run the Awk-based mysql2sqlite to convert and import the dump into an SQLite DB.

On Windows, install Cygwin, open a DOS box, run Bash, and then run mysql2sqlite:

setup-x86.exe --allow-unsupported-windows option --site http://ctm.crouchingtigerhiddenfruitbat.org/pub/cygwin/circa/2022/11/23/063457
 
c:\cygwin\bin>bash
 
./mysql2sqlite dump.sql | sqlite3 test.sql.sqlite

SELECT … LIKE

select * from zip where COD_MOD LIKE "123%";

Adding numbers in a column

CAST(SUM(MYCOL) AS INT)

Computing a percentage

SELECT round((COUNT(rowid))/(SELECT COUNT(*)*0.01 FROM people),2) FROM people WHERE zip="12345"

How to display data in Unicode in a DOS box?

How to create a modal form from a parent form?

How to define columns with internal names and beautified names?

Opening two databases with the SQLite CLI client

Listing duplicates

SELECT id,name,count(name) FROM companies GROUP BY name HAVING COUNT(name) > 1;

Counting distinct rows

SELECT COUNT (DISTINCT name) FROM members;

Removing duplicates

DELETE FROM members
WHERE ID NOT IN
(
    SELECT MIN(ID)
    FROM members
    GROUP BY name
)
Ie. for each name, it groups them (only one if unique; several into one if duplicates), selects the smallest ID from the set, and then deletes any row whose ID doesn't exist in the table.

How to SELECT all rows with any date set to eg. 2009?

In case rows have a field that contain dates formatted as YYYY-MM-DD and we wish to SELECT all rows whose year is set to 2009:

SELECT * FROM invoices,phones WHERE phones_nbr=invoices_phones_nbr AND STRFTIME('%Y',invoices_date_sent) = "2009"

How to SELECT calls from the past two weeks?

SELECT * FROM calls WHERE (julianday('now') - julianday(calls_date)) < 15

[Unix] PHP+PDO can read but can't write

Make sure the SQLite file is owned by the user under which PHP runs, and that this user or group has write access to the directory where the file lives:

[/var/www]# ll
drwxrwxr-x  2 root  www     512 Mar 24 22:12 .
-rw-rw-r--  1 root  www    3072 Mar 24 22:12 test.sqlite

What does PRIMARY KEY do?

Primary key means that the database will treat that column as the unique identifier for each row. You can only have one primary key per table. A primary key can be an integer or text column (or presumably real or other type, too).

If you designate an INTEGER column as also being the PRIMARY KEY, then SQLite will auto assign its value incrementally each time you insert a new row, unless you assign a value explicitly. More info in http://www.sqlite.org/lang_createtable.html

What does AUTOINCREMENT do?

The AUTOINCREMENT keyword prevents a PRIMARY KEY from being reused even after it is deleted.

How to ignore records that don't match a given value in a second table?

The trick is to use a sub-query to get all the values from a second table, and use this with the NOT IN() function in the main query:

SELECT * FROM mymaintable WHERE mymaincol NOT IN (SELECT mysubcol FROM mysubtable);

How to check if a record exists?

Alternative to "SELECT count(*) WHERE...":

SELECT 1 FROM contacts WHERE contacts_phones_tel GLOB "123*" LIMIT 1;
select exists (select * from contacts where contacts_phone_tel glob ?);
SELECT NULL FROM sqlite_master WHERE type='table' AND lower(name)=?

How to SELECT all columns from one table, and some from another table?

When JOINing two tables, you don't need to specify each column:

SELECT table1.*, table2.col1 FROM table1,table2 WHERE table1.foreign=table2.id

Why add AUTOINCREMENT to PRIMARY KEY?

The AUTOINCREMENT keyword prevents an primary key from being reused even after it is deleted.

How to create an autoincremented index in SQLite 2.x?

id INTEGER AUTO PRIMARY KEY. In 3.x, use INTEGER PRIMARY KEY AUTOINCREMENT.

How to have SQLite set a timestamp to 1 when creating a record?

CREATE TRIGGER insert_mytable_timestamp AFTER INSERT ON mytable
BEGIN
        UPDATE mytable SET timestamp = 1 WHERE rowid=new.rowid;
END;

How to have SQLite increment a timestamp with each UPDATE?

Combined with the above, useful when using a column to check if a row was updated by another user before saving changes:

create table mytable (id INTEGER AUTO PRIMARY KEY, name VARCHAR(50), timestamp INTEGER);
 
CREATE TRIGGER update_timestamp UPDATE ON mytable
BEGIN
        UPDATE mytable SET timestamp = old.timestamp + 1 WHERE rowid=new.rowid;
END;
 
insert into mytable values (NULL,'Marge',NULL);
 
select * from mytable;
 
update mytable set name='Homer' where id=1;
 
select * from mytable;

How to convert a 2.x database to 3.x?

sqlite olddb .dump | sqlite3 newdb

How to dump a database into a text file as SQL commands?

C:\>sqlite3 db.sqlite .dump > db.sqlite.sql

How to dump a table into a text file as SQL commands?

sqlite> .output dump.sql

sqlite> .dump phones

How to export a SELECT into TSV (CSV, with TABs instead)?

UTF8?

sqlite> .headers on

sqlite> .separator \t

sqlite> .output test.csv

sqlite> select * from MyTable where COL=Blah;

sqlite> .output

sqlite> .headers off

What does ".mode csv" do?

It's only used to format the output:

.mode MODE ?TABLE? Set output mode where MODE is one of:

Importing CSV data

We wish to import date from a CSV-formated file called data.csv which doesn't have a primary key. We will first create a temporary table to import data from this file, and then copy this contents into a persistent table which does have an auto-incremented primary key in its first column:

  1. CREATE TABLE mytable (key INTEGER PRIMARY KEY,field1,field2);
  2. CREATE TEMPORARY TABLE tmp (field1,field2);
  3. .separator \t (or .separator ;)
  4. .import data.csv tmp
  5. SELECT COUNT(*) FROM tmp;
  6. BEGIN;
  7. INSERT INTO mytable SELECT NULL,* FROM tmp;
  8. COMMIT;
  9. SELECT * FROM mytable WHERE key=1;

Important: Empty columns are not set to NULL but rather an empty string. Here's how to set things straight after importing data : UPDATE mytable SET field2 = NULL WHERE field2 = "";

How to import a subset of a CVS

An easier way is to first edit the source filel to only keep the columns you're interested in, before importing the subset into SQLite.

Otherwise, here's how to do it in SQLite:

CREATE TABLE subset (COL1,COL2);

CREATE TEMP TABLE full (COL1,COL2,COL3);

.separator \t (in case columns are tab-separated)

.import data.csv full

BEGIN;

INSERT INTO subset (COL1,COL2) SELECT COL1,COL2 from full;

COMMIT;

SELECT COUNT(*) FROM subset;

.quit

How to import a single table, semicolon-delimited (CSV) data?

If input data is not UTF8, you must first convert it to UTF8 using eg. GNUWin32's iconv : "C:\Program Files\GnuWin32\bin\iconv.exe" -f "windows-1252" -t "UTF-8" "input.Latin1.txt" > "output.UTF8.txt"

Make sure the the first row contains column names.

.separator ";"

.import output.UTF8.txt mytable

.schema mytable

SELECT COUNT(*) FROM mytable;

How to import tab-delimited data?

Here's the easy way:

  1. CREATE phones (tel TEXT,name TEXT);
  2. .separator \t
  3. .import data.csv phones
  4. SELECT * FROM phones;

If the target table includes NULL's, you must do this in two steps by using a temporary table:

  1. CREATE TEMP TABLE temp_customer (tel TEXT,name TEXT);
  2. .separator \t
  3. .import data.csv temp_customer
  4. SELECT * FROM temp_customer;
  5. INSERT INTO customer SELECT NULL, tel, name FROM temp_customer;
  6. SELECT * FROM customer;
  7. DROP TABLE temp_customer;

How to import tab-delimited data when some columns may be empty?

Error: datatype mismatch

How to import a batch of SQL commands?

C:\>type db.sqlite.sql | sqlite3 db.sqlite

How to delete some rows from a table?

DELETE FROM MyTable WHERE COL1="Blah";

DELETE FROM MyTable WHERE COL1<>"Blah";

How to delete all rows from a table?

DELETE FROM MyTable;

VACUUM;

How to delete a table?

DROP TABLE IF EXISTS MyTable;

VACUUM;

How to copy data from one table to another?

CREATE TABLE IF NOT EXISTS target (COL1 TEXT);

INSERT INTO target (COL1) SELECT COL1 FROM source [WHERE COL1="Blah"];

SELECT COUNT(*) FROM target;

How to add a column?

ALTER TABLE employees ADD first_name VARCHAR(50)

How to rename a column?

ALTER TABLE table_name RENAME COLUMN current_name TO new_name;

How to remove a column

As of November 2020, SQLite provides no way to do this: You must create a new table, copy records from the current table, drop the current table, and rename the new table:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

CREATE TABLE IF NOT EXISTS new_mytable("COL1" TEXT);

INSERT INTO new_mytable("COL1") SELECT "COL1" FROM mytable;

DROP TABLE mytable;

ALTER TABLE new_mytable RENAME TO mytable;

COMMIT;

PRAGMA foreign_keys=on;

VACUUM;

How to rename a table?

ALTER TABLE existing_table RENAME TO new_table;

How to rename indexes?

There's no way: You need to delete and recreate them:

DROP INDEX old_index;

CREATE INDEX new_index ON demo(mycol);

Performance

http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuning

http://www.sqlite.org/cvstrac/wiki?p=PerformanceConsiderations

Checking/recovering

(TEMP STUFF) The sqlite.exe shell automatically attempts to recover the database when it runs.  If sqlite.exe failed, then the database is unrecoverable.

The "PRAGMA synchronous" setting is only important for recovery from power failures and OS crashes.  It should not matter here. On the other hand, the difference between NORMAL and FULL should be minimal.

PRAGMA integrity_check;

I get a code 11 (SQLITE_CORRUPT) returned from the PRAGMA integrity_check command.

If you have an IDE hard drive that's caching writes, there's not much the OS and database software can do to prevent corruption on power loss. It's possible to avoid this with tagged queueing, but most drives don't support that. The FreeBSD folks tried to solve this by turning off write caching by default. Unfortunately, this hurt performance so much they had to turn it back on and just recommend SCSI drives for important data.

I believe that SQLite does survive power loss without problems on Linux.  However, I have received reports that the windows API function FlushFileBuffers() sometimes lies and does not really flush contents to the disk surface as it claims it does.  This is just hearsay - I have not independently verified those reports. If FlushFileBuffers() does lie and a power loss occurred in the middle of a COMMIT, then database corruption is possible on windows.  This is a bug in the OS and there is not anything SQLite (or any other database engine) can do about it.

From what I am told, most IDE drives do signal the OS when the data reaches the platter.  I'm also told that the Linux fsync() call does not return until it gets that signal.  The Windows FlushFileBuffers(), on the other hand, does not wait for the data to get to platter.  So on a windows system, there is a brief moment of vulnerability where a power loss can lose data.  But on Linux, that window of vulnerability is zero.

The above is how IDE drives are *suppose* to work.  There is wide- spread suspicion that many cheap IDE drives do not implement the protocol correctly.  If your have one of those broken IDE disks, all bets are off.

Transactions can be started manually using the BEGIN command. Such transactions usually persist until the next COMMIT or ROLLBACK command. But a transaction will also ROLLBACK if the database is closed or if an error occurs and the ROLLBACK conflict resolution algorithm is specified. See the documention on the ON CONFLICT clause for additional information about the ROLLBACK conflict resolution algorithm.

The PRAGMA command is used to modify the operation of the SQLite library. The pragma command is experimental and specific pragma statements may be removed or added in future releases of SQLite. Use this command with caution.

PRAGMA integrity_check;

The command does an integrity check of the entire database. It looks for out-of-order records, missing pages, malformed records, and corrupt indices. If any problems are found, then a single string is returned which is a description of all problems. If everything is in order, "ok" is returned.

File Locking And Concurrency In SQLite Version 3

SQLite and concurrency

Here's what SQLite author D. Richard Hipp has to say about using SQLite in a multihost and/or multiapp context: "Can multiple applications or multiple instances of the same application access a single database file at the same time? On Windows, Microsoft's documentation says that locking may not work under FAT filesystems if you are not running the Share.exe daemon. People who have a lot of experience with Windows tell me that file locking of network files is very buggy and is not dependable. If what they say is true, sharing an SQLite database between two or more Windows machines might cause unexpected problems."

This said, if your application won't need to support high concurrency in the near future, if ever, and deploying a client/server DBMS like MySQL or Oracle is overkill, here are things to do to lower the risks of corrupting data:

An easy way to handle concurrency, is to add a column to each table: This columns contains a counter that is set to 1 when creating the record, and incremented with every update. Those two operations can be achieved through triggers:

C:\>sqlite database.db
 
sqlite> .tables
mytable
 
sqlite> .schema mytable
 
create table mytable (id INTEGER AUTO PRIMARY KEY, name VARCHAR(50), timestamp INTEGER);
 
CREATE TRIGGER mytable_insert_timestamp AFTER INSERT ON mytable
BEGIN
        UPDATE mytable SET timestamp = 1 WHERE rowid=new.rowid;
END;
 
CREATE TRIGGER mytable_update_timestamp UPDATE ON mytable
BEGIN
        UPDATE mytable SET timestamp = old.timestamp + 1 WHERE rowid=new.rowid;
END;

From then on, when SELECTing a record, make sure you save the value of timestamp into a variable: If another user updated the record while you were still making changes locally, when performing an UPDATE with the original value of timestamp, SQLite will not find this record, and a subsequent SELECT will return no record that matches: This is a hint that this record was updated in parallel by someone else. In this case, you can either ask the user if he wishes to read the latest record from the database (and lose his changes), display the two versions side by side on his computer and let him merge them... or ignore the changes made by the other user and just send this user's changes:

SELECT MYCOL, COUNTER WHERE ID=1
... this user goes out for lunch...
UPDATE MyTable SET MYCOL='Jane' WHERE ID=1 AND COUNTER=123;
SELECT MYCOL WHERE ID=1 AND COUNTER=124
... if this last SELECT returns nothing, you know you've been had

Another solution to check if a row was updated by another user between the time you SELECTed it and the time you tried to UPDATE it, is to grab the original value of the column you wish to update, and include it in the UPDATE query. Here's an example in Python:

import apsw
 
connection=apsw.Connection("test.sqlite")
cursor=connection.cursor()
 
sql = 'SELECT id,mycol FROM mytable WHERE id=1'
for id, original_value in cursor.execute(sql):
        try:
                sql = "UPDATE mytable SET mycol=? WHERE id=? AND mycol=?"
                cursor.execute(sql, ("my new value",id,original_value) )
        except:
                print "Failed UPDATING"
                        
connection.close(True)

Here's the same example but directly in the SQLite CLI:

CREATE TABLE IF NOT EXISTS mytable (id INTEGER PRIMARY KEY, label VARCHAR);
 
INSERT INTO mytable (label) VALUES ("old");
 
//Other user updates the record while you're busy
UPDATE mytable SET label="concurrency" WHERE id=1;
 
UPDATE mytable SET label="new" WHERE id=1 AND label="old";
 
//Either checks that no row was returned, or checks the value of this column through SELECT:
SELECT label FROM mytable;
concurrency

Also read

Indexing a column

create index myindex on tbl1 (mycol);

Set all values of a column to NULL

UPDATE t1 set COL1=null;

Updating a column

update tbl1 set mydate='2003-10-01' where id='1';
select * from tbl1 where mydate > '2003-01-01';

Here's how to copy values from another table:

UPDATE t1 SET COL1=t2.COL1 FROM t2 WHERE t2.COL2=t1.COL2;

Checking tables

.tables;

Checking schemas

.schema;

Reading the list of tables in a database

SELECT tbl_name AS "Table name" FROM sqlite_master WHERE type="table";

Inserting a date

Checking which tables live in a database

SELECT * FROM sqlite_master;

Importing DBF data

Exportizer can convert DBase data to CSV, which you can use to import into SQLite.

Forcing integer primary key to start at a given number

In case you'd rather that SQLite started creating a table's primary key automatically by starting at eg. 100,001 instead of 1:

SQL.Clear;
 
SQL.Add('CREATE TABLE IF NOT EXISTS mytable (id INTEGER PRIMARY KEY, label VARCHAR);');
 
SQL.Add('INSERT INTO mytable (id,label) VALUES (100001,"my label");');
//Next id = 100002
SQL.Add('INSERT INTO mytable (label) VALUES ("my label");');
 
ExecSQL;

GUI clients

My favorite is SQLiteStudio; MiTec SQLite Query is nice too.

SQLiteStudio

MiTec SQLite Query 3.0.0.0

Small and fast, but for some reason, search through CTRL+F dialog doesn't search within the app's window (?)

https://www.mitec.cz/sqliteq.html

DB Browser for SQLite

http://sqlitebrowser.org/

SQLite Management Studio

Navicat for SQLite

Valentina Studio (Pro)

Navicat for SQLite

SQLiteExpert (Professional)

SQLiteSpy

SQLiteManager

SpatiaLite GUI

SQLiteBrowser

SQLiteExplorer

SQLiteMaestro

SQLitespeed

SQLite browser online

Resources

Temp

sqlite_open: "If the third argument is not NULL and an error occurs while trying to open the database, then an error message will be written to memory obtained from malloc() and *pzErrMsg will be made to point to this error message.  The calling function is responsible for freeing the memory when it has finished with it.  It should be freed  with sqlite_freemem() if being called in a Windows DLL, or using sqliteFree() if statically linked."