Basic knowledge of SQLite for iOS Data Processing

When using a database, the main task is to establish a connection with the database and execute SQL statements.

SQLite contains the following two important objects (pointers to struct ):

Sqlite3,

Indicates the object connecting to the database.

Sqlite3_stmt,

Indicates the object of an SQL statement.

Important functions in SQLite:

Sqlite3_open (),

The function that opens the database. The return value is an integer, indicating the result of the open operation. If the result is equal to the constant sqlite_ OK, the database is successfully opened. If opening fails, the corresponding error code is returned.

Sqlite3_prepare (),

To execute an SQL statement, you must first compile it into a bytecode. The sqlite3_prepare function is used to compile SQL statements.

Sqlite3_step (),

After the SQL statement is compiled using the sqlite3_prepare function, you can use the step function for execution. For execution statements with return values (such as select), calling the sqlite_step function once will execute and record a row of valid results to sqlite3_stmt. To continue obtaining the subsequent running results, execute the sqlite3_step function until all the results are recorded. For example, sqlite3_stmt has the statement "select * From studenttable order by ID". If the result contains multiple data records, use the sqlite3_step () function to obtain only the content of the first row, continue to call this function until all results have been recorded. For statements without return values (such as insert/update/delete), run the command once.

Sqlite3_column (),

For select statements, you must read the results after using sqlite3_step. In fact, there is no function called sqlite3_column (), which is the alias of a series of functions. They work together to retrieve values from the current results by column numbers. For example, after a SELECT statement is run, one of the results is 01 "what_if" "male ". This result contains three columns. You can use this function to find the values of a specific column. Different functions need to be called based on different value types. For example, for the first column, if the data type is integer, sqlite3_column_int (stmt, 0) is called to return the value of the first column. The data type of the second column is text. Correspondingly, sqlite3_column_text (stmt, 1) is called) obtain the text information of the second column. This function also includes the byte type and double type.

Sqlite3_finalize (),

After sqlite3_stmt is used up, use the sqlite3_finalize () function to destroy it. If you think of sqlite3_stmt as an object, this function is equivalent to its destructor. When sqlite3_stmt is used, you must call this function. Otherwise, memory leakage may occur.

Sqlite3_close (),

After the database operation is completed, you must disable the database connection. Use the sqlite3_close () function to close the function. Obviously, this is in pairs with sqlite_open. Before closing, use sqlite3_finalize () to destroy all sqlite3_stmt objects.

Sqlite3_exec (),

Sqlite3_exec () provides a convenient way to execute SQL statements. You only need to pass the SQL statement string (char *) to this function. We specify the callback function to process the returned values after execution. However, the sqlite3_exec () function is generally used to execute statements without return values (such as create/drop). For operations with return values (such as select ), sqlite3_prepare, sqlite3_step (), and sqlite3_clumn () are often used together to obtain the execution result.

 

Appendix:

 The most important SQLite API command

1. function: sqlite3_open (parameter: file name, database address) Open the database
2. function: sqlite3_prepare (parameter: database, UTF-8 format SQL, maximum read length, statement address, result address) converts an SQL statement in UTF-8 format to a compiled statement, returns a pointer to the statement, which can be passed to other functions.
3. function: sqlite3_step (parameter: compiled statement) move forward a record or return an error in the result returned by the compiled statement
4. function: sqlite3_column_int (parameter: compiled statement and field number) returns an int field in the current record. There are several other similar functions that return the specified fields in the current record.
5. function: sqlite3_column_string (parameter: compiled statement and field number)
Returns a char *, a string, and several other similar functions from the current record. They return the specified fields in the current record.
6. function: sqlite3_finalize (parameter: compiled Statement)
Delete compiled statements
7. function: sqlite3_close (parameter: database)
Close Database

General steps for using the SQLite database:

1. Open the database;

2. Prepare the statement one by one each time;

3. Traverse results-read fields;

4. Delete statements;

5. Shut down the database.

 

You can also use sqlite3_exec () and sqlite3_get_table () to simplify the preceding steps.