CODEWITHSUNDEEP
iosiphoneimagesqlitesave
user2674668
user2674668

Reputation: 117

Save image to database and load from server

I stored images in server. Then I fetch the images and save to db1.sqlite file through image URL. Images are saved to db1.sqlite file like URL. How to display the images from saved URL. product_image having the imageURL path.

code:

sqlite table structure:

CREATE TABLE "product" ("id" INTEGER PRIMARY KEY  NOT NULL , "cat_id" INTEGER NOT NULL , "product_image" VARCHAR NOT NULL , "order_by" INTEGER NOT NULL )

Insert code:

const char *sqlInsert = [[NSString stringWithFormat:@"insert into product (id, cat_id,product_image,order_by) values ('%@','%@','%@','%@')", [tuser objectForKey:@"id"], [tuser objectForKey:@"cat_id"],[tuser objectForKey:@"product_image"],[tuser objectForKey:@"order_by"]] cStringUsingEncoding:NSUTF8StringEncoding];

NSLog(@"product insert %s", sqlInsert);

if(sqlite3_prepare_v2(database, sqlInsert, -1, &addStmt, NULL) != SQLITE_OK)
    NSAssert1(0, @"Error while creating add statement. '%s'", sqlite3_errmsg(database));

if(SQLITE_DONE != sqlite3_step(addStmt))
    NSAssert1(0, @"Error while inserting data. '%s'", sqlite3_errmsg(database));

Image fetching:

NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
//NSLog(@"docs dir is %@", documentsDirectory);

NSString *path = [documentsDirectory stringByAppendingPathComponent:@"db1.sqlite"];
//NSLog(@"filepath %@",path);

mArray = [[NSMutableArray alloc]init];
if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {

    const char *sql = "SELECT id,cat_id,product_image FROM product order by order_by";

    NSLog(@"sql is %s",sql);

    sqlite3_stmt *statement;
    //  int catID = 0;
    if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
        // We "step" through the results - once for each row.
        while (sqlite3_step(statement) == SQLITE_ROW) {

            int length = sqlite3_column_bytes(statement, 2);
            NSData *imageData = [NSData dataWithBytes:sqlite3_column_blob(statement, 2) length:length];

            UIImage *image = [UIImage imageWithData:imageData];

            [mArray addObject:image];

            [image release];
        }
    }
    sqlite3_finalize(statement);
}
else {
    sqlite3_close(database);
    NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
    // Additional error handling, as appropriate...
}

Upvotes: 0

Views: 226

Answers (1)

Rob
Rob

Reputation: 437522

Chatting with you offline, I discovered that product_image is not a NSData. (I had mistakenly inferred from the presence of sqlite3_column_blob later, that you were trying to store binary data, which is not the case.) Apparently product_image is string that contains the URL. So store it as a string. And never use stringWithFormat in your SQL statements, but rather use the sqlite3_bind_xxx functions with ? placeholders, e.g.:

const char *sqlInsert = "insert into product (id, cat_id, product_image, order_by) values (?, ?, ?, ?)";

if (sqlite3_prepare_v2(database, sqlInsert, -1, &addStmt, NULL) != SQLITE_OK)
    NSAssert1(0, @"Error while creating add statement. '%s'", sqlite3_errmsg(database));

if (sqlite3_bind_int(addStmt, 1, [[tuser objectForKey:@"id"] intValue]) != SQLITE_OK)
    NSAssert1(0, @"Error binding 1. '%s'", sqlite3_errmsg(database));

if (sqlite3_bind_int(addStmt, 2, [[tuser objectForKey:@"cat_id"] intValue]) != SQLITE_OK)
    NSAssert1(0, @"Error binding 2. '%s'", sqlite3_errmsg(database));

if (sqlite3_bind_text(addStmt, 3, [[tuser objectForKey:@"product_image"] UTF8String], -1, NULL) != SQLITE_OK)
    NSAssert1(0, @"Error binding 3. '%s'", sqlite3_errmsg(database));

if (sqlite3_bind_int(addStmt, 4, [[tuser objectForKey:@"order_by"] intValue]) != SQLITE_OK)
    NSAssert1(0, @"Error binding 4. '%s'", sqlite3_errmsg(database));

if (SQLITE_DONE != sqlite3_step(addStmt))
    NSAssert1(0, @"Error while inserting data. '%s'", sqlite3_errmsg(database));

// don't forget to finalize

sqlite3_finalize(addStmt);

If you do this, you have to change your code that retrieves the images, too. For example, you have:

while (sqlite3_step(statement) == SQLITE_ROW) {

    int length = sqlite3_column_bytes(statement, 2);
    NSData *imageData = [NSData dataWithBytes:sqlite3_column_blob(statement, 2) length:length];

    UIImage *image = [UIImage imageWithData:imageData];

    [mArray addObject:image];

    [image release];
}

A couple of thoughts:

  1. The [image release] is an over-release. The imageWithData returns an autorelease object, so you don't need/want to release it. This sort of error can be catastrophic, so I'd suggest you run your code through the static analyzer to help identify these sorts of bugs (by choosing "Analyze" from the "Product" menu, or pressing command+shift+B). You should have zero warnings generated by the static analyzer. The analyzer is an incredibly useful tool to identify programming errors, so avail yourself of this excellent tool.

  2. That prior point is moot, though, because you're not storing an actual image in NSData, but rather you're storing a URL in a string, so you should be using sqlite3_column_text, instead.

  3. Once you convert the const char * you get from sqlite3_column_text into a NSString, you then want to take that string and create a URL, e.g.:

    NSURL *url = [NSURL URLWithString:urlStringFromDatabase];

  4. You can then use that NSURL in conjunction with a UIImageView category like SDWebImage, to asynchronously retrieve the image (and do all of the appropriate cacheing, etc.).

    [imageView setImageWithURL:url
          placeholderImage:[UIImage imageNamed:@"placeholder.png"]];

  5. And, at the risk of repeating myself, your image retrieval code checks whether the SQLite functions returned, for example SQLITE_OK, but if not, you're not showing the sqlite3_errmsg. Always look at this when debugging apps. It will save you literally hours in development/debugging.

Upvotes: 1

Related Questions