IPhone development-SQLite database usage

From: http://yuxiang13115204.blog.163.com/blog/static/26802022200921410845642/

I want to use sqlite3.0 to create a database and then create a table in the database.

First, introduce the Lib library of sqlite3.0. Then include the header file # import <sqlite3.h>

[1]Open the database. If not, create

Sqlite3 * database _;

-(Bool) Open

{
Nsarray * paths = nssearchpathfordirectoriesindomains (nsdocumentdirectory, nsuserdomainmask, yes );
Nsstring * documentsdirectory = [paths objectatindex: 0];
Nsstring * Path = [documentsdirectory stringbyappendingpathcomponent: @ "mydb. SQL"];
Nsfilemanager * filemanager = [nsfilemanager defaultmanager];
Bool find = [filemanager fileexistsatpath: path];

// find the database file mydb. SQL
If (FIND) {
nslog (@ "database file have already existed. ");
If (sqlite3_open ([path utf8string], & database _)! = Sqlite_ OK) {
sqlite3_close (Database _);
nslog (@ "error: Open Database File. ");
return no;
}< br> return yes;
}< br> If (sqlite3_open ([path utf8string], & database _) = sqlite_ OK) {
bfirstcreate _ = yes;
[self Createchannelstable : Database _]; // implement the function later Createchannelstable

Return yes;
} Else {
Sqlite3_close (Database _);
Nslog (@ "error: open database file .");
Return no;
}
Return no;
}

[2] Create a table

// Create a table. Assume that there are five fields (ID, CID, title, imagedata, imagelen)

// Note that ID is the primary key of the table, which must exist.

// CID, and title are both strings, imagedata is binary data, and imagelen is the length of binary data.
-(Bool) createchannelstable :( sqlite3 *) dB {
Char * SQL = "CREATE TABLE channels (ID integer primary key ,\
CID text ,\
Title text ,\
Imagedata blob ,\
Imagelen integer )";
Sqlite3_stmt * statement;
If (sqlite3_prepare_v2 (dB, SQL,-1, & statement, nil )! = Sqlite_ OK ){
Nslog (@ "error: failed to prepare statement: Create channels table ");
Return no;
}
Int success = sqlite3_step (statement );
Sqlite3_finalize (statement );
If (success! = Sqlite_done ){
Nslog (@ "error: failed to dehydrate: Create Table channels ");
Return no;
}
Nslog (@ "create table 'channels' successed .");
Return yes;
}

 

[3]Insert a record to the table

Assume that channle is a data structure that stores the content of a record.

-(bool) insertonechannel :( channel *) channel {
nsdata * imagedata = uiimagepngrepresentation (channel. image _);
nsinteger imagelen = [imagedata length];
sqlite3_stmt * Statement;
static char * SQL = "insert into channels (CID, title, imagedata, imagelen) \
values (?,?,?,?) ";

// The number of question marks must match the number of fields in (CID, title, imagedata, imagelen) to indicate unknown values. 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 numbers 1, 2, and 3 represent the question mark
Sqlite3_bind_text (statement, 1, [channel. ID _ utf8string],-1, sqlite_transient );
Sqlite3_bind_text (statement, 2, [channel. Title _ utf8string],-1, sqlite_transient );
Sqlite3_bind_blob (statement, 3, [imagedata bytes], imagelen, sqlite_transient );
Sqlite3_bind_int (statement, 4, imagelen );

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 = % @", channel. ID _);
Return yes;
}

 

[4] database query

Obtain all the records in the table and put them in the fchannels array.

-(Void) getchannels :( nsmutablearray *) fchannels {
Sqlite3_stmt * Statement = nil;
Char * SQL = "select * From channels ";
If (sqlite3_prepare_v2 (Database _, SQL,-1, & statement, null )! = Sqlite_ OK ){
Nslog (@ "error: failed to prepare statement with message: Get channels .");
}
// In the query result set, all records are traversed one by one. The number corresponds to the column value.
While (sqlite3_step (statement) = sqlite_row ){
Char * cid = (char *) sqlite3_column_text (statement, 1 );
Char * Title = (char *) sqlite3_column_text (statement, 2 );
Byte * imagedata = (byte *) sqlite3_column_blob (statement, 3 );
Int imagelen = sqlite3_column_int (statement, 4 );
Channel * channel = [[channel alloc] init];
If (CID)
Channel. ID _ = [nsstring stringwithuf8string: Cid];
If (title)
Channel. Title _ = [nsstring stringwithuf8string: title];
If (imagedata ){
Uiimage * image = [uiimage imagewithdata: [nsdata datawithbytes: imagedata length: imagelen];
Channel. Image _ = image;
}
[Fchannels addobject: Channel];
[Channel release];
}
Sqlite3_finalize (statement );
}

 

Let's simply talk about this.