ios - 将字符串从SQLite存储到NSMutableArray

我正在开发一个使用ASIHTTPRequest显示各种提要的应用程序。用户可以将存储在SQLite数据库中的新源添加到应用中。目前，我在FeedsViewController的viewDidLoad方法中将提要提供给应用程序，但我希望能够检索包含源链接的数据并将其存储在数组中以使用它。

目前，该应用看起来像在当前代码的下方:

- (void)viewDidLoad
{
    [super viewDidLoad];

    self.title =@"Lajmet";
    self.navigationController.navigationBar.tintColor = [UIColor blackColor];
    //self.tabBarController.tabBar.tintColor = [UIColor blackColor];
    self.allEntries = [NSMutableArray array];
    self.queue = [[NSOperationQueue alloc] init];
    self.feeds = [NSMutableArray arrayWithObjects:@"http://pcworld.al/feed",@"http://geek.com/feed", @"http://feeds.feedburner.com/Mobilecrunch",@"http://zeri.info/rss/rss-5.xml",
                  nil];


    [self.tableView reloadData];

    [self refresh];


}

我的视图包含添加和删除新源的函数(AddSourcesViewController)，看起来像this，下面是代码:

- (void)viewDidLoad
{
    [super viewDidLoad];
    arrayOfSource = [[NSMutableArray alloc]init];
    [[self myTableView]setDelegate:self];
    [[self myTableView]setDataSource:self];
    [self createOrOpenDB];

    // Display sources in table view
    sqlite3_stmt *statement;

    if (sqlite3_open([dbPathString UTF8String], &sourceDB)==SQLITE_OK) {
        [arrayOfSource removeAllObjects];

        NSString *querySql = [NSString stringWithFormat:@"SELECT * FROM SOURCES"];
        const char* query_sql = [querySql UTF8String];

        if (sqlite3_prepare(sourceDB, query_sql, -1, &statement, NULL)==SQLITE_OK) {
            while (sqlite3_step(statement)==SQLITE_ROW) {
                NSString *name = [[NSString alloc]initWithUTF8String:(const char *)sqlite3_column_text(statement, 1)];
                NSString *link = [[NSString alloc]initWithUTF8String:(const char *)sqlite3_column_text(statement, 2)];
                NSString *category = [[NSString alloc]initWithUTF8String:(const char *)sqlite3_column_text(statement, 3)];

                SourceDB *source = [[SourceDB alloc]init];

                [source setName:name];
                [source setLink:link];
                [source setCategory:category];

                [arrayOfSource addObject:source];
            }
        }
    }
    [[self myTableView]reloadData];
}

- (void)createOrOpenDB
{
    NSArray *path = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *docPath = [path objectAtIndex:0];

    dbPathString = [docPath stringByAppendingPathComponent:@"sources.db"];

    char *error;

    NSFileManager *fileManager = [NSFileManager defaultManager];
    if (![fileManager fileExistsAtPath:dbPathString]) {
        const char *dbPath = [dbPathString UTF8String];

        //creat db here
        if (sqlite3_open(dbPath, &sourceDB)==SQLITE_OK) {
            const char *sql_stmt = "CREATE TABLE IF NOT EXISTS SOURCES (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, LINK TEXT, CATEGORY TEXT)";
            sqlite3_exec(sourceDB, sql_stmt, NULL, NULL, &error);
            sqlite3_close(sourceDB);
        }
    }
}

- (IBAction)addSourceButton:(id)sender
{
    char *error;
    if (sqlite3_open([dbPathString UTF8String], &sourceDB)==SQLITE_OK) {
        NSString *inserStmt = [NSString stringWithFormat:@"INSERT INTO SOURCES(NAME,LINK,CATEGORY) values ('%s', '%s','%s')",[self.nameField.text UTF8String], [self.linkField.text UTF8String],[self.categoryField.text UTF8String]];

        const char *insert_stmt = [inserStmt UTF8String];

        if (sqlite3_exec(sourceDB, insert_stmt, NULL, NULL, &error)==SQLITE_OK) {
            NSLog(@"Source added");

            SourceDB *source = [[SourceDB alloc]init];

            [source setName:self.nameField.text];
            [source setLink:self.linkField.text];
            [source setCategory:self.categoryField.text];

            [arrayOfSource addObject:source];
        }
        sqlite3_close(sourceDB);
    }
}

- (IBAction)deleteSourceButton:(id)sender
{
    [[self myTableView]setEditing:!self.myTableView.editing animated:YES];
}

-(void)tableView:(UITableView *)tableView commitEditingStyle:(UITableViewCellEditingStyle)editingStyle forRowAtIndexPath:(NSIndexPath *)indexPath
{
    if (editingStyle == UITableViewCellEditingStyleDelete) {
        SourceDB *s = [arrayOfSource objectAtIndex:indexPath.row];
        [self deleteData:[NSString stringWithFormat:@"Delete from sources where name is '%s'", [s.name UTF8String]]];
        [arrayOfSource removeObjectAtIndex:indexPath.row];

        [tableView deleteRowsAtIndexPaths:[NSArray arrayWithObject:indexPath] withRowAnimation:UITableViewRowAnimationFade];
    }
}

-(void)deleteData:(NSString *)deleteQuery
{
    char *error;

    if (sqlite3_exec(sourceDB, [deleteQuery UTF8String], NULL, NULL, &error)==SQLITE_OK) {
        NSLog(@"Source deleted");
    }
}

