English | 简体中文 | 繁體中文 | Русский язык | Français | Español | Português | Deutsch | 日本語 | 한국어 | Italiano | بالعربية

SQLite Basic Tutorial

SQLite Advanced Tutorial

SQLite Program Interface

SQLite C / C ++

In this chapter, you will learn how to use SQLite in C / C ++program, we use SQLite.

installation

In our C / C ++Before you start using SQLite in your program, you need to make sure that the SQLite library is set up on your computer. You can refer to the 'SQLite Installation' chapter to learn about the installation process.

C / C ++interface APIs

The following are important C / C ++ SQLite interface examples, which are sufficient to meet your needs for using SQLite from C / C ++Requirements for using SQLite database in the program. If you are looking for more complex applications, you can refer to the SQLite official documentation.

Serial NumberAPI and Description
1

sqlite3_open(const char *filename, sqlite3 **ppDb)

This routine opens a connection to the SQLite database file and returns a database connection object for use by other SQLite routines.

IffilenameIf the parameter is NULL or ':memory:', then sqlite3_open() will create a memory database in RAM, which exists only during the session.

If the filename is not NULL, then sqlite3_open() attempts to open the database file using its value. If a file with the given name does not exist, then sqlite3_open() will open a new database file with the given name.

2

sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void *data, char **errmsg)

This routine provides a quick and easy way to execute SQL commands provided by the sql parameter, which can consist of multiple SQL commands.

Here, the first parametersqlite3is an open database object,sqlite_callbackis a callback whosedatais the first parameter and will return errmsg to capture any errors raised by the routine.

sqlite3_exec() routine parses and executessqleach command specified in the sqlite parameter until the end of the string or an error occurs.

3

sqlite3_close(sqlite3*)

This routine closes the previously opened SQLite database by calling3the opened database connection_open(). All prepared statements associated with the connection should be completed before closing the connection.

If there are any incomplete queries, then sqlite3_close() will return SQLITE_BUSY, the error message cannot be closed due to incomplete statements.

Connect to the database

The following C code segment shows how to connect to an existing database. If the database does not exist, it will create the database, and finally return a database object.

#include <stdio.h>
#include <sqlite3.h>　
int main(int argc, char*　argv[]) {
　　　sqlite3　*db;
　　　char　*zErrMsg = 0;
　　　int rc;
　　　rc = sqlite3_open("test.db", &db); if( rc ) {
　　　　　　fprintf(stderr, "Failed to open database: %s\n", sqlite3_errmsg(db));
　　　　　　return(0);
　　　} else {
　　　　　　fprintf(stderr, "Database successfully opened\n");
　　　}
　　　sqlite3_close(db);
}

Now, let's compile and run the above program totest.dbCreate a database in the current directory. You can change the path as needed.

$gcc test.c　-l sqlite3
$./a.out
Database successfully opened

If you want to use C ++If you have the source code, you can compile the code as follows-

$g++　test.c　-l sqlite3

Here, we will link the program with sqlite3The libraries are linked together to provide the functions required by the C program. This will create a database file test.db in your directory, and you will get the following results.

-rwxr-xr-x.　1　root root　7383　May　8　02:06　a.out
-rw-r--r--.　1　root root　　323　May　8　02:05　test.c
-rw-r--r--.　1　root root            0 May　8　02:06　test.db

Create table

The following C code segment is used to create a table in the previously created database-

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>　
static int callback(void　*NotUsed, int argc, char　**argv, char　**azColName) {
　　　int i;
　　　for(i = 0; i<argc; i++) {
　　　　　　printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
　　　}
　　　printf("\n");
　　　return 0;
}
int main(int argc, char*　argv[]) {
　　　sqlite3　*db;
　　　char　*zErrMsg = 0;
　　　int rc;
　　　char　*sql;
　　　/*Open database*/
　　　rc = sqlite3_open("test.db", &db);　　　
　　　if( rc ) {
　　　　　　fprintf(stderr, "Failed to open database: %s\n", sqlite3_errmsg(db));
　　　　　　return(0);
　　　} else {
　　　　　　fprintf(stdout, "Database successfully opened\n");
　　　}
　　　/*　Create SQL statement　*/
　　　sql = "CREATE TABLE COMPANY("  \
　　　　　　"ID INT PRIMARY KEY            NOT NULL,"  \
　　　　　　"NAME            TEXT NOT NULL,"  \
　　　　　　"AGE            INT NOT NULL,"  \
　　　　　　"ADDRESS        CHARطلاق50),"  \
　　　　　　"SALARY        REAL);";
　　　/*　Execute SQL statement　*/
　　　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, "Table created successfully\n");
　　　}
　　　sqlite3_close(db);
　　　return 0;
}

