Linux C programming Database (sqlite3 Application)

The C/C ++ language calls the SQLite function interface to manage databases (create databases, create tables, insert data, query, and delete data ).

Compile
SQLite library file:
Libsqlite3.a libsqlite3.la libsqlite3.so libsqlite3.so. 0 libsqlite3.so. 0.8.6 pkgconfig
Executable file:
Sqlite3

This test:
The Directory of the sqlite3 library file is:/usr/local/lib
The Directory of the executable sqlite3 file is/usr/local/bin.
The Directory of the header file sqlite3.h is:/usr/local/include

Use the LS command to view the following information:
[Root @ localhost config] # ls/usr/local/lib
Libclamav. A libclamunrar_iface.a libclamunrar. So libsqlite3.so
Libclamav. La libclamunrar_iface.la libclamunrar. so.5 libsqlite3.so. 0
Libclamav. So libclamunrar_iface.so libclamunrar. so.5.0.3 libsqlite3.so. 0.8.6
Libclamav. so.5 libclamunrar_iface.so.5 libmstring. So pkgconfig
Libclamav. so.5.0.3 libclamunrar_iface.so.5.0.3 libsqlite3.a
Libclamunrar. A libclamunrar. La libsqlite3.la

This directory contains the library files:
Libsqlite3.a libsqlite3.la libsqlite3.so libsqlite3.so. 0 libsqlite3.so. 0.8.6 pkgconfig

Start SQLite programming:
1. the following example shows how to use the C/C ++ interface of SQLite. the database name is obtained by the first parameter and the first parameter or more parameters are SQL Execution statements.
This function calls sqlite3_open () to open the database, and CALLS sqlite3_close () to close the database connection.
Procedure 1: opendbslite. C:

# Include <stdio. h> <br/> # include <stdlib. h> <br/> # include <sqlite3.h> <br/> int main (void) <br/>{< br/> sqlite3 * DB = NULL; <br/> char * zerrmsg = 0; <br/> int RC; </P> <p> // open the specified database file, if not, a database file with the same name will be created. <br/> rc = sqlite3_open ("zieckey. DB ", & dB); <br/> If (RC) <br/> {<br/> fprintf (stderr," can't open database: % s/n ", sqlite3_errmsg (db); <br/> sqlite3_close (db); <br/> exit (1 ); <br/>}< br/> else PR INTF ("You have opened a sqlite3 database named zieckey. DB successfully! /Ncongratulations! Have fun! ^-^/N "); </P> <p> sqlite3_close (db); // close the database <br/> return 0; <br/>}< br/>

Compile (problem ):
[Root @ localhost liuxltest] # gcc-O opendbsqlite. c
/Tmp/ccuquuqn. O: In function 'main ':
Opendbsqlite. c :(. Text + 0x2e): Undefined reference to 'sqlite3 _ open'
Opendbsqlite. c :(. Text + 0x42): Undefined reference to 'sqlite3 _ errmsg'
Opendbsqlite. c :(. Text + 0x67): Undefined reference to 'sqlite3 _ close'
Opendbsqlite. c :(. Text + 0x8a): Undefined reference to 'sqlite3 _ close'
Collect2: LD returned 1 exit status

Compile (solution ):
The above problem occurs because the library file is not found.
Because the user's own library file is used, the library used should be specified. We can compile it like this:
[Root @ localhost liuxltest] # gcc-O opendbsqlite. C-lsqlite3
You can use the-lsqlite3 option. (The library file we generated earlier is libsqlite3.so. 0.8.6, and so on. Remove the lib and later version marks, and sqlite3 is left. Therefore, it is-lsqlite3)
Run:
[Root @ localhost liuxltest] #./opendbsqlite
You have opened a sqlite3 database named zieckey. DB successfully!
Congratulations! Have fun! ^-^
[Root @ localhost liuxltest] #

 

2. Insert: insert
Insert data to the database in C language:
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 */
);

Procedure 2: insert. C:

# Include <stdio. h> <br/> # include <stdlib. h> <br/> # include "sqlite3.h" <br/> # DEFINE _ debug _ <br/> int main (void) <br/>{< br/> sqlite3 * DB = NULL; <br/> char * zerrmsg = 0; <br/> int RC; <br/> rc = sqlite3_open ("zieckey. DB ", & dB); // open the specified database file. If not, a database file with the same name will be created. <br/> If (RC) <br/>{< br/> fprintf (stderr, "can't open database: % s/n", sqlite3_errmsg (db )); <br/> sqlite3_close (db); <br/> exit (1); <br/>}< br/> E LSE printf ("You have opened a sqlite3 database named zieckey. DB successfully! /Ncongratulations! Have fun! ^-^/N "); <br/> // creates a table. If the table exists, it is not created and a prompt message is displayed, stored in zerrmsg <br/> char * SQL = "CREATE TABLE sensordata (/<br/> ID integer primary key,/<br/> sensorid integer, /<br/> sitenum integer,/<br/> time varchar (12),/<br/> sensorparameter real/<br/> );"; <br/> sqlite3_exec (dB, SQL, 0, 0, & zerrmsg); <br/> # ifdef _ debug _ <br/> printf ("% s/n ", zerrmsg); <br/> # endif <br/> // insert data <br/> char * sql1 = "insert into/" sensordata/"values (null, 1, 1, '20140901', 200605011206); "; <br/> sqlite3_exec (dB, sql1, 0, 0, & zerrmsg ); <br/> char * sql2 = "insert into/" sensordata/"values (null, 1, 1, '20140901', 200605011306 );"; <br/> sqlite3_exec (dB, sql2, 0, 0, & zerrmsg); <br/> sqlite3_close (db); // close the database <br/> return 0; <br/>}< br/>

