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):

• SQLHelper (a.k.a. PSVBUTLS32.DLL uploaded in the Files section of the SQLite Yahoo group by Steve O'Hara from Pivotal Solutions Ltd.; psvbutls32.zip contains the compiled DLL that returns an array of string, and VB declarations in a TXT file, with pssql.zip containing the source C file, the required DEF file to compile this into a DLL, and VB declarations in a BAS file; VB Wrapper returns a table of VARIANTS and allows you to ignore the headers)
• Visual Basic 6 & .NET wrapper for SQLite. http://www.ag-software.com/SQLite/default.asp (Free)
• .NET. Mono ADO.NET provider for SQL Lite: http://www.go-mono.com/sqlite.html (Free)
• SQLitePlus COM DLL wrapper ($100 license; Requires the SQLite DLL, since SQLitePlus is just a front-end)
• SQLiteDB - Lightweight database library in a 330Kb ActiveX COM DLL

Compiling SQLite

Windows

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:

• Resource Template -> binary res.rct : What's the difference with a Resource Script?
• Project | Settings : C/C++ tab, category = Code Generation, Calling convention = _cdecl, _stdcall
• EDais :: Tutorials :: Using C++ DLL's in VB (MS VC++ 6, though)
• Compiling SQLite to make it VB-friendly: http://www.tannertech.net/sqlite3vb/ and http://www.persistentrealities.com/index.php?p=18

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

• http://www.aducom.com
• Wrapper; Can work with or without the SQLite DLL
• The components are bidirectional by default but will generate an internal table with results, resulting in important memory use on large result sets, so choose your SELECT with care
• A clientdataset is not needed, but if used, you must set unidirectional to true, because the clientdataset will ALSO cache data which means that you have two resultsets in memory
• Note that TEXT data are handled as TMemo, so is displayed as "(MEMO)" in DBGrid. Use VARCHAR instead (which can be used as-is, without specifying a length)

Components

• TASQLiteDatabase
• TASQLiteTable
• TASQLiteQuery (read-only for SELECT)
• TASQLiteUpdateSQL (complement to Query, to update a dataset)
• TASQLitePragma (to update PRAGMA infos in a database)
• TASQLiteLog (to log SQL commands)

Notes

• The Download section is a bit confusing to newbies. File in "Object files for static linking" are the compiled version of the source code from www.sqlite.org that you must add to build a static version of the ASqlite component. File in the "SQLite > Latest sources" section are the source of the ASqlite component.
• As of May 2007, after compiling SQLite3.c into an OBJ file, a few things must be changed to ASGSQLite3.pas in the ASqlite3Null200704A component so that it can be compiled statically into your Delphi EXE, but it should no longer be needed in the next release by Aducom. See readme.txt and the forum for more information.

Compiling

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:

• sqlite-source-3_4_1.zip no longer contains sqlite3.c. You must download sqlite-amalgamation-3_4_1.zip instead
• After editing ASGSQLite3.pas, tried to compile the package, but got: "[Error] ASGSQLite3.pas(1): Unsatisfied forward or external declaration: '__streams'". Until it gets fixed in later versions, the solution is simply to copy the line {$L 'OBJ\streams.obj'} available further below, and paste it right after the reference to the OBJ file:
  
  {$IFDEF SQLite_Static}
  Var
    __HandlerPtr:Pointer;
  
  {$L 'OBJ\sqlite3_4_1.obj'}
  {$L 'OBJ\streams.obj'} //Copied from below to solve compiling error
  [...]
  {$L 'OBJ\initcvt.obj'}
  {$L 'OBJ\streams.obj'} 

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);

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 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

• "DISQLite3 implements a self-contained, embeddable, zero-configuration SQL database engine" (also reachable at www.zeitungsjunge.de)
• Looks the most active

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:

• The Native API, through DISQLite3API: Simple programs can still make do with only 3 functions: sqlite3_open, sqlite3_exec, and sqlite3_close
• The Object Layer: TDISQLite3Database and TDISQLite3Statement are powerful wrappers around the native API. They simplify database development and add additional security checks.
• TDataSet Layer: TDISqlite3UniDirQuery descends from TDataSet descendant which allows commonly used controls and report generators to work with DISQLite3.

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:

ZeosLib