After compiling and executing the above program, it will create the COMPANY table in test.db, and the final file list is as follows-

-rwxr-xr-x.　1　root root　9567　May　8　02:31　a.out
-rw-r--r--.　1　root root　1207　May　8　02:31　test.c
-rw-r--r--.　1　root root　3072　May　8　02:31　test.db

Insert operation

The following C code segment demonstrates how to create a record in the COMPANY table created in the above example-

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>　
static int callback(void　*NotUsed, int argc, char　**argv, char　**azColName) {
　　　int i;
　　　for(i = 0; i<argc; i++) {
　　　　　　printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
　　　}
　　　printf("\n");
　　　return 0;
}
int main(int argc, char*　argv[]) {
　　　sqlite3　*db;
　　　char　*zErrMsg = 0;
　　　int rc;
　　　char　*sql;
　　　/*　Open database　*/
　　　rc = sqlite3_open("test.db", &db);　　　
　　　if( rc ) {
　　　　　　fprintf(stderr, "Failed to open database: %s\n", sqlite3_errmsg(db));
　　　　　　return(0);
　　　} else {
　　　　　　fprintf(stderr, "Database successfully opened\n");
　　　}
　　　/*　Create SQL statement　*/
　　　sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  \
　　　　　　　　　"VALUES (1, 'Paul',　32, 'California',　20000.00 ); 	\
　　　　　　　　　"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " 	\
　　　　　　　　　"VALUES (2, 'Allen',　25　15000.00 ); \
　　　　　　　　　"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
　　　　　　　　　"VALUES (3, 'Teddy',　23, 'Norway',　20000.00 );" \
　　　　　　　　　"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
　　　　　　　　　"VALUES (4, 'Mark',　25, 'Rich-Mond ',　65000.00 );";
　　　/*　Execute SQL statement　*/
　　　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, "Record created successfully\n");
　　　}
　　　sqlite3_close(db);
　　　return 0;
}

After compiling and executing the above program, it will create the given record in the COMPANY table and display the following two lines-

Database opened successfully
Record created successfully

Select operation

Before we continue with the actual example to retrieve records, let's look at some details about the callback functions used in the example. The callback provides a method to retrieve results from the SELECT statement. It has the following declaration-

typedef int (*sqlite3_callback)_TypeInfo(
　　　void*,　　　　/*　sqlite3_exec() of the4Data provided in the parameters of the　*/
　　　int,　　　　　　/*　Number of columns in the row　*/
　　　char**,　　　/*　String array representing fields in the row　*/
　　　char**　　　　/*　String array representing column names　*/
);

If the above callback is provided as the third parameter to the sqlite_exec() routine, SQLite will call this callback function for each record processed in each SELECT statement executed within the SQL parameters.

The following C code segment shows how to retrieve and display records from the COMPANY table created in the above example.-

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>　
static int callback(void　*data, int argc, char　**argv, char　**azColName){
　　　int i;
　　　fprintf(stderr, "%s: ", (const char*)data);
　　　
　　　for(i = 0; i<argc; i++){
　　　　　　printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
　　　}
　　　
　　　printf("\n");
　　　return 0;
}
int main(int argc, char*　argv[]) {
　　　sqlite3　*db;
　　　char　*zErrMsg = 0;
　　　int rc;
　　　char　*sql;
　　　const char*　data = "Callback function called";
　　　/*　Open database　*/
　　　rc = sqlite3_open("test.db", &db);　　　
　　　if( rc ) {
　　　　　　fprintf(stderr, "Failed to open database: %s\n", sqlite3_errmsg(db));
　　　　　　return(0);
　　　} else {
　　　　　　fprintf(stderr, "Database successfully opened\n");
　　　}
　　　/*　Create SQL statement　*/
　　　sql = "SELECT　*　from COMPANY";
　　　/*　Execute SQL statement　*/
　　　rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
　　　
　　　if( rc != SQLITE_OK ) {
　　　　　　fprintf(stderr, "SQL error: %s\n", zErrMsg);
　　　　　　sqlite3_free(zErrMsg);
　　　} else {
　　　　　　fprintf(stdout, "Operation completed successfully\n");
　　　}
　　　sqlite3_close(db);
　　　return 0;
}

After compiling and executing the above program, the following results will be produced.