Compile and run:
[Root @ localhost liuxltest] # gcc-O insert. C-lsqlite3
[Root @ localhost liuxltest] #./insert
You have opened a sqlite3 database named zieckey. DB successfully!
Congratulations! Have fun! ^-^
(Null)
(Null)
(Null)
[Root @ localhost liuxltest] #
Check whether data is inserted:
[Root @ localhost liuxltest] #/usr/local/bin/sqlite3 zieckey. DB "select * From sensordata"

 

3. Query: selete
Query data in the database in C language.
Function interface sqlite3_get_table (dB, SQL, & azresult, & nrow, & ncolumn, & zerrmsg );
Explanation:
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.
Procedure 3: Query. C:
# Include <stdio. h> <br/> # include <stdlib. h> <br/> # include "sqlite3.h" <br/> # DEFINE _ debug _ <br/> int main (void) <br/>{< br/> sqlite3 * DB = NULL; <br/> char * zerrmsg = 0; <br/> int RC; </P> <p> rc = sqlite3_open ("zieckey. DB ", & dB); // open the specified database file. If not, a database file with the same name will be created. <br/> If (RC) <br/>{< br/> fprintf (stderr, "can't open database: % s/n", sqlite3_errmsg (db )); <br/> sqlite3_close (db); <br/> exit (1); <br/>} <Br/> else printf ("You have opened a sqlite3 database named zieckey. DB successfully! /Ncongratulations! Have fun! ^-^/N "); </P> <p> // create a table. If the table exists, it is not created and a prompt is displayed, stored in zerrmsg <br/> char * SQL = "CREATE TABLE sensordata (/<br/> ID integer primary key,/<br/> sensorid integer, /<br/> sitenum integer,/<br/> time varchar (12),/<br/> sensorparameter real/<br/> );"; <br/> sqlite3_exec (dB, SQL, 0, 0, & zerrmsg ); <br/> # ifdef _ debug _ <br/> printf ("zerrmsg = % s/n", zerrmsg ); <br/> # endif </P> <p> // insert data <br /> SQL = "insert into/" sensordata/"values (null, 1, 1, '000000', 200605011206);"; <br/> sqlite3_exec (dB, SQL, 0, 0, & zerrmsg); </P> <p> SQL = "insert into/" sensordata/"values (null, 1, 1, '123', 200605011306 ); "; <br/> sqlite3_exec (dB, SQL, 0, 0, & zerrmsg); </P> <p> int nrow = 0, ncolumn = 0; <br/> char ** azresult; // result of storing two-dimensional arrays <br/> // query data <br/>/* <br/> int sqlite3_get_table (sqli TE3 *, const char * SQL, char *** result, int * nrow, int * ncolumn, char ** errmsg ); <br/> the result stores the data you query in the form of an array, first the table name and then the data. <Br/> nrow and ncolumn are the number of rows and columns of the result set returned by the query statement, 0 <br/> */<br/> SQL = "select * From sensordata"; <br/> sqlite3_get_table (dB, SQL, & azresult, & nrow, & ncolumn, & zerrmsg); <br/> int I = 0; <br/> printf ("row: % d column = % d/N", nrow, ncolumn); <br/> printf ("/nthe result of querying is:/N"); </P> <p> for (I = 0; I <(nrow + 1) * ncolumn; I ++) <br/> printf ("azresult [% d] = % s/n", I, azresult [I]); <br/> // release the azresult's memory space <br/> sqlite3_free_table (azresult ); </P> <p> # ifdef _ debug _ <br/> printf ("zerrmsg = % s/n", zerrmsg ); <br/> # endif <br/> sqlite3_close (db); // close the database <br/> return 0; </P> <p >}</P> <p>

Here, a query statement is used: "select * From sensordata ",
Compile and run:
[Root @ localhost liuxltest] # gcc-O query. C-lsqlite3
[Root @ localhost liuxltest] #./Query
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)
[Root @ localhost liuxltest] #
Here we can see that the first five pieces of azresult are exactly the attributes of the sensordata column in our table, and 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/n", I, azresult [I]);

The output contains zerrmsg = (null), which is the error message retained by zerrmsg. As you can see, zerrmsg is null, indicating that there is no error message during execution.