- (IBAction)showSourceButton:(id)sender
{
    sqlite3_stmt *statement;

    if (sqlite3_open([dbPathString UTF8String], &sourceDB)==SQLITE_OK) {
        [arrayOfSource removeAllObjects];

        NSString *querySql = [NSString stringWithFormat:@"SELECT * FROM SOURCES"];
        const char* query_sql = [querySql UTF8String];

        if (sqlite3_prepare(sourceDB, query_sql, -1, &statement, NULL)==SQLITE_OK) {
            while (sqlite3_step(statement)==SQLITE_ROW) {
                NSString *name = [[NSString alloc]initWithUTF8String:(const char *)sqlite3_column_text(statement, 1)];
                NSString *link = [[NSString alloc]initWithUTF8String:(const char *)sqlite3_column_text(statement, 2)];
                NSString *category = [[NSString alloc]initWithUTF8String:(const char *)sqlite3_column_text(statement, 3)];

                SourceDB *source = [[SourceDB alloc]init];

                [source setName:name];
                [source setLink:link];
                [source setCategory:category];

                [arrayOfSource addObject:source];
            }
        }
    }
    [[self myTableView]reloadData];
}

现在，我在FeedsViewConrtoller中创建了一个方法:

-(void)ReadData
{
    [self createOrOpenDB];

    feedsArray = [[NSMutableArray alloc] init];

    const char *dbPath = [dbPathString UTF8String];

    if (sqlite3_open(dbPath, &sourceDB)==SQLITE_OK)
    {
        const char *sqlstmt = "SELECT LINK FROM SOURCES";
        sqlite3_stmt *completedstmt;

        if (sqlite3_prepare_v2(sourceDB, sqlstmt, -1, &completedstmt, NULL)==SQLITE_OK)
        {

            while(sqlite3_step(completedstmt)==SQLITE_ROW)
            {

                NSString *link = [[NSString alloc]initWithUTF8String:(const char *)sqlite3_column_text(completedstmt, 2)];

                SourceDB *source = [[SourceDB alloc]init];

                [source setLink:link];

                [feedsArray addObject:link];




            }
        }
    }
}

当我改变

self.feeds = [NSMutableArray arrayWithObjects:@"http://pcworld.al/feed",@"http://geek.com/feed", @"http://feeds.feedburner.com/Mobilecrunch",@"http://zeri.info/rss/rss-5.xml",
                  nil];

至:

self.feeds = [NSMutableArray arrayWithArray:feedsArray];

我收到以下错误:

2013-08-14 15:03:55.328 ShqipCom [3128:c07](空)

我在这里做错了什么？

非常感谢!

格兰尼特

最佳答案

在ReadData中，您将返回一列，但随后使用以下命令检索第三列:

NSString *link = [[NSString alloc]initWithUTF8String:(const char *)sqlite3_column_text(completedstmt, 2)];

应该是:

NSString *link = [[NSString alloc]initWithUTF8String:(const char *)sqlite3_column_text(completedstmt, 0)];

还有其他一些想法:

我们很难回答这个问题的部分原因是您没有报告SQLite错误。我建议在SQLite测试失败时进行日志记录，例如:

if (sqlite3_prepare_v2(sourceDB, sqlstmt, -1, &completedstmt, NULL)==SQLITE_OK
{
    while(sqlite3_step(completedstmt)==SQLITE_ROW)
    {
        NSString *link = [[NSString alloc]initWithUTF8String:(const char *)sqlite3_column_text(completedstmt, 2)];

        SourceDB *source = [[SourceDB alloc]init];

        [source setLink:link];

        [feedsArray addObject:link];
    }
}

而您可能想要以下内容:

if (sqlite3_prepare_v2(sourceDB, sqlstmt, -1, &completedstmt, NULL)!=SQLITE_OK
{
    NSLog(@"%s: prepare error: %s", __FUNCTION__, sqlite3_errmsg(sourceDB));
    return;
}

int rc;
while ((rc = sqlite3_step(completedstmt)) == SQLITE_ROW)
{
    NSString *link = [[NSString alloc]initWithUTF8String:(const char *)sqlite3_column_text(completedstmt, 2)];

    SourceDB *source = [[SourceDB alloc]init];

    [source setLink:link];

    [feedsArray addObject:link];
}

if (rc != SQLITE_DONE)
{
    NSLog(@"%s: step error: %s", __FUNCTION__, sqlite3_errmsg(sourceDB));
    return;
}

您确实应该检查每个SQLite调用的结果，如果未成功，则报告错误消息。否则，你会失明。

在您的INSERT语句上，您是否100％确定这三个字段永远不会带有撇号？ (此外，如果用户正在输入任何这些数据，则必须担心SQL注入攻击。)通常，您将在SQL中使用?占位符，您将使用sqlite3_prepare_v2准备SQL，然后使用sqlite3_bind_text进行绑定将文本值输入到这些占位符，然后输入sqlite3_step并确认SQLITE_DONE的返回值。

注意，检查每个sqlite3_xxx函数调用的返回结果并进行所有必要的sqlite3_bind_xxx调用有点麻烦。在您的下一个项目中，如果要简化SQLite代码，则可能要考虑使用FMDB。