With no version of SQL Server and SQL Compact available for Windows 10 IoT and Windows 10 Phone, the suggested alternative is SQLite. Whilst early evaluations indicate it isn’t available for Win10 IoT Background and Console apps, it’s not too hard to use SQLite with a Windows 10 UWP (Universal) app. The app can be rebuilt without modification and using the exact same project content for the desktop, phone and IoT Windows 10 targets.

Note: The solution code for this blog will be added to https://iotsampler.codeplex.com Its not there yet. Coming with the next blog in this series.

About SQLite

“SQLite is an in-process library that implements a self-contained, server-less, zero-configuration, transactional SQL database engine. The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private. SQLite is the most widely deployed database in the world with more applications than we can count, including several high-profile projects.

  • · SQLite is an embedded SQL database engine.
  • · Unlike most other SQL databases:

o SQLite does not have a separate server process.

o SQLite reads and writes directly to ordinary disk files.

  • · A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file.
  • · The database file format is cross-platform - you can freely copy a database between 32-bit and 64-bit systems or between big-endian and little-endian architectures.

o It can be compiled for ARM and x86/64 CPUs

  • · It is well suited for application data storage.
  • · It is not as a replacement for relational database servers but is a replacement for file open/close.”
    Ref:
    https://sqlite.org/about.html

A SQLite Exerciser Console App

This app gives the newbie a broad brush introduction to SQLite. The Universal app in the next section will implement the same functionality in a UI.

