一：USING SQLITE3
1.add libsqlite3.dylib library to your project
2.#import "sqlite3.h"
3.declare a variable of type sqlite3 ,like this "sqlite3 *db"
4.openDB
-(void) openDB{ NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask,YES); NSString *documentDir = [ paths objcetAtIndex:0]; NSString *fileDB = [documentDir stringByAppendingPathComponent:@"database.sql"]; if(sqlite3_open([fileDB UTF8String],&db) != SQLITE_OK){ sqlite3_close(db); NSAssert(0,@"database failed to open"); }}?
The sqlite3_open() c function open a SQLite database whose filename is specified as the "fileDB"
?
sqlite3_open() result codes:
?
#define SQLITE_OK 0 /* Successful result */#define SQLITE_ERROR 1 /* SQL error or missing database */#define SQLITE_INTERNAL 2 /* Internal logic error in SQLite */#define SQLITE_PERM 3 /* Access permission denied */#define SQLITE_ABORT 4 /* Callback routine requested an abort */#define SQLITE_BUSY 5 /* The database file is locked */#define SQLITE_LOCKED 6 /* A table in the database is locked */#define SQLITE_NOMEM 7 /* A malloc() failed */#define SQLITE_READONLY 8 /* Attempt to write a readonly database */#define SQLITE_INTERRUPT 9 /* Operation terminated by sqlite3_interrupt()*/#define SQLITE_IOERR 10 /* Some kind of disk I/O error occurred */#define SQLITE_CORRUPT 11 /* The database disk image is malformed */#define SQLITE_NOTFOUND 12 /* NOT USED. Table or record not found */#define SQLITE_FULL 13 /* Insertion failed because database is full */#define SQLITE_CANTOPEN 14 /* Unable to open the database file */#define SQLITE_PROTOCOL 15 /* NOT USED. Database lock protocol error */#define SQLITE_EMPTY 16 /* Database is empty */#define SQLITE_SCHEMA 17 /* The database schema changed */#define SQLITE_TOOBIG 18 /* String or BLOB exceeds size limit */#define SQLITE_CONSTRAINT 19 /* Abort due to constraint violation */#define SQLITE_MISMATCH 20 /* Data type mismatch */#define SQLITE_MISUSE 21 /* Library used incorrectly */#define SQLITE_NOLFS 22 /* Uses OS features not supported on host */#define SQLITE_AUTH 23 /* Authorization denied */#define SQLITE_FORMAT 24 /* Auxiliary database format error */#define SQLITE_RANGE 25 /* 2nd parameter to sqlite3_bind out of range */#define SQLITE_NOTADB 26 /* File opened that is not a database file */#define SQLITE_ROW 100 /* sqlite3_step() has another row ready */#define SQLITE_DONE 101 /* sqlite3_step() has finished executing */
?
?
5.creating a table
?
-(void) createTableNamed:(NSString *) tableNamewithField1:(NSString *) field1withField2:(NSString *) field2 {char *err;NSString *sql = [NSString stringWithFormat:@“CREATE TABLE IF NOT EXISTS [email protected] ([email protected] TEXT PRIMARY KEY, [email protected] TEXT);”,tableName, field1, field2];if (sqlite3_exec(db, [sql UTF8String], NULL, NULL, &err) != SQLITE_OK) {sqlite3_close(db);NSAssert(0, @“Tabled failed to create.”);}}?
?
?6.Inserting Records
?
-(void) insertRecordIntoTableNamed:(NSString *) tableNamewithField1:(NSString *) field1field1Value:(NSString *) field1ValueandField2:(NSString *) field2field2Value:(NSString *) field2Value {NSString *sql = [NSString stringWithFormat:@“INSERT OR REPLACE INTO [email protected] ([email protected], [email protected]) VALUES ([email protected],[email protected])“,tableName, field1, field2, field1Value, field2Value];char *err;if (sqlite3_exec(db, [sql UTF8String], NULL, NULL, &err) != SQLITE_OK) {sqlite3_close(db);NSAssert(0, @“Error updating table.”);}}?
?
?7.Bind Variables
?
?
?
?
NSString *sqlStr = [NSString stringWithFormat:@“INSERT OR REPLACE INTO [email protected] ([email protected], [email protected]) VALUES (?,?)“,tableName, field1, field2];const char *sql = [sqlStr UTF8String];
?
?
sqlite3_stmt *statement;if (sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK) {sqlite3_bind_text(statement, 1, [field1Value UTF8String], -1, NULL);sqlite3_bind_text(statement, 2, [field2Value UTF8String], -1, NULL);}?
?To execute the SQL statement, you use the sqlite3_step() function, followed by the sqlite3_
finalize() function to delete the prepared SQL statement
?
?
if (sqlite3_step(statement) != SQLITE_DONE)NSAssert(0, @“Error updating table.”);sqlite3_finalize(statement);
?
?
写道
you used the sqlite3_exec() function to execute
SQL statements. In this example, you actually use a combination of sqlite3_
prepare(), sqlite3_step(), and sqlite3_finalize() functions to do the same
thing. In fact, the sqlite3_exec() function is actually a wrapper for these three
functions. For non-query SQL statements (such as for creating tables, inserting
rows, and so on), it is always better to use the sqlite3_exec() function.
SQL statements. In this example, you actually use a combination of sqlite3_
prepare(), sqlite3_step(), and sqlite3_finalize() functions to do the same
thing. In fact, the sqlite3_exec() function is actually a wrapper for these three
functions. For non-query SQL statements (such as for creating tables, inserting
rows, and so on), it is always better to use the sqlite3_exec() function.
?
?sqlite3_exec()? 是sqlite3_ prepare(), sqlite3_step(), and sqlite3_finalize() 三个函数的一个封装，最好的方法是直接使用sqlite3_exec()?
?
?
?8。Retrieving Records
?
?
-(void) getAllRowsFromTableNamed: (NSString *) tableName {//---retrieve rows---NSString *qsql = @“SELECT * FROM CONTACTS”;sqlite3_stmt *statement;if (sqlite3_prepare_v2( db, [qsql UTF8String], -1, &statement, nil) ==SQLITE_OK) {while (sqlite3_step(statement) == SQLITE_ROW) {char *field1 = (char *) sqlite3_column_text(statement, 0);NSString *field1Str = [[NSString alloc] initWithUTF8String: field1];char *field2 = (char *) sqlite3_column_text(statement, 1);NSString *field2Str = [[NSString alloc] initWithUTF8String: field2];NSString *str = [[NSString alloc] initWithFormat:@“%@ - [email protected],field1Str, field2Str];NSLog(str);[field1Str release];[field2Str release];[str release];}//---deletes the compiled statement from memory---sqlite3_finalize(statement);}}?
?
?
?
?
?
?
?
?
?