SQLLite in iOS

Source: Internet
Author: User

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 );


Sqlite3_step (stmt );

6Release SQL file resources

Sqlite3_finalize (stmt );

7Disable ISqlite3 database on Phone

Sqlite3_close (database );


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 );}


-(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 );}


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.