• "ZeosLib is a set of database components for MySQL, PostgreSQL, Interbase, Firebird, MS SQL, Sybase, Oracle, DB/2 for Delphi, Kylix and C++ Builder. Lazarus support is under way."
• Site a bit messy (no home page, the whole thing lives in a forum)
• Apparently, no static version available, so a DLL must be provided
• http://delphi.icm.edu.pl/authors/a0003694.htm

ExplainThat Delphi SQLite Wrapper

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

SQLite Quick

• http://www.sed-p.net/-SQLite-.html
• Derived from TLiteDB?

SQLite With PHP

• PHP 101 (part 9): SQLite My Fire! - Part 1
• SQLite Functions
• SQLite Introduction
• Upgrading to PHP5 - SQLite

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_DecryptString$ 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.

' Module
Public Const SQLITE_ISO8859 = 1
Public Const SQLITE_OK = 0                  '*/ Successful result */
Public Const SQLITE_ERROR = 1               '*/ SQL error or missing database */
Public Const SQLITE_INTERNAL = 2            '*/ An internal logic error in SQLite */
Public Const SQLITE_PERM = 3                '*/ Access permission denied */
Public Const SQLITE_ABORT = 4               '*/ Callback routine requested an abort */
Public Const SQLITE_BUSY = 5                '*/ The database file is locked */
Public Const SQLITE_LOCKED = 6              '*/ A table in the database is locked */
Public Const SQLITE_NOMEM = 7               '*/ A malloc() failed */
Public Const SQLITE_READONLY = 8            '*/ Attempt to write a readonly database */
Public Const SQLITE_INTERRUPT = 9           '*/ Operation terminated by sqlite_interrupt() */
Public Const SQLITE_IOERR = 10               '*/ Some kind of disk I/O error occurred */
Public Const SQLITE_CORRUPT = 11             '*/ The database disk image is malformed */
Public Const SQLITE_NOTFOUND = 12            '*/ (Internal Only) Table or record not found */
Public Const SQLITE_FULL = 13                '*/ Insertion failed because database is full */
Public Const SQLITE_CANTOPEN = 14            '*/ Unable to open the database file */
Public Const SQLITE_PROTOCOL = 15            '*/ Database lock protocol error */
Public Const SQLITE_EMPTY = 16               '*/ (Internal Only) Database table is empty */
Public Const SQLITE_SCHEMA = 17              '*/ The database schema changed */
Public Const SQLITE_TOOBIG = 18              '*/ Too much data for one row of a table */
Public Const SQLITE_CONSTRAINT = 19          '*/ Abort due to contraint violation */
Public Const SQLITE_MISMATCH = 20            '*/ Data type mismatch */
Public Const SQLITE_MISUSE = 21              '*/ Library used incorrectly */
Public Const SQLITE_NOLFS = 22               '*/ Uses OS features not supported on host */
Public Const SQLITE_AUTH = 23                '*/ Authorization denied */
Public Const SQLITE_ROW = 100                '*/ sqlite_step() has another row ready */
Public Const SQLITE_DONE = 101               '*/ sqlite_step() has finished executing */
 
Declare Function sqlite_libversion Lib "SQLITE.DLL" () As Long
Declare Function sqlite_open Lib "SQLITE.DLL" (ByVal szFilename As String, ByVal lMode As Long, lpErrMsg As Long) As Long
Declare Sub sqlite_close Lib "SQLITE.DLL" (ByVal lpSQLite As Long)
Declare Function sqlite_exec Lib "SQLITE.DLL" (ByVal hSQLite As Long, ByVal szSql As String, ByVal sqlite_callback As Long, cbParam As Any, lpErrMsg As Long) As Long
Declare Sub sqlite_freemem Lib "SQLITE.DLL" (ByVal lpSz As Long)
 
Declare Function sqlite_get_table Lib "SQLITE.DLL" (ByVal hSQLite As Long, ByVal szSql As String, lpTable As Long, nRow As Long, nColumn As Long, lpErrMsg As Long) As Long
Declare Function sqlite_free_table Lib "SQLITE.DLL" (ByVal lpTable As Long) As Long
 
