SQLITE3 inserting a null value

Question

sqlite3_bind_int(statement, 1, nil); 

How can I add null to my statement above as I make this field automatically incremented.

Here is my code:

 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); } 

To choose:

 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); 

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

Answer 1

sqlite3_bind_int(statement, 1, nil) will associate 0 with the first anchor point, and not with NULL. 0 and NULL are not the same in SQL.

I think you are looking for sqlite3_bind_null . It binds NULL to the anchor point, as in:

 sqlite3_bind_null(statement, 1); 

There is no parameter for NULL; This is the name of the function.

In addition, for unrelated parameters, the default value is NULL. Therefore, if you are not using sqlite3_reset , you do not need to use this at all.

(Since we are talking about bindings and discards, I should mention sqlite3_clear_bindings , which binds all parameters to NULL.)

Problem 2

You updated this by specifying SIGABRT in the line NSString * f1.

There are two problems with the code that I see:

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

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

  it should be:

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

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

I suggest doing this:

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

Using this, you will get nil NSString if the column data is NULL . You can decide on this, and you can easily do f1 ?: @"" When you really need a line.

Another approach is to modify the request:

 SELECT Field FROM Table; 

To:

 SELECT COALESCE(Field,'') FROM Table; 

You can no longer determine if the field was originally NULL , but maybe you don't care.