1. Download the SQLite C++ library files: (from http://www.sqlite.org/download.html)

· sqlite-amalgamation-201507021816.zip
OR

· sqlite-amalgamation-3081002.zip

2. Extract the files. Note the four files:

· sqlit3.h

· sqlite3.c

· sqlite3ext.h

· shell.c

We want the first two. These provide the native sqlite library and can be used to build a Win32 static lib, a DLL or included in a Win32 console app.

3. In Visual Studio 2015 on a Windows 10 system create a new C++ Console project. I often disable PrecompileHeaders.

4. Copy the two files above into the project folder and add them to the project.

5. Test build the project, set an x86 target. Try it for ARM if you wish.

6. Copy the main source file from here and overwrite your main source file with this. Note the change to main interface.

#include "stdafx.h"
#include <stdio.h>
#include "sqlite3.h"
#include <string.h>

FILE * fstream;

#define DBNAME "C:\\Temp\\MyDB"

// SQLite DBMS Syntax: http://www.sqlite.org/lang.html
// SQLite C++ API: http://www.sqlite.org/c3ref/intro.html
// Callback to make use of returned queries with sqlite_exec3
static int callback(void *NotUsed, int argc, char **argv, char **azColName)
{
     return 0;
}

// Check to the database file exists
bool FileExists(const TCHAR *filename)
{
     DWORD fileAttr;
     fileAttr = GetFileAttributes(fileName);
     if (0xFFFFFFFF == fileAttr)
              return false;
     return TRUE;
}

// Convert Widestring to char *
void ConvertToChar(LPCWSTR inStr, char * outStr
{
     WideCharToMultiByte(CP_UTF8, 0, inStr, -1, outStr, sizeof(outStr), NULL, NULL);
}

// Create the sqlite temp directory
static void sqlite_init()
{
}

int main(int argc, char* argv)
{
     sqlite3 *db;
     char *zErrMsg = 0;
     int rc;

     LPCWSTR dbName = _T(DBNAME);
     char dbNameChar = DBNAME;
     return 0;
}

7. Build the app for x86 on the Local Machine, deploy and test it.
Just check that all is well this far.


SQLite temporary files:

From http://www.sqlite.org/c3ref/open.html

The encoding used for the filename argument of sqlite3_open() and sqlite3_open_v2() must be UTF-8, not whatever codepage is currently defined. Filenames containing international characters must be converted to UTF-8 prior to passing them into sqlite3_open() or sqlite3_open_v2().

From: http://www.sqlite.org/c3ref/temp_directory.html (and above link)

The temporary directory must be set prior to calling sqlite3_open or sqlite3_open_v2. Otherwise, various features that require the use of temporary files may fail.
 

8. Implement sqlite_init() as follows:

LPCWSTR zPath = L"c:\\Temp\\Sqlite";
LPCWSTR logfFile = L"c:\\Temp\\Sqlite.log";
fstream = _wfopen(logfFile, L"w");

//Create the sqlite temp dir directory if it doesn't exist
//Assume if it does exist then the temp dir has been set already
if (!FileExists(zPath))
{
     char zPathBuf[MAX_PATH + 1];
     ConvertToChar(zPath, zPathBuf);
     sqlite3_temp_directory = sqlite3_mprintf("%s", zPathBuf);
     fwprintf(fstream, L"Set sqlite temp directory\r\n");
}

Callback makes use of returned queries with sqlite_exec3

9. Implement callback() as follows:

     int i;

     for (i = 0; i<argc; i++) {

          //Some Unicode conversions 
          TCHAR colStr[MAX_PATH + 1];
          TCHAR valStr[MAX_PATH + 1];
          valStr[0] = (TCHAR)0;

          MultiByteToWideChar(CP_UTF8, 0, azColName[i], -1, colStr, MAX_PATH + 1);
          if (argv[i] != NULL)
               MultiByteToWideChar(CP_UTF8, 0, argv[i], -1, valStr, MAX_PATH + 1);
               fwprintf(fstream,L"%s = %s\t", colStr, valStr);
     }
     fwprintf(fstream,L"\r\n");
     return 0;

10. Add the following code in main() to delete the database file if it exists and to open and close it
Build and test the app.

     //Delete the db if it exists
     if (FileExists(dbName))
     {
          fwprintf(fstream,L"DB File exists so deleting it.\r\n");
          DeleteFile(dbName);
     }
     rc = sqlite3_open(dbNameChar, &db);
     if (rc) {
          fwprintf(fstream, L"Error: Can't open database: %s\n", sqlite3_errmsg(db));
          sqlite3_close(db);
          return 1;
     }
     fwprintf(fstream,L"Opened the DB\r\n");
     sqlite3_close(db);
     fwprintf(fstream,L"Closed the DB\r\n");

11. Add the following code in main() at between the  first and last lines (in blue) so as to create a new table, add some records and run a query for them.

Build and test the app.

     fwprintf(fstream,L"Opened the DB\r\n"); // <—This line is already in main()
     rc = sqlite3_exec(db, "CREATE TABLE tblStuff ( id INTEGER PRIMARY KEY , name, value INTEGER )", callback, 0, &zErrMsg);
     fwprintf(fstream,L"Created a table\r\n");
     //Hint: Insert NULL for id to auto-increment as its INTEGER PRIMARY KEY

     rc = sqlite3_exec(db, "INSERT INTO tblStuff Values (NULL,'Temperature1',23)", callback, 0, &zErrMsg);
     rc = sqlite3_exec(db, "INSERT INTO tblStuff Values (NULL,'Temperature2',67)", callback, 0, &zErrMsg);
     rc = sqlite3_exec(db, "INSERT INTO tblStuff Values (NULL,'Humidity1',98)", callback, 0, &zErrMsg);

     fwprintf(fstream,L"Added some records:\r\n");
     rc = sqlite3_exec(db, "SELECT * FROM tblStuff", callback, 0, &zErrMsg);
     if (rc != SQLITE_OK) {
          fwprintf(fstream, L"Error: SQL error: %s\n", zErrMsg);
          sqlite3_free(zErrMsg);
     }
     sqlite3_close(db);
     fwprintf(fstream,L"Closed the DB\r\n"); //<--  This line is already in main()

12. Insert the following code in main() below that previous code to test reopening the database.
Build and test the app.

     //Reopen the db
     rc = sqlite3_open(dbNameChar, &db);
     if (rc) {
          fwprintf(fstream, L"Error: Can't open database: %s\n", sqlite3_errmsg(db));
          close(db);
          return 1;
     }

     fwprintf(fstream,L"Reopened the DB\r\n");
     fwprintf(fstream,L"Records are still there?:\r\n");
     rc = sqlite3_exec(db, "SELECT * FROM tblStuff", callback, 0, &zErrMsg);

     //Insert code add some more records
     //Insert code to do some Update and Delete functions.
     //Insert code to drop (delete) the table.

     if (rc != SQLITE_OK) {
          fwprintf(fstream, L"Error: SQL error: %s\n", zErrMsg);
          sqlite3_free(zErrMsg);
     }
     sqlite3_close(db);
     fwprintf(fstream,L"Closed the DB\r\n");

13. Add the following code in main() at //Insert code add some more records
Build and test the app.

     rc = sqlite3_exec(db, "INSERT INTO tblStuff Values (NULL,'Temperature1',79)", callback, 0, &zErrMsg);
     rc = sqlite3_exec(db, "INSERT INTO tblStuff Values (NULL,'Temperature2',88)", callback, 0, &zErrMsg);
     rc = sqlite3_exec(db, "INSERT INTO tblStuff Values (NULL,'Humidity1',70)", callback, 0, &zErrMsg);
     fwprintf(fstream,L"Added some more records:\r\n");
     rc = sqlite3_exec(db, "SELECT * FROM tblStuff", callback, 0, &zErrMsg);

14. Add the following code in main() at //Insert code to do some Update and Delete functions.

     rc = sqlite3_exec(db, "UPDATE tblStuff SET name = 'Temperature10' WHERE name = 'Temperature1'", callback, 0, &zErrMsg);
     rc = sqlite3_exec(db, "DELETE FROM tblStuff WHERE name = 'Humidity1'", callback, 0, &zErrMsg);
     fwprintf(fstream, L"Updated and deleted some records:\r\n");
     rc = sqlite3_exec(db, "SELECT * FROM tblStuff", callback, 0, &zErrMsg);

15. (Optionally) Add the following code in main() at //Insert code to drop (delete) the table.

     rc = sqlite3_exec(db, "DROP TABLE tblStuff", callback, 0, &zErrMsg);
     fwprintf(fstream,L"Dropped the table\r\n");

16. Build and test the app. Smile


Comment:

This app won’t run on Windows 10 IoT. A subsequent blog will discuss this issue. The next blog will though implement a Universal Windows Platform App (Universal App) that runs on the desktop, phone and IoT devices to exercise SQLite functionality similar to this app.