SQLLite in iOS

SQLLite in iOS

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.

 

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 terminator ''.
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.

 

Obtain the file path

 

- (NSString *)getDataBasePath{    NSString* path = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];    NSString* filePath = [path stringByAppendingPathComponent:@database.db];    //   NSString* filePath  = [NSHomeDirectory() stringByAppendingPathComponent:@Document/database.db];        return filePath;}

 

Open Database

-(Void) openDatabase {// 1. Obtain the file path NSString * filePath = [self getDataBasePath]; // 2. oc string ---> c string const char * filename = [filePath UTF8String]; // 3. Open the database: if the database file exists, open it directly, the system will create a file with the same name under this path. // 1 'database file name c string // 2' database object sqlite3 ** ppDb int rc = sqlite3_open (filename, & _ database ); // SQLITE_ OK operation successful // if (rc! = SQLITE_ OK) {// shut down the database: sqlite3_close (_ database); // insert assertion: if the value of the conditional expression is NO, the program crashes, the value of parameter 2 is output: // 1' <# condition #> conditional judgment // 2' NSAssert (NO, @ database opening failed, check the file path);} else {NSLog (@ database opened successfully );}}

Create a data table

-(IBAction) createTable :( id) sender {// 1. Open the database [self openDatabase]; // 2. Create the SQL statement of the table: contactTable NSString * createSql = @ create table ContactTable (name TEXT, phone TEXT); char * errmsg = nil; // 3. Execute the SQL statement for creating a TABLE: // 1' database object // 2' SQL statement-C string converts the OC string into a C string // a callback function in the C language: similar to the proxy method, NULL // 4' the first parameter in the callback function: NULL // 5' execution error message int rc = sqlite3_exec (_ database, [createSql UTF8String], NULL, NULL, & errmsg); // SQLITE_ OK Execution successful // table creation failed if (rc! = SQLITE_ OK) {NSLog (@ data table creation failed);} else {NSLog (@ database table created successfully);} // close the database sqlite3_close (_ database) after use );}

Insert data

-(IBAction) insertData :( id) sender {// 1. Open the database [self openDatabase]; // 2. SQL statement :? The placeholder representing the field content. How many fields do I need? NSString * insertSql = @ insert into ContactTable VALUES (?,?); // Prepare a Buffer Structure sqlite3_stmt * stmt = nil; // 3. Prepare to insert data: Convert the SQL statement into a bytecode, do not execute the statement // 1' database object // 2' SQL statement c string // 3' length of the SQL statement: strlen function to obtain strlen ([insertSql UTF8String], enter-1 here to read the first character from the SQL statement to the first terminator; read the complete SQL statement // 4' Statement Buffer // 5'pztail: if the SQL statement specified by parameter 3 is not complete, this parameter retains the remaining part; NULL int rc = sqlite3_prepare_v2 (_ database, [insertSql UTF8String],-1, & stmt, NULL); // if data preparation is complete: SQLITE_ OK if (rc = SQLITE_ OK) {// 4. Bind the content to the cache: // 1 'specify the cache zone // 2' field index: Starting from 1. By default, each record will add a ROWID // 3' field value: c string // 4' String Length // 5' function pointer: NULL NSString * name = @ zhangsan; sqlite3_bind_text (stmt, 1, [name UTF8String], strlen ([name UTF8String]), NULL); NSString * phone = @ phone number; sqlite3_bind_text (stmt, 2, [phone UTF8String], strlen ([phone UTF8String]), NULL); // 5. Execute an SQL statement. If an error occurs during the binding process, the content in the cache area is left empty. int rc = sqlite3_step (stmt ); // whether the insert operation is successful // SQLITE_DONE, another record, if (rc = SQLITE_DONE) {NSLog (@ data inserted successfully);} else {NSLog (@ failed to insert data ____ % s, sqlite3_errmsg (_ database ));} // 6. Release the cache: handle sqlite3_finalize (stmt);} // 7. Close the database sqlite3_close (_ database );}

Delete data

-(IBAction) deleteData :( id) sender {// 1. Open the database [self openDatabase]; // 2. SQL statement NSString * deleteSql = @ DELETE FROM ContactTable WHERE name = 'hangsan'; // 3. cache sqlite3_stmt * stmt = NULL; // 4. Prepare the SQL statement int rc = sqlite3_prepare_v2 (_ database, [deleteSql UTF8String], strlen ([deleteSql UTF8String]), & stmt, NULL ); // 5. The preparation is complete. if (rc = SQLITE_ OK) {// 6. Execute the SQL statement if (sqlite3_step (stmt) = SQLITE_DONE) {NSLog (@ data deletion completed);} // 7. Release the cache sqlite3_finalize (stmt);} // 8. Close the database sqlite3_close (_ database );}

Modify data

-(IBAction) upDateData :( id) sender {// 1. open the database [self openDatabase]; // 2. SQL statement // NSString * updateSql = @ UPDATE ContactTable SET phone = '000000' WHERE name = 'hangsan '; NSString * updateSql = @ UPDATE ContactTable SET phone = '000000' where rowid = 1; // 3. cache area sqlite3_stmt * stmt = NULL; // 4. prepare SQL int rc = sqlite3_prepare_v2 (_ database, [updateSql UTF8String],-1, & stmt, NULL); // 5. if (rc = SQLITE_ OK) {// 6. run SQL if (sqlite3_step (stmt) = SQLITE_DONE) {NSLog (@ data update successful);} else {NSLog (@ data update failed);} // 7. release sqlite3_finalize (stmt);} // 8. close the database sqlite3_close (_ database );}

Query

-(IBAction) queryData :( id) sender {// NSMutableArray * mArray = [NSMutableArray array. open the database [self openDatabase]; // 2. SQL statement NSString * querySql = @ SELECT * FROM ContactTable; // 3. prepare a cache zone sqlite3_stmt * stmt = NULL; // 4. prepare to read data: int rc = sqlite3_prepare_v2 (_ database, [querySql UTF8String],-1, & stmt, NULL); // 5. if (rc = SQLITE_ OK) {// 6. execute SQL: if there are multiple data records or you do not know how many rows of data are in the database, use the while loop to execute // if the execution is complete, return SQLITE_DONE, and no longer use the while statement; if there is another data, return SQLITE_ROW and continue to the while LOOP while (sqlite3_step (stmt) = SQLITE_ROW) {// 7. retrieve data from the cache: // 1' cache // 2' field index: char * name = (char *) sqlite3_column_text (stmt, 0) starting from 0 ); char * phone = (char *) sqlite3_column_text (stmt, 1); // convert the C string ---> OC string NSString * nameStr = [[NSString alloc] initwithuf8string: name]; // [NSString stringwithuf8string: name]; NSString * phoneStr = [[NSString alloc] initwithuf8string: phone]; // each record contains a telephone number and a name: placed in an array, A piece of data represents a record [mArray addObject: nameStr]; [mArray addObject: phoneStr];} // release the cache: written outside the while LOOP sqlite3_finalize (stmt );} for (id object in mArray) {NSLog (@ The retrieved data is ____ % @, object);} // close the database sqlite3_close (_ database );}