sqlite--Code Manipulation

1. Create a database

nsstring * DocPath = [nssearchpathfordirectoriesindomains(nsdocumentdirectory, Nsuserdomainmask, YES)lastobject];

NSLog(@ "%@", DocPath);

nsstring * FilePath = [DocPath stringbyappendingpathcomponent:@ "Stu.sql"];

if (sqlite3_open([FilePath utf8string], &_mysql)! =Sqlite_ok) {

NSLog(@ "open error");

return 0;

}

NSLog(@ "open OK");

2. Create a table

nsstring *createtablesql = @ "Create TABLE IF not EXISTS Student (ID integer PRIMARY key autoincrement,name varchar (+), address varchar (+), Icon blob) ";

char *error = nil;

if (sqlite3_exec(_mysql, [Createtablesql utf8string], nil, Nil , &error)! =Sqlite_ok) {

NSLog(@ "creat table error");

return 0;

}

NSLog(@ "creat table OK");

3. Inserting data

NSString *insertsql = @ "INSERT into Student (Name,address,icon) VALUES (?,?,?)" ;

sqlite3_stmt *statement;

if (sqlite3_prepare_v2(MySQL, [insertsql utf8string],-1, &statement, NULL)! = Sqlite_ok) {

NSLog(@ "Insert prepare error");

return;

}Else{

NSLog(@ "Insert prepare OK");

nsdata * imageData = uiimagepngrepresentation(self. Iconimageview. Image);

Sqlite3_bind_text(statement, 1, [_nametextfield. Text utf8string],-1, NULL);

Sqlite3_bind_text(statement, 2, [_addtextfield. Text utf8string],-1, NULL);

Sqlite3_bind_blob(statement, 3, [imageData bytes], (int) [imageData length], NULL);

if(sqlite3_step(statement)! =Sqlite_done) {

NSLog(@ "Insert Error");

}Else{

NSLog(@ "Insert OK");

}

}

sqlite3_finalize(statement);

4. Querying data

NSString *selectsql = @ "SELECT * from Student";

sqlite3_stmt *statement;

if (sqlite3_prepare_v2(MySQL, [selectsql utf8string],-1, &statement, NULL)! = Sqlite_ok) {

NSLog(@ "read prepare error");

return;

}Else{

NSLog(@ "read prepare OK");

while (sqlite3_step(statement) = =Sqlite_row) {

Student * stu = [[Student alloc]init];

Char * name = (char *)sqlite3_column_text(statement, 1);

Char * address = (char *)sqlite3_column_text(statement, 2);

byte * bytes = (byte *)Sqlite3_column_blob(statement, 3);

int length = sqlite3_column_bytes(statement, 3);

nsdata * data = [nsdata datawithbytes: bytes length: length];

UIImage * image = [UIImage imagewithdata:d ATA];

Stu. name = [nsstring stringwithutf8string: name];

Stu. address = [nsstring stringwithutf8string: address];

Stu. icon = image;

[_studentarray addobject: stu];

}

}

sqlite--Code Manipulation