Use the SQLite database developed by iPhone

IPhone SQLite Database DevelopmentThis document describes how to useSQLite3.0 createDatabaseAnd thenDatabaseCreate a table. First, introduceSQLite3.0 lib library. Then include the header file # import

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

 
 

  
  
// Create a table. Assume that there are five fields: id, cid, title, imageData, and 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 );
  
  
}
 
 

Summary:IPhone SQLite Database DevelopmentI hope this article will help you.