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:

this

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

Ah, I see the issue: In ReadData, sqlite3_column_text(completedstmt, 2) should be sqlite3_column_text(completedstmt, 0). — Rob
– Rob, Commented Aug 14, 2013 at 14:19
i used 2 instead of 0 because the links are stored in the third column. I'll try to step through the ReadData as you suggested and see what the log shows :) — Granit
– Granit, Commented Aug 14, 2013 at 14:46
Yeah, it uses the column number in select statement, not in the table. — Rob
– Rob, Commented Aug 14, 2013 at 14:47

Rob · Accepted Answer · 2013-08-14 14:40:13Z

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:

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.

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.

edited Aug 14, 2013 at 14:40

answered Aug 14, 2013 at 13:48

Rob

441k74 gold badges851 silver badges1.1k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Granit Over a year ago

The data exists in the database, so i think that the problem is with retrieving the data.

Rob Over a year ago

@Xero see revised answer, though I'm surprised it didn't generate an exception. In your question, I don't even see you calling ReadData but I assume you really did.

Granit Over a year ago

Thank you very much! I incorporated your answer in my project and now the data is added to the array. '2013-08-14 16:56:33.078 ShqipCom[3452:c07] ( "pcworld.al/feed", "geek.com/feed" )'

user1525369 · Accepted Answer · 2013-08-14 13:18:49Z

0

Your can add any object in NSMutableArray by

[myArray name addObject:mYString]; // add whatever object here;

if you want to add Array to you NSMutableArray

[myArray addObjectsFromArray:anOtherArray];

answered Aug 14, 2013 at 13:18

user1525369

1 Comment

Granit Over a year ago

I still get the message: 2013-08-14 15:03:55.328 ShqipCom[3128:c07] (null) even though the Links exist in the database

Collectives™ on Stack Overflow

Store String from SQLite to NSMutableArray

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related