4. Delete: Delete
C language to delete specific data in the database.
Procedure 4: delete. C:

# Include <stdio. h> <br/> # include <stdlib. h> <br/> # include "sqlite3.h" <br/> # DEFINE _ debug _ <br/> int main (void) <br/>{< br/> sqlite3 * DB = NULL; <br/> char * zerrmsg = 0; <br/> int RC; </P> <p> rc = sqlite3_open ("zieckey. DB ", & dB); // open the specified database file. If not, a database file with the same name will be created. <br/> If (RC) <br/>{< br/> fprintf (stderr, "can't open database: % s/n", sqlite3_errmsg (db )); <br/> sqlite3_close (db); <br/> exit (1); <br/>} <Br/> else printf ("You have opened a sqlite3 database named zieckey. DB successfully! /Ncongratulations! Have fun! ^-^/N "); </P> <p> // create a table. If the table exists, it is not created and a prompt is displayed, stored in zerrmsg <br/> char * SQL = "CREATE TABLE sensordata (/<br/> ID integer primary key,/<br/> sensorid integer, /<br/> sitenum integer,/<br/> time varchar (12),/<br/> sensorparameter real/<br/> );"; <br/> sqlite3_exec (dB, SQL, 0, 0, & zerrmsg ); <br/> # ifdef _ debug _ <br/> printf ("zerrmsg = % s/n", zerrmsg ); <br/> # endif </P> <p> // insert data <br/> SQL = "insert into/" sensordata/"values (null, 1, 1, '20140901', 200605011206); "; <br/> sqlite3_exec (dB, SQL, 0, 0, & zerrmsg ); </P> <p> SQL = "insert into/" sensordata/"values (null, 23, 45, '123', 200605011306 );"; <br/> sqlite3_exec (dB, SQL, 0, 0, & zerrmsg); </P> <p> SQL = "insert into/" sensordata/"values (null, 34, 45, '000000', 200605011306); "; <br/> sqlite3_exec (dB, SQL, 0, 0, & zerrmsg ); </P> <p> int nrow = 0, ncolumn = 0; <br/> char ** azresult; // two-dimensional array storage result <br/> // query data <br/> SQL = "select * From sensordata"; <br/> sqlite3_get_table (dB, SQL, & azresult, & nrow, & ncolumn, & zerrmsg); <br/> int I = 0; <br/> printf ("row: % d column = % d/N", nrow, ncolumn); <br/> printf ("/nthe result of querying is:/N"); <br/> for (I = 0; I <(nrow + 1) * ncolumn; I ++) <br/> printf ("azresult [% d] = % s/n", I, azresult [I]); <br/> // delete data <br/> SQL = "delete from sensordata where sensorid = 1;"; <br/> sqlite3_exec (dB, SQL, 0, 0, & zerrmsg); <br/> # ifdef _ debug _ <br/> printf ("zerrmsg = % s/n", zerrmsg ); <br/> # endif <br/> SQL = "select * From sensordata"; <br/> sqlite3_get_table (dB, SQL, & azresult, & nrow, & ncolumn, & zerrmsg); <br/> printf ("/n/nrow: % d column = % d", nrow, ncolumn ); <br/> printf ("/nafter deleting, the result of querying is:/N"); <br/> for (I = 0; I <(nrow + 1) * ncolumn; I ++) <br/> printf ("azresult [% d] = % s/n", I, azresult [I]); </P> <p> // release the azresult memory <br/> sqlite3_free_table (azresult ); </P> <p> # ifdef _ debug _ <br/> printf ("zerrmsg = % s/n", zerrmsg ); <br/> # endif <br/> sqlite3_close (db); // closes the database <br/> return 0; </P> <p >}< br/>

Compile and run:
[Root @ localhost liuxltest] # gcc-O delete Delete. C-lsqlite3
[Root @ localhost liuxltest] #./delete
You have opened a sqlite3 database named zieckey. DB successfully!
Congratulations! Have fun! ^-^
Zerrmsg = (null)
Row: 3 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] = 23
Azresult [12] = 45
Azresult [13] = 200605011306
Azresult [14] = 16.4
Azresult [15] = 3
Azresult [16] = 34
Azresult [17] = 45
Azresult [18] = 200605011306
Azresult [19] = 15.4
Zerrmsg = (null)

Row: 2 column = 5
After deleting, the result of querying is:
Azresult [0] = ID
Azresult [1] = sensorid
Azresult [2] = sitenum
Azresult [3] = Time
Azresult [4] = sensorparameter
Azresult [5] = 2
Azresult [6] = 23
Azresult [7] = 45
Azresult [8] = 200605011306
Azresult [9] = 16.4
Azresult [10] = 3
Azresult [11] = 34
Azresult [12] = 45
Azresult [13] = 200605011306
Azresult [14] = 15.4
Zerrmsg = (null)
[Root @ localhost liuxltest] #
From the output results of the program, we can see that we have three records before the data is deleted. After the data is deleted, we find that there are fewer records in the database. This allows us to delete data.