' Form
Private Sub Form_DblClick()
    Dim lDB As Long, lErrMsg As Long, cbParam As Long, lResult As Long
    Dim lpTable As Long, nRows As Long, nColumns As Long
    Dim szRow As Long
    Dim szColumn As Long
    Dim sVersion As String * 128
    Dim iCounter As Long
    Dim pRow As Long, pColumn As Long, sResult As String * 256
    Dim lItems As Long
    Dim bCar As Byte
    Dim sTemp As String
    Dim iPos As Integer
    
    'Get rid of current DB, if any
    If (Dir("test.db", 0) = "test.db") Then
        Kill "test.db"
    End If
 
    lDB = sqlite_open("test.db", 0, 0&)
    If (lDB = 0&) Then
        MsgBox "Error opening database"
        Exit Sub
    End If
    
    Call sqlite_exec(lDB, "create table tbl1(one varchar(10), two smallint);", 0&, cbParam, 0&)
    
    Call sqlite_exec(lDB, "BEGIN;", 0&, cbParam, 0&)
    For iCounter = 1 To 10000
        Call sqlite_exec(lDB, "insert into tbl1 values('user " & Str$(iCounter) & "',10);", 0&, cbParam, 0&)
    Next iCounter
    Call sqlite_exec(lDB, "COMMIT;", 0&, cbParam, 0&)
    
    'GPF... Let's use sqlite_get_table instead
    'Call sqlite_exec(lDB, "select * from tbl1;", AddressOf sqlite_callback, cbParam, lErrMsg)
    
    lResult = sqlite_get_table(lDB, "select * from tbl1", lpTable, nRows, nColumns, lErrMsg)
    If lResult = SQLITE_OK Then
        
        'Get rid of header....
        For lItems = nColumns To (nRows * nColumns) + 1
            CopyMemory pRow, ByVal lpTable + (4 * lItems), 4
            'Search for NULL to get length of item
            iCounter = 0
            Do
                CopyMemory bCar, ByVal pRow + iCounter, 1
                If (bCar = vbNull) Then
                    Exit Do
                End If
                iCounter = iCounter + 1
            Loop
            CopyMemory ByVal sVersion, ByVal pRow, iCounter
            
            'Concatenate columns
            If sTemp = "" Then
                sTemp = sVersion
                'Ger rid of trailing NULL which prevents both columns to be displayed in List1
                iPos = InStr(1, sTemp, Chr$(0))
                If iPos Then
                    sTemp = (Left$(sTemp, iPos - 1))
                End If
            Else
                sTemp = sTemp & "*" & sVersion
                List1.AddItem sTemp
                sTemp = ""
            End If
        Next lItems
        sqlite_free_table lpTable
    Else
        MsgBox "Err sqlite_get_table"
    End If
 
    sqlite_close (lDB)
End Sub

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:

• The server is a single EXE, and should be acting like a web server, so clients can use standard HTTP controls to communication with the server
• The server listens on TCP80 for incoming queries, and is configured to only accept one connection at once, so as to solve the concurrency issue
• The server also listens for broadcasts, so as to let clients locate the server without having to specify the server's address manually
• Parameters are sent by the client through POST
• Watch out for big SELECTs : send as a zipped file?

As of June 2007, here are the client/server solutions I found. My favorites are uSQLiteServer and SL4Sockets.

SQL4Sockets

• The freeware version supports two active sockets. The commercial version supports 25 sockets
• "Communication between the client and SQL4Sockets is simple delimited UTF8 encoded text over a TCP socket"
• "The database should always be called ‘database.db’ and should reside in the same directory as the server. Configuration settings are encrypted and saved in configuration.asc."
• "Launching the server with the switches –h -l tells it to hide and listen. If you issue “QUIT;” as a SELECT statement (i.e. with Chr(20)), if you are the only current active socket then the server will shut down. This is particularly useful for local use. Alternatively the server can be set up to run as a service."
• Create a database as "database.db" with eg. CREATE TABLE, put it alongside S4SWin32.exe, open a DOS box, and run "S4SWin32.exe -h -l". An icon should show in the icon tray, and the server is listening on TCP2006

uSQLiteServer

• Roger Irwin's uSQLiteServer is an SQLite network wrapper. It uses the public domain TechFell protocol to handle communication between database clients and servers.
• "BTW, at the time of writing there is no server source, which might sound ironic as the binaries exist. Truth is that the uSQLiteServer was incubated in an application which already had the TechFell protocol, but was hacked about with #ifdefs and all to get a stand alone server. My next task is to try and clean this up into a standalone app/library. This will be written in C and be as portable as possible (coding is similar to the Cclient example). The aim of the first server source release will be to have a clean and simple codebase which may be compiled on as many platforms as possible, I will not be adding functionality or changing the interface."

