Eli Bendersky's website » Compiling SQLite on Windows

Compiling SQLite on Windows

September 23rd, 2009 at 5:34 am

Here’s a short guide to compiling SQLite on Windows using Visual C++ Express 2005 and using the compiled DLL in a simple C program.

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

Add sqlite3.h and sqlite3.c from the amalgamation to the project.
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.
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>

This entry was posted on Wednesday, September 23rd, 2009 at 05:34 and is filed under C / C++, Databases. You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.

5 Responses to “Compiling SQLite on Windows”

Lee Says:
October 30th, 2009 at 22:23
Thank you! The best set of instructions I’ve seen so far.

I just tried them using Visual Studio 2008 Pro, and all worked as described here. My only difficulty was in learning where VC++ expects assorted files to be located and adjusting settings accordingly. I also used a name other than sqlite3 for the DLL project, to help prevent me from confusing my build from the official build.
Peter Doubleday Says:
July 16th, 2010 at 20:12
Still works with VS 2010 Express … and you still need the SQLITE_ENABLE_COLUMN_METADATA preprocessor directive.

Many thanks!

Carl B

Says:
November 18th, 2010 at 00:38

Here is my very simple SQLite3 c++ wrapper.
There are some bugs here and there, but overall it works very nicely.
It requires the use of another library i designed names SQLogger.

#ifndef SQLITEWHIZ_H
#define SQLITEWHIZ_H

#include <string>

#include "sqlite3/sqlite3.h"

#include "SQLogger.h"

#define DEFAULT_DB_LOCATION "Data/Database/database.db"
#define ACTIVE_LOGGING false

namespace SQLApp
{
	class SQLiteWhiz
	{
	public:

		SQLiteWhiz( );

		SQLiteWhiz( char *loc );

		SQLiteWhiz( char *loc, bool logEnabled );

		~SQLiteWhiz( );

		void Logging( bool logIt );

		bool GetInt( int &refVal, const char *sql, ... );

		bool GetStr( std::string &refVal, const char *sql, ... );

		bool GetChar( char* refVal, const char *sql, ... );

		bool CountRows( int &rows, const char *sql, ... );

		char* Format( char *message, ... );

		bool Exe( const char *sql, ... );

	private:
		int Open( );

		void Close( );

		void Common( );

		bool LogError( void );

		Logger SQLog;

		sqlite3 *database;

		char *location;

		bool isOpen;

		bool loggingEnabled;

	};
}

#endif // SQLITEWHIZ_H

#include <stdio.h>
#include <stdarg.h>
#include <time.h>
#include <string>

#include "sqlite3/sqlite3.h"
#include "SQLogger.h"
#include "SQLiteWhiz.h"

namespace SQLApp
{
	// Class default constructor.
	SQLiteWhiz::SQLiteWhiz( )
	{
		location = DEFAULT_DB_LOCATION;
		isOpen = false;
		loggingEnabled = ACTIVE_LOGGING;
		Common( );
	}

	// Class constructor with override.
	SQLiteWhiz::SQLiteWhiz( char *loc )
	{
		location = loc;
		isOpen = false;
		loggingEnabled = ACTIVE_LOGGING;
		Common( );
	}

	// Class constructor with override.
	SQLiteWhiz::SQLiteWhiz( char *loc, bool logEnabled )
	{
		location = loc;
		isOpen = false;
		loggingEnabled = logEnabled;
		Common( );
	}

	void SQLiteWhiz::Common( )
	{
		// The database is not open yet.
		isOpen = false;

		// Display output in the logfile if loggingEnabled equals true.
		if( loggingEnabled )
		{
			// Add the string to the logfile.
			if( SQLog.Log( SQLog.INFO_Lvl, "SQLite3 Version: %s", sqlite3_libversion( ) ) )
				exit( -1 );

			SQLog.Log( SQLog.INFO_Lvl,"Searching for Database..." );
			// Test database connectivity.
			if( Open( ) )
			{
				SQLog.Log( SQLog.FATAL_ERROR_Lvl,"Unable to find the database." );
				SQLog.Log( SQLog.FATAL_ERROR_Lvl, "EXITING APPLICATION IMMEDIATELY!" );
				exit( -1 );
			}
			SQLog.Log( SQLog.INFO_Lvl,"Database found!" );
			Close( );
		}
		else
		{
			if( Open( ) )
				exit( -1 );

			Close( );
		}
	}

