Linux C sqlite3 Programming

Document directory

• Sqlite3
• Common functions

Sqlite3

SQLite is a lightweight database. Similar to common MySQL databases. Compared with MySQL, the data type is basically the same, but all SQLite commands are ". "(for example, in MySQL, check which databases are" show databases "and in SQLite ". databases "). Of course, SQL statements have the same syntax rules.

1. Host system: archlinux
2. GCC: 4.6.1 20110819
3. Sqlite3: 3.7.8 20110919

The SQLite database is not installed by default. Therefore, you must first install sqlite3:

1. # Pacman-s sqlite3

You can use "sqlite3 + database name" to open a database.

Common functions

1. Sqlite3_open (Const Char* Filename, sqlite3 ** ppdb );
2. Sqlite3_close (sqlite3 * dB );
3. Sqlite3_exec (sqlite3 *,Const Char* SQL,Int(* Callback )(Void*,Int,Char**,Char**),Void*,Char** Errmsg );

These three functions can basically complete all sqlite3 operations. The header file to be used is "sqlite3.h ".

• Open (new) Database
  1. Sqlite3 * dB;
  2. Sqlite3_open ("test. DB", & dB );
• Close Database
  1. Sqlite3_close (db );
• Execute SQL statements (take table creation as an example)
  1. CharSQL [128];
  2. Memset (SQL, \ 0, 128 );
  3. Sprintf (SQL, "% S % s", "CREATE TABLE", "test_tb", "(ID integer primary key, Data Text )");
  4. Sqlite3_exec (dB, SQL, null );

  The meaning of the first and second parameters in sqlite3_exec is well understood. The third parameter is a callback function. The fourth parameter can be used to input a parameter to the callback function. The fifth parameter is the return value after the SQL statement is executed. I tried it several times. The returned values of the fifth parameter are garbled. Therefore, we recommend that you directly view the return value of the sqlite3_exec function, which returns an integer, you can use this number to learn the execution result.

  The following example shows how the callback function is executed: query a table in the database and write the table content to the file.

  1. # Include <stdio. h>
  2. # Include <string. h>
  3. # Include <sqlite3.h>
  5. /* Callback function:
  6. * Arg: The parameter passed in from the main function.
  7. * Values: the value of the query result.
  8. * Names: name of the query result Column
  9. */
  10. IntWf_callback (Void* Arg,IntNR,Char** Values,Char** Names)
  11. {
  12. IntI;
  13. File* FD;
  14. CharSTR [128];
  15. FD = (File*) ARG; // forcibly convert the void * parameter to file *
  16. For(I = 0; I <NR; I ++) {// write the query result to the file.
  17. Memset (STR, '\ 0', 128 );
  18. Sprintf (STR, "\ t % s \ t", values [I]);
  19. Fwrite (STR,Sizeof(Char),Sizeof(STR), FD );
  20. }
  21. Memset (STR, '\ 0', 128 );
  22. Fwrite ("\ n ",Sizeof(Char), 2, FD); // write a new line record
  23. Return0; // The callback function returns 0 normally.
  24. }
  27. IntMain ()
  28. {
  29. CharSQL [128];
  30. Sqlite3 * dB;
  31. File* FD;
  33. Sqlite3_open ("test. DB", & dB); // open (or create) A database
  34. Memset (SQL, \ 0, 128 );
  35. /* Create a table */
  36. Strcpy (SQL, "CREATE TABLE Tb (ID integer primary key, Data Text )");
  37. Sqlite3_exec (dB, SQL, null );
  39. /* Create a new file and save the database query results to the file */
  40. FD = fopen ("test", "W ");
  41. Fwrite ("Result: \ n ",Sizeof(Char), 10, FD );
  42. Memset (SQL, \ 0, 128 );
  43. Strcpy (SQL, "select * from TB ");
  44. Sqlite3_exec (dB, SQL, wf_callback, FD, null );
  45. Fclose (FD );
  47. Sqlite3_close (db); // close the database
  48. Return0;
  49. }

  The compilation and running results are as follows:

  Let's take a look at the callback function wf_callback (). The sqlite3_exec () function executes the SQL statement first. If the execution result of the SQL statement is not empty, the callback function is called for processing. The test file contains three rows of data, so the wf_callback () function is called three times (here we can also add a global variable to the callback function to count, to check whether the callback function is called multiple times ). Because the callback function is called multiple times, open the file in the main function and pass the file descriptor as a parameter to the callback function. Otherwise, write fopen and fclose to the callback function. Each time a callback function is called, The callback function uses "W" to open the file, the result of the second callback function call overwrites the result of the first callback function call, so that the content written to the file is incorrect (only the last record is written to the file ).

  If some SQL functions, such as sum (), are called in the query statement, you can also use the callback function to process the query results, for example:

  1. Strcpy (SQL, "select sum (ID) from TB ");
  2. Sqlite3_exec (dB, SQL, sum_callback, null, null );

  The callback function can be:

  1. Int result;
  2. Int sum_callback (void * Arg, int NR, char ** values, char ** names)
  3. {
  4. Result = atoi (Values [0]);
  5. Return 0;
  6. }

  Then process the result. Of course, you can also directly process the result in the callback function without defining this global variable. It should be noted that the query results are in the form of character arrays. Therefore, if the expected results are Int or float, the corresponding functions are required for conversion, such as atoi (), atof.

  Address: http://www.linuxidc.com/Linux/2011-10/45920.htm