DatenHaus dhRPCServer + dhSQLite COM client

• "dhRPCServer is the "flagship" in our Tool-Set and contains not only an XCopy-deployable Serverpart, it comes of course with an appropriate RPCClient-Part too. The communication between the Client- and the Server-Part is done over TCP/IP-sockets under usage of a fast binary protocol (and one single, definable port only).Basically it is the "barebone-version" of our larger dBAS-Applicationserver (mentioned here), optimized and tuned over the last years.
• "dhSQLite is our latest tool and was developed as a fast alternative to ADO, encapsulating the super-fast SQLite-engine. With only two Dlls you get a complete Replacement to the whole ADO/JET-environment - no dependency-hazzle anymore (working even on Linux/Wine). The dhSQLite-Wrapper is not absolutely compatible to ADO - but using it feels like working with ADO (or the somewhat older DAO) - just look at the Source inside the Demo below (wich contains comparison-tests with ADO/JET) - there are very few differences regarding the Code, but huge differences in terms of performance.
• Apparently, requires several DLLs to run
  
  We've developed our wrapper with dhRPCServer in mind, so we finetuned especially the Heap-management, the Serialization/Deserialization-performance and the UpdateBatch- capabilities of the dhSQLite-Recordsets"

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 not production-ready for Windows"

REAL SQL Server

• Unlimited Connections         $500   
• http://www.realsoftware.com

SQLiteDBMS

• SQLiteDBMS-beta         sqlitedbms-0.5.1        November 14, 2006
• Windows only?
• http://sqlitedbms.sourceforge.net

SQLite_on_Sockets

• 17.Jun.2004
• http://www.it77.de/sqlite/sqlite_sockets.htm

TerraInformatica SQLiteDBServer

• As of June 2007, still under development
• http://www.terrainformatica.com/sqlitedbserver/

Tips & Tricks

Opening two databases with the SQLite CLI client

Listing duplicates

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

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 export a SELECT into TSV (CSV, with TABs instead)?

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 import a batch of SQL commands?

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

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 modify a column?

In case you need to either change a column name, or remove a column that is not the right-most, you need to select all the existing records into a temporary table, drop the old table, create a new one, and insert the records from the temporary table:

1. CREATE TEMP TABLE temp_new_table (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);
2. INSERT INTO temp_new_table SELECT NULL, name FROM old_table;
3. DROP TABLE old_table;
4. CREATE TABLE new_table (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);
5. INSERT INTO new_table SELECT * FROM temp_new_table;
6. SELECT * FROM new_table;
7. DROP TABLE temp_new_table;

How to add a column?

ALTER TABLE employees ADD first_name VARCHAR(50)

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 default_synchronous;
• PRAGMA default_synchronous = FULL; (2)
• PRAGMA default_synchronous = NORMAL; (1)
• PRAGMA default_synchronous = OFF; (0)

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.

• PRAGMA synchronous;
• PRAGMA synchronous = FULL; (2)
• PRAGMA synchronous = NORMAL; (1)
• PRAGMA synchronous = OFF; (0)

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:

• Add code to your application, so that the first person to work in the morning will trigger a backup of the database file, ie. if no DUMMY.DB.BAK, then copy DUMMY.DB to DUMMY.DB.BAK
• Take snapshots every so often during the day too, so that in case the database is corrupted, you always have a good version to fall back on that is not too old
• Since the oplock (opportunistic lock) and read cache features were buggy in older versions of Windows, make sure you are either using an updated or more recent version of Windows, or, if this feature is still not reliable, that they are disabled. Some users have reported problems when mixing 9x and NT hosts; Their solution was to use either all 9x or all NT. Don't know if MS has corrected this bug and made this history
• Always close the database file when you're done; Don't leave a database file open for long periods of time, even if you're not making any changes
• Use transactions (BEGIN -> insert/update/delete -> COMMIT). To lock the database for exclusive use, execute this command: BEGIN; No other process will be able to access the database until you execute either COMMIT or ROLLBACK.
• SQLite uses the file locking feature of the OS to lock the database file whenever someone makes changes, ie. doing a SELECT is OK, but doing an INSERT isn't. If someone is currently updating the database, SQLite returns BUSY. Wait a second, and try again up to 5 times; If the database is still busy, display a dialog telling the user what is happening, and explain what to do (eg. whether any of the other hosts has gone south, in which case they should kill the runaway app or reboot)
• When installing your application, create a file that will be used as mutex, ie. it will tell hosts whether the database is BUSY or FREE. You should not create/delete this file every time you wish to access the database, as this operation is very costly, and will fail within a few seconds after running a concurrency test. Opening a file with read/write lock is much more reliable less error-prone. FYI, the instruction in VB is Open sMutex For Binary Lock Read Write As #iFile
• Run "PRAGMA integrity_check;" in SQLite during off-hours to do a complete integrity check of the database
• Provide an easy way for customers to launch a repair (.dump / restore)
• Use the latest and greatest SQLite to make sure you
• Host the database on a U2W SCSI drive instead of IDE
• If all else fails, roll your own wrapper to build a light client/server solution

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

