How do I insert string to sqlite in Objective-C?

Question

I could not insert NSMutableString to sqlite. I get the information successfully from web server and create the sqlite file successfully but I could not insert the data from web to sqlite.

I think the problem is here.

NSString *insertSQL = @"INSERT INTO Questions VALUES (result)";

but I am not sure and I could not solve this problem. Could anyone help me please?

- (void)getInforamtionFromWeb {
    NSURL *url = [NSURL URLWithString:kGetUrl];
    data = [NSData dataWithContentsOfURL:url];
    NSError *error;
    json = [NSJSONSerialization JSONObjectWithData:data options:kNilOptions error:&error];
    result = [[NSMutableString alloc] init];
    for (NSObject * obj in json)
    {
        [result appendString:[obj description]];
    }
}

-(void)initiatSqlite{
    // Get the documents directory
    dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    docsDir = dirPaths[0];
    // Build the path to the database file
    _databasePath = [[NSString alloc]
                     initWithString: [docsDir stringByAppendingPathComponent:
                                      @"Questions.db"]];
    NSFileManager *filemgr = [NSFileManager defaultManager];
    if ([filemgr fileExistsAtPath: _databasePath ] == NO)
    {
        const char *dbpath = [_databasePath UTF8String];
        if (sqlite3_open(dbpath, &_contactDB) == SQLITE_OK)
        {
            char *errMsg;
            const char *sql_stmt =
            "CREATE TABLE IF NOT EXISTS Questions (ID INTEGER PRIMARY KEY AUTOINCREMENT, Question Text, AnswerA Text, AnswerB Text, AnswerC Text, AnswerD Text, CorrectAnswer Text, Explanation Text)";
            if (sqlite3_exec(_contactDB, sql_stmt, NULL, NULL, &errMsg) != SQLITE_OK)
            {
                [self Worningtitle:@"Error" Message:@"Failed to create table"];
            }
            sqlite3_close(_contactDB);
        } else {
            [self Worningtitle:@"Error" Message:@"Failed to open/create database"];

        }
    }

}
- (void) insertData
{
    sqlite3_stmt    *statement;
    const char *dbpath = [_databasePath UTF8String];


    if (sqlite3_open(dbpath, &_contactDB) == SQLITE_OK)
    {
        NSString *insertSQL = @"INSERT INTO Questions VALUES (result)";

        const char *insert_stmt = [insertSQL UTF8String];
        NSLog(@"%s",insert_stmt);
        sqlite3_prepare_v2(_contactDB, insert_stmt, -1, &statement, NULL);
        sqlite3_bind_text(statement, 1, [result UTF8String], -1, SQLITE_TRANSIENT);

        if (sqlite3_step(statement) == SQLITE_DONE)
        {
            NSLog(@"Product added");
        } else {
            NSLog(@"Failed to add Product");
        }

        sqlite3_finalize(statement);
        sqlite3_close(_contactDB);
    }
}

rmaddy · Accepted Answer · 2016-04-30 15:04:19Z

2

You are correct. Your INSERT statement is incorrect. It needs to be:

NSString *insertSQL = @"INSERT INTO Questions VALUES (?)";

The ? is where the sqlite3_bind_xxx will be applied.

Keep in mind that your Questions table has lots of columns but you are only inserting a single value. You should specify which column the value should be inserted into. Example:

NSString *insertSQL = @"INSERT INTO Questions (Question) VALUES (?)";

Replace Question with the proper column name that your result value should be put in.

BTW - your code needs a lot more work to make it better. You need to add a lot more error checking, especially with sqlite3_prepare_v2.

Based on wanting to insert more data, you would do something like this:

