Sqlite database inserts and reads image data

In iOS, use the sqlite database to store images. First, convert your images to the NSData format, and then add a row of blob data to the database.

Assume that the test_table (name, image) Table exists in the database. The following code writes the binary data of the image file test.png to the sqlite database:

CHAR * name = "test ";

NSString * nameString = [NSString stringWithCString: name encoding: NSUTF8StringEncoding];

NSString * filePath = [[NSBundle mainBundle] pathForResource: nameString ofType: @ "png"];

IF ([[NSFileManager defaultManager] fileExistsAtPath: filePath])

{

NSData * imgData = UIImagePNGRepresentation ([UIImage imageWithContentsOfFile: filePath]);

Const CHAR * sequel = "insert into test_table (name, image) values (?,?) ";

Sqlite3_stmt * UPDATE;

IF (sqlite3_prepare_v2 (DATABASE, sequel,-1, & UPDATE, NULL) = SQLITE_ OK)

{

Sqlite3_bind_text (UPDATE, 1, name,-1, NULL );

Sqlite3_bind_blob (UPDATE, 2, [imgData bytes], [imgData LENGTH], NULL );

IF (sqlite3_step (UPDATE) = SQLITE_DONE)

{

NSLog (@ "data written ");

}

Sqlite3_finalize (UPDATE );

}

}

ELSE

{

NSLog (@ "file does not exist ");

}

The following code reads the image binary data from the database and then displays the image:

Const CHAR * sequel = "select image from test_table where name =? ";

Sqlite3_stmt * getimg;

IF (sqlite3_prepare_v2 (DATABASE, sequel,-1, & getimg, NULL) = SQLITE_ OK)

{

CHAR * name = "test ";

Sqlite3_bind_text (UPDATE, 1, name,-1, NULL );

IF (sqlite3_step (getimg) = SQLITE_ROW)

{

INT bytes = sqlite3_column_bytes (getimg, 0 );

Byte * VALUE = (Byte *) sqlite3_column_blob (getimg, 1 );

IF (bytes! = 0 & VALUE! = NULL)

{

NSData * DATA = [NSData dataWithBytes: value length: bytes];

UIImage * img = [UIImage imageWithData: DATA];

UIImageView * aview = [[UIImageView alloc] initWithFrame:

CGRectMake (0.0, 0.0, IMAGE_WIDTH, IMAGE_HEIGHT)];

Aview. image = img;

[SELF. VIEW addSubview: aview];

[Aview release];

}

}

Sqlite3_finalize (getimg );

}

Example 2: store images

/Save Small Image Data by given main url

-(Void) SaveImagesToSql: (NSData *) imgData :( NSString *) mainUrl

{

NSLog (@ "n ****** Save image to SQLite ****** n ");

Const char * sqliteQuery = "insert into images (URL, IMAGE) VALUES (?, ?) ";

Sqlite3_stmt * statement;

If (sqlite3_prepare_v2 (articlesDB, sqliteQuery,-1, & statement, NULL) = SQLITE_ OK)

{

Sqlite3_bind_text (statement, 1, [mainUrl UTF8String],-1, SQLITE_TRANSIENT );

Sqlite3_bind_blob (statement, 2, [imgData bytes], [imgData length], SQLITE_TRANSIENT );

Sqlite3_step (statement );

}

Else NSLog (@ "SaveBody: Failed from sqlite3_prepare_v2. Error is: % s", sqlite3_errmsg (articlesDB ));

// Finalize and close database.

Sqlite3_finalize (statement );

}

Read image:

// Load images from data base with given image url

-(NSData *) LoadImagesFromSql: (NSString *) imageLink

{

NSData * data = nil;

NSString * sqliteQuery = [NSString stringWithFormat: @ "select image from images where url = '% @'", imageLink];

Sqlite3_stmt * statement;

If (sqlite3_prepare_v2 (articlesDB, [sqliteQuery UTF8String],-1, & statement, NULL) = SQLITE_ OK)

{

If (sqlite3_step (statement) = SQLITE_ROW)

{

Int length = sqlite3_column_bytes (statement, 0 );

Data = [NSData dataWithBytes: sqlite3_column_blob (statement, 0) length: length];

}

}

// Finalize and close database.

Sqlite3_finalize (statement );

Return data;

}