sqlite3的斑点问题
问题描述:
可能重复:
sqlite3 insert and read BLOB data in databasesqlite3的斑点问题
我尝试读写BLOB数据到数据库中。但是当我尝试读取blob数据时 - 它的大小为0.并且我所有的字段数据都移动了。这里是代码:
请帮助我,我做错了什么?感谢名单!
-(int) addUser: (MyData *) user table: (NSString *) tableName
{
sqlite3_stmt *updStmt =nil;
const char *sql = "INSERT INTO Users (Name, Surname, Email, Phone, Image) VALUES (?, ?, ?, ?, ?);";
int res = SQLITE_ERROR;
res = sqlite3_prepare_v2(database, sql, -1, &updStmt, NULL);
if(res!= SQLITE_OK)
{
NSLog(@"Error while creating update statement:%@", sqlite3_errmsg(database));
return res;
}
res = sqlite3_bind_text(updStmt, 1, [user.name UTF8String], -1, SQLITE_TRANSIENT);
res = sqlite3_bind_text(updStmt, 2, [user.surname UTF8String], -1, SQLITE_TRANSIENT);
res = sqlite3_bind_text(updStmt, 3, [user.email UTF8String], -1, SQLITE_TRANSIENT);
res = sqlite3_bind_text(updStmt, 4, [user.telephone UTF8String], -1, SQLITE_TRANSIENT);
NSData *imageData = [NSData dataWithData: UIImagePNGRepresentation(user.image)];
res = sqlite3_bind_blob(updStmt, 5, imageData, -1, SQLITE_BLOB);
if((res = sqlite3_step(updStmt)) != SQLITE_DONE)
{
NSLog(@"Error while updating: %@", sqlite3_errmsg(database));
sqlite3_reset(updStmt);
return res;
}
sqlite3_reset(updStmt);
return res;
}
在这里我怎么能读这个字段:
-(MyData *) createUserWithDetailsByID: (int) uID
{
NSString *query = [NSString stringWithFormat: @SELECT_USER_ALLDATA_BY_ID, uID];
MyData *user = nil;
sqlite3_stmt *statement;
int res = SQLITE_ERROR;
res = sqlite3_prepare_v2 (database, [query UTF8String], -1, &statement, nil);
if (res == SQLITE_OK)
{
if (sqlite3_step(statement) == SQLITE_ROW)
{
//if there are results in row
char *buf = NULL;
user = [[MyData alloc] init];
int num = sqlite3_column_int(statement, 0);
user.uID = num;
buf = (char *)sqlite3_column_text(statement, 1);
if (buf)
user.name = [NSString stringWithFormat: @"%s", buf];
else
user.name = @"";
buf = (char *)sqlite3_column_text(statement, 2);
if (buf)
user.surname = [NSString stringWithFormat: @"%s", buf];
else
user.surname = @"";
buf = (char *)sqlite3_column_text(statement, 3);
if (buf)
user.email = [NSString stringWithFormat: @"%s", buf];
else
user.email = @"";
buf = (char *)sqlite3_column_text(statement, 4);
if (buf)
user.telephone = [NSString stringWithFormat: @"%s", buf];
else
user.telephone = @"";
int len = 0;
len = sqlite3_column_bytes(statement, 5);
NSData *imgData = [[NSData alloc] initWithBytes: sqlite3_column_blob(statement, 5) length: len];
UIImage *i;
[i initWithData:imgData];
}
}
sqlite3_finalize(statement);
return user;
}
答
NSData *imageData = [NSData dataWithData: UIImagePNGRepresentation(user.image)];
res = sqlite3_bind_blob(updStmt, 5, [imageData bytes], [imageData length], SQLITE_BLOB);
我认为当你写,你应该使用[imageData bytes]
和[imageData length]
。 NSData
是一个obj-C类,它不是一个无符号字符数组。
答
Thanx all!与你的帮助我解决了问题,并希望分享像我这样的未来初学者的结果。)
-(void) addToDB
{
NSLog(@"\nCreating db");
NSString *str = @"CREATE TABLE IF NOT EXISTS Images (image1 BLOB);";
int res = SQLITE_ERROR;
res = sqlite3_open([@"aa.sql" UTF8String], &database);
res = sqlite3_exec(database, [str UTF8String], NULL, NULL, NULL);
sqlite3_stmt *updStmt =nil;
const char *sql = "INSERT INTO Images (image1) VALUES (?);";
res = sqlite3_prepare_v2(database, sql, -1, &updStmt, NULL);
if(res!= SQLITE_OK)
{
NSLog(@"Error while creating update statement:%@", sqlite3_errmsg(database));
}
UIImage *img = [UIImage imageNamed: @"flower.png"];
NSData *imageData = [NSData dataWithData: UIImagePNGRepresentation(img)];
res = sqlite3_bind_blob(updStmt, 1, [imageData bytes], [imageData length] , NULL);
if((res = sqlite3_step(updStmt)) != SQLITE_DONE)
{
NSLog(@"Error while updating: %@", sqlite3_errmsg(database));
sqlite3_reset(updStmt);
}
res = sqlite3_reset(updStmt);
res = sqlite3_close(database);
}
-(void) readFromDB
{
NSLog(@"\nReading from db");
NSString *query = @"SELECT image1 from Images";
int res = SQLITE_ERROR;
int len = 0;
res = sqlite3_open([@"aa.sql" UTF8String], &database);
sqlite3_stmt *statement;
res = sqlite3_prepare_v2 (database, [query UTF8String], -1, &statement, nil);
if (res == SQLITE_OK)
{
if (sqlite3_step(statement) == SQLITE_ROW)
{
len = sqlite3_column_bytes(statement, 0);
NSData *imgData = [[NSData alloc] initWithBytes: sqlite3_column_blob(statement, 0) length: len];
UIImage *img = [[UIImage alloc] initWithData:imgData];
self.view1.image = img;
}
}
sqlite3_finalize(statement);
res = sqlite3_close(database);
}
+0
不客气。 – AechoLiu 2010-11-19 08:03:19
Thanx,我会试试看! – yozhik 2010-11-18 21:45:08