IPhone/iOS development-SQLite database usage

IPhone development-SQLite database usage

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;
}
 Return Yes;
}
 If (Sqlite3_open ([path utf8string], & database _) = sqlite_ OK ){
Bfirstcreate _ = yes;
[Self createchannelstable: Database _]; //  Implement the createchannelstable function later.  
         Return Yes;
} Else {
Sqlite3_close (Database _);
Nslog ( @"  Error: Open Database File.  " );
 Return No;
}
 Return No;
}

[2] Create a table

 1   // Create a table with five fields (ID, CID, title, imagedata, imagelen)
  2   //  Note that ID is the primary key of the table, which must exist.
  3   //  CID, and title are both strings, imagedata is binary data, and imagelen is the length of the binary data.  
  4  
 5 -(Bool) createchannelstable :( sqlite3 *) dB
 6 {
 7       Char * SQL ="  Create Table channels (ID integer primary key ,\  
  8 CID text ,\
 9 Title text ,\
 10 Imagedata blob ,\
 11 Imagelen integer) "  ;  
  12 Sqlite3_stmt * statement;
 13       If (Sqlite3_prepare_v2 (dB, SQL ,- 1 , & Statement, nil )! = Sqlite_ OK ){
 14 Nslog ( @"  Error: failed to prepare statement: Create channels table  " );
 15           Return No;
 16 }
 17      Int Success = sqlite3_step (statement );
 18 Sqlite3_finalize (statement );
 19       If (Success! = Sqlite_done ){
 20 Nslog ( @"  Error: failed to dehydrate: Create Table Channels  " );
 21           Return No;
 22 }
23 Nslog ( @"  Create Table 'channels' successed.  " );
 24       Return Yes;
 25 }

 

[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 the fields (CID, title, imagedata, imagelen), representing 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, 3, and 4 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 );
}