//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];

更多信息请查看IT技术专栏