SQLITE3 使用

一：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 *) tableName
withField1:(NSString *) field1
withField2:(NSString *) field2 {
char *err;
NSString *sql = [NSString stringWithFormat:
@“CREATE TABLE IF NOT EXISTS ‘%@‘ (‘%@‘ TEXT PRIMARY KEY, ‘%@‘ 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 *) tableName
withField1:(NSString *) field1
field1Value:(NSString *) field1Value
andField2:(NSString *) field2
field2Value:(NSString *) field2Value {
NSString *sql = [NSString stringWithFormat:
@“INSERT OR REPLACE INTO ‘%@‘ (‘%@‘, ‘%@‘) VALUES (‘%@‘,’%@‘)“,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 ‘%@‘ (‘%@‘, ‘%@‘) 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);

 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:@“%@ - %@“,
field1Str, field2Str];
NSLog(str);
[field1Str release];
[field2Str release];
[str release];
}
//---deletes the compiled statement from memory---
sqlite3_finalize(statement);
}
}