Application crashes with Assertion Failure message

问题:

In my application i keep some data in local Sqlite db. Sqlite open, insert all working fine. but when i try to update data of a particular row/record if fails with ASSERTION FAILURE message..

* Assertion failure in -[gss_databaseHandler updateRecord::::], /Users/gss/Desktop/SalesPro copy/SalesPro/../gss_databaseHandler.m:210

NSString *databasePath;
// Method to open a database, the database will be created if it doesn't exist
-(void)initDatabase
{
// Create a string containing the full path to the sqlite.db inside the documents folder
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
databasePath = [documentsDirectory stringByAppendingPathComponent:@"contact1"];

// Check to see if the database file already exists
bool databaseAlreadyExists = [[NSFileManager defaultManager] fileExistsAtPath:databasePath];

// Open the database and store the handle as a data member
if (sqlite3_open([databasePath UTF8String], &databaseHandle) == SQLITE_OK)
{
    // Create the database if it doesn't yet exists in the file system
    if (!databaseAlreadyExists)
    {
        // Create the contactList table
        const char *sqlStatement = "CREATE TABLE IF NOT EXISTS contactList (sapCustId TEXT, sapContactId TEXT, record_ID NUMERIC PRIMARY KEY, timestamp TEXT)";
        char *error;
        if (sqlite3_exec(databaseHandle, sqlStatement, NULL, NULL, &error) == SQLITE_OK)
        {
                NSLog(@"Database and tables created.");
        }
        else
        {
                NSLog(@"Error: in creating/opening database");
        }

    }
  }
}