Also read

• Improving Concurrency In SQLite
• Simultaneosly using sqlite on windows
• vers 3.0 concurrency issues
• Multiuser experience under win32 anyone?
• Checking the busy state
• sqlite - Multi Threading
• Opportunistic Locking and Read Caching on Microsoft Windows Networks - A Data Access Worldwide White Paper by Dennis Piccioni
• Re: [sqlite] Win32 Locking issues and Re: [sqlite] Win32 Locking issues

Indexing a column

create index myindex on tbl1 (mycol);

Updating a column

update tbl1 set mydate='2003-10-01' where id='1';

select * from tbl1 where mydate > '2003-01-01';

Checking tables

.tables;

Checking schemas

.schema;

Inserting a date

Checking which tables live in a database

SELECT * FROM sqlite_master;

Importing data

We wish to import date from a CSV-formated file called filesql which doesn't have a primary key. We will first create a temporary table ton 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. c:\>sqlite test.db
2. CREATE TABLE mytable (key INTEGER PRIMARY KEY,field1,field2);
3. CREATE TEMPORARY TABLE tmp (field1,field2);
4. .separator \t
5. .import data.csv tmp
6. SELECT * FROM tmp;
7. BEGIN;
8. INSERT INTO mytable SELECT NULL,* FROM tmp;
9. COMMIT;
10. SELECT * FROM mytable WHERE key=1;
11. .quit

Resource

Tools

• SQLite Administrator, a free Windows GUI appplication to work with SQLite 2 and 3. As of 0.8.3.0, pretty good, although I found that the query function was not very intuitive
• SqliteExplorer, a GUI application for manipulating SQLite databases written in Delphi
• SQLite2007 Pro Enterprise Manager
• SQLite Spy
• SQLite Database Browser
•  
• SQLite Analyzer
• SQLite Maestro
• Vive's dataPro and SQLPro
• SQLite Expert
•  
• Arke Systems' Sqlite Explorer
•  
• SQLite Control Center is a visual tool for working with SQLite database files; "Version 1.0.1 released 6/3/2006 - upgraded to SQLite version 3.3.5 and wxWidgets 2.6.3."
• Ivan Sivak's SQLite Datase Manager, available in free and pro versions
• SQLite Admin, the equivalent to PhpMyAdmin to access a SQLite database through the web
• SQLite Server, a cross-platform commercial c/s version of SQLite
• ViewStruct, A freeware Win32 GUI program that shows SQLite database's structure, i.e. table names and SQL statements that created the tables
•  
• SQLiteDbServer - lightweight multithreaded TCP/IP sqlite-based database server

Sites

• SQLite Tutorial by Mike Chirico
• The Definitive Guide to SQLite by Michael Owens
• Frequent references on the SQLite site:
  • SQL Syntax (the SQL language that is understood by SQLite) and SQL As Understood By SQLite
  • FAQ
  • The C language interface to the SQLite library and The C/C++ Interface For SQLite Version 3,
  • Archives of the mailing list (mirror; Old mailing list at Yahoo which is now closed)
  • Wiki section
• SQLite Optimization FAQ
• SQLite API Documentation
• Embed SQL with SQLite by Adam Turoff
• Embedding SQLite in a c program
• mod_sqlite - A Network Database Server for SQLite
• SQLite 3.0.7 by Clay Dowling

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."