sqlite3-individual functions

First, add framework:libsqlite3.0.dylib need to include in the header file of the corresponding file: #import "/usr/include/sqlite3.h" and add the required library in the frameworks, otherwise it will error: Undefined symbols: "_sqlite3_open", referenced from: Add Storage method is: Image Select the SQLite library: Image selected effect: Image below is the code://sqlite [Self       OpenDatabase];       [Self createtable];       [Self inserttable];                     [Self querytable];       [Self deletetable];   [Self querytable]; Implementation://open database-(void) OpenDatabase {nsarray *documentspaths=nssearchpathfordirectoriesindomains (NSDocument                                                                    Directory, Nsuserdomainmask        , YES);              NSString *databasefilepath=[[documentspaths objectatindex:0] stringbyappendingpathcomponent:@ "Db.sql"]; if (Sqlite3_open ([Databasefilepath utf8string], &database) ==sqlite_ok) {NSLog (@ "Open SQLITE db OK.")        ); } else {NSLog (@ "Can not open SQLITE db ");        Close database Sqlite3_close (database);       }}//create table-(void) createtable {char *errormsg;              const char *createsql= "CREATE table if not EXISTS persons (ID integer primary key autoincrement,name text)";        if (sqlite3_exec (database, createsql, NULL, NULL, &ERRORMSG) ==SQLITE_OK) {NSLog (@ "Create OK.");           } else {NSLog (@ "Can not create TABLE");       [Self errorreport: (NSString *) Createsql];          }}//insert table-(void) inserttable {char *errormsg;        const char *insertsql= "INSERT into persons (name) values (' Tian Zhou ')";        if (sqlite3_exec (database, insertsql, NULL, NULL, &ERRORMSG) ==SQLITE_OK) {NSLog (@ "insert OK.");           } else {NSLog (@ "Can not-insert it to table");       [Self errorreport: (NSString *) Insertsql]; }}//error-(void) Errorreport: (NSString *) Item {char *errormsg; if (Sqlite3_exec (database, (const char *) item, NULL, NULL, &ERRORMSG) ==SQLITE_OK) {NSLog (@ "%@ OK.", I        TEM);            } else {NSLog (@ "error:%s", errormsg);        Sqlite3_free (ERRORMSG);        }}//query table-(void) QueryTable {const char *selectsql= "Select id,name from persons";        Sqlite3_stmt *statement;             if (SQLITE3_PREPARE_V2 (database, Selectsql,-1, &statement, nil) ==sqlite_ok) {NSLog (@ "select OK."); while (Sqlite3_step (statement) ==sqlite_row)//sqlite_ok sqlite_row {int _id=sqlite3_c                Olumn_int (statement, 0); NSString *name=[[nsstring Alloc] initwithcstring: (char *) sqlite3_column_text (statement, 1) Encoding:                Nsutf8stringencoding];            NSLog (@ "Row>>id%i, name>>%@", _id,name);          }} else {//error [Self errorreport: (NSString *) Selectsql];   } sqlite3_finalize (statement);       }//delete table-(void) deletetable {char *errormsg;              [Self opendatabase];       const char *sql = "DELETE from persons where id=24";        if (sqlite3_exec (database, SQL, NULL, NULL, &ERRORMSG) ==SQLITE_OK) {NSLog (@ "delete OK.");}           else {NSLog (@ "Can not delete it");       [Self errorreport: (NSString *) SQL]; }} command line: Sqlite3 database//Create Databases Crate table Tablefile (ID smallint, file_name varchar, up_s Tate smallint, file_size smallint);   Create a table insert into tablefile values (1, "200110101.rcu", 100, 4500); Insert data 5 How to access the SQLite3 database Sqlite3_open//Open the database Sqlite3_prepare//Convert the UTF-8 formatted SQL statement to a pointer to the prepared statement sqlite3_column_string// Returns a string of a row sqlite3_finalize//Delete a prepared statement sqlite3_close//Close Database 6 source code-(ID) Lookupsingularsql: (NSString *) SQL Fortyp E: (NSString *) RetType{sqlite3_stmt *statement;  ID result; if (statement = [self prepare:sql]) {if (sqlite3_step (statement) = = Sqlite_row) {if ([RetType compare:@ "text"] = = Nsor  Deredsame) {char temp_buf[256];  memset (temp_buf, 0, 256); sprintf (Temp_buf, "%s%s%s%s", (char *) sqlite3_column_text (statement,0), (char *) sqlite3_column_text (statement,1), (  char *) Sqlite3_column_text (statement,2), (char *) Sqlite3_column_text (statement,3));  result = [NSString stringwithutf8string:temp_buf];  result = [NSString stringwithutf8string: (char *) Sqlite3_column_text (statement,0)];              } else if ([RetType compare:@ "integer"] = = nsorderedsame) {result = (ID) sqlite3_column_int (statement,0);  }}} sqlite3_finalize (statement);  return result; There should be a member variable, such as my Code: @interface detailviewcontroller:uiviewcontroller {Uipopovercontroller *popovercontroller; Uitoolbar *toolbar; ID Detailitem; UILabel *detaildescriptionlabel; Sqlite3 *database; Open Database The SQLite database is a file database and is stored in the file system. So you need to know where to save the file, see iOS forThe operation of the file. For example, this article is saved to the documents directory. Code: Nsarray *documentspaths=nssearchpathfordirectoriesindomains (NSDocumentDirectory, NSUserDomainMask, YES); NSString *databasefilepath=[[documentspaths objectatindex:0] stringbyappendingpathcomponent:@ "MyDB"]; if (Sqlite3_open ([Databasefilepath utf8string], &database) ==sqlite_ok) {NSLog (@ "Open SQLITE db OK."); View the documents directory via SSH and discover that the MyDB file has been created. SQLite's strategy is to open the file if it is available, or create a database if it is not. It is important to note that the C syntax is used, and the Sqlite3_open is passed to the database address. When the database database is closed, to close, such as when exiting the application:-(void) Viewdidunload {//Release any retained subviews of the main view.//e.g. Self.myoutl et = nil; Sqlite3_close (database); Self.popovercontroller = nil; } When the database is opened, if there is no table, the table is built: char *errormsg; const char *createsql= "CREATE table if not EXISTS persons (ID integer primary key autoincrement,name text)"; if (sqlite3_exec (database, createsql, NULL, NULL, &ERRORMSG) ==SQLITE_OK) {NSLog (@ "create OK.");} It is important to note that ErrorMsg is an address, because the function is to write a typo message by using an address reference. Insert record and build table statements to table similar to: const char *insertsql= "INSERT into Persons (nAME) VALUES (' Zhang San '); if (sqlite3_exec (database, insertsql, NULL, NULL, &ERRORMSG) ==SQLITE_OK) {NSLog (@ "insert OK.");} Handling of error messages if you use errormsg in multiple places, you should clear the string after each use, such as this: if (sqlite3_exec (database, createsql, NULL, NULL, &errormsg) = = SQLITE_OK) {NSLog (@ "create OK.");} else {NSLog (@ "error:%s", errormsg), Sqlite3_free (errormsg), query result set result set, need Statement:const char *selectsql= "SELECT Id,name from persons "; Sqlite3_stmt *statement; if (SQLITE3_PREPARE_V2 (database, Selectsql,-1, &statement, nil) ==sqlite_ok) {NSLog (@ "select OK.");} while (Sqlite3_step (statement) ==sqlite_row) {int _id=sqlite3_column_int (statement, 0); Char *name= (char *) sqlite3_ Column_text (statement, 1); NSLog (@ "Row>>id%i, Name%s", _id,name); } sqlite3_finalize (statement); But here's a question, look at the print log: image garbled. Because of the type of char that is used directly. The solution is to replace Char:while (Sqlite3_step (statement) ==sqlite_row) {int _id=sqlite3_column_int (statement, 0) with NSString; NSString *name=[[nsstring Alloc] initwithcstring: (char *) Sqlite3_column_text (statemenT, 1) encoding:nsutf8stringencoding]; NSLog (@ "Row>>id%i, Name%@", _id,name); } char does an explicit encoding when generating nsstring. Problem Solving: Image This description: write to the database, with char, no problem, write the database encoding is right; removed from the library, may default to use ASCII decoding, resulting in garbled display. To use the SQLite C API, refer to the official function document frequently: http://www.sqlite.org/c3ref/funclist.html

　　

sqlite3-individual functions