SQLite - C/C++ for Beginners

Hello there, aspiring programmers! Today, we're going to embark on an exciting journey into the world of SQLite using C/C++. Don't worry if you've never written a line of code before - we'll start from the very basics and work our way up. By the end of this tutorial, you'll be amazed at what you can accomplish!

What is SQLite?

Before we dive into the code, let's understand what SQLite is. Imagine you have a digital notebook where you can store all kinds of information in an organized way. That's essentially what SQLite is - a lightweight, file-based database that doesn't require a separate server process. It's like having a mini-database right in your pocket!

C/C++ Interface APIs

To interact with SQLite using C/C++, we need to use something called APIs (Application Programming Interfaces). Think of these as special tools that allow our C/C++ program to communicate with SQLite. Here are the main APIs we'll be using:

API Function	Description
sqlite3_open()	Opens a connection to an SQLite database
sqlite3_exec()	Executes SQL statements
sqlite3_close()	Closes the database connection
sqlite3_errmsg()	Returns error messages

Don't worry if these seem intimidating now. We'll explore each one with plenty of examples!

Connect To Database

Let's start by connecting to a database. This is like opening our digital notebook to start writing.

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

int main() {
    sqlite3 *db;
    int rc = sqlite3_open("test.db", &db);

    if (rc) {
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
        return(0);
    } else {
        fprintf(stderr, "Opened database successfully\n");
    }

    sqlite3_close(db);
    return 0;
}

Let's break this down:

1. We include the necessary headers: stdio.h for input/output and sqlite3.h for SQLite functions.
2. In main(), we declare a pointer to sqlite3, which represents our database connection.
3. We use sqlite3_open() to open (or create) a database named "test.db".
4. We check if the connection was successful. If not, we print an error message.
5. Finally, we close the database connection with sqlite3_close().

Create a Table

Now that we have our notebook open, let's create a page to store some information. In database terms, we call this a table.

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

    rc = sqlite3_open("test.db", &db);

    if (rc) {
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
        return(0);
    }

    char *sql = "CREATE TABLE STUDENTS("
                "ID INT PRIMARY KEY     NOT NULL,"
                "NAME           TEXT    NOT NULL,"
                "AGE            INT     NOT NULL,"
                "GRADE          CHAR(1) );";

    rc = sqlite3_exec(db, sql, 0, 0, &zErrMsg);

    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
    } else {
        fprintf(stdout, "Table created successfully\n");
    }

    sqlite3_close(db);
    return 0;
}

In this example:

1. We open the database as before.
2. We define an SQL statement to create a table named STUDENTS with columns for ID, NAME, AGE, and GRADE.
3. We use sqlite3_exec() to execute this SQL statement.
4. We check if the execution was successful and print an appropriate message.

INSERT Operation

Great! We have our table. Now let's add some data to it.

char *sql = "INSERT INTO STUDENTS (ID,NAME,AGE,GRADE) "
            "VALUES (1, 'Alice', 20, 'A' ); "
            "INSERT INTO STUDENTS (ID,NAME,AGE,GRADE) "
            "VALUES (2, 'Bob', 21, 'B' ); ";

rc = sqlite3_exec(db, sql, 0, 0, &zErrMsg);

if (rc != SQLITE_OK) {
    fprintf(stderr, "SQL error: %s\n", zErrMsg);
    sqlite3_free(zErrMsg);
} else {
    fprintf(stdout, "Records inserted successfully\n");
}

Here, we're inserting two records into our STUDENTS table. The SQL statement uses the INSERT INTO syntax to add new rows.

SELECT Operation

Now, let's retrieve the data we just inserted. This is where the real magic of databases shines!

static int callback(void *NotUsed, int argc, char **argv, char **azColName) {
    for(int i = 0; i < argc; i++) {
        printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    printf("\n");
    return 0;
}

// In main()
char *sql = "SELECT * from STUDENTS";
rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);

if (rc != SQLITE_OK) {
    fprintf(stderr, "SQL error: %s\n", zErrMsg);
    sqlite3_free(zErrMsg);
} else {
    fprintf(stdout, "Operation done successfully\n");
}

This example introduces a callback function. When we SELECT data, SQLite calls this function for each row in the result. It's like having a helper that reads out each entry in our notebook!

UPDATE Operation

What if we made a mistake and need to change some data? That's where UPDATE comes in.

char *sql = "UPDATE STUDENTS set GRADE = 'A' where ID=2";
rc = sqlite3_exec(db, sql, 0, 0, &zErrMsg);

if (rc != SQLITE_OK) {
    fprintf(stderr, "SQL error: %s\n", zErrMsg);
    sqlite3_free(zErrMsg);
} else {
    fprintf(stdout, "Records updated successfully\n");
}

This SQL statement changes Bob's grade to an A. Good job, Bob!

DELETE Operation

Sometimes, we need to remove data. Let's see how to delete a record.

char *sql = "DELETE from STUDENTS where ID=2";
rc = sqlite3_exec(db, sql, 0, 0, &zErrMsg);

if (rc != SQLITE_OK) {
    fprintf(stderr, "SQL error: %s\n", zErrMsg);
    sqlite3_free(zErrMsg);
} else {
    fprintf(stdout, "Record deleted successfully\n");
}

This removes Bob's record from our table. Sorry, Bob!

And there you have it! We've covered the basics of working with SQLite in C/C++. Remember, programming is like learning a new language - it takes practice. Don't be discouraged if things don't click immediately. Keep experimenting, and soon you'll be writing database applications like a pro!

Credits: Image by storyset