ios - 我无法将值插入到 sqlite 数据库中

Question

我使用以下代码将代码插入数据库。数据被获取并按“:::”分割，并且被分割的值被添加到数据库中。当我检查数据库时，数据没有添加到SQLITE中

#pragma mark - Web View Delegate
- (BOOL)webView:(UIWebView *)webView shouldStartLoadWithRequest:(NSURLRequest *)request navigationType:(UIWebViewNavigationType)navigationType {
    NSString *strResponse = [[request URL] absoluteString];
    if ([strResponse hasPrefix:@"ios:"]) {

        NSString *newString = [strResponse substringFromIndex:[@"ios:" length]];
        newString = [newString stringByReplacingPercentEscapesUsingEncoding:NSUTF8StringEncoding];

        UIAlertView *uiAlert = [[UIAlertView alloc] initWithTitle:@"Message" message:newString delegate:self cancelButtonTitle:@"No" otherButtonTitles:@"Yes", nil];
        [uiAlert show];

        NSArray *arrItems = [newString componentsSeparatedByString:@":::"];
        for (NSString *strItems in arrItems) {

            [strItems stringByRemovingPercentEncoding];
            NSMutableDictionary *mDic = [[NSMutableDictionary alloc] init];
            [mDic setObject:strItems forKey:@"item_name"];
            [mDic setObject:@"0" forKey:@"is_deleted"];
            [[DataBase connection] insertTableData:mDic AndTableName:@"school_feed"];
        }

        return NO;
    }
    return YES;
}

插入表数据方法

- (void)insertTableData:(NSMutableDictionary *)mDictDetails  AndTableName:(NSString*)table_name {
    NSArray *arrAllKey = [mDictDetails allKeys];

    NSString *key = @"";
    NSString *values = @"";

    for (NSInteger i = 0; i <arrAllKey.count ; i++) {

        NSString *column = arrAllKey[i];
        if ([key isEqualToString:@""]) {
            key = column;
            values =  @"?";
        } else {
            key = [key stringByAppendingString:[NSString stringWithFormat:@",%@", column]];
            values = [values stringByAppendingString:@", ?"];
        }
    }

    NSString *query = [NSString stringWithFormat: @"INSERT OR REPLACE INTO %@ (%@) values(%@)", table_name, key, values];
    const char *sql = [query UTF8String];

    sqlite3_stmt *insertStatement; // statement created

    if (sqlite3_prepare_v2(database, sql, -1, &insertStatement, NULL) != SQLITE_OK) {
        NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
    }// if

    for (int i = 0; i <arrAllKey.count; i++) {
        NSString *column = arrAllKey[i];
        NSString *strKeys = [NSString stringWithFormat:@"%@", [mDictDetails objectForKey:column]];
        sqlite3_bind_text(insertStatement, i + 1, [strKeys UTF8String], -1, SQLITE_TRANSIENT);
    }

    sqlite3_step(insertStatement);// executing query
    sqlite3_finalize(insertStatement); // finalizing statement
}

Answer 1

试试这个:

static sqlite3 *database = nil;
static sqlite3 *contactDB;

创建数据库

- (void)viewDidLoad
{

        NSString *docsDir;
        NSArray *dirPaths;
        // 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: @"student.db"]];

        NSLog(@"Database Path: %@",databasePath);


        NSFileManager *filemgr = [NSFileManager defaultManager];
        if ([filemgr fileExistsAtPath: databasePath ] == NO)
        {
            const char *dbpath = [databasePath UTF8String];
            if (sqlite3_open(dbpath, &database) == SQLITE_OK)
            {
                char *errMsg;
                const char *sql_stmt ="create table if not exists studentsDetail (regno integer primary key, name text, department text, year text)";
                if (sqlite3_exec(database, sql_stmt, NULL, NULL, &errMsg)
                    != SQLITE_OK)
                {
                    NSLog(@"Failed to create table");
                }
                sqlite3_close(database);
            }
            else {

                NSLog(@"Failed to open/create database");
            }
        }
        /*End Databse creation*/
}

保存数据:

- (IBAction)saveData:(id)sender
    {
        //regNoTextField,nameTextField,departmentTextField,yearTextField

        NSString *num= regNoTextField.text;
        int value = [num intValue];

        NSString *name= nameTextField.text;
        NSString *dep=departmentTextField.text;
        NSString *year=yearTextField.text;

      /*  NSLog(@"no   : %d",value);
        NSLog(@"name : %@",name);
        NSLog(@"depa : %@",dep);
        NSLog(@"year : %@",year);
        */

        sqlite3_stmt *statement = nil;
        const char *dbpath = [databasePath UTF8String];


        if (regNoTextField.text.length>0 &&nameTextField.text.length>0 &&
            departmentTextField.text.length>0 &&yearTextField.text.length>0 )
        {
            if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK)
            {
            NSString *insertSQL = [NSString stringWithFormat:@"insert into studentsDetail (regno,name, department, year) values(\"%d\",\"%@\", \"%@\", \"%@\")",value,name, dep, year];


                const char *insert_stmt = [insertSQL UTF8String];
                sqlite3_prepare_v2(contactDB, insert_stmt,-1, &statement, NULL);

                if (sqlite3_step(statement) == SQLITE_DONE)
                {

                    UIAlertView *alertView = [[UIAlertView alloc] initWithTitle:@"Student Detail" message:@"Save Data" delegate:self cancelButtonTitle:@"OK" otherButtonTitles:nil];

                    [alertView show];
                }
                else
                {
                    UIAlertView *alertView = [[UIAlertView alloc] initWithTitle:@"Student Detail" message:@"Unsuccessfull Save" delegate:self cancelButtonTitle:@"OK" otherButtonTitles:nil];

                    [alertView show];
                }
                sqlite3_finalize(statement);
                sqlite3_close(contactDB);
            }
        }
        else
        {
            UIAlertView *alertView = [[UIAlertView alloc] initWithTitle:@"Student Detail" message:@"Plese Entre Textfield Data" delegate:self cancelButtonTitle:@"OK" otherButtonTitles:nil];

            [alertView show];

        }
    }