Introduction to C language-based SQLite operation interface functions

SQLite is an open-source embedded file-type database/ S-based relational databases, such as MySQL. it is embedded, because all the functions of SQLite are packaged in a DLL. We only need to use the export interface to operate the SQLite database, in this way, the data storage function can be easily integrated into the user'sProgramAnd run in the client program's process space to say that it is a file type, because the SQLite database file is an independent file (SQLite itself does not limit the database file extension ), nothing else. Data, table structures, queries, views, and so on are all stored in this database file and will not depend on the main features of SQLite in any database environment:  1  . No deployment required. No configuration, no Server  2  . Cross-platform  2  . Convenient data file management  3 For more comprehensive support of the sql92 standard, SQLite basically implements the sql92 standard. For other incompatible areas, see the official instructions. Link: http: //  Www.sqlite.org/omitted.html     4  The SQL statement execution speed is fast, and the comparison data has a lot of online evaluations, so I won't say much here.  5  A wide range of applications. The most famous integration should be Android. Other PHP and Python have been integrated, so it is still very good.  6  . Perfect support for Unicode encoding. in the SQLite interface, all strings are encoded and interacted with each other using utf8 or UTF16, and some provide the interface functions for both encoding at the same time, therefore, multi-language support is definitely not a problem (this is also an important reason for my preference for SQLite, haha ). SQLite database operations are actually the same as conventional database operations:  1  . Connect to the database.  2  . Construct and execute SQL statements  3  For select statements, query results can be obtained.  4 . After the database is used, shut down the database. The following functions do not cover all API functions. After all, SQLite provides different API functions for the same function, this is mainly manifested in the parameter and configuration functions. If you need more information, please refer to the official documentation.  1  . Open the database: API function:  Int  Sqlite3_open (  Const   Char * Filename, /*  Database file path (UTF-8 code)  */  Sqlite3 ** Ppdb /*  Output: SQLite database handle  */ );  Int  Sqlite3_open16 (  Const   Void * Filename, /*  Database file path (UTF-16)  */  Sqlite3 ** Ppdb /*  Output: SQLite database handle  */  ); If the call is successful, sqlite_ OK is returned; otherwise, the error code is returned.  2 . Construct an SQL statement, which is irrelevant to SQLite itself. You can use appropriate methods to construct the statement as needed. Remember to convert the string encoding to utf8/UTF16  3  . Execute the SQL statement. A simple method to execute the SQL statement in SQLite is to call the function:  Int  Sqlite3_exec (sqlite3 *, /*  Opened database handle  */  Const   Char * SQL, /*  Utf8 encoded SQL statement  */  Int (* Callback )(Void *, Int , Char **, Char **), /*  The callback function that processes the results returned by the SELECT statement in the callback function.  */  Void *, /*  Parameters passed to the callback function  */  Char ** Errmsg /*  Error Message */  ); In fact, sqlite3_exec only encapsulates sqlite3_prepare and sqite3_step (that is, the pre-compilation technology in SQL). The main purpose is to provide convenience for users. I personally think it would be better to use the latter, here is a summary:  Int  Sqlite3_prepare (sqlite3 * DB, /*  Opened database handle  */  Const   Char * Zsql, /*  Utf8 encoded SQL statement, which can be parameterized  */  Int Nbyte, /* The length of the SQL statement in bytes.-1 can be passed, that is, the string ends with \ 0.  */  Sqlite3_stmt ** Ppstmt, /*  Output: Pre-compiled SQL statement handle  */  Const   Char ** Pztail /*  Output: points to the first byte in the zsql buffer to skip valid SQL strings.  */  );  Int  Sqlite3_prepare_v2 (sqlite3 * DB, /* Opened database handle  */  Const   Char * Zsql, /*  Utf8 encoded SQL statement, which can be parameterized  */  Int Nbyte, /*  The length of the SQL statement in bytes.-1 can be passed, that is, the string ends with a wide character \ 0.  */  Sqlite3_stmt ** Ppstmt, /*  Output: Pre-compiled SQL statement handle  */ Const   Char ** Pztail /*  Output: points to the first byte in the zsql buffer to skip valid SQL strings.  */  );  Int  Sqlite3_prepare16 (sqlite3 * DB, /*  Opened database handle  */  Const   Void * Zsql, /*  UTF16 encoded SQL statement, which can be parameterized */  Int Nbyte, /*  The length of the SQL statement in bytes.-1 can be passed, that is, the string ends with a wide character \ 0.  */  Sqlite3_stmt ** Ppstmt, /*  Output: Pre-compiled SQL statement handle  */  Const   Void ** Pztail /*  Output: points to the first byte in the zsql buffer to skip valid SQL strings.  */  ); Int  Sqlite3_prepare16_v2 (sqlite3 * DB, /*  Opened database handle  */  Const   Void * Zsql, /*  UTF16 encoded SQL statement, which can be parameterized  */  Int Nbyte, /*  The length of the SQL statement in bytes.-1 can be passed, that is, the string ends with a wide character \ 0.  */  Sqlite3_stmt ** Ppstmt, /*  Output: Pre-compiled SQL statement handle  */  Const   Void ** Pztail /*  Output: points to the first byte in the zsql buffer to skip valid SQL strings.  */  ). The SQL statement with parameters can define the parameters as follows: ? ? Nnn: vvv @ vvv $ vvv parameter number starts from 1. For example, insert into DB values ( ? 1 ,? 2  ) For functions of the V2 version, SQLite adds enhancement functions as needed. For new programs, we recommend that you use functions of the V2 version. You only need to compare them with the original functions. For more information, see the original official version. Int Sqlite3_step (sqlite3_stmt * ); Execute a pre-compiled SQL statement. Before that, if the SQL statement is parameterized, you can call sqlite3_bind to bind the data,  Int , String  , Blob, etc. If the execution is successful, sqlite_done is returned. If the query has a result, sqlite_row is returned. You can use the API to obtain the first row of data in the result, to obtain the next row of data, you can call sqlite3_step again until sqlite_done is returned, indicating that there is no data in the end. If you need to re-bind data to the pre-compiled SQL and execute it, you need to reset the data before calling step, that is, the function:  Int Sqlite3_reset (sqlite3_stmt * Pstmt); below are the functions related to data binding to pre-compiled SQL statements: the first parameter of the following function refers to the pre-compiled SQL handle, and the second parameter refers to the bound parameter number, parameter numbers corresponding to parameterized SQL statements:  Int Sqlite3_bind_blob (sqlite3_stmt *, Int ,Const   Void *, Int N, Void (*)( Void * ); This function is used to bind the binary data blob. The last parameter is a callback function. After data is successfully bound, it is called and generally used to automatically release the corresponding buffer zone.  Int Sqlite3_bind_double (sqlite3_stmt *, Int , Double  ); This function is bound to a double floating point number.  Int Sqlite3_bind_int (sqlite3_stmt *, Int , Int ); This function is bound to an int integer.  Int Sqlite3_bind_int64 (sqlite3_stmt *, Int  , Sqlite3_int64); this function is used to bind an integer with a 64-bit length, corresponding to the long structure in C. Because the range of an int may not meet the requirements of a large data volume, therefore, SQLite also supports 64-bit integers. After all, SQLite officially claims that SQLite supports up to 2 TB of data.  Int Sqlite3_bind_null (sqlite3_stmt *, Int  ); Bind an empty data to the specified Column  Int Sqlite3_bind_text (sqlite3_stmt *, Int , Const   Char *, Int N,Void (*)( Void * ); This function is bound to a string. The source string is UTF-8 encoded.  Int Sqlite3_bind_text16 (sqlite3_stmt *, Int , Const   Void *, Int , Void (*)( Void * ); This function is bound to a string, the source string is UTF16 encoded  Int Sqlite3_bind_value (sqlite3_stmt *, Int ,Const Sqlite3_value * ); This function is bound to the general data stored in the SQLite structure sqlite3_value. sqlite3_value can be all of the above types, which is not very common.  Int Sqlite3_bind_zeroblob (sqlite3_stmt *, Int , Int  N); this function binds all zero BLOB data of the specified size.  3  . To obtain the SQL query result, you also need to obtain the result for the SELECT statement. as mentioned above, after sqlite3_step is called, the first row of results will be returned for queries with results. In this case, you can use the API function to obtain the results of specified fields in the current row:  Const   Void * Sqlite3_column_blob (sqlite3_stmt *, Int Icol); this function obtains the data of the corresponding column in BLOB data format. The bolb length is obtained using sqlite3_column_bytes.  Int Sqlite3_column_bytes (sqlite3_stmt *, Int  Icol );  Int Sqlite3_column_bytes16 (sqlite3_stmt *, Int  Icol); this function can be used to return the byte length of blob and string. for blob, the two functions have the same effect, but for the string sqlite3_column_bytes, the return is the UTF-8 encoded String Length, while sqlite3_column_bytes16 returns the UTF16 encoded String Length, during which necessary string format conversion will be performed  Double Sqlite3_column_double (sqlite3_stmt *, Int  Icol); this function returns the double data column  Int Sqlite3_column_int (sqlite3_stmt *, Int  Icol); this function returns the int data column sqlite3_int64 sqlite3_column_int64 (sqlite3_stmt *, Int  Icol); this function returns a 64-bit integer, that is, long data.  Const Unsigned Char * Sqlite3_column_text (sqlite3_stmt *, Int  Icol );  Const   Void * Sqlite3_column_text16 (sqlite3_stmt *, Int Icol); this function returns a string, in which the string output by sqlite3_column_text uses utf8 encoding sqlite3_column_text16 using UTF16 Encoding  Int Sqlite3_column_type (sqlite3_stmt *, Int  Icol); this function returns the Data Type of the corresponding column sqlite3_value * Sqlite3_column_value (sqlite3_stmt *, Int  Icol); this function uses the sqlite3_value struct to return data. The above is based on the column ID to obtain the corresponding column data. If you want to obtain the column data through the column name, you need to convert the column name to the corresponding column ID. You can use the following function:  Const   Char * Sqlite3_column_name (sqlite3_stmt *, Int  N );  Const  Void * Sqlite3_column_name16 (sqlite3_stmt *, Int  N); this function returns the name of the corresponding column.  4  . Close the database  Int Sqlite3_close (sqlite3 * DB). You can use this function to close the database.