How to operate the database sqlite3 in C/C ++ in Linux (very good! Design compilation links and many other issues !)

From: http://blog.chinaunix.net/uid-21556133-id-118208.html

Install sqlite3:
Download the sqlite3.2.2 source code from www.sqlite.org and follow the steps in readme:
Tar xzf sqlite3.2.2.tar.gz
Mkdir BLD
CD BLD
../Sqlite3.2.2/configure
Make
Make install
Run the sqlite3 test. DB command in shell to check whether the installation is successful.

0. Introduction

This article describes how to call the SQLite function interface in C/C ++ to manage databases,
This includes creating databases, creating tables, inserting data, querying data, and deleting data.
1. Description

Here we assume that you have compiled the SQLite library file:
Libsqlite3.a libsqlite3.la libsqlite3.so libsqlite3.so. 0 libsqlite3.so. 0.8.6 pkgconfig
And executable files: sqlite3
Assume that the installation directory of your sqlite3 is in the/usr/local/sqlite3 directory.
If not, copy your installation file to the directory/usr/local/sqlite3,
In this way, the following operations are more unified to reduce the probability of errors.
For example: [root @ localhost Home] # cp-RF sqlite-3.3.8-ix86 // usr/local/sqlite3
Here suppose/home/sqlite-3.3.8-ix86/is your installation directory, that is, your SQLite is installed here
In this case, the Directory of the sqlite3 library is:/usr/local/sqlite3/lib
The Directory of the executable sqlite3 file is/usr/local/sqlite3/bin.
The Directory of the header file sqlite3.h is:/usr/local/sqlite3/include
Haola, Now we start our sqlite3 programming journey in Linux.
2. Start

