Home »
C programming language
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
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/