Access the local iPhone database sqlite3

The iPhone supports accessing the iPhone's local database through sqlite3.

 

The usage is as follows:

1: add the Development Kit libsqlite3.0.dylib

First, set the project file, add the sqlite3 database development kit for iPhone in the project, right-click frameworks under the project, and select libsqlite3.0.dylib file (for example ).

Libsqlite3.0.dylib file address:
/Developer/platforms/iphoneos. Platform/developer/sdks/iphoneos2.2.sdk/usr/lib/libsqlite3.0.dylib

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 main functions:

Sqlite3 * PDB, database handle, similar to file handle

Sqlite3_stmt * stmt, which is equivalent to the command object of ODBC, used to save compiled SQL statements



Sqlite3_open () to open the database

Sqlite3_exec (): executes non-query SQL statements.

Sqlite3_prepare (): This function is used to prepare SQL statements, execute select statements, or use parameter BIND (encapsulate sqlite3_exec ).

Sqlite3_step (): After sqlite3_prepare is called, use this function to move in the record set.

Sqlite3_close () to close database files



There are also a series of functions used to obtain data from record set fields, such

Sqlite3_column_text (), which is text-type data.

Sqlite3_column_blob (), fetch blob-type data

Sqlite3_column_int (), which is an int-type data.

SQL request processing in preparedstatement Mode
Feature: You can bind parameters to the generation process. The execution process is like ado, and a row of results is returned each time.

1. First create a 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 there are any definite parameters)
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. Execution Process
Int sqlite3_step (sqlite3_stmt *);
Possible return values:
* Sqlite_busy: the database is locked and you need to wait for another attempt until the attempt is successful.
* 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) to get 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 details about the error, refer to the return value of the sqlite3_errmsg function)
* Sqlite_misuse: this function is used incorrectly (generally, the process is not correctly initialized)

4. Clear the statement object at the end
Int sqlite3_finalize (sqlite3_stmt * pstmt );
Resources that should be occupied during cleaning before the database is shut down.

5. Execute the reset Process
Int sqlite3_reset (sqlite3_stmt * pstmt );
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 in the result.
Int sqlite3_column_count (sqlite3_stmt * pstmt );
If no return value is returned, for example, update, 0 is returned.

2. Obtain the number of data contained in the current row.
Int sqlite3_data_count (sqlite3_stmt * pstmt );
If sqlite3_step returns sqlite_row, you can obtain the number of columns. Otherwise, it is zero.

3. Get the data of a column in the Data row
Sqlite3_column_xxx (sqlite3_stmt *, int icol );
After sqlite3_step returns sqlite_row, use it to obtain the data in column icol.
Xxx represents:
BLOB: pointer to the memory for storing data
Bytes, bytes16: Get the size of the Blob type data, or convert text to the string length of utf8/UTF16.
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 that is suitable for the column type, the resulting value is converted.

4. Obtain the Data Type of a column in the Data row.
Int sqlite3_column_type (sqlite3_stmt *, int icol );
Returned values: sqlite_integer, sqlite_float, sqlite_text, sqlite_blob, and sqlite_null
The method used is similar to the sqlite3_column_xxx () function.

From: http://hi.baidu.com/hh20040410/blog/item/6020fa3dc3541de73c6d97cc.html