SQLite with C language

SQLite with C: In this tutorial, we are going to learn about the SQLite with C programming language, we will learn how to create SQLite database, how to check created tables, etc? By IncludeHelp Last updated : April 13, 2023

Introduction to SQLite database

SQLite is a relational database; it is used for embedded devices. Now a day it is using worldwide for different embedded devices.

SQLite Database Features

SQLite database has following features:

Serverless
Zero Configuration
Transactional SQL database engine

Operating Systems that SQLite Database Supports

SQLite supports the following operating systems:

Linux
Windows
Mac
Solaris
Android

Languages Support SQLite

SQLite can be used with the following programming languages

Delphi
C
C#
C++
Go
Java
JavaScript
LiveCode
Lue
Objective-C
Perl
PHP
Python
Ruby
Visual Basic, and
Xojo

How to Use sqlite3 Tool for SQLite?

To use Sqlite3 tool first we need to install it into our system, it is easily available for all operating systems. Sqlite3 tool is a command-line tool. We can use all the most SQL statement in the SQLite database.

Create Database

$ sqlite3 mytest.db
SQlite version 3.8.2 2015-11-07 15:54:36
Enter  ".help" for instructions
Enter SQL statements terminated with a ";"

In the above example, we passed the database name with the sqlite3 tool using the command line, now "mytest.db" database will be created in your system.

Check Tables

    Sqlite> .tables

The above command display list of created tables in selected database.

Exit from Database

    Sqlite> .exit

Above command is used to exit or quit from selected database.

SQLite (sqlite3) with C Programming

Requirements

Linux operating system
GNU C Compiler (GCC)

First C program with SQLite (sqlite3)

We will write the first program by which we can get the version number of installed Sqlite database.

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

int main()
{
    printf("%s\n", sqlite3_libversion());
    return 0;
}

In the above example, we include header file <sqlite3.h> that contains a declaration for multiple functions but we used one of them is sqlite3_libversion() which returns the version number of installed SQLite database.

Compile C program with SQLite (sqlite3)

$ gcc getversion.c -o getversion -lsqlite3 -std=c

In above compilation statement getversion.c is source code file and getversion is executable output file. And to compile sqlite library we need to use complile flags like -lsqlite -std=c .

Execute C program with SQLite (sqlite3)

$ ./gerversion

Output

3.8.2

Working C program with SQLite (sqlite3)

Now, we use another example for getting SQLite database version using SQLite Query in c program.

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

int main(){
    sqlite3* DB;
    sqlite_stmt* RES;
    int rec = 0;

    rec = sqlite3_open(":memory:", &DB);
    if (rec != SQLITE_OK) {
        printf("\nDatabase cannot opened: %s", sqlite3_errmsg(DB));
        sqlite3_close(DB);
        return 1;
    }

    rec = sqlite3_prepare_v2(DB, "select SQLITE_VERSION()", -1, &RES, 0);
    if (rec != SQLITE_OK) {
        printf("\nFailed to get data: %s", sqlite3_errmsg(DB));
        sqlite3_close(DB);
        return 1;
    }

    rec = sqlite3_step(RES);
    if (rec == SQLITE_ROW) {
        printf("%s\n", sqlite3_column_text(RES, 0));
    }

    sqlite3_finalize(RES);
    sqlite3_close(DB);

    return 0;
}

In the above example, we used SQLITE_VERSION() query to get version number of SQLite database.

Also, we used the following pointers:

    sqlite3 *DB;
    sqlite3_stmt *RES;

Here, DB is used to represent database and RES is used to represent SQL statement.

We used following functions in above example:

sqlite3_open() to open data here we use ":memory:" to read sqlite version.
sqlite3_close() to close opened database.
sqlite3_errmsg() to display error message.
sqlite3_prepare_v2() to execute SQL query.
sqlite3_step() to get record one by one.
sqlite3_finalize() is used to destroy the object of a prepared statement.

Reference: http://zetcode.com/db/sqlitec/