	bool SQLiteWhiz::LogError( void )
	{
		// Grab ahold of the SQLite database.
		sqlite3_mutex_enter( sqlite3_db_mutex( database ) );

		// Add the sqlite3 error message to the logfile.
		if( SQLog.Log( SQLog.ERROR_Lvl, "%s", sqlite3_errmsg( database ) ) )
			return true;

		// Release hold of the database when finished.
		sqlite3_mutex_leave( sqlite3_db_mutex( database ) );

		return false;
	}

	// Class destructor.
	SQLiteWhiz::~SQLiteWhiz( )
	{
		// Empty 
	}

	void SQLiteWhiz::Logging( bool logIt )
	{
		loggingEnabled = logIt;
	}

	// Opens the database so it can be used.
	//  Private.
	int SQLiteWhiz::Open( )
	{
		int err = 0;
		// Attempt to open the database.
		//  Return 1 if an error occurs.
		err = sqlite3_open_v2( location, &database, SQLITE_OPEN_READWRITE, NULL );

		if( err != SQLITE_OK )
		{
			isOpen = false;
			if( loggingEnabled )
			{
				LogError( );
			}
			return 1;
		}

		if( loggingEnabled )
		{
			// Add the sqlite3 error message to the logfile.
			SQLog.Log( SQLog.INFO_Lvl, "The Database Successfully Opened: %s", location );
		}

		isOpen = true;

		return 0;
	}

	// Closes the database after use.
	//  Private.
	void SQLiteWhiz::Close( )
	{
		if( isOpen == true )
		{
			sqlite3_close( database );
			isOpen = false;
		}
	}

	bool SQLiteWhiz::CountRows( int &rows, const char *sql, ... )
	{
		if( Open() )
		{
			if( loggingEnabled )
			{
				LogError( );
				return true;
			}
		}

		char *tmp;
		sqlite3_stmt *stmt;
		const char *tail;
		int rc;
		va_list argList;
		va_start( argList, sql );

		tmp = sqlite3_vmprintf( sql, argList );

		va_end( argList );

		// Grab the number of rows in the SQL statement.
		// char *countRowsSQL = "SELECT COUNT(*) AS NumberOfCodes FROM OPC";

		rc = sqlite3_prepare( database, tmp, strlen( tmp ), &stmt, &tail );
		if( rc != SQLITE_OK )
		{
			LogError( );
			return true;
		}
		sqlite3_step( stmt );
		rows = sqlite3_column_int( stmt, 0 );

		sqlite3_finalize(stmt);

		Close( );
		return false;
	}

	bool SQLiteWhiz::GetInt( int &refVal, const char *sql, ... )
	{
		if( Open() )
		{
			if( loggingEnabled )
			{
				LogError( );
			}
			return true;
		}

		char *tmp;
		int rc;

		sqlite3_stmt *stmt;
		const char *tail;

		va_list argList;
		va_start( argList, sql );

		tmp = sqlite3_vmprintf( sql, argList );

		va_end( argList );

		if( loggingEnabled )
		{
			SQLog.Log( SQLog.INFO_Lvl, "Processing SQL Statement: '%s'", tmp );
		}
		// Prepare the SQL statement via the sqlite3_prepare function.
		rc = sqlite3_prepare( database, tmp, strlen( tmp ), &stmt, &tail );
		if( rc != SQLITE_OK )
		{
			LogError( );
		}

		int ncols = sqlite3_column_count( stmt );
		rc = sqlite3_step( stmt );

		refVal = sqlite3_column_int( stmt, 0 );

		sqlite3_finalize( stmt );

		if( loggingEnabled )
		{
			SQLog.Log( SQLog.INFO_Lvl, "SQL Statement Executed Successfully!" );
		}

		Close( );
		return false;
	}

