How to insert an array into a SQLite database?

Question

I'm trying to insert an array into a SQLite database and I'm having difficulties getting the array variables to insert.

How can I get variables from the array (username & fullName) to insert in the db?

Here are the error messages:

Property 'username' not found on object of type 'NSString *'
Property 'fullName' not found on object of type 'NSString *'

Here's my code...

if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {

    NSMutableArray *items = result;

    sqlite3_stmt *insert_statement;

    // prepare the insert statement
    const char*sql = "INSERT INTO people (username, fullName) VALUES(?,?)"; 
    sqlite3_prepare_v2(database, sql, -1, &insert_statement, NULL);

    // iterate over an array of dictionaries
    for (NSString *str in items) {

        // NSLog(@"%@",str);

        // bind variables
        sqlite3_bind_text(insert_statement, 1, [str.username UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(insert_statement, 2, [str.fullName UTF8String], -1, SQLITE_TRANSIENT);

        // insert fails
        if (sqlite3_step(insert_statement) != SQLITE_DONE) {
            NSLog(@"Insert failed: %s", sqlite3_errmsg(database));
        }

        // reset the statement
        sqlite3_reset(insert_statement);
    }

    // release the statement
    sqlite3_finalize(insert_statement);
}`

For what it's worth, I highly recommend using an Objective-C sqlite wrapper like FMDB or PLDatabase - it'll make your life much easier — Kristian Glass
– Kristian Glass, Commented Sep 8, 2011 at 1:00
first of all your code has errors in part of getting username and fullname from array. We can see "str" object which is never used later. So fix your question code and we can help ). And so far your code in sqlite part looks good. Did you successfully open DB before insert? — Sergnsk
– Sergnsk, Commented Sep 8, 2011 at 4:13
What's the error message you get? Publish here the declaration for items. How do you create items Array? Are you getting the error message "Insert failed: ".? If yes, then your db handle is set up properly. One less thing to worry about. — SayeedHussain
– SayeedHussain, Commented Sep 8, 2011 at 4:56
@crypticcoder I've edited the question and included the error messages above. The problem occurs when I try to declare the variables (str.username & str.fullName). — kevin
– kevin, Commented Sep 8, 2011 at 5:45
Yeah Kevin, your problem is now apparent. How do you expect your str which is an NSString* to give you str.username and str.fullname. str is an NSString like "Kevin". It's not a struct that you can use like str.fullname. That's part 1. You should now tell me where do you have your fullname and username lists. How do you construct them? — SayeedHussain
– SayeedHussain, Commented Sep 8, 2011 at 5:50

Catalin · Accepted Answer · 2012-10-09 20:15:39Z

2

This code works well ok combined with starting a transaction and committing it:

// start transaction
sqlite3_stmt *begin_transaction_stmt;
const char *beginTrans = "BEGIN EXCLUSIVE TRANSACTION";

if (sqlite3_prepare_v2(database, beginTrans, -1, &begin_transaction_stmt, NULL) != SQLITE_OK) {
    sqlite3_close(database);
    return NO;
}

sqlite3_step(begin_transaction_stmt);
sqlite3_finalize(begin_transaction_stmt);

========== [your code from the post above comes here] =============

// commit transaction
sqlite3_stmt *end_transaction_stmt;
const char *endTrans = "COMMIT";
if (sqlite3_prepare_v2(database, endTrans, -1, &end_transaction_stmt, NULL) != SQLITE_OK) {
    sqlite3_close(database);
    return NO;
}

sqlite3_step(end_transaction_stmt);
sqlite3_finalize(end_transaction_stmt);

answered Oct 9, 2012 at 20:15

Catalin

1,9094 gold badges27 silver badges34 bronze badges

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

Collectives™ on Stack Overflow

How to insert an array into a SQLite database?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related