- (void) updateRecord:(int)recordID:(NSString *)sapCustId:(NSString *)sapContactId:(NSString *)timestamp {

[self initDatabase];

sqlite3_stmt *updateStmt = nil;

    if(updateStmt == nil) {
        const char *sql_stmt = "update contactList Set sapCustId = ?, sapContactId = ?, timestamp = ? Where record_ID = ?";

     if(sqlite3_prepare_v2(databaseHandle, sql_stmt, -1, &updateStmt, NULL) != SQLITE_OK)
         NSAssert1(0, @"Error while creating update statement. '%s'", sqlite3_errmsg(databaseHandle));
    }

    sqlite3_bind_text(updateStmt, 0, [sapCustId UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(updateStmt, 1, [sapContactId UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(updateStmt, 2, recordID);
    sqlite3_bind_text(updateStmt, 3, [timestamp UTF8String], -1, SQLITE_TRANSIENT);

    if(SQLITE_DONE != sqlite3_step(updateStmt))
        NSAssert1(0, @"Error while updating. '%s'", sqlite3_errmsg(databaseHandle));

sqlite3_finalize(updateStmt);
sqlite3_close(databaseHandle);

//Reclaim all memory here.
[sapContactId release];
[sapCustId release];

}

Pls help!..

回答1:

Try this

You are missing sqlite3_reset, and numbering should start from 1

- (void) updateRecord:(int)recordID:(NSString *)sapCustId:(NSString *)sapContactId:(NSString *)timestamp {

[self initDatabase];

sqlite3_stmt *updateStmt = nil;

    if(updateStmt == nil) {
        const char *sql_stmt = "update contactList Set sapCustId = ?, sapContactId = ?, timestamp = ? Where record_ID = ?";

     if(sqlite3_prepare_v2(databaseHandle, sql_stmt, -1, &updateStmt, NULL) != SQLITE_OK)
         NSAssert1(0, @"Error while creating update statement. '%s'", sqlite3_errmsg(databaseHandle));
    }

    sqlite3_bind_text(updateStmt, 1, [sapCustId UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(updateStmt, 2, [sapContactId UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(updateStmt, 3, [timestamp UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(updateStmt, 4, recordID);

    if(SQLITE_DONE != sqlite3_step(updateStmt))
        NSAssert1(0, @"Error while updating. '%s'", sqlite3_errmsg(databaseHandle));

sqlite3_reset(updateStmt);
sqlite3_finalize(updateStmt);
sqlite3_close(databaseHandle);

//Reclaim all memory here.
[sapContactId release];
[sapCustId release];

}

Hope this helps you..

EDIT :-

You have not added sqlite3_finalize(); in any of your previous function and so you are getting database lock error..

回答2:

So, a couple of thoughts:

1. As Prateek and Lefteris suggested, you should change the order of the bind calls so that they match the order of the fields in the SQL. They also start with an index of one, not zero. I've always found it odd that the index for sqlite3_bind calls start at one, whereas the index for sqlite3_column calls start at zero, but it's just the way it works.

2. While it's probably fine, if you ever do an assertion, you should generally make sure you first (a) finalize any successfully prepared statements; and (b) close the database. You don't want to risk leaving your database in an inconsistent state.

3. You probably should not release sapContactId or sapCustId here. You didn't retain or do anything to increase their retain count, so it's probably not prudent to be releasing them here. If you run your code through the static analyzer (press shift+command+B or choose "Analyze" from the product menu), you'll undoubtedly see suggestions/warnings there.

4. As outlined in the official SQLite An Introduction To the SQLite C/C++ Interface, your sequence of open, prepare_v2, step, finalize, and close is perfectly correct. You do not need to call sqlite3_reset. The purpose of reset is to allow you to reuse a previous prepared sqlite3_stmt, but you're not doing that here, so reset is unnecessary here and achieves nothing.

5. At one point in your comments, you said that you received an error "database locked". If you're still getting that having addressed the above items, let us know where you're getting it (at open? during the prepare of a SQL statement?), as there can be different sources of this problem. Certainly multi-threaded database operations can cause this. You need help us diagnose this by showing us precisely which line you're getting this locked message on, and describe what other database operations (beyond the code in your original question) that you might be doing, especially if you're doing anything in a background queue/thread.

   The challenge in solving "database locked" problems is that the problem invariably is not in the code in which you're experiencing the "database locked" error, but in some previous database interaction that you failed to handle properly. Technically, a failure to call sqlite3_finalize could cause the "database locked" problem, but in practice that's rarely the case. I see "database locked" errors usually occurring because of other issues, but we really need to know whether you're experiencing it during the opening of the database, or while trying to prepare or step a SQL statement. You need to tell us which for us to go further in the diagnosis. But, like I said, the problem undoubtedly rests in other/prior database interactions, so you might need to share more about what other database operations you're doing prior to the "database locked" error.

---

If you're interested in a discussion regarding the rewrite of the code in your original question, see below.

A minor suggestion, but I'd suggest altering your initDatabase method to make sure you log an error message if the sqlite3_open call fails, such as:

-(void)initDatabase
{
    // Create a string containing the full path to the sqlite.db inside the documents folder
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *databasePath = [documentsDirectory stringByAppendingPathComponent:@"contact1.sqlite"];

    // Check to see if the database file already exists
    bool databaseAlreadyExists = [[NSFileManager defaultManager] fileExistsAtPath:databasePath];

    // Open the database and store the handle as a data member
    if (sqlite3_open([databasePath UTF8String], &databaseHandle) == SQLITE_OK)
    {
        // Create the database if it doesn't yet exists in the file system
        if (!databaseAlreadyExists)
        {
            // Create the contactList table
            const char *sqlStatement = "CREATE TABLE IF NOT EXISTS contactList (sapCustId TEXT, sapContactId TEXT, record_ID NUMERIC PRIMARY KEY, timestamp TEXT)";
            char *error;
            if (sqlite3_exec(databaseHandle, sqlStatement, NULL, NULL, &error) == SQLITE_OK)
            {
                NSLog(@"Database and tables created.");
            }
            else
            {
                NSLog(@"%s: error creating table: %s", __FUNCTION__, sqlite3_errmsg(databaseHandle));
            }
        }
    }
    else
    {
        NSLog(@"%s: error opening database: %s", __FUNCTION__, sqlite3_errmsg(databaseHandle));
    }
}

Second, you look at insertRecord:

• You want to fix the order that you use the four bind statements.

• You can also eliminate the redundant if statement.

• If you use assertions or return from the middle of a method doing database interactions, you always want to do a graceful cleanup. For example, you'll see if my prepare failed, I'll just close, but if the step failed, then I make sure to finalize and close.

• You probably should get rid of those inappropriate release statements, moving them to the right place in the code.

• You should change the method signature to have named parameters (because in the General Rules section of the Coding Guidelines, Apple says that we should name our method keywords.

Thus:

- (void) insertRecord:(int)recordID
            sapCustId:(NSString *)sapCustId
         sapContactId:(NSString *)sapContactId
            timestamp:(NSString *)timestamp
{
    [self initDatabase];

    sqlite3_stmt *statement = nil;

    const char *sql_stmt = "INSERT INTO contactList (sapCustId, sapContactId, timestamp, record_ID) VALUES (?, ?, ?, ?)";

    if(sqlite3_prepare_v2(databaseHandle, sql_stmt, -1, &statement, NULL) != SQLITE_OK)
    {
        NSLog(@"%s: insert prepare error: '%s'", __FUNCTION__, sqlite3_errmsg(databaseHandle));
        sqlite3_close(databaseHandle);
        NSAssert(0, @"insert prepare error");
    }

    sqlite3_bind_text(statement, 1, [sapCustId UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(statement, 2, [sapContactId UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(statement, 3, [timestamp UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(statement, 4, recordID);

    if (sqlite3_step(statement) != SQLITE_DONE)
    {
        NSLog(@"%s: insert step error: '%s'", __FUNCTION__, sqlite3_errmsg(databaseHandle));
        sqlite3_finalize(statement);
        sqlite3_close(databaseHandle);
        NSAssert(0, @"insert step error");
    }

    sqlite3_finalize(statement);
    sqlite3_close(databaseHandle);
}