iOS use of SQLite3

iOS use of SQLite3

First, in Firefox open sqlite3 (if not, select Tools, add-ons, added) new Sqlite3 database, Contacts,

Create a members table, field Id,integer, primary key, self-increment, name,varchar;email,varchar,null;birthday,datetime,null.

Add some data to the table:

Second, create a new empty appliation, add a homeviewcontroller, and a component libsqlite3.dylib, to support the sqlite3 of the connection, closing, adding and deleting changes and other operations.

1. HomeViewController.h Code:

#import <UIKit/UIKit.h> #import "sqlite3.h" @interface homeviewcontroller:uiviewcontroller{sqlite3 *db;//Declare a Sqlite3 Database}-(NSString *) filepath;//the path to the database file. Usually in the sandbox documents inside the Operation @end

2. HOMEVIEWCONTROLLER.M Code:

#import "HomeViewController.h"

@interface Homeviewcontroller () @end @implementation Homeviewcontroller

This method is used to return the full path information of the database in the Documents folder

-(NSString *) filepath{Nsarray *paths = Nssearchpathfordirectoriesindomains (NSDocumentDirectory, NSUserDomainMask, YE    S);    NSString *documentsdir = [Paths objectatindex:0]; return [Documentsdir stringbyappendingpathcomponent:@ "Contacts.sqlite"];}

Ways to open a database

-(void) opendb{     if (Sqlite3_open ([[Self FilePath] utf8string], &db)! = SQLITE_OK) {    &N Bsp   Sqlite3_close (db);        Nsassert (0, @ "Database open failed. ");   }} //Insert Data Method-(void) Insertrecordintotablename: (NSString *) tablename        & nbsp             &NBSP;WITHFIELD1: (NSString *) field1 field1value: (NSString *) field1value                        ANDFIELD2: (NSString *) field2 field2value: (NSS Tring *) field2value                        ANDFIELD3: (nsstring *) field3 Field3value: (NSString *) field3value{/* Method 1: Classic method     NSString *sql = [NSString stringwithformat:@] INSERT into '%@ ' ('%@ ', '%@ ', '%@ ') VALUES ('%@ ', '%@ ', '%@ ') ', TableName, field1, Field2, field3, Field1value, Field2value , field3value];    Char *err;    if (sqlite3_exec (DB,[SQL Utf8string], NULL, NULL, &AMP;ERR)! = SQLITE_OK) {        sqlite3_close (db);        Nsassert (0, @ "Insert data Error! ");   }    * *

Method 2: Binding Methods for variables

NSString *sql = [NSString stringwithformat:@ "INSERT into '%@ ' ('%@ ', '%@ ', '%@ ') VALUES (?,?,?)", TableName, Field1, Fiel        D2, field3];        Sqlite3_stmt *statement; if (SQLITE3_PREPARE_V2 (DB, [SQL Utf8string],-1, &statement, nil) = = Sqlite_ok) {sqlite3_bind_text (statement,        1, [Field1value utf8string], -1,null);        Sqlite3_bind_text (statement, 2, [Field2value utf8string], -1,null);    Sqlite3_bind_text (statement, 3, [Field3value utf8string], -1,null); } if (Sqlite3_step (statement)! = Sqlite_done) {nsassert (0, @ "Insert data failed!        ");    Sqlite3_finalize (statement); }                                 }

 

Query data-(void) getallcontacts{     NSString *sql = @ "SELECT * FROM members";    sqlite3_stmt *stat ement;        if (SQLITE3_PREPARE_V2 (DB, [SQL Utf8string], 1, &statement, nil) = = Sqlite_ok ) {        while (sqlite3_step (statement) = = Sqlite_row) {          &NBSP;&NB sp;            char *name = (char *) sqlite3_column_text (statement, 0);      & nbsp     NSString *namestr = [[NSString alloc] initwithutf8string:name];          &NBSP;&NBS p;            char *email = (char *) sqlite3_column_text (statement, 1);      & nbsp     NSString *emailstr = [[NSString alloc] initwithutf8string:email];          &NBSP;&N bsp;            char *birthday = (char *) sqlite3_column_text (statement, 2);    &NB Sp   &NBsp   NSString *birthdaystr = [[NSString alloc] initwithutf8string:birthday];          &NBSP;&N bsp;            NSString *info = [[NSString alloc] initwithformat:@ "%@-%@-%@",  &NB Sp                           NAMESTR, EMAILSTR, Birthdaystr] ;                        NSLOG (info);                        [nameStr release];            [emailstr release];            [birthdaystr release];        &NBS P   [Info release];       }        sqlite3_finalize (statement);   } } -(void) viewdidload{    [self opendb];       [self insertrecordintotablename:@ "Members"withfield1:@ "Name" field1value:@ "Lee 1" andfield2:@ "email" field2value:@ "[email protected]" andfield3:@ "birthday "field3value:@" 12-45-78 "];        [self insertrecordintotablename:@] members" withfield1:@ " Name "field1value:@" Lee 2 "andfield2:@" email "field2value:@" [email protected] "andfield3:@" Birthday "Field3value: @ "12-45-78"];        [self insertrecordintotablename:@ ' members ' withfield1:@ ' name ' field1value:@ "Lee 3" andfield2:@ "email" field2value:@ "[email protected]" andfield3:@ "Birthday" field3value:@ " 12-45-78 "];                [self getallcontacts];        Sqlite3_close (db);        [Super Viewdidload];}   @end  

Effects after inserting data:

The effect of the query:

Third, Summary:

1. Data query: The SQLITE3_EXEC () function executes the SQL statement, which is useful when there are no return values (such as creating a table, inserting a record, deleting a record, and so on).

The SQLITE3_STAT structure, the SQLITE3_PREPARE_V2 () function, the Sqlte3_step () function, and the sqlite3_finalize () function are also used.

The query is divided into three stages: preparation stage: Sqlite3_stat, SQLITE3_PREPARE_V2 ()

Execution Stage: Sqlte3_step ()

Termination phase: Sqlite3_finalize ()

2. Schedule:

Sqlite3 The return value of the database when it is opened and what it means

return value	Describe	return value	Describe
Sqlite_ok=0	Return success	Sqlite_full=13	Database full, insert failed
Sqlite_error=1	SQL error or wrong database	Sqlite_cantopen=14	Cannot open database file
sqlite_internal=2	SQLite's internal logic error	Sqlite_protocol=15	Database Lockout protocol error
Sqlite_perm=3	Access Denied	Sqlite_empty=16	database table is empty
Sqlite_abort=4	callback function Request Interrupt	Sqlite_schema=17	Database schema Changes
Sqlite_busy=5	Database file is locked	Sqlite_toobig=18	Too many rows for one table data
Sqlite_locked=6	A table in the database is locked	Sqlite_constraint=19	aborted because of a constraint violation
Sqlite_nomen=7	Memory allocation failure	Sqlite_mismatch=20	Data types do not match
Sqlite_readonly=8	Attempt to write to a read-only database	Sqlite_misuse=21	Database error usage
Sqlite_interrupt=9	End operation by Sqlite_interrupt ()	Sqlite_nolfs=22	Using features not supported by the host operating system
sqlite_ioerr=10	Disk I/O error occurred	Sqlite_auth=23	Illegal authorization
sqlite_corrupt=11	Database disk mirroring deformity	Sqlite_format=24	Secondary database format error
Sqlite_notfound=12	(Internal only) table or record does not exist	Sqlite_notadb=26	Not a database file is open

iOS use of SQLite3