	bool SQLiteWhiz::GetStr( std::string &refVal, const char *sql, ... )
	{
		if( Open() )
		{
			if( loggingEnabled )
			{
				LogError( );
			}
			return true;
		}

		char *tmp;
		int rc;

		sqlite3_stmt *stmt;
		const char *tail;

		va_list argList;
		va_start( argList, sql );

		tmp = sqlite3_vmprintf( sql, argList );

		va_end( argList );

		if( loggingEnabled )
		{
			SQLog.Log( SQLog.INFO_Lvl, "Processing SQL Statement: '%s'", tmp );
		}
		// Prepare the SQL statement via the sqlite3_prepare function.
		rc = sqlite3_prepare( database, tmp, strlen( tmp ), &stmt, &tail );
		if( rc != SQLITE_OK )
		{
			LogError( );
		}

		int ncols = sqlite3_column_count( stmt );
		rc = sqlite3_step( stmt );

		refVal = (char*)sqlite3_column_text( stmt, 0 );

		sqlite3_finalize( stmt );

		if( loggingEnabled )
		{
			SQLog.Log( SQLog.INFO_Lvl, "SQL Statement Executed Successfully!" );
		}

		Close( );
		return false;
	}

	bool SQLiteWhiz::GetChar( char *refVal, const char *sql, ... )
	{
		if( Open() )
		{
			if( loggingEnabled )
			{
				LogError( );
			}
			return true;
		}

		char *tmp;
		int rc;

		sqlite3_stmt *stmt;
		const char *tail;

		va_list argList;
		va_start( argList, sql );

		tmp = sqlite3_vmprintf( sql, argList );

		va_end( argList );

		if( loggingEnabled )
		{
			SQLog.Log( SQLog.INFO_Lvl, "Processing SQL Statement: '%s'", tmp );
		}

		// Prepare the SQL statement via the sqlite3_prepare function.
		rc = sqlite3_prepare( database, tmp, strlen( tmp ), &stmt, &tail );
		if( rc != SQLITE_OK )
		{
			LogError( );
		}

		int ncols = sqlite3_column_count( stmt );
		rc = sqlite3_step( stmt );

		char *tmps = NULL;

		tmps = (char*)sqlite3_column_text( stmt, 0 );

		strcpy_s( refVal, sizeof refVal, tmps );

		//strcpy_s( refVal, tmps );

		sqlite3_finalize( stmt );

		if( loggingEnabled )
		{
			SQLog.Log( SQLog.INFO_Lvl, "SQL Statement Executed Successfully!" );
		}

		Close( );
		return false;
	}

	bool SQLiteWhiz::Exe( const char *sql, ... )
	{
		if( Open( ) )
		{
			if( loggingEnabled )
			{
				LogError( );
			}
			return true;
		}

		char *err;
		char *tmp;
		va_list ap;
		va_start( ap, sql );
		tmp = sqlite3_vmprintf( sql, ap );
		va_end( ap );

		if( loggingEnabled )
		{
			SQLog.Log( SQLog.INFO_Lvl, "Processing SQL Statement: '%s'", tmp );
		}

		// Database functionality goes here.
		int rc = sqlite3_exec( database, tmp, NULL, NULL, &err );
		if( rc != SQLITE_OK )
		{
			if ( err != NULL )
			{
				if( loggingEnabled )
				{
					LogError( );
				}
				sqlite3_free( err );
			}
		}

		sqlite3_free( tmp );

		if( loggingEnabled )
		{
			SQLog.Log( SQLog.INFO_Lvl, "SQL Statement Executed Successfully!" );
		}

		Close( );
		return false;
	}

	char* SQLiteWhiz::Format( char *message, ... )
	{
		char *val;
		va_list ap;

		va_start( ap, message );
		val = sqlite3_vmprintf( message, ap );
		va_end( ap );

		return val;
	}
}

Ben Says:
April 4th, 2012 at 20:16
I stumbled across your post while looking for tips to help in the compilation of 3.7.11. (Thank you, it was helpful!) Rather than use an existing DLL and dumpbin to retrieve a current list of exported symbols, you could also define SQLITE_API using dllexport:

-D”SQLITE_API=extern __declspec(dllexport)”
Daniel Berger Says:
May 15th, 2012 at 06:42
Thanks for this. In the current version of sqlite you’ll also want to add SQLITE_ENABLE_RTREE to the preprocessor definitions.