IPhone MySQL Database operation code example

Database operation

Open Database

-(BOOL) opendatabase{

Nsarray*paths = Nssearchpathfordirectoriesindomains (nsdocumentdirectory, Nsuserdomainmask,yes);

NSString *documentsdirectory = [pathsobjectatindex:0];

NSString *path = [documentsdirectorystringbyappendingpathcomponent:@ "Mydb.sql"];

Nsfilemanager*filemanager = [Nsfilemanagerdefaultmanager];

BOOL find = [Filemanagerfileexistsatpath:path];

Locate the database file 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_];//in the back to implement the function createchannelstable

return YES;

}else {

Sqlite3_close (Database_);

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

return NO;

}

return NO;

}

Create a table

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

Char*sql = "CREATE TABLE reports (ID integer primary key,stime text,stitle text,scal 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;

}

Insert Table

-(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,?,?,?,?)";

The number of question marks matches the number of fields in (Cid,title,imagedata,imagelen), representing unknown values, and the values and fields are associated below.

int success =SQLITE3_PREPARE_V2 (database_, SQL,-1, &statement,null);

if (Success!=sqlite_ok) {

NSLog (@ "error:failed to Insert:channels");

return NO;

}

The number 1,2,3,4 here represents the first few question marks

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");

return NO;

}

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

return YES;

}

Query table

-(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.");

}

The query result set a single traversal of all the records, where the numbers correspond to the column values.

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