Compiling SQLite on Windows

Download the files

First of all, download the latest SQLite. This guide refers to the currently modern version - 3.6.18, but other versions should also work with just minor modifications.

You'll need the "amalgamation" archive and the precompiled Windows DLL archive (for the .def file). The amalgamation can be used to statically link SQLite with your code (by just throwing in the single .h and .c files into your project), but here we'll use it to compile a DLL.

Create a MSVC project

I'm using Microsoft Visual C++ 2005 Express edition, but this shouldn't be difficult to customize for older or newer versions.

Open MSVC and create a new Win32 project. In the Application Wizard select DLL and tick "Empty project".

1. Add sqlite3.h and sqlite3.c from the amalgamation to the project.
2. Copy the sqlite3.def file from the source distribution into the project directory. Note that the .def file can be generated from sqlite3.dll using the dumpbin tool.
3. In the project settings, add THREADSAFE to the preprocessor definitions and sqlite3.def in Linker -> Input -> Module definition file

Compile

Build the project. With the described setup, I got the error:

1>Linking...
1>sqlite3.def : error LNK2001: unresolved external symbol sqlite3_column_database_name
1>sqlite3.def : error LNK2001: unresolved external symbol sqlite3_column_database_name16
1>sqlite3.def : error LNK2001: unresolved external symbol sqlite3_column_origin_name
1>sqlite3.def : error LNK2001: unresolved external symbol sqlite3_column_origin_name16
1>sqlite3.def : error LNK2001: unresolved external symbol sqlite3_column_table_name
1>sqlite3.def : error LNK2001: unresolved external symbol sqlite3_column_table_name16
1>sqlite3.def : error LNK2001: unresolved external symbol sqlite3_table_column_metadata
1>D:\eli\sqlite\sqlite_msvc_build\sqlite3\Debug\sqlite3.lib : fatal error LNK1120: 7 unresolved externals

After some digging in the code of SQLite, it appears that the following preprocessor definition has to be added in the project properties to make it compile cleanly: SQLITE_ENABLE_COLUMN_METADATA

When it's added, the compilation succeeds. A sqlite3.dll file appears in the Debug directory of the project (or Release, depending on the build configuration).

MSVC automatically generated an import library for me (called sqlite3.lib) which can be used for the convenience of implicit linking to SQLite. If it doesn't do it for you, open the MSVC console, go to the directory where the .def file resides, and run lib /def:sqlite.def.

Using the C API

Now let's set up a simple example usage of the SQLite C API. Here's a source file:

#include <stdio.h>
#include <stdlib.h>
#include "sqlite3.h"


int main(int argc, char **argv)
{
    sqlite3* db;
    char* zErr;
    int rc;
    char* sql;

    rc = sqlite3_open("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 integer primary key,"
          "                       name text, cid int)";

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

    sql = "insert into episodes (name,id) values ('Cinnamon Babka2',1)";
    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);
    return 0;
}

All this does is create a simple database with a table and insers one entry into the table. This code comes from the samples for the "Definitive guide to SQLite" book.

To compile, create a new MSVC project and add this file. In the "additional include directories" point to the amalgamation that contains the sqlite3.h header. Also, in Linker -> Input -> Additional dependencies point to the .lib file created in the previous section. This is used to implicitly link the SQLite API calls.

The project should build without problems. To run it, make sure the DLL you created is in the same directory with the new .exe file.

After running the sample program, a test.db file is created. You can use the SQLite command line utility to examine the file and make sure everything is as expected:

... path ... >sqlite3 test.db
SQLite version 3.6.14.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
episodes
sqlite> select * from episodes;
1|Cinnamon Babka2|
sqlite>