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?
Submitted by IncludeHelp, on September 12, 2019

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 has following features

  • Serverless
  • Zero Configuration
  • Transactional SQL database engine

It supports following operating systems

  • Linux
  • Windows
  • Mac
  • Solaris
  • Android

It can be used with following programming languages

  • C
  • C++
  • PHP
  • Java
  • Python etc

In this article we will learn Sqlite3 tool for SQLite database, 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.

Creation for database in SQLite

    $ 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.

How to check created tables in SQLite database?

    Sqlite> .tables

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

How to exit from database?

    Sqlite> .exit

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

Here, we will use the Linux operating system and GNU C Compiler (GCC). 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.

Now, we will know how to compile this program?

    $ 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 .

Execution of the file:

    $ ./gerversion

Output

3.8.2

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/






Comments and Discussions

Ad: Are you a blogger? Join our Blogging forum.





Languages: » C » C++ » C++ STL » Java » Data Structure » C#.Net » Android » Kotlin » SQL
Web Technologies: » PHP » Python » JavaScript » CSS » Ajax » Node.js » Web programming/HTML
Solved programs: » C » C++ » DS » Java » C#
Aptitude que. & ans.: » C » C++ » Java » DBMS
Interview que. & ans.: » C » Embedded C » Java » SEO » HR
CS Subjects: » CS Basics » O.S. » Networks » DBMS » Embedded Systems » Cloud Computing
» Machine learning » CS Organizations » Linux » DOS
More: » Articles » Puzzles » News/Updates


© https://www.includehelp.com some rights reserved.