如果 FK 为空，则 SQLite3 预准备语句将失败.不应该

Question

我在iOS应用程序中使用Sqlite3。如果我尝试从控制台运行命令：

 INSERT INTO documents (name,fk_1) VALUES ('pluto','')

一切都很好。但是当通过预准备语句执行此操作时，同样失败：

INSERT INTO documents (name,fk_1) VALUES (?,?)

说"外键约束失败"。fk_1 = ''的值。

该表文档是

CREATE TABLE "documents" ("id" integer   PRIMARY KEY AUTOINCREMENT NOT NULL ,"name" varchar,"attivita_id" integer NULL REFERENCES attivita(id) ON DELETE CASCADE)

我假设我可以将NULL值用作 FK，而且我确实可以，因为从命令行（第一个查询）它可以工作。准备好的陈述不可能这样吗？执行写入的代码是：

-(NSInteger)writeData:(NSDictionary* )data table:(NSString* )table
{
    sqlite3_stmt    *sqlStatement;
    NSMutableArray  *columns   = [[NSMutableArray alloc] init];
    NSMutableArray  *values    = [[NSMutableArray alloc] init];
    NSMutableDictionary *temp = [[NSMutableDictionary alloc] initWithDictionary:data];
    @try {
        assert([data count] != 0);
        if ([[data allKeys] count] == 0) return 1;
        // Names/Values for INSERT
        //  id is AUTOINCREMENT
        [temp removeObjectForKey:@"id"];
        [columns addObjectsFromArray:[temp allKeys]];
        NSString        *cols      = [columns componentsJoinedByString:@","];
        NSMutableString *colNames  = [[NSMutableString alloc] initWithString:
                                      [NSString stringWithFormat:@"INSERT INTO %s (",[table UTF8String]]];
        [colNames appendString:cols];
        [colNames appendString:@")"];
        // Values for INSERT
        [values addObjectsFromArray:[temp allValues] ];
        // Prepare the ? marks for bindings
        NSMutableArray * marks = [NSMutableArray new];
        for (int i=0 ; i < [values count]; i++)
             [marks addObject:@"?"];
        NSMutableString *s = [[NSMutableString alloc] init];
        NSMutableString *valNames   = [[NSMutableString alloc] initWithString:@" VALUES ("];
        [valNames appendString:[marks componentsJoinedByString:@","]];
        [valNames appendString:@")"];
        [colNames appendString:valNames];
        const char *sql = [colNames UTF8String];
#ifdef DEBUG
        // NSLog(@"avvDB writeDATA insert string %@",colNames);
#endif
        if(sqlite3_prepare_v2(db, sql, -1, &sqlStatement, NULL) != SQLITE_OK)
        {
            NSLog(@"DB:Problem with prepare statement write %s, data: %@",sqlite3_errmsg(db),data);
            NSLog(@"DB:Query %@",colNames);
            return 0;
        }
        // binding
    for(int i = 0; i < [values count]; i++)
    {
    // According to the doc, if the 3rd value is a null pointer, the effect is similar to 
   // sqlite3_bind_null
          if ([[values objectAtIndex:i] length] == 0 || [values objectAtIndex:i] == nil)
            sqlite3_bind_text(sqlStatement, i+1, nil,-1, SQLITE_TRANSIENT);
        else
        {
            [s setString:[NSString stringWithFormat:@"%@",[values objectAtIndex:i]]];
            const char* currentValue = [s UTF8String];
            sqlite3_bind_text(sqlStatement, i+1, currentValue,-1, SQLITE_TRANSIENT);
        }
    }
    if (sqlite3_step(sqlStatement) == SQLITE_DONE) {
       // NSLog(@"Location  inserted on database");
    }
    else {
        NSLog(@"DB:Error on write: %i %s %@",sqlite3_errcode(db),sqlite3_errmsg(db),data);
        NSLog(@"DB:Query %@",colNames);
    }
   // if (sqlite3_exec(db, sql, NULL, NULL, NULL) == SQLITE_OK)
   // {
   //     NSLog(@"Last id %llu %s",sqlite3_last_insert_rowid(db),sqlite3_errmsg(db));
   //  }
//    NSLog(@"Ecexecuted  write %@",colNames);
    valNames = nil;
    colNames = nil;
    marks    = nil;
    s        = nil;

}// end try
@catch(NSException* e)
{
    NSLog(@"Eccezione in write %@",[e reason]);
}
@finally {
    sqlite3_finalize(sqlStatement);
    sqlStatement = nil;
    return sqlite3_last_insert_rowid(db);
}

}

Answer 1

空字符串与 NULL 不同。

若要将参数设置为 NULL，请使用 sqlite3_bind_null。或者，不要在 INSERT 语句中提及空参数。