SQLite common functions

SQLite common functions:

1. Open the database:
Note: To open a database, the file name does not have to exist. If the file does not exist, SQLite will be automatically created. The first parameter refers to the file name, and the second parameter is the defined sqlite3 ** struct pointer (key data structure). You don't need to worry about the underlying details of this structure.
Int sqlite3_open (

Const char * filename,/* database filename (UTF-8 )*/

Sqlite3 ** ppdb/* Out: SQLite dB handle */

);
Returned value: indicates whether the operation is correct (sqlite_ OK is normal)

 

2. Shut down the database:
Note: If you enable a database with sqlite3_open, do not forget to use this function to close the database at the end.

Int sqlite3_close (sqlite3 *); // The parameter is the struct, that is, the database handle.

3. Execute the SQL statement:
Description: This function is used to execute one or more SQL statements, separated by the ";" sign. We recommend that you specify the third parameter callback function when executing one or more SQL statements. In the callback function, you can obtain detailed procedures for executing SQL statements, if all SQL statements are executed, 0 is returned. Otherwise, the execution is not completely successful. Fifth parameter: if the execution fails (no 0 is returned), you can view the fifth elaborated value. To view detailed error information.
Int sqlite3_exec (
Sqlite3 *,/* opened database handle */
Const char * SQL,/* SQL statement to be executed */
Sqlite_callback,/* callback function */
Void *,/* parameters passed to the callback function */
Char ** errmsg/* save error message */
);
In general, sqlite3_callback and void * after sqlite3_callback can both be null, indicating that callback is not required. For example, if you perform insert and delete operations, you do not need to use callback. When used as a SELECT statement, a callback is required, because sqlite3 checks the data and uses the callback to tell you what data has been found.

 

4. Exec callback

Typedef int (* sqlite3_callback) (void *, Int, char **, char **);

Note: Your callback function must be defined as the type of the above function.
For example:

Int loadmyinfo (void * para, int n_column, char ** column_value, char ** column_name)
{
// Para is the void * parameter you pass in sqlite3_exec

// Using the Para parameter, you can pass in some special pointers (such as class pointers and structure pointers), and then forcibly convert them to the corresponding type (here it is the void * type, must be forcibly converted to your type ). Then operate on the data

// N_column indicates the number of fields in the record (that is, the number of columns in the record)

// Char ** column_value is a key value. The retrieved data is saved here. It is actually a one-dimensional array (not a two-dimensional array ), each element is a char * value and a field content (expressed as a string and ended with \ 0)

// Char ** column_name corresponds to column_value, indicating the field name of this field

5. Take the current insert position:
Function: return the last time you inserted the data. Starting from 1, sqlite3 * opens the database for you to get the handle.
Long long int sqlite3_last_insert_rowid (sqlite3 *);

6. Non-callback SELECT query:
Function: execute a query SQL statement and return a record set.
Int sqlite3_get_table (
Sqlite3 *,/* opened database handle */
Const char * SQL,/* SQL statement to be executed */
Char *** resultp,/* Save the pointer to the returned record set */
Int * nrow,/* Number of records returned (and the number of rows identified )*/
Int * ncolumn,/* Number of returned fields (How many columns )*/
Char ** errmsg/* error message returned */
)

Note: The third parameter is the query result, which is a one-dimensional array. The memory layout is: the first line is the field name, followed by the value of each field.
Instance:

Int main (INT, char **)
{
Sqlite3 * dB;
Int result;
Char * errmsg = NULL;

Char ** dbresult;

Int nrow, ncolumn;

Int I, J;

Int index;

Result = sqlite3_open ("c: \ dcg_database.db", & dB );
If (result! = Sqlite_ OK)
{
Return-1;
}

// Database operation code

// Assume that the mytable_1 table has been created.

// Start the query. The passed dbresult is already char **. Here, an & get address character is added, and the passed result is Char ***

Result = sqlite3_get_table (dB, "select * From mytable_1", & dbresult, & nrow, & ncolumn, & errmsg );

If (sqlite_ OK = Result)
{
// Query successful
Index = ncolumn; // the data in the first row before dbresult is the field name, which is the real data starting from the ncolumn index.
Printf ("% d records \ n", nrow );

For (I = 0; I <nrow; I ++)
{
Printf ("Number % d records \ n", I + 1 );
For (j = 0; j <ncolumn; j ++)
{
Printf ("field name: % s?> Field Value: % s \ n ", dbresult [J], dbresult [Index]);
+ Index; // The Field Value of dbresult is continuous. From The 0th index to the ncolumn-1 index, the field name starts from the ncolumn index, followed by the field value, it uses a flat form to represent a two-dimensional table (traditional row-column representation ).
}
Printf ("------- \ n ");

}
}

// Here, the char query result is released no matter whether the database query is successful or not, and the function provided by SQLite is used for release.
Sqlite3_free_table (dbresult );
// Close the database
Sqlite3_close (db );

Return 0;
}

7. Release query results:
Function: releases the memory occupied by the currently queried record set.
Void sqlite3_free_table (char ** result );

 

Example: (simple C language instance for SQLite database using callback function)

1. # include <stdio. h>

2.

3. # include <sqlite3.h>

4.

5.

6.

7. Static int callback (void * notused, int argc, char ** argv, char ** azcolname)

8.

9 .{

10.

11. Int I;

12.

13. For (I = 0; I <argc; I ++)

14.

15 .{

16.

17. printf ("% s = % s \ n", azcolname [I], argv [I]? Argv [I]: "null ");

18.

19 .}

20.

21. printf ("\ n ");

22.

23. Return 0;

24.

25 .}

26.

27.

28.

29. Int main (INT argc, char ** argv)

30.

31 .{

32.

33. sqlite3 * dB;

34.

35. char * zerrmsg = 0;

36.

37. Int RC;

38.

39.

40.

41. If (argc! = 3)

42.

43 .{

44.

45. fprintf (stderr, "Usage: % s Database SQL-STATEMENT \ n", argv [0]);

46.

47. Return 1;

48.

49 .}

50.

51. Rc = sqlite3_open (argv [1], & dB );

52.

53. If (RC)

54.

55 .{

56.

57. fprintf (stderr, "can't open database: % s \ n", sqlite3_errmsg (db ));

58.

59. sqlite3_close (db );

60.

61. Return 1;

62.

63 .}

64.

65. Rc = sqlite3_exec (dB, argv [2], callback, 0, & zerrmsg );

66.

67. If (RC! = Sqlite_ OK)

68.

69 .{

70.

71. fprintf (stderr, "SQL error: % s \ n", zerrmsg );

72.

73. sqlite3_close (db );

74.

75. Return 1;

76.

77 .}

78.

79. sqlite3_close (db );

80.

81. Return 0;

82.

83 .}