Use of SQLite3 for iOS data storage, iossqlite3

Use of SQLite3 for iOS data storage, iossqlite3

SQLite3 is an embedded iOS database. SQLite3 is very effective in the storage and retrieval of a large amount of data. It makes it unnecessary to add every object to the memory. Data can also be aggregated to obtain results faster than performing these operations on objects.

SQLite3 uses the structured SQL query language, which is the standard language for interacting with relational databases.

Use of SQLite3:

1. Import the header file

# Import <sqlite3.h>

2. Create or open a database

// Create and open the database sqlite3 * database; // If sqlite3_open returns SQLITE_ OK, it indicates that the database has been opened successfully. // SQLite3 adopts portable C, and the file path of the database must be passed in the form of a C string (non-NSString. If (sqlite3_open ("/path/databaseFile", & database )! = SQLITE_ OK)
{
Sqlite3_close (database); NSAssert (0, @ "Failed to open database ");
}

3. Create a table

// CREATE a new TABLE NSString * createSQL = @ "create table if not exists fields" "(row integer primary key, FIELD_DATA TEXT);"; char * errorMsg; // sqlite3_exec run any command for SQLite3 that does not return data if (sqlite3_exec (database, [createSQL UTF8String], NULL, NULL, & errorMsg )! = SQLITE_ OK) {sqlite3_close (database); NSAssert (0, @ "Error creating table: % s", errorMsg );}

Note: After sqlite3_exec is executed, if the value is SQLITE_ OK, the execution is successful; otherwise, the error message is stored in errorMsg.

Sqlite3_exec can be used to perform operations without returning results, such as creation, insertion, and deletion.

4. operate on a table 4.1 to store data to the database

Sqlite3 * database; if (sqlite3_open ([[self dataFilePath] UTF8String], & database )! = SQLITE_ OK) {sqlite3_close (database); NSAssert (0, @ "Failed to open database") ;}// example: store the UITextField value to the database for (int I = 0; I <4; I ++) {UITextField * field = self. lineFields [I]; // INSERT operation char * update = "INSERT OR REPLACE INTO FIELDS (ROW, FIELD_DATA)" "VALUES (?, ?); "; Char * errorMsg = NULL; // create stmt sqlite3_stmt * stmt; // no matter which data, the first parameter of any bound function points to the sqlite3_stmt if (sqlite3_prepare_v2 (database, update,-1, & stmt, nil) used in the call of sqlite3_prepare_v2. = SQLITE_ OK) {// SQLITE_ OK indicates that the execution is successful/* sqlite3_bind_int (stmt, 1, I); there are three parameters: the first is sqlite3_stmt type variable, used in the previous sqlite3_prepare_v2. The second is the label index of the variable to be constrained. The third parameter is the value to be added. */Sqlite3_bind_int (stmt, 1, I); sqlite3_bind_text (stmt, 2, [field. text UTF8String],-1, NULL);} if (sqlite3_step (stmt )! = SQLITE_DONE) NSAssert (0, @ "Error updating table: % s", errorMsg); // sqlite3_finalize destroys the prepared statement sqlite3_prepare created by sqlite3_finalize (stmt );} // sqlite3_close close closes the database connection opened with sqlite3_open. Any preparation statements related to this connection must be released before the function is called );

4.2 query Databases

 

// Create query and sqlite3_stmt NSString * query = @ "select row, FIELD_DATA from fields order by row"; sqlite3_stmt * statement; // no matter which data is used, the first parameter of any bound function points to the sqlite3_stmt if (sqlite3_prepare_v2 (database, [query UTF8String],-1, & statement, nil) = SQLITE_ OK used in the call to sqlite3_prepare_v2) {// sqlite3_step is used to execute the prepared statement created with sqlite3_prepare. This statement is executed to the available position of the first row of the result. To continue to the second row of the result, you only need to call sqlite3_setp () again (). Continue to call sqlite3_setp () to know that this statement is complete. statements that do not return results (such as INSERT, UPDATE, or DELETE), sqlite3_step () if only one execution is performed, the system returns while (sqlite3_step (statement) = SQLITE_ROW) {int row = sqlite3_column_int (statement, 0); char * rowData = (char *) sqlite3_column_text (statement, 1 );
NSString * fieldValue = [[NSString alloc] initwithuf8string: rowData]; UITextField * field = self. lineFields [row]; field. text = fieldValue;
} // Sqlite3_finalize the prepared statement sqlite3_finalize (statement) created by sqlite3_prepare before destruction;} // sqlite3_close closes the connection to the database opened with sqlite3_open, any preparation statement related to this connection must be released before calling this function to close the function sqlite3_close (database );

5. Use constraints

In practice, we often use something called a constraint variable to construct an SQL string for insertion, query, or deletion.

For example, to perform the insert operation with two constraints variables, the first variable is of the int type, and the second is a C string:

char *sql = "insert into oneTable values (?, ?);";sqlite3_stmt *stmt;if (sqlite3_prepare_v2(database, sql, -1, &stmt, nil) == SQLITE_OK) {    sqlite3_bind_int(stmt, 1, 235);    sqlite3_bind_text(stmt, 2, "valueString", -1, NULL);}if (sqlite3_step(stmt) != SQLITE_DONE)    NSLog(@"Something is Wrong!");sqlite3_finalize(stmt);

Here, sqlite3_bind_int (stmt, 1,235); has three parameters:

The first is a variable of the sqlite3_stmt type, which is used in the previous sqlite3_prepare_v2.

The second is the label index of the variable to be constrained.

The third parameter is the value to be added.

Some functions have two more variables, for example

sqlite3_bind_text(stmt, 2, "valueString", -1, NULL);

The fourth parameter represents the length to be passed in the third parameter. For a C string,-1 indicates passing all strings.

The fifth parameter is a callback function, for example, to clear the memory after execution.

 

6. Functions commonly used in SQLite3

Sqlite3_open (): to open a database, you must first open the database before operating the database. This function opens a connection to an sqlite database file and returns a database connection object. This operation is the first sqlite function to be called in the program, and is also a prerequisite for other sqlite APIs. Many sqlite interface functions require a pointer to a database connection object as their first parameter.

Sqlite3_prepare (): converts SQL text into a prepared statement object, and returns a pointer to this object. This interface requires a database connection pointer and a text containing SQL statements to be prepared. It does not actually execute the (evaluate) SQL statement. It only prepares this SQL statement for execution.

Sqlite3_step (): Execute the prepared statement created in sqlite3_prepare. This statement is executed to the available position of the first row of the result. To continue to the second row of the result, you only need to call sqlite3_setp () again (). Continue to call sqlite3_setp () to know that this statement is complete. statements that do not return results (such as INSERT, UPDATE, or DELETE) and sqlite3_step () will be returned only once.

Sqlite3_column (): Execute sqlite3_step () and execute a prepared statement to return a column in the current row of the result set. Each time sqlite3_step is used to get the column of a result set, this process can be called multiple times to query the values of each column in this row. Column Operations have multiple functions, all of which are prefixed with sqlite3_column.

Sqlite3_finalize (): destroys the prepared statement created by sqlite3_prepare. Each prepared statement must be destroyed using this function to prevent memory leakage. Calling this function on a null pointer has no effect. At the same time, you can prepare a statement to call this function at any time in its life cycle: After one or more calls to sqlite_reset before the statement is executed, or after any sqlite3_step call, whether or not the statement is executed

Sqlite3_close (): Close the database connection opened with sqlite3_open. Any preparation statements related to this connection must be released before the function is called.