Software

Using SQLite with C++

by Professor Petabyte

How to Install SQLite3 on Windows 11

1. Download SQLite Tools
• Go to the official SQLite download page: https://www.sqlite.org/download.html
• Scroll down to the section titled "Precompiled Binaries for Windows"
• Download the ZIP file named: sqlite-tools-win32-x86-*.zip (The asterisk * will be the version number, e.g., `sqlite-tools-win32-x86-3420000.zip`)


2. Extract the ZIP File
• Right-click the downloaded ZIP file and select "Extract All..."
• Choose a location to extract the files (e.g., 'C:\sqlite)


3. Add SQLite to Your System PATH

This lets you run 'sqlite3' from any command prompt window.

• Press Windows key + S, type "Environment Variables", and select "Edit the system environment variables"
• In the System Properties window, click "Environment Variables..."
• Under System variables, find and select "Path", then click "Edit..."
• Click "New" and add the path to the folder where you extracted SQLite (e.g., 'C:\sqlite')
• Click OK on all windows to save and exit


4. Verify Installation
• Open Command Prompt (press Windows key, type 'cmd', and hit Enter)
• Type:

  sqlite3

• If installed correctly, you'll see the SQLite prompt:

  SQLite version 3.x.x 202x-xx-xx
  Enter ".help" for usage hints.

5. Start Using SQLite
• You can now create databases, run SQL queries, and explore SQLite features directly from the command line.

Example: C++ with SQLite

#include 
#include    // SQLite library header

// Callback function to handle query results
static int callback(void* NotUsed, int argc, char** argv, char** azColName) {
    for (int i = 0; i < argc; i++) {
        std::cout << azColName[i] << " = " 
                  << (argv[i] ? argv[i] : "NULL") << "\n";
    }
    std::cout << "-------------------\n";
    return 0;
}

int main() {
    sqlite3* DB;
    char* errorMessage;

    // 1. Open database (creates file if it doesn't exist)
    int exit = sqlite3_open("example.db", &DB);
    if (exit) {
        std::cerr << "Error opening DB: " << sqlite3_errmsg(DB) << std::endl;
        return -1;
    }
    std::cout << "Opened database successfully!\n";

    // 2. Create a table
    std::string createTable = 
        "CREATE TABLE IF NOT EXISTS users("
        "id INTEGER PRIMARY KEY AUTOINCREMENT,"
        "name TEXT NOT NULL,"
        "age INT NOT NULL);";

    exit = sqlite3_exec(DB, createTable.c_str(), NULL, 0, &errorMessage);
    if (exit != SQLITE_OK) {
        std::cerr << "Error creating table: " << errorMessage << std::endl;
        sqlite3_free(errorMessage);
    }

    // 3.Insert sample data
    std::string insertData = 
        "INSERT INTO users (name, age) VALUES('Alice', 30);"
        "INSERT INTO users (name, age) VALUES('Bob', 25);";

    exit = sqlite3_exec(DB, insertData.c_str(), NULL, 0, &errorMessage);
    if (exit != SQLITE_OK) {
        std::cerr << "Error inserting data: " << errorMessage << std::endl;
        sqlite3_free(errorMessage);
    }

    // 4.Read & output the data
    std::string query = "SELECT * FROM users;";
    std::cout << "\nQuery Results:\n";
    exit = sqlite3_exec(DB, query.c_str(), callback, NULL, &errorMessage);
    if (exit != SQLITE_OK) {
        std::cerr << "Error selecting data: " << errorMessage << std::endl;
        sqlite3_free(errorMessage);
    }

    sqlite3_close(DB);
    return 0;
}

Output Example

Opened database successfully!

Query Results:
id = 1
name = Alice
age = 30
-------------------
id = 2
name = Bob
age = 25
-------------------

In This Example:

1. Opens a SQLite database file (example.db)
2. Creates a users table
3. Inserts two records
4. Selects and prints them

Conclusion

SQLite is an effective database for 'small' amounts of data, upto a few thousand records. The maximum theoretical size for an SQLite database is approximately 281 terabytes (TB), however practical limitations usually come into play before reaching this theoretical maximum:

• Filesystem limitations
  The underlying filesystem on which the SQLite database file resides may impose its own maximum file size, which can be less than SQLite's limit.


• Performance considerations
  As the database grows significantly, performance can degrade, especially with frequent writes or complex queries, due to the single-file nature of SQLite and potential I/O bottlenecks.


• Memory and system resources
  The amount of available memory and other system resources can also become a limiting factor for very large databases, particularly when dealing with large query results or caching.


• Application-specific needs
  The specific requirements and usage patterns of an application will dictate what constitutes a "realistic" maximum size for an SQLite database in that context. While SQLite can handle hundreds of gigabytes, for extremely large-scale, high-concurrency, or distributed data needs, a client/server database might be more suitable.
  

It is if nothing else an excellent free tool to learn about basic SQL queries. It may not have all of the 'bells and whistles' that a full SQL database such as SQL-Server has, but it is nonetheless very capable for small projects and learning SQL.