首页 > ios > FMDB 使用是插入data类型的问题

FMDB 使用是插入data类型的问题

人恒过,然后能改;困于心,衡于虑,而后作;征于色,发于声,而后喻。

—写给折腾了半天的自己

  今天遇见了一个很郁闷的错误,花了很长的时间才解决掉,先阐述一下,
  我计划把一个字典通过[NSKeyedArchiver archivedDataWithRootObject:dict] 转化成NSData类型存进sqlite数据库中,但是,写出后发现了这样的问题。首先是读出来的数据用[NSKeyedUnarchiver unarchiveObjectWithData:tempData]失败。我就打印输出一下,发现这样的结果,为了对比明显,我就用[NSUserDefaults standardUserDefaults]储存一个进行对比,于是惊奇的发现了区别:
  语句:(tempData1是NSUserDefaults存储的对比)

NSLog(@"tempData = %@,%d,%d",[tempData class],
[tempData isKindOfClass:[NSData class]],[tempData length]);
NSLog(@"tempData1 = %@,%d,%d",[tempData1 class],
[tempData1 isKindOfClass:[NSData class]],[tempData1 length]);

图片
图片
图片
  而在数据库中存储的也是:
图片
  这个是什么原因呢?我花了半天的时间找啊找,最后发现是自己的代码错误,代码如下:

NSString* sqlString=[NSString stringWithFormat:@"REPLACE INTO detail(bin,mark,uid,timestamp) VALUES ('%@','%@','%@','%@');",data,[urlString md5String],uid,timestamp];
NSLog(@"sqlString=%@",sqlString);

  问题就出现在这儿了,我为了保证每一次的sql语句正确,一般都会输出一下看看,结果这儿的NSData类型的数据在这一步被活生生的转化成了NSString类型了,而且和打印输出的一样,如果再一次从数据库中以NSData类型读出来的时候,就变了味道。再看看FMDB的官方说法.

Data Sanitization

  When providing a SQL statement to FMDB, you should not attempt to “sanitize” any values before REPLACEion. Instead, you should use the standard SQLite binding syntax:

REPLACE INTO myTable VALUES (?, ?, ?)

  The ? character is recognized by SQLite as a placeholder for a value to be REPLACEed. The execution methods all accept a variable number of arguments (or a representation of those arguments, such as an NSArray, NSDictionary, or a va_list), which are properly escaped for you.
  Alternatively, you may use named parameters syntax:

REPLACE INTO myTable VALUES (:id, :name, :value)

  The parameters must start with a colon. SQLite itself supports other characters, but internally the Dictionary keys are prefixed with a colon, do not include the colon in your dictionary keys.

NSDictionary *argsDict = [NSDictionary dictionaryWithObjectsAndKeys:@"My Name", @"name", nil];
[db executeUpdate:@"REPLACE INTO myTable (name) VALUES (:name)" withParameterDictionary:argsDict];

  Thus, you SHOULD NOT do this (or anything like this):

[db executeUpdate:[NSString stringWithFormat:@"REPLACE INTO myTable VALUES (%@)", @"this has \" lots of ' bizarre \" quotes '"]];

  Instead, you SHOULD do:

[db executeUpdate:@"REPLACE INTO myTable VALUES (?)", @"this has \" lots of ' bizarre \" quotes '"];

  All arguments provided to the -executeUpdate: method (or any of the variants that accept a va_list as a parameter) must be objects. The following will not work (and will result in a crash):

[db executeUpdate:@"REPLACE INTO myTable VALUES (?)", 42];

  The proper way to REPLACE a number is to box it in an NSNumber object:

[db executeUpdate:@"REPLACE INTO myTable VALUES (?)", [NSNumber numberWithInt:42]];

  Alternatively, you can use the -execute*WithFormat: variant to use NSString-style substitution:

[db executeUpdateWithFormat:@"REPLACE INTO myTable VALUES (%d)", 42];

  Internally, the -execute*WithFormat: methods are properly boxing things for you. The following percent modifiers are recognized: %@, %c, %s, %d, %D, %i, %u, %U, %hi, %hu, %qi, %qu, %f, %g, %ld, %lu, %lld, and %llu. Using a modifier other than those will have unpredictable results. If, for some reason, you need the % character to appear in your SQL statement, you should use %%.

  这些英文比较简单,2级半的英语水平都能看懂,我就不翻译了。直说最后,我的语句是这样写的

if ([db executeUpdate:@"REPLACE INTO detail(bin,mark,uid,timestamp) VALUES
 (?,?,?,?);",[NSKeyedArchiver archivedDataWithRootObject:dict],
[urlString md5String],uid,timestamp]) {
    NSLog(@"REPLACE OK");
}

  这样写就对了。
总结:1.遇到错误的话,应该首先去看官方的文档,而不是自己瞎试,按着自己的方法来对的概率是很低的。
2.只要有恒心,没有什么难道自己的!

  1. 还没有评论
评论提交中, 请稍候...

留言


可以使用的标签: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>
Trackbacks & Pingbacks ( 0 )
  1. 还没有 trackbacks