Store String from SQLite to NSMutableArray

Question

I'm working on an app that displays various feeds using ASIHTTPRequest. The user can add new sources to the app that are stored in an SQLite database. Currently I give the feeds to the app in the viewDidLoad method of my FeedsViewController but i want to be able to retrieve the data that contains the link for the source and store it in an array to use it.

Currently the app looks like and below the current code:

- (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];


}

My view that contains the function (AddSourcesViewController) to add and delete new sources looks like this and here the code:

- (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];
}

Now i created a method in my 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];




            }
        }
    }
}

And when i change

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];

to:

self.feeds = [NSMutableArray arrayWithArray:feedsArray];

i get the following error:

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

What am i doing wrong here?

Thanks a lot!

Granit

Answer 1

In ReadData, you're returning one column, but then retrieving the third column with:

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

That should be:

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

A couple of other thoughts:

1. Part of the reason it's hard for us to answer this is that you aren't reporting SQLite errors. I'd suggest logging when a SQLite test failed, e.g.:

   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];
       }
   }
   

   whereas you might want something like:

   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;
   }
   

   You really should be checking the result of every SQLite call, and reporting an error message if not successful. Otherwise you're flying blind.

2. On your INSERT statement, are you 100% sure that those three fields will never have an apostrophe in them? (Also, if the user is entering any of this data, you have to be concerned about SQL injection attacks.) Generally you'd use ? placeholders in your SQL, you'd prepare the SQL with sqlite3_prepare_v2, and then use sqlite3_bind_text to bind text values to those placeholders, and then you'd sqlite3_step and confirm a return value of SQLITE_DONE.

Note, checking each and every sqlite3_xxx function call return result and doing all of the necessary sqlite3_bind_xxx calls is a little cumbersome. In your next project, if you want to streamline your SQLite code, you might want to consider using FMDB.