Sqlite3 instructions for iPhone Development

2. Operations in the Code:

The following is the code.

1 first obtain the sqlite3Database file address

 

NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *path = [documentsDirectory stringByAppendingPathComponent:@"database_name"];

 

2OpenSqlite3 database file on iPhone

 

sqlite3 *database;
sqlite3_open([path UTF8String], &database);

 

3 PreparationSQL --- SQL statements

 

sqlite3_stmt *stmt;
const char *sql = "SELECT * FROM table_name WHERE pk=? and name=?";
sqlite3_prepare_v2(database, sql, -1, &stmt, NULL);

 

4Bonding Parameters

 

// Specify the first int Parameter
Sqlite3_bind_int (stmt, 1, 1 );
// Specify the second string parameter
Sqlite3_bind_text (stmt, 2, [Title utf8string],-1, sqlite_transient );

 

5RunSQL

 

sqlite3_step(stmt);

 

6Release SQL file resources

 

sqlite3_finalize(stmt);

 

7Disable ISqlite3 database on phone

 

sqlite3_close(database);

http://hi.baidu.com/clickto/blog/item/0c6904f787c34125720eec87.html

The following describes how to use SQLite to create a database and then query the content. Two important structs and five major functions: sqlite3 * PDB, database handle, which is similar to the file handle file sqlite3_stmt * stmt, which is equivalent to the command object of ODBC, it is used to save the compiled SQL statement sqlite3_open (), open the database sqlite3_exec (), execute the non-query SQL statement sqlite3_prepare (), prepare the SQL statement, and execute the SELECT statement or use the parameter bind, this function is used to encapsulate sqlite3_exec ). sqlite3_step (): After sqlite3_prepare is called, use this function to move in the record set. Sqlite3_close (): closes database files. There are also a series of functions used to obtain data from record set fields, such as sqlite3_column_text () and retrieve text data. Sqlite3_column_blob (): Obtain the BLOB Data sqlite3_column_int (). The process of processing SQL requests using the int data preparedstatement method features: You can bind parameters to generate the process. The execution process is like ado, and a row of results is returned each time. 1. first, establish the statement object: int sqlite3_prepare (sqlite3 * dB,/* database handle */const char * zsql,/* SQL statement, UTF-8 encoded */INT nbytes, /* length of zsql in bytes. */sqlite3_stmt ** ppstmt,/* Out: Statement handle */const char ** pztail/* Out: pointer to unused portion of zsql */); 2. parameters in the binding process (if any) int sqlite3_bind_xxxx (sqlite3_stmt *, Int ,...); the second int type parameter indicates the sequence number of the parameter in SQL (starting from 1 ). The third parameter is the value of the parameter to be bound. Additional Parameters for blob and text values: the fourth parameter is the length of the string (UNICODE 8or16), excluding the ending '\ 0 '. The fifth parameter of the type is void (*) (void *), which indicates the function used to clear the parameter string after SQLite processing ends. Unknown parameters that are not bound will be considered null. 3. Execute the process int sqlite3_step (sqlite3_stmt *); Possible return value: * sqlite_busy: the database is locked. Wait until the attempt is successful again. * Sqlite_done: successful execution process (you need to execute it again to restore the database status) * sqlite_row: returns a row of results (using sqlite3_column_xxx (sqlite3_stmt *, int icol) obtain the results of each column. If you call it again, the result of the next row is returned. * Sqlite_error: A running error occurs and the process cannot be called again (for error content, see sqlite3_errmsg function return value) * sqlite_misuse: this function is used incorrectly (generally, the process is not properly initialized) 4. the statement object int sqlite3_finalize (sqlite3_stmt * pstmt) should be cleared before the database is closed. 5. Execute int sqlite3_reset (sqlite3_stmt * pstmt) during the reset process. The process will return to the status before execution, and the bound parameters will not change. Other tool functions 1. the total number of rows obtained is int sqlite3_column_count (sqlite3_stmt * pstmt). If no return value is returned, for example, update, 02 is returned. the number of data contained in the current row is int sqlite3_data_count (sqlite3_stmt * pstmt). If sqlite3_step returns sqlite_row, the number of columns can be obtained. Otherwise, the value is zero. 3. Obtain sqlite3_column_xxx (sqlite3_stmt *, int icol) for a column in the Data row. Use sqlite3_step to obtain the data in column icol after sqlite_row is returned. Xxx represents BLOB: pointer to the memory for storing data bytes, bytes16: Get the size of the Blob type data, or convert text to utf8/UTF16 String Length. Double, Int, int64: Value text, text16: String pointer type: Data Type of the column (sqlite_integer, sqlite_float, sqlite_text, sqlite_blob, sqlite_null) Note: if you use a different data reading method for this column, the resulting value is converted. 4. the data type of a column in the Data row is int sqlite3_column_type (sqlite3_stmt *, int icol). return values: sqlite_integer, sqlite_float, sqlite_text, sqlite_blob, and methods used by sqlite_null () functions are similar.