SQLITE3插入一个空值 [英] SQLITE3 inserting a null value

问题描述

sqlite3_bind_int(statement, 1, nil);

如何在我的网站中添加 null 上面的陈述，因为我正在使这个字段自动增加。

How can I add a null in my above statement , as I am making this field auto increment.

这是我的代码：

if (sqlite3_open([[self dataFilePath] UTF8String], &database)!= SQLITE_OK) 
 {
 sqlite3_close(database);
 NSAssert(0, @"Failed to open database");
 }
NSLog(@"json count ========== %i",[jsonArray count]);
    for (int i =0 ; i < [jsonArray count]; i++) 
    // while (i < [jsonArray count])
{ 
 dictionary = [jsonArray objectAtIndex:i];
 char *errorMsg;

 sqlite3_stmt *statement;
 if(sqlite3_prepare_v2(database, [insertQuery UTF8String], -1, &statement, nil) == SQLITE_OK) 
 {
 // NSLog(@"%@",[dictionary valueForKey:@"text"]);
 sqlite3_bind_null(statement, 1);
 sqlite3_bind_text(statement, 2, [[dictionary valueForKey:@"date"] UTF8String], -1, NULL);
    // NSLog(@"date %@",[dictionary valueForKey:@"date"]);

 sqlite3_bind_text(statement, 3, [[dictionary valueForKey:@"text"] UTF8String], -1, NULL);
    // NSLog(@"text %@",[dictionary valueForKey:@"text"]);

 }
 if (sqlite3_step(statement) != SQLITE_DONE)
    {
        NSAssert1(0, @"Error updating table: %s", errorMsg); 
        sqlite3_finalize(statement);
    }
 sqlite3_close(database);
 }

选择：

sqlite3 *database;
if (sqlite3_open([[self dataFilePath] UTF8String], &database)
    != SQLITE_OK) { sqlite3_close(database);
    NSAssert(0, @"Failed to open database");
}
NSString *query = [self selectQueryInDB];
sqlite3_stmt *statement;
if (sqlite3_prepare_v2(database, [query UTF8String],
                       -1, &statement, nil) == SQLITE_OK) {
    while (sqlite3_step(statement) == SQLITE_ROW) 
    { 
        int row = sqlite3_column_int(statement, 0);

        if (row == indexPathRow+1) 
        {
            char *field1 = (char *)sqlite3_column_text(statement, 1);
            char *field2 = (char *)sqlite3_column_text(statement, 2);
            NSString *f1 = [[NSString alloc] initWithUTF8String:field1];

            NSString *f2 = [[NSString alloc] initWithUTF8String:field2];

            NSString *fullData = [NSString stringWithFormat:@"%@ %@",f1,f2];
            NSLog(@"%@",fullData);
        }

    }
    sqlite3_finalize(statement);
}
sqlite3_close(database);

我在NSString * f1中获得SIGABRT为 ***终止app到期未被捕获的异常'NSInvalidArgumentException'，原因：'*** - [NSPlaceholderString initWithUTF8String：]：NULL cString'
请告诉我我做错了什么。

I am getting SIGABRT in NSString *f1 as *** Terminating app due to uncaught exception 'NSInvalidArgumentException', reason: '*** -[NSPlaceholderString initWithUTF8String:]: NULL cString' Please tell me what i am doing wrong.

推荐答案

sqlite3_bind_int（statement，1，nil）将0绑定到第一个绑定点，而不是NULL 。 0和NULL在SQL中不一样。

sqlite3_bind_int(statement, 1, nil) will bind 0 to the first bind point, not NULL. 0 and NULL are not the same things in SQL.

我认为你在寻找 sqlite3_bind_null 。它将NULL绑定到绑定点，如：

I think you're looking for sqlite3_bind_null. It binds NULL to a bind point, as in:

sqlite3_bind_null(statement, 1);

此处没有NULL参数;它位于函数名称中。

There's no parameter here for NULL; it's in the name of the function.

此外，未绑定参数默认为NULL。因此，除非你使用 sqlite3_reset ，否则你根本不需要使用它。

Also, unbound parameters default to NULL. So unless you're using sqlite3_reset, you shouldn't need to use this at all.

（因为我们关于绑定和重置的话题，我应该提一下 sqlite3_clear_bindings ，它将所有参数绑定为NULL。）

(Since we're on the topic of bindings and resets, I should mention sqlite3_clear_bindings, which binds all parameters to NULL.)

问题2

您更新了此内容，以提及NSString * f1行中的SIGABRT。

You updated this to mention a SIGABRT in the NSString *f1 line.

我看到您的代码存在两个问题：

There's two problems with your code that I see:

1. sqlite3_column_text 基于0;第一列是0，而不是1.（是的，列是基于0的。即使绑定是从1开始的。）

1. sqlite3_column_text is 0 based; the first column is 0, not 1. (Yes, columns are 0 based. Even though bindings are 1-based.)

char *field1 = (char *)sqlite3_column_text(statement, 1);

应为：

char *field1 = (char *)sqlite3_column_text(statement, 0);

sqlite3_column_text 可以返回 NULL 如果 NULL 在数据中，但 initWithUTF8String：不接受 NULL 作为值。

sqlite3_column_text can return NULL if NULL is in the data, but initWithUTF8String: does not accept NULL as a value.

我建议这样做：

NSString *f1 = field1 ? [[NSString alloc] initWithUTF8String:field1] : nil;

使用这个，你最终会得到一个零 NSString 如果数据中有 NULL 列。您可以对此做出决定，当您真正需要字符串时，很容易做 f1？：@。

Using this, you'll end up with a nil NSString if you had a NULL column in the data. You can make decisions on this, and it's easy to do f1 ?: @"" when you really need a string.

另一种方法是更改​​查询：

Another approach is to change the query:

SELECT Field FROM Table;

收件人：

SELECT COALESCE(Field,'') FROM Table;

你将无法再判断Field是否原来是 NULL ，但也许你不在乎。

You'll no longer be able to tell if Field was originally NULL, but maybe you don't care.

这篇关于SQLITE3插入一个空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！