Home » C programming language

Table creation, data insertion in SQLite database using C language

SQLite with C (create table & insert data): Here, we are going to learn how to create a table and insert data in the table in SQLite database using C programming language?
Submitted by IncludeHelp, on September 14, 2019

C programs to create table & insert data in SQLite database

Here, will create a "SCHOOL.db" database, create a table "STUDENT" and insert data using c program.

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

int main(void) 
{
    
    sqlite3 * dbPtr;
    char *errMsg = 0;
    
    int rec = sqlite3_open("SCHOOL.db", & dbPtr);
    
    if (rec != SQLITE_OK) 
    {        
        printf("Database cannot Opened: %s\n", sqlite3_errmsg(dbPtr));
        sqlite3_close(dbPtr);
        
        return 1;
    }
    
    char *sql = "DROP TABLE IF EXISTS STUDENT;" 
                "CREATE TABLE STUDENT(rollNum INT, Name TEXT, Section varchar );" 
                "INSERT INTO STUDENTS VALUES(1, 'Arvind', 'A');" 
                "INSERT INTO STUDENT VALUES(2, 'Amit', 'C');" 
                "INSERT INTO STUDENT VALUES(3, 'Shivang', 'A');" 
                "INSERT INTO STUDENT VALUES(4, 'RAGHU', 'A');" 
                "INSERT INTO STUDENT VALUES(5, 'SACHIN', 'C');" 
                "INSERT INTO STUDENT VALUES(6, 'HITEN', 'B');";

    rec = sqlite3_exec(dbPtr, sql, 0, 0, & errMsg);
    
    if (rec != SQLITE_OK ) {
        
        printf("SQL error: %s\n", errMsg);
        
        sqlite3_free(errMsg);        
        sqlite3_close(dbPtr);
        
        return 1;
    } 
    
    sqlite3_close(dbPtr);    
    return 0;
}

In the above example, we are connecting to database "SCHOOL.db" database, if the database is already created into the database, if the database is not created in SQLITE3 database then an error message will be prompt on display screen.

If we connected successfully to the database then we STUDENT table will be created automatically, we table is already created into the database then it drops the table and creates the table again.

char *sql = 
	"DROP TABLE IF EXISTS STUDENT;" 
	"CREATE TABLE STUDENT(rollNum INT, Name TEXT, Section varchar );" 
	"INSERT INTO STUDENTS VALUES(1, 'Arvind', 'A');" 
	"INSERT INTO STUDENT VALUES(2, 'Amit', 'C');" 
	"INSERT INTO STUDENT VALUES(3, 'Shivang', 'A');" 
	"INSERT INTO STUDENT VALUES(4, 'RAGHU', 'A');" 
	"INSERT INTO STUDENT VALUES(5, 'SACHIN', 'C');" 
	"INSERT INTO STUDENT VALUES(6, 'HITEN', 'B');";

In the above code, we made a string that contains CREATE and INSERT and DROP statements. Here we are inserting 6 rows into STUDENT table. Here all statements are separated by a semicolon.

    rec = sqlite3_exec(dbPtr, sql, 0, 0, & errMsg);

In the above statement, we are using sqlite3_exec() function to execute SQL queries into the SQLite database.

If the given SQL statement is not incorrect format. Then it produces an error message.

    sqlite3_free(errMsg);

Then above function will free errMsg.

Now come to terminal to check data is inserted into the database or not? First, we set console mode and on the header to see headers of the table.

sqlite> .mode column  
sqlite> .headers on

sqlite> SELECT * FROM STUDENT;

rollNum       Name        Section     
----------  ----------  ----------
1		Arvind		A
2		Amit		C
3		Shivang		A
4		RAGHU		A
5		SACHIN		C
6		HITEN		B 

The above data is written into STUDENT table of "SCHOOL.db" database.

How to know last inserted row id?

Sometimes we required what is the last inserted row id into the database table? Here in the below code, we will get last inserted row id using c program.

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

int main(void) 
{
    
    sqlite3 * dbPtr;
    char *errMsg = 0;
    
    int rec = sqlite3_open("SCHOOL.db", & dbPtr);
    
    if (rec != SQLITE_OK) 
    {        
        printf("Database cannot Opened: %s\n", sqlite3_errmsg(dbPtr));
        sqlite3_close(dbPtr);
        
        return 1;
    }
    
    char *sql = "DROP TABLE IF EXISTS STUDENT;" 
                "CREATE TABLE STUDENT(id INT PRIMARY KEY, Name TEXT, Section varchar );" 
                "INSERT INTO STUDENTS VALUES('Arvind', 'A');" 
                "INSERT INTO STUDENT VALUES('Amit', 'C');" 
                "INSERT INTO STUDENT VALUES('Shivang', 'A');" 
                "INSERT INTO STUDENT VALUES('RAGHU', 'A');" 
                "INSERT INTO STUDENT VALUES('SACHIN', 'C');" 
                "INSERT INTO STUDENT VALUES('HITEN', 'B');";
    
    rec = sqlite3_exec(dbPtr, sql, 0, 0, & errMsg);
    
    if (rec != SQLITE_OK )
    {
        printf("SQL error: %s\n", errMsg);
        sqlite3_free(errMsg);        
    } 
    else 
    {    
        printf("Table STUDENT created successfully\n");
    }
    
    int lastId = sqlite3_last_insert_rowid(dbPtr);
    printf("Last inserted row id is %d\n", lastId);

    sqlite3_close(dbPtr);
    
    return 0;
}

Here, "STUDENT" table is created and id column is automatically incremented.

    int lastId = sqlite3_last_insert_rowid(dbPtr);

In above statement, sqlite3_last_insert_rowid() function is used to get last inserted row id, it returns integer value.

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