Delete data from the database sqlite3

# Include <stdio. h>
# Include <sqlite3.h>
# Include <stdlib. h>
# DEFINE _ debug _
Int main (void)
{
Sqlite3 * DB = NULL; // declare SQLite key structure pointer
Char * zerrmsg = 0;
Int RC;
// Open or create a database file
Rc = sqlite3_open ("Delete. DB ", & dB); // open the specified database file. If it does not exist, a database file with the same name will be created. You need to input a pointer to the database, because sqlite3_open functions need to allocate memory for these pointers so that the DB Pointer Points to the memory zone.
If (RC! = Sqlite_ OK) {// or RC
Fprintf (stderr, "can't open database: % s \ n", sqlite3_errmsg (db ));
Sqlite3_close (db );
Exit (1); // open failed, exit
}
Else printf ("You have opened a sqlite3 database named Delete. DB successfully! \ Ncongratulation! Have fun! \ N ");
// Create a table
Char * SQL = "CREATE TABLE sensordata (ID integer primary key, sensorid integer, sitenum integer, name varchar (12), sensorparamter real );";
Sqlite3_exec (dB, SQL, 0, 0, & zerrmsg );
# Ifdef _ debug _ // If _ debug _ has been defined earlier, the information of the created table is output.
Printf ("zerrmsg = % s \ n", zerrmsg );
# Endif
// Insert data
SQL = "insert into \" sensordata \ "values (null, 11,12, 'beijing', 1);"; // enter the content in the table
Sqlite3_exec (dB, SQL, 0, 0, & zerrmsg );
SQL = "insert into \" sensordata \ "values (null, 13, 14, 'shanghai', 2 );";
Sqlite3_exec (dB, SQL, 0, 0, & zerrmsg );
SQL = "insert into \" sensordata \ "values (null, 15, 16, 'guangzhou ', 3 );";
Sqlite3_exec (dB, SQL, 0, 0, & zerrmsg );
SQL = "insert into \" sensordata \ "values (null, 15, 16, 'shenzhen ', 4 );";
Sqlite3_exec (dB, SQL, 0, 0, & zerrmsg );
Int ROW = 0, column = 0; // used to record the number of rows and columns in the following result set
Char ** result; // a two-dimensional array is used to store results.
// Query data
SQL = "select * From sensordata"; // query statement. The function interface corresponding to the C language is sqlite3_get_table (dB, SQL, & result, & Row, & column, & zerrmsg)
Sqlite3_get_table (dB, SQL, & result, & Row, & column, & zerrmsg); // result: stores the data to be queried in an array. The first is the table name, then the data
Int I = 0;
Printf ("row: % d column: % d \ n", row, column );
Printf ("\ nthe result of deleteing is: \ n ");
// Output the queried data
For (I = 0; I <(row + 1) * column; I ++)
Printf ("result [% d] = % s \ n", I, result [I]);
// Delete data
SQL = "delete from sensordata where sensorid = 11"; // Delete the data row corresponding to Beijing
Sqlite3_exec (dB, SQL, 0, 0, & zerrmsg );
# Ifdef _ debug _
Printf ("zerrmsg = % s \ n", zerrmsg );
# Endif
// Output the deleted data
SQL = "select * From sensordata ";
Sqlite3_get_table (dB, SQL, & result, & Row, & column, & zerrmsg );
Printf ("\ n \ nrow: % d column: % d \ n", row, column );
Printf ("\ nafter deleting, the result of deleting is: \ n ");
For (I = 0; I <(row + 1) * column; I ++)
Printf ("result [% d] = % s \ n", I, result [I]);
Sqlite3_free_table (result); // release the memory space of result
# Ifdef _ debug _ // If _ debug _ has been defined earlier, the information of the created table is output.
Printf ("zerrmsg = % s \ n", zerrmsg); // retain the error message. If it is null, no error message is returned during execution.
# Endif
Sqlite3_close (db); // close the database
Return 0;
}
Note:
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 *, // lst argument to callback function
Char ** ermsg // error MSG written here
);
The function prototype of sqlite3_get_table is as follows:
Int sqlite3_get_table (sqlite3 *, const char * SQL, char *** result, int * nrow, int * ncolumn, char ** errmsg );
// Result: stores the data to be queried in an array. First, the table name is followed by the data. nrow and ncolumn are used to record the number of rows and columns returned by the query statement, respectively, if no result is found, 0 is returned.
Compile:
Root @ cky-desktop:/home/src # gcc-O Delete-l sqlite3 Delete. c
Run the executable file:
Root @ cky-desktop:/home/src # sudo./delete
You have opened a sqlite3 database named Delete. DB successfully!
Congratulation! Have fun!
Table sensordata already exists
Zerrmsg = (null)
Row: 4 column: 5
The result of deleteing is:
Result [0] = ID
Result [1] = sensorid
Result [2] = sitenum
Result [3] = Name
Result [4] = sensorparamter
Result [5] = 1
Result [6] = 11
Result [7] = 12
Result [8] = Beijing
Result [9] = 1.0
Result [10] = 2
Result [11] = 13
Result [12] = 14
Result [13] = Shanghai
Result [14] = 2.0
Result [15] = 3
Result [16] = 15
Result [17] = 16
Result [18] = Guangzhou
Result [19] = 3.0
Result [20] = 4
Result [21] = 15
Result [22] = 16
Result [23] = Shenzhen
Result [24] = 4.0
Zerrmsg = (null)
Row: 3 column: 5
After deleting, the result of deleting is:
Result [0] = ID
Result [1] = sensorid
Result [2] = sitenum
Result [3] = Name
Result [4] = sensorparamter
Result [5] = 2
Result [6] = 13
Result [7] = 14
Result [8] = Shanghai
Result [9] = 2.0
Result [10] = 3
Result [11] = 15
Result [12] = 16
Result [13] = Guangzhou
Result [14] = 3.0
Result [15] = 4
Result [16] = 15
Result [17] = 16
Result [18] = Shenzhen
Result [19] = 4.0
Zerrmsg = (null)