iOS database Operation SQLite

The iphone supports access to the iphone's local database via Sqlite3. Use the method as follows 1: To add the development package Libsqlite3.0.dylib first is to set up the project file, add the iphone version of the Sqlite3 database development package to the project, under Project frameworks right-click, and then select Libsqlite3.0.dylib file. Libsqlite3.0.dylib file Address:/developer/platforms/iphoneos.platform/developer/sdks/iphoneos2.2.sdk/usr/lib/ LIBSQLITE3.0.DYLIB2, action in the code: Then the code is next. 1 first get the address of the Sqlite3 database file on the iphone nsarray *paths = Nssearchpathfordirectoriesindomains (NSDocumentDirectory, Nsuserdomainmask, YES); NSString *documentsdirectory = [Paths objectatindex:0]; NSString *path = [documentsdirectory stringbyappendingpathcomponent:@ "database_name"];2 Open the Sqlite3 database file on the iphone sqlite3 *database;sqlite3_open ([path utf8string], &database); 3 Prepare SQL---sql statement sqlite3_ stmt *stmt;const Char *sql = "SELECT * FROM table_name WHERE pk=? and name=? "; SQLITE3_PREPARE_V2 (Database, SQL,-1, &stmt, NULL); 4 bonding parameters//bonding the first int parameter sqlite3_bind_int (stmt, 1, 1);// Bonding the second string parameter Sqlite3_bind_text (stmt, 2, [title utf8string], 1, sqlite_transient); 5 Execute SQL Sqlite3_step (stmt); 6 Release SQL Text resource sqlite3_finalize (stmt); 7 off ISqlite3 database sqlite3_close on the phone; http://hi.baidu.com/clickto/blog/item/ 0c6904f787c34125720eec87.html follow the steps for using SQLite to create a database and then query the contents of it. 2 important structures and 5 main functions: Sqlite3 *pdb, database handle, similar to file handle files sqlite3_stmt *stmt, this equivalent ODBC command object for saving compiled SQL statement SQL Ite3_open (), open Database Sqlite3_exec (), execute non-query SQL statement sqlite3_prepare (), prepare SQL statement, execute SELECT statement, or use parameter bind. Use this function (encapsulates the sqlite3_exec). Sqlite3_step (), use this function to move through the recordset after calling Sqlite3_prepare. Sqlite3_close (), close the database file There is also a series of functions to get data from the recordset field, such as Sqlite3_column_text (), taking the text type of data. Sqlite3_column_blob (), take the BLOB type of data Sqlite3_column_int (), take the data of type int preparedstatement the process characteristic of processing SQL request: Can bind parameter, build procedure. Executes as if it were ADO, returning a row of results each time. 1. First establish the Statement object: int sqlite3_prepare (Sqlite3 *db,/* Database handle */const char *zsql,/* SQL STA Tement, 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, ifThere are no definite parameters) int sqlite3_bind_xxxx (sqlite3_stmt*, int, ...); The second int type parameter-represents the ordinal of a parameter in SQL (starting at 1).  The third parameter is the value to bind the parameter to. Extra arguments for BLOB and text values: The fourth parameter is the length of the string (Unicode 8or16), excluding the end ' \ s '. The fifth parameter, type void (*) (void*), represents the function used to clean up the parameter string after SQLite processing ends. An unknown parameter that is not bound is considered null. 3. Execution procedure int sqlite3_step (sqlite3_stmt*); possible return value: *sqlite_busy: The database is locked and needs to wait until it is successfully tried again.         *sqlite_done: Successful execution (need to execute again to restore Database state) *sqlite_row: Returns a row of results (using sqlite3_column_xxx (sqlite3_stmt*, int icol) to get the results of each column. Calling again will return the result of the next row. *sqlite_error: Run error, procedure cannot be called again (Error content reference sqlite3_errmsg function return value) *sqlite_misuse: Wrong use of this function (usually the procedure is not properly initialized) 4. At the end, clean up the statement object int sqlite3_finalize (sqlite3_stmt *pstmt) and the resources that should be consumed during the cleanup process before closing the database. 5. Execution of the Reset procedure int sqlite3_reset (sqlite3_stmt *pstmt); The process will go back to a state that was not executed before the binding parameter does not change. Other tool functions 1. The resulting total number of rows int sqlite3_column_count (sqlite3_stmt *pstmt); If the procedure has no return value, such as Update, 02 is returned. Gets the number of data contained in the current row, int sqlite3_data_count (sqlite3_stmt *pstmt), and if Sqlite3_step returns Sqlite_row, the number of columns can be obtained, otherwise zero. 3. Get Data sqlite3_column_xxx (sqlite3_stmt*, int icol) for a column in the data row, and after Sqlite3_step returns Sqlite_row, makeUse it to get the data for column Icol. where XXX stands for: blob: Pointer to hold data memory bytes, BYTES16: Gets the size of the BLOB type data, or the string length of text converted to UTF8/UTF16. Double, int, int64: numeric text,text16: String pointer type: The 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 the column that is appropriate for the type of the column itself, the resulting value will be the converted result. 4. The type int sqlite3_column_type (sqlite3_stmt*, int icol) that gets the data for a column in the data row; The return value: Sqlite_integer,sqlite_float,sqlite_text, Sqlite_blob,sqlite_null uses a method similar to the SQLITE3_COLUMN_XXX () function. There are many application programs on the Mac that can be used to produce it, and the UI interface is convenient. However, if you do not want to install the software separately, the MAC system also has built-in sqlite3 components that can be built by the console. First, we start by opening any of the text editor software, which is created in the form of SQL, and saved into Data.sql. 1BEGIN transaction;2create TABLE ' Info ' (_id INTEGER PRIMARY KEY, ' Name ' text, ' Tel ' text, ' Address ' text); 3INSERT into ' I NFO ' VALUES (1, ' Richie ', ' 1234567 ', ' Taizhong '); 4INSERT into ' info ' values (2, ' Eric ', ' 7654321 ', ' Taipei City '); 5INSERT into ' info ' VALUES (3, ' Andy ', ' 1234321 ', ' Kaohsiung '); 6COMMIT; and then the following command under the console to produce Data.rdb this sqlite file1sqlite3 Data.rdb < The Data.sqlios case was to use the Sqlite library to first add a pre-generated repository to the case, and then add the LIbsqlite3.0.dylib. The next step is to write the code, xxxAppDelegate.h must import sqlite3.h, and declare a SQLITE3 structure. 1#import "Sqlite3.h" 2 3@interface xxxappdelegate:nsobject <uiapplicationdelegate>4{5 sqlite3* database;6} in XXX APPDELEGATE.M's didfinishlaunchingwithoptions function starts with the associated Code 1-(BOOL) Application: (UIApplication *) application Didfinishlaunchingwithoptions: (nsdictionary *) launchoptions2{3//Check if the repository exists, does not exist copy4 nsstring *path = [NSSEARCHPA Thfordirectoriesindomains (NSDocumentDirectory, Nsuserdomainmask, YES) lastobject];5 nsstring *file = [path stringByApp endingpathcomponent:@ "Data.rdb"];6 if ([[Nsfilemanager defaultmanager] fileexistsatpath:file] = = FALSE) 7 {8 N sstring *fromfile = [[NSBundle mainbundle] pathforresource:@ "Data.rdb" oftype:nil];9 [[Nsfilemanager DefaultManager ] Copyitematpath:fromfile topath:file error:nil];10}11//Open12 if (sqlite3_open ([File utf8string], &databa SE)! = SQLITE_OK) NSAssert1 (0, @ "Failed to open database with MESSAGE '%s '. ", sqlite3_errmsg (Database)); Self.window.rootViewController = self.viewcontroller;16 [Self.window make keyandvisible];17 return yes;18}19 20-(void) dealloc21{22 sqlite3_close (database); [Super dealloc];24} simply say, After adding Data.rdb to the project, the library will appear in the app suite, but each app will only be read by the proprietary Documents. Therefore, it is necessary to determine whether the file exists in the Documents, and if it does not exist, copy it to the record and then open the repository. So why do we have to judge? Why not copy every time? Because if you don't want the repository to be overwritten every time the app version is updated, you have to make a file presence and no judgment. After reading the library with the successful open library, you can begin to read and write the information. The method of reading the library is also very simple, as long as you are familiar with the SQL method, you should have no problem. 1NSString *sql = [NSString stringwithformat:@ "SELECT * from Event"];2sqlite3_stmt *statement;3if (SQLITE3_PREPARE_V2 ( Database, 1,-1, &statement, NULL) = = SQLITE_OK) 4{5 while (sqlite3_step (statement) = = Sqlite_row) 6 {7 NSS Tring *strvalue = [NSString stringwithutf8string: (char*) sqlite3_column_text (statement, 0)];8 int intvalue = Sqlite3 _column_int (statement, 1); 9}10}11sqlite3_finalize (statement); It must be noted that Sqlite3_column_text, Sqlite3_column_int Be responsible for obtaining information, you must specify whichColumn index. Execute SQL command The above is the use of SELECT, but if you need to do INSERT, DELETE, UPDATE and other actions, it is simply simple, just the following instructions. 1char *errmsg;2nsstring *sql = [NSString stringwithformat:@ "CREATE TABLE ' Info ' (_id INTEGER PRIMARY KEY, ' Name ' TEXT, ' Te L ' text, ' Address ' text) "];3sqlite3_exec (database, 1, nil, nil, &errmsg);

　　

iOS database Operation SQLite