JSON值未保存到iPhone中的SQLite数据库
问题描述：
我有一个应用程序，其中通过HTTP POST方法将数据发布到服务器API数据库并获得JSON格式的响应.我想读取JSON数据并将其保存在SQLite数据库中.我已经完成了通过HTTP POST方法将数据发布到Web服务器API的操作，但是尚未读取JSON数据并将其保存在数据库中.
I have an application in which I am posting data to my server API database through HTTP POST method and get response in JSON format. I want to read the JSON data and save it in SQLite database. I have done with posting data to Web server API through HTTP POST method but reading the JSON data and saving it in database has not been achieved.
我创建了一个包含所有JSON数组对象的类.
I have created a class which consists of all the JSON array objects.
这是.h文件:
#import <Foundation/Foundation.h>
//TokenID":"Vao13gifem","isError":false,"ErrorMessage":"","Result":[{"UserId":"153","FirstName":"Rocky","LastName":"Yadav","Email":"rocky@itg.com","ProfileImage":null,"ThumbnailImage":null,"DeviceInfoId":"12"}],"ErrorCode":900}
//Terminating in response to SpringBoard's termination.
@interface Result : NSObject {
NSString * UserID;
NSString *FirstName;
NSString *LastName;
NSString *Email;
NSString *ProfileImage;
NSString *ThumbnailImage;
NSString *DeviceInfoId;
}
@property (nonatomic,retain) NSString *UserID;
@property (nonatomic,retain) NSString *FirstName;
@property (nonatomic,retain) NSString *LastName;
@property (nonatomic,retain) NSString *Email;
@property (nonatomic,retain) NSString *ProfileImage;
@property (nonatomic,retain) NSString *ThumbnailImage;
@property (nonatomic,retain) NSString *DeviceInfoId;
@end
这是.m文件
#import "Result.h"
@implementation Result
@synthesize UserID;
@synthesize FirstName;
@synthesize LastName;
@synthesize Email;
@synthesize ProfileImage;
@synthesize ThumbnailImage;
@synthesize DeviceInfoId;
- (void)dealloc {
[super dealloc];
[UserID release];
[FirstName release];
[LastName release];
[Email release];
[ProfileImage release];
[ThumbnailImage release];
[DeviceInfoId release];
}
@end
这是我的apicontroller.m
This is my apicontroller.m
#import "apicontroller.h"
#import "Result.h"
#import <sqlite3.h>
#define DATABASE_NAME @"journey.sqlite"
#define DATABASE_TITLE @"journey"
@implementation apicontroller
@synthesize txtUserName;
@synthesize txtPassword;
@synthesize txtfirstName;
@synthesize txtlast;
@synthesize txtEmail;
@synthesize webData;
- (NSString *) getWritableDBPath {
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory , NSUserDomainMask, YES);
NSString *documentsDir = [paths objectAtIndex:0];
return [documentsDir stringByAppendingPathComponent:DATABASE_NAME];
}
-(void)createEditableCopyOfDatabaseIfNeeded
{
// Testing for existence
BOOL success;
NSFileManager *fileManager = [NSFileManager defaultManager];
NSError *error;
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,
NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:DATABASE_NAME];
NSLog(@"%@",writableDBPath);
success = [fileManager fileExistsAtPath:writableDBPath];
if (success)
return;
// The writable database does not exist, so copy the default to
// the appropriate location.
NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath]
stringByAppendingPathComponent:DATABASE_NAME];
success = [fileManager copyItemAtPath:defaultDBPath
toPath:writableDBPath
error:&error];
if(!success)
{
NSAssert1(0,@"Failed to create writable database file with Message : '%@'.",
[error localizedDescription]);
}
}
-(void)sendRequest
{
UIDevice *device = [UIDevice currentDevice];
NSString *udid = [device uniqueIdentifier];
NSString *sysname = [device systemName];
NSString *sysver = [device systemVersion];
NSString *model = [device model];
NSLog(@"idis:%@",[device uniqueIdentifier]);
NSLog(@"system nameis :%@",[device systemName]);
NSLog(@"System version is:%@",[device systemVersion]);
NSLog(@"System model is:%@",[device model]);
NSLog(@"device orientation is:%d",[device orientation]);
NSString *post = [NSString stringWithFormat:@"Loginkey=%@&Password=%@&DeviceCode=%@&Firmware=%@&IMEI=%@",txtUserName.text,txtPassword.text,model,sysver,udid];
NSData *postData = [post dataUsingEncoding:NSASCIIStringEncoding allowLossyConversion:YES];
NSString *postLength = [NSString stringWithFormat:@"%d", [postData length]];
NSLog(@"%@",postLength);
NSMutableURLRequest *request = [[[NSMutableURLRequest alloc] init] autorelease];
[request setURL:[NSURL URLWithString:@"http://192.168.0.68:91/JourneyMapperAPI?RequestType=Login"]];
[request setHTTPMethod:@"POST"];
[request setValue:postLength forHTTPHeaderField:@"Content-Length"];
[request setValue:@"application/x-www-form-urlencoded" forHTTPHeaderField:@"Content-Type"];
[request setHTTPBody:postData];
NSURLConnection *theConnection = [[NSURLConnection alloc] initWithRequest:request delegate:self];
if (theConnection) {
webData = [[NSMutableData data] retain];
NSLog(@"%@",webData);
}
else
{
}
}
-(void)connection:(NSURLConnection *)connection didReceiveResponse:(NSURLResponse *)response
{
[webData setLength: 0];
}
-(void)connection:(NSURLConnection *)connection didReceiveData:(NSData *)data
{
[webData appendData:data];
}
-(void)connection:(NSURLConnection *)connection didFailWithError:(NSError *)error
{
[connection release];
[webData release];
}
-(void)connectionDidFinishLoading:(NSURLConnection *)connection
{
NSString *loginStatus = [[NSString alloc] initWithBytes: [webData mutableBytes] length:[webData length] encoding:NSUTF8StringEncoding];
NSLog(@"%@",loginStatus);
NSString *json_string = [[NSString alloc] initWithData:webData encoding:NSUTF8StringEncoding];
NSDictionary *result = [json_string JSONValue];
NSArray *values = [result objectForKey:@"Result"];
NSMutableArray *results = [[NSMutableArray alloc] init];
for (int index = 0; index<[values count]; index++) {
NSMutableDictionary * value = [values objectAtIndex:index];
Result * result = [[Result alloc] init];
result.UserID = [value objectForKey:@"UserId"];
result.FirstName = [value objectForKey:@"FirstName"];
result.LastName =[value objectForKey:@"LastName"];
result.Email =[value objectForKey:@"Email"];
result.ProfileImage =[value objectForKey:@"ProfileImage"];
result.ThumbnailImage =[value objectForKey:@"ThumbnailImage"];
result.DeviceInfoId =[value objectForKey:@"DeviceInfoId"];
[results addObject:result];
[result release];
}
for (int index = 0; index<[results count]; index++) {
Result * result = [results objectAtIndex:index];
//save the object variables to database here
[self createEditableCopyOfDatabaseIfNeeded];
NSString *filePath = [self getWritableDBPath];
sqlite3 *database;
if(sqlite3_open([filePath UTF8String], &database) == SQLITE_OK) {
const char *sqlStatement = "insert into UserInformation(UserID,DeviceId,FirstName,Email,) VALUES (?,?,?,?)";
sqlite3_stmt *compiledStatement;
if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
sqlite3_bind_text( compiledStatement, 1, [result.UserID UTF8String],-1,SQLITE_TRANSIENT);
sqlite3_bind_text(compiledStatement, 2, [result.DeviceInfoId UTF8String],-1,SQLITE_TRANSIENT);
sqlite3_bind_text (compiledStatement, 3, [result.FirstName UTF8String],-1,SQLITE_TRANSIENT);
sqlite3_bind_text (compiledStatement, 4, [result.Email UTF8String],-1,SQLITE_TRANSIENT);
}
if(sqlite3_step(compiledStatement) != SQLITE_DONE ) {
NSLog( @"Save Error: %s", sqlite3_errmsg(database) );
}
else {
UIAlertView *alert = [[UIAlertView alloc]initWithTitle:@"UIAlertView" message:@"Record added" delegate:self cancelButtonTitle:@"OK" otherButtonTitles:nil];
[alert show];
[alert release];
alert = nil;
}
sqlite3_finalize(compiledStatement);
}
sqlite3_close(database);
}
[loginStatus release];
[connection release];
[webData release];
}
-(IBAction)click:(id)sender
{
[self sendRequest];
}
-(void)touchesBegan:(NSSet *)touches withEvent:(UIEvent *)event{
[txtfirstName resignFirstResponder];
[txtlast resignFirstResponder];
[txtUserName resignFirstResponder];
[txtPassword resignFirstResponder];
[txtEmail resignFirstResponder];
}
// Implement viewDidLoad to do additional setup after loading the view, typically from a nib.
- (void)viewDidLoad {
[super viewDidLoad];
//[self sendRequest];
}
/*
// Override to allow orientations other than the default portrait orientation.
- (BOOL)shouldAutorotateToInterfaceOrientation:(UIInterfaceOrientation)interfaceOrientation {
// Return YES for supported orientations.
return (interfaceOrientation == UIInterfaceOrientationPortrait);
}
*/
- (void)didReceiveMemoryWarning {
// Releases the view if it doesn't have a superview.
[super didReceiveMemoryWarning];
// Release any cached data, images, etc. that aren't in use.
}
- (void)viewDidUnload {
[super viewDidUnload];
// Release any retained subviews of the main view.
// e.g. self.myOutlet = nil;
}
- (void)dealloc {
[super dealloc];
}
@end
在connectionFinishLoading方法中，它不比较每个对象，而是直接在NSLog中打印错误:
In the connectionFinishLoading method it does not compare each object, it directly prints the error in NSLog:
(Save Error:near)":语法错误)；
(Save Error:near")":syntax error);
如何解决此错误?
答
我的猜测是，SQL语句的列列表中的结尾逗号引起错误:
My guess is that the trailing comma in the list of columns in your SQL statement is causing the error:
const char *sqlStatement = "insert into UserInformation(UserID,DeviceId,FirstName,Email,) VALUES (?,?,?,?)";
请注意，Email和)之间有一个逗号.
Note that there’s a comma between Email and ).
尝试:
const char *sqlStatement = "insert into UserInformation(UserID,DeviceId,FirstName,Email) VALUES (?,?,?,?)";
相反.