Now we will perform a test.
Now let's write a C/C ++ program to call the SQLite API function.
The following example shows how to use the C/C ++ interface of SQLite. the database name is obtained by the first parameter and the second or more parameters are SQL Execution statements. this function calls sqlite3_open () to open the database in line 16, and sqlite3_close () to close the database connection in line 25.
[Root @ localhost temp] # vi opendbsqlite. c
Press the I key to switch to the input mode and enter the following code:
// Name: opendbsqlite. c
// This prog is used to test C/C ++ API for sqlite3.it is very simple, ha!
// Author: zieckey All Rights Reserved.
// Data: 2006/11/13
# Include <stdio. h>
# Include <sqlite3.h>
Int main (void)
{
Sqlite3 * DB = NULL;
Char * zerrmsg = 0;
Int RC;
// Open the specified database file. If it does not exist, a database file with the same name will be created.
Rc = sqlite3_open ("zieckey. DB", & dB );
If (RC)
{
Fprintf (stderr, "can't open database: % s
", Sqlite3_errmsg (db ));
Sqlite3_close (db );
Exit (1 );
}
Else printf ("You have opened a sqlite3 database named zieckey. DB successfully!
Congratulations! Have fun! ^-^
");
Sqlite3_close (db); // close the database
Return 0;
}
Exit and save. (After the code is entered, Press ESC and enter: WQ. Press enter to pull it)
Haola, now compiled: [root @ localhost temp] # GCC opendbsqlite. C-o dB. Out
Or you may encounter the following problems:
[Root @ localhost temp] # GCC opendbsqlite. C-o dB. Out
Opendbsqlite. C: 11: 21: sqlite3.h: No file or directory
Opendbsqlite. C: In function 'main ':
Opendbsqlite. C: 19: 'sqlite3 'undeclared (first use in this function)
Opendbsqlite. C: 19: (each undeclared identifier is reported only once
Opendbsqlite. C: 19: for each function it appears in .)
Opendbsqlite. C: 19: 'db' undeclared (first use in this function)
This is because the header file is not found.
You may encounter a problem like this:
[Root @ localhost temp] # GCC opendbsqlite. C-o dB. Out
/Tmp/cctkitnn. O (. Text + 0x2b): In function 'main ':
: Undefined reference to 'sqlite3 _ open'
/Tmp/cctkitnn. O (. Text + 0x45): In function 'main ':
: Undefined reference to 'sqlite3 _ errmsg'
/Tmp/cctkitnn. O (. Text + 0x67): In function 'main ':
: Undefined reference to 'sqlite3 _ close'
/Tmp/cctkitnn. O (. Text + 0x8f): In function 'main ':
: Undefined reference to 'sqlite3 _ close'
Collect2: LD returned 1 exit status
This is a problem where the library file is not found.
Next we will solve these problems.
Because the user's own library file is used, the library used should be specified. We can compile it like this:
[Root @ localhost temp] # GCC opendbsqlite. C-o dB. Out-lsqlite3
I can use the-lsqlite3 option (the Library file we generated earlier is libsqlite3.so. 0.8.6, etc,
Remove the preceding lib and later version marks, and sqlite3 is left. Therefore, it is-lsqlite3 ).
If we select the installation path during compilation and installation, for example:
.......
# ../SQLite/configure -- prefix =/usr/local/sqlite3
# Make
.......
In this way, the library file of SQLite will be generated in the/usr/local/sqlite3/lib directory during compilation and installation.
The SQLite header file will be generated under the/usr/local/sqlite3/include directory.
In this case, you must specify the path of the library file because the default path does not contain/usr/local/sqlite3/lib.
[Root @ localhost temp] # GCC opendbsqlite. C-o dB. Out-lsqlite3-L/usr/local/sqlite3/lib
If not, you may need to specify the path of the header file sqlite3.h as follows:
[Root @ localhost temp] # GCC opendbsqlite. C-o dB. Out-lsqlite3-L/usr/local/sqlite3/lib-I/usr/local/sqlite3/include
In this way, you can compile and run:
[Root @ localhost temp] #./DB. Out
./DB. Out: Error while loading shared libraries: libsqlite3.so. 0: cannot open shared object file: no such file or directory
The preceding error may occur during running.
This problem occurs because no static compilation is selected during the compilation process, so it is dynamically compiled according to the default compilation.
After dynamic compilation, because the executable file must call the system library file at runtime,
Then, you may not be able to find the library files you need by searching the default library file path.
This causes errors such as "error while loading shared libraries.
We can solve this problem as follows:
Method 1: static Compilation
Add the-static parameter during compilation, for example
[Root @ localhost temp] # GCC opendbsqlite. C-o dB. Out-lsqlite3-L/usr/local/sqlite3/lib-I/usr/local/sqlite3/include-static
[Root @ localhost temp] # ll
Total usage 1584
-Rwxr-XR-x 1 Root 1596988 November 13 10:50 dB. Out
-RW-r -- 1 Root 614 November 13 10:31 opendbsqlite. c
The output file dB. Out is 1596988 kb.
Run, okay, no error
[Root @ localhost temp] #./DB. Out
You have opened a sqlite3 database named zieckey. DB successfully!
Congratulations! Have fun! ^-^
Method 2: reconfigure the system environment variable LD_LIBRARY_PATH
In this case, you must specify the path of the libsqlite3.so. 0 library file, that is, configure the system environment variable LD_LIBRARY_PATH,
So that the system can find libsqlite3.so. 0.
Remove-static. During Compilation:
[Root @ localhost temp] # GCC opendbsqlite. C-o dB. Out-lsqlite3-L/usr/local/sqlite3/lib-I/usr/local/sqlite3/include
[Root @ localhost temp] # ll
Total usage 36
-Rwxr-XR-x 1 Root 12716 November 13 10:56 dB. Out
-RW-r -- 1 Root 614 November 13 10:31 opendbsqlite. c
[Root @ localhost temp] #
The size of the output file dB. Out is 12716 kb, which is much smaller than the static compilation.
Therefore, we recommend using the dynamic compilation method.
Now let's specify the value of the system environment variable LD_LIBRARY_PATH.
Input in shell:
[Root @ localhost temp] # export LD_LIBRARY_PATH =/usr/local/sqlite3/lib: $ LD_LIBRARY_PATH
Run again
[Root @ localhost temp] #./DB. Out
You have opened a sqlite3 database named zieckey. DB successfully!
Congratulations! Have fun! ^-^
Is it a sense of accomplishment? Well, it's very quick to get started.
3. insert: insert

We just learned how to call the C/C ++ API of sqlite3. Let's take a look at how to insert data to the database in C.
Okay. Now we edit a piece of C code named insert. C.
// Name: insert. c
// This prog is used to test C/C ++ API for sqlite3. It is very simple, ha!
// Author: zieckey All Rights Reserved.
// Data: 2006/11/18
# Include <stdio. h>
# Include <stdlib. h>
# Include "sqlite3.h"
# DEFINE _ debug _
Int main (void)
{
Sqlite3 * DB = NULL;
Char * zerrmsg = 0;
Int RC;
Rc = sqlite3_open ("zieckey. DB", & dB); // open the specified database file. If it does not exist, a database file with the same name will be created.
If (RC)
{
Fprintf (stderr, "can't open database: % s
", Sqlite3_errmsg (db ));
Sqlite3_close (db );
Exit (1 );
}
Else printf ("You have opened a sqlite3 database named zieckey. DB successfully!
Congratulations! Have fun! ^-^
");
// Create a table. If the table exists, it is not created and a prompt is provided, which is stored in zerrmsg.
Char * SQL = "CREATE TABLE sensordata (
Id integer primary key,
Sensorid integer,
Sitenum integer,
Time varchar (12 ),
Sensorparameter real
);";
Sqlite3_exec (dB, SQL, 0, 0, & zerrmsg );
# Ifdef _ debug _
Printf ("% s
", Zerrmsg );
# Endif
// Insert data
SQL = "insert into" sensordata "values (null, 1, 1, '20140901', 200605011206 );";
Sqlite3_exec (dB, SQL, 0, 0, & zerrmsg );
SQL = "insert into" sensordata "values (null, 1, 1, '20140901', 200605011306 );";
Sqlite3_exec (dB, SQL, 0, 0, & zerrmsg );
Sqlite3_close (db); // close the database
Return 0;
}
Okay. Write the above code into a file and name it insert. C.
Explanation:
The prototype of sqlite3_exec is described as follows:
Int sqlite3_exec (
Sqlite3 *,/* an open database */
Const char * SQL,/* SQL to be executed */
Sqlite_callback,/* callback function */
Void *,/* 1st argument to callback function */
Char ** errmsg/* error MSG written here */
);
Compile:
[Root @ localhost temp] # GCC insert. C-lsqlite3-L/usr/local/sqlite3/lib-I/usr/local/sqlite3/include
Insert. C: 28: 21: Warning: multi-line string literals are deprecated
[Root @ localhost temp] #
Run
[Root @ localhost temp] #./A. Out
./A. Out: Error while loading shared libraries: libsqlite3.so. 0: cannot open shared object file: no such file or directory
[Root @ localhost temp] #
In the same case, for example, the preceding solution:
[Root @ localhost temp] # export LD_LIBRARY_PATH =/usr/local/sqlite3/lib: $ LD_LIBRARY_PATH
[Root @ localhost temp] #./A. Out
You have opened a sqlite3 database named zieckey. DB successfully!
Congratulations! Have fun! ^-^
(Null)
(Null)
(Null)
[Root @ localhost temp] #
Run successfully. Now let's see if data is inserted.
[Root @ localhost temp] #/usr/local/sqlite3/bin/sqlite3 zieckey. DB
SQLite version 3.3.8
Enter ". Help" for instructions
SQLite> select * From sensordata;
1 | 1 | 1 | 1 | 200605011206 | 18.9
2 | 1 | 1 | 1 | 200605011306 | 16.4
SQLite>
Haha, it's already inserted, isn't it?
Is it easy?
4. Query: selete

Now, we know how to call the C/C ++ API of sqlite3 to create a database, create a table, and insert data,
Next we will look at how to query data in the database in C language.
Okay. Now we edit a piece of C code named query. C.
// Name: Query. c
// This prog is used to test C/C ++ API for sqlite3. It is very simple, ha!
// Author: zieckey All Rights Reserved.
// Data: 2006/11/18
# Include <stdio. h>
# Include <stdlib. h>
# Include "sqlite3.h"
# DEFINE _ debug _
Int main (void)
{
Sqlite3 * DB = NULL;
Char * zerrmsg = 0;
Int RC;
Rc = sqlite3_open ("zieckey. DB", & dB); // open the specified database file. If it does not exist, a database file with the same name will be created.
If (RC)
{
Fprintf (stderr, "can't open database: % s
", Sqlite3_errmsg (db ));
Sqlite3_close (db );
Exit (1 );
}
Else printf ("You have opened a sqlite3 database named zieckey. DB successfully!
Congratulations! Have fun! ^-^
");
// Create a table. If the table exists, it is not created and a prompt is provided, which is stored in zerrmsg.
Char * SQL = "CREATE TABLE sensordata (
Id integer primary key,
Sensorid integer,
Sitenum integer,
Time varchar (12 ),
Sensorparameter real
);";
Sqlite3_exec (dB, SQL, 0, 0, & zerrmsg );
# Ifdef _ debug _
Printf ("zerrmsg = % s
", Zerrmsg );
# Endif
// Insert data
SQL = "insert into" sensordata "values (null, 1, 1, '20140901', 200605011206 );";
Sqlite3_exec (dB, SQL, 0, 0, & zerrmsg );
SQL = "insert into" sensordata "values (null, 1, 1, '20140901', 200605011306 );";
Sqlite3_exec (dB, SQL, 0, 0, & zerrmsg );
Int nrow = 0, ncolumn = 0;
Char ** azresult; // The result is stored in a two-dimensional array.
// Query data
/*
Int sqlite3_get_table (sqlite3 *, const char * SQL, char *** result, int * nrow, int * ncolumn, char ** errmsg );
Result stores the data you query in the form of an array, first the table name, and then the data.
Nrow and ncolumn are the number of rows in the result set returned by the query statement, and the number of columns. If no result is found, 0 is returned.
*/
SQL = "select * From sensordata ";
Sqlite3_get_table (dB, SQL, & azresult, & nrow, & ncolumn, & zerrmsg );
Int I = 0;
Printf ("row: % d column = % d
", Nrow, ncolumn );
Printf ("
The result of querying is:
");
For (I = 0; I <(nrow + 1) * ncolumn; I ++)
Printf ("azresult [% d] = % s
", I, azresult [I]);
// Release the azresult's memory space
Sqlite3_free_table (azresult );
# Ifdef _ debug _
Printf ("zerrmsg = % s
", Zerrmsg );
# Endif
Sqlite3_close (db); // close the database
Return 0;
}
Here we use a query statement "select * From sensordata ",
In C language, the corresponding function interface is sqlite3_get_table (dB, SQL, & azresult, & nrow, & ncolumn, & zerrmsg );
This function interface has been commented out in the program.
Next let's compile and run it,
[Root @ localhost temp] # export LD_LIBRARY_PATH =/usr/local/sqlite3/lib: $ LD_LIBRARY_PATH
[Root @ localhost temp] # GCC query. C-lsqlite3-L/usr/local/sqlite3/lib-I/usr/local/sqlite3/include
Query. C: 29: 21: Warning: multi-line string literals are deprecated
[Root @ localhost temp] #./A. Out
You have opened a sqlite3 database named zieckey. DB successfully!
Congratulations! Have fun! ^-^
Zerrmsg = (null)
Row: 2 column = 5
The result of querying is:
Azresult [0] = ID
Azresult [1] = sensorid
Azresult [2] = sitenum
Azresult [3] = Time
Azresult [4] = sensorparameter
Azresult [5] = 1
Azresult [6] = 1
Azresult [7] = 1
Azresult [8] = 200605011206
Azresult [9] = 18.9
Azresult [10] = 2
Azresult [11] = 1
Azresult [12] = 1
Azresult [13] = 200605011306
Azresult [14] = 16.4
Zerrmsg = (null)
Here we can see that the first five pieces of azresult are exactly the attributes of the sensordata column in our table,
Then the data we want to query. Therefore, in our program, we only have the I <(nrow + 1) * ncolumn judgment condition:
For (I = 0; I <(nrow + 1) * ncolumn; I ++)
Printf ("azresult [% d] = % s", I, azresult [I]);
The output contains a statement like zerrmsg = (null), which is an error message retained by zerrmsg,
As you can see, the zerrmsg is null, indicating that there is no error message during execution.