- (BOOL)insertData {
    BOOL result = NO; // failed
    const char *dbpath = [_databasePath UTF8String];
    if (sqlite3_open(dbpath, &_contactDB) == SQLITE_OK) {
        const char *insert_stmt = "INSERT INTO Questions(AnswerA,AnswerB,AnswerC,AnswerD,CorrectAnswer,Question) VALUES (?,?,?,?,?,?)";
        sqlite3_stmt *statement;

        if (sqlite3_prepare_v2(_contactDB, insert_stmt, -1, &statement, NULL) == SQLITE_OK) {
            sqlite3_bind_text(statement, 1, [answerA UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, 2, [answerB UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, 3, [answerC UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, 4, [answerD UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, 5, [correctAnswer UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, 6, [question UTF8String], -1, SQLITE_TRANSIENT);

            if (sqlite3_step(statement) == SQLITE_DONE) {
                NSLog(@"Product added");
                result = YES;
            } else {
                NSLog(@"Failed to add Product: %s", sqlite3_errmsg(_contactDB));
            }

            sqlite3_finalize(statement);
        } else {
            NSLog(@"Unable to prepare statement: %s", sqlite3_errmsg(_contactDB);
        }

        sqlite3_close(_contactDB);
    } else {
        NSLog(@"Unable to open database: %@", sqlite3_errmsg(_contactDB));
    }

    return result;
}

Please note that you need to update each of the sqlite3_bind_text calls with references to the actual variables/values that contain the data for each column.

You also don't need to bind a value for ID since it is setup to automatically populate.

Note all of the proper error checking. I also made it so insertData returns a BOOL indicating whether the data was successfully added or not.

edited Apr 30, 2016 at 15:04

answered Apr 30, 2016 at 4:19

rmaddy

319k44 gold badges548 silver badges591 bronze badges

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

4 Comments

user4410354 Over a year ago

Thank you very much, the string is much more than below, then how do I insert this to sqlite? { AnswerA = Yet; AnswerB = Gh; AnswerC = Gf; AnswerD = Gh; CorrectAnswer = D; Explanation = "Explanation:yet"; ID = 35; Question = Ty; } 2016-04-30 00:22:27.929 Pharm Tech[9960:824218] { AnswerA = Yet; AnswerB = Gh; AnswerC = Gf; AnswerD = Gh; CorrectAnswer = D; Explanation = "Explanation:yet"; ID = 35; Question = Ty; }{ AnswerA = Gf; AnswerB = Gh; Answer

rmaddy Over a year ago

For each column you need to add the column name in the first set of parentheses in the INSERT statement, add a ? in the second set of parentheses, and add a corresponding call to sqlite3_bind_xxx appropriate for the data type.

user4410354 Over a year ago

I did this, what is next, I tried a lot. can you please help me? NSString *insertSQL = @"INSERT INTO Questions(AnswerA,AnswerB,AnswerC,AnswerD,CorrectAnswer,ID,Question) VALUES (?)"; const char *insert_stmt = [insertSQL UTF8String]; NSLog(@"%s",insert_stmt); sqlite3_prepare_v2(_contactDB, insert_stmt, -1, &statement, NULL); sqlite3_bind_text(statement, 1, [result UTF8String], -1, SQLITE_TRANSIENT);

user4410354 Over a year ago

finally I did it. Thank you very much. you are my hero. I already working on this more than one week. finally did it.. you are the best.

Keyur Hirani · Accepted Answer · 2016-04-30 04:25:25Z

0

Hear make common method for all table :

+(BOOL)insertWithReturnConfirmInTable:(NSString *)tableName Param:(NSDictionary *)params
{
        NSArray *keys = [[NSArray alloc] initWithArray:[self GetColumnsForTable:tableName]];

        //insert statement
        NSString *query = [NSString stringWithFormat:@"INSERT INTO %@(",tableName];

        //set keys
        for (int i=0; i<[keys count]; i++) {
            if([keys objectAtIndex:i] == [keys lastObject])
            {
                query = [query stringByAppendingString:[NSString stringWithFormat:@"%@) VALUES (",[keys objectAtIndex:i]]];
                for (int m=0; m<[keys count]; m++) {

                    if([keys objectAtIndex:m] == [keys lastObject])
                        query = [query stringByAppendingString:@"?)"];
                    else
                        query = [query stringByAppendingString:@"?,"];
                }
            }
            else
            {
                query = [query stringByAppendingString:[NSString stringWithFormat:@"%@,",[keys objectAtIndex:i]]];
            }
        }
        NSLog(@"Query : %@",query);

        if (sqlite3_open([[self getDBPath] UTF8String], &dbObj) == SQLITE_OK) {

            sqlite3_stmt *addStmt = nil;

            if (sqlite3_prepare_v2(dbObj, [query UTF8String], -1, &addStmt, NULL) != SQLITE_OK)
                NSAssert1(0, @"Error while creating insert statement. '%s'",sqlite3_errmsg(dbObj));

            //for Values
            for (int i=0; i<[keys count]; i++) {
                sqlite3_bind_text(addStmt, i+1, [[NSString stringWithFormat:@"%@",[params objectForKey:[keys objectAtIndex:i]]] UTF8String], -1, SQLITE_TRANSIENT);
            }

            if (SQLITE_DONE != sqlite3_step(addStmt)) {
                NSLog(@"Error while inserting data. '%s'", sqlite3_errmsg(dbObj));
                sqlite3_close(dbObj);
                return NO;
            }
            else {
                sqlite3_finalize(addStmt);
                NSLog(@"data inserted in table : %@",tableName);
                sqlite3_close(dbObj);
                return YES;
            }


        } else {
            NSLog(@"Error in opening database.");
            sqlite3_close(dbObj);
            return NO;
        }

    }
}

Call method : Pass all field name as key and all value as value in dictionary. and pass table name.

NSDictionary *param = [[NSDictionary alloc]initWithObjectsAndKeys:@"1",@"S_Id",@"abc",@"Name", nil];
[database insertInTable:@"Child" Param:param];

answered Apr 30, 2016 at 4:25

Keyur Hirani

1,60714 silver badges24 bronze badges

5 Comments

rmaddy Over a year ago

1) Use NSMutableString for query. Much better than using stringByAppendingString. 2) Why 3 calls to sqlite3_close? You only need 1 if you put it in the right place and have 1 return statement. 3) You are not finalizing the prepared statement if the call sqlite3_step fails. 4) You should log sqlite3_errmsg is anything fails.

rmaddy Over a year ago

5) Using stringWithFormat in the sqlite3_bind_text call is a bad idea. It's not needed for strings and it should not be used for other types. 6) This code makes assumes that all columns are text. So this is not a good general approach.

Keyur Hirani Over a year ago

Thank rmaddy for suggestion. But when we are sqlite3_open at that time sqlite3_close. Otherwise when use multi operation at that time may be issue.

Keyur Hirani Over a year ago

I have no longer experience in ios . but i post my ans may be usefull to any one.

rmaddy Over a year ago

It may be useful but it needs a lot of clean up and it only works with tables that only have text columns.

Collectives™ on Stack Overflow

How do I insert string to sqlite in Objective-C?

2 Answers 2

4 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related