Database opened successfully
Callback function called: ID =　1
NAME = Paul
AGE =　32
ADDRESS = California
SALARY =　20000.0
Callback function called: ID =　2
NAME = Allen
AGE =　25
ADDRESS = Texas
SALARY =　15000.0
Callback function called: ID =　3
NAME = Teddy
AGE =　23
ADDRESS = Norway
SALARY =　20000.0
Callback function called: ID =　4
NAME = Mark
AGE =　25
ADDRESS = Rich-Mond
SALARY =　65000.0
Operation completed successfully

UPDATE operation

The following C code segment shows how to use the UPDATE statement to update any record and then retrieve and display the updated record from the COMPANY table.

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>　
static int callback(void　*data, int argc, char　**argv, char　**azColName){
　　　int i;
　　　fprintf(stderr, "%s: ", (const char*)data);
　　　
　　　for(i = 0; i<argc; i++) {
　　　　　　printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
　　　}
　　　printf("\n");
　　　return 0;
}
int main(int argc, char*　argv[]) {
　　　sqlite3　*db;
　　　char　*zErrMsg = 0;
　　　int rc;
　　　char　*sql;
　　　const char*　data = "Callback function called";
　　　/*　Open database　*/
　　　rc = sqlite3_open("test.db", &db);　　　
　　　if( rc ) {
　　　　　　fprintf(stderr, "Failed to open database: %s\n", sqlite3_errmsg(db));
　　　　　　return(0);
　　　} else {
　　　　　　fprintf(stderr, "Database successfully opened\n");
　　　}
　　　/*Create a combined SQL statement*/
　　　sql = "UPDATE COMPANY set SALARY =　25000.00 where ID=1; \

　　　　　　　　　"SELECT　*　from COMPANY";
　　　/*　Execute SQL statement　*/
　　　rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
　　　
　　　if( rc != SQLITE_OK ) {
　　　　　　fprintf(stderr, "SQL error: %s\n", zErrMsg);
　　　　　　sqlite3_free(zErrMsg);
　　　} else {
　　　　　　fprintf(stdout, "Operation done successfully\n");
　　　}
　　　sqlite3_close(db);
　　　return 0;
}

After compiling and executing the above program, the following results will be produced.

Database opened successfully
Callback function called: ID =　1
NAME = Paul
AGE =　32
ADDRESS = California
SALARY =　25000.0
Callback function called: ID =　2
NAME = Allen
AGE =　25
ADDRESS = Texas
SALARY =　15000.0
Callback function called: ID =　3
NAME = Teddy
AGE =　23
ADDRESS = Norway
SALARY =　20000.0
Callback function called: ID =　4
NAME = Mark
AGE =　25
ADDRESS = Rich-Mond
SALARY =　65000.0
Operation done successfully

Delete operation

The following C code segment demonstrates how to use the DELETE statement to delete any record and then retrieve and display the remaining records from the COMPANY table.

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>　
static int callback(void　*data, int argc, char　**argv, char　**azColName) {
　　　int i;
　　　fprintf(stderr, "%s: ", (const char*)data);
　　　
　　　for(i = 0; i<argc; i++) {
　　　　　　printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
　　　}
　　　printf("\n");
　　　return 0;
}
int main(int argc, char*　argv[]) {
　　　sqlite3　*db;
　　　char　*zErrMsg = 0;
　　　int rc;
　　　char　*sql;
　　　const char*　data = "Callback function called";
　　　/*　Open database　*/
　　　rc = sqlite3_open("test.db", &db);　　　
　　　if( rc ) {
　　　　　　fprintf(stderr, "Failed to open database: %s\n", sqlite3_errmsg(db));
　　　　　　return(0);
　　　} else {
　　　　　　fprintf(stderr, "Database successfully opened\n");
　　　}
　　　/*　Create a combined SQL statement　*/
　　　sql = "DELETE from COMPANY where ID=",2; \

　　　　　　　　　"SELECT　*　from COMPANY";
　　　/*　Execute SQL statement　*/
　　　rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
　　　
　　　if( rc != SQLITE_OK ) {
　　　　　　fprintf(stderr, "SQL error: %s\n", zErrMsg);
　　　　　　sqlite3_free(zErrMsg);
　　　} else {
　　　　　　fprintf(stdout, "Operation done successfully\n");
　　　}
　　　sqlite3_close(db);
　　　return 0;
}

After compiling and executing the above program, the following results will be produced.

Database opened successfully
Callback function called: ID =　1
NAME = Paul
AGE =　32
ADDRESS = California
SALARY =　20000.0
Callback function called: ID =　3
NAME = Teddy
AGE =　23
ADDRESS = Norway
SALARY =　20000.0
Callback function called: ID =　4
NAME = Mark
AGE =　25
ADDRESS = Rich-Mond
SALARY =　65000.0
Operation done successfully
SQLite JavaSQLite Common Functions