IPhone MySQL數據庫操作代碼例子

 　　//database operation

　　打開數據庫

　　-(BOOL) opendatabase{

　　NSArray*paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask,YES);

　　NSString *documentsDirectory = [pathsobjectAtIndex:0];

　　NSString *path = [documentsDirectorystringByAppendingPathComponent:@"mydb.sql"];

　　NSFileManager*fileManager = [NSFileManagerdefaultManager];

　　BOOL find = [fileManagerfileExistsAtPath:path];

　　//找到數據庫文件mydb.sql

　　if (find) {

　　NSLog(@"Database file have already existed.");

　　if(sqlite3_open([pathUTF8String], &database_) !=SQLITE_OK) {

　　sqlite3_close(database_);

　　NSLog(@"Error: open database file.");

　　return NO;

　　}

　　return YES;

　　}

　　if(sqlite3_open([path UTF8String], &database_) ==SQLITE_OK) {

　　//bFirstCreate_ = YES;

　　[selfcreateChannelsTable:database_];//在後面實現函數createChannelsTable

　　return YES;

　　}else {

　　sqlite3_close(database_);

　　NSLog(@"Error: open database file.");

　　return NO;

　　}

　　return NO;

　　}

　　創建表

　　- (BOOL) createChannelsTable:(sqlite3*)db{

　　char*sql = "CREATE TABLE reports (id integer primary key,stime text,stitle text,scal text,sruntime text)";

　　sqlite3_stmt *statement;

　　if(sqlite3_prepare_v2(db, sql, -1, &statement,nil) !=SQLITE_OK) {

　　NSLog(@"Error: failed to prepare statement:create reports table");

　　return NO;

　　}

　　int success =sqlite3_step(statement);

　　sqlite3_finalize(statement);

　　if ( success !=SQLITE_DONE) {

　　NSLog(@"Error: failed to dehydrate:CREATE TABLE reports");

　　return NO;

　　}

　　NSLog(@"Create table 'reports' successed.");

　　return YES;

　　}

　　插入表

　　- (BOOL)insertOneChannel:(NSString*)stime mytitle:(NSString*)stitle mycal:(NSString*)scal myruntime:(NSString*)sruntime

　　{

　　sqlite3_stmt *statement;

　　staticchar*sql = "INSERT INTO reports (id,stime,stitle,scal,sruntime) VALUES(NULL,?,?,?,?)";

　　//問號的個數要和(cid,title,imageData,imageLen)裡面字段的個數匹配，代表未知的值，將在下面將值和字段關 聯。

　　int success =sqlite3_prepare_v2(database_, sql, -1, &statement,NULL);

　　if (success !=SQLITE_OK) {

　　NSLog(@"Error: failed to insert:channels");

　　return NO;

　　}

　　//這裡的數字1，2，3，4代表第幾個問號

　　//sqlite3_bind_text(statement, 1, stime, -1, SQLITE_TRANSIENT);

　　char*p = [stime cStringUsingEncoding:1];

　　sqlite3_bind_text(statement,1, [stime cStringUsingEncoding:1], -1,SQLITE_TRANSIENT);

　　sqlite3_bind_text(statement,2, [stitle cStringUsingEncoding:1], -1,SQLITE_TRANSIENT);

　　sqlite3_bind_text(statement,3, [scal cStringUsingEncoding:1], -1,SQLITE_TRANSIENT);

　　sqlite3_bind_text(statement,4, [sruntime cStringUsingEncoding:1], -1,SQLITE_TRANSIENT);

　　success =sqlite3_step(statement);

　　sqlite3_finalize(statement);

　　if (success ==SQLITE_ERROR) {

　　NSLog(@"Error: failed to insert into the database with message.");

　　return NO;

　　}

　　NSLog(@"Insert One Channel#############:id = _");

　　return YES;

　　}

　　查詢表

　　- (void) getChannels:(NSMutableArray*)fChannels{

　　sqlite3_stmt *statement =nil;

　　char*sql = "SELECT * FROM reports";

　　if (sqlite3_prepare_v2(database_, sql, -1, &statement,NULL) !=SQLITE_OK) {

　　NSLog(@"Error: failed to prepare statement with message:get channels.");

　　}

　　//查詢結果集中一條一條的遍歷所有的記錄，這裡的數字對應的是列值。

　　while (sqlite3_step(statement) ==SQLITE_ROW) {

　　//char* cid = (char*)sqlite3_column_text(statement, 1);

　　char* stime = (char*)sqlite3_column_text(statement,1);

　　char* stitle =(char*)sqlite3_column_text(statement,2);

　　char* scal = (char*)sqlite3_column_text(statement,3);

　　char* sruntime= (char*)sqlite3_column_text(statement,4);

　　//NSString *tmp = [NSString stringWithCString:stitle encoding:1];

　　myreportitem* ri = [[myreportitemalloc]init];

　　ri.mytime= [NSString stringWithCString:stime encoding:1];