“数据库锁定"更新查询时iOS中出现错误 [英] "database locked" error in ios while updating query

问题描述

我正在使用以下代码通过 sqlite 更新查询.
但是正在收到数据库锁定错误" .
我尝试搜索一些SO链接，建议关闭数据库，但是我再次遇到相同的错误.我已经提到我在代码中出现错误的地方.

I am using the below code to update the query using sqlite.
But am getting "database is locked error".
I tried searching some SO link and it was suggested to close database, but I did that again am getting the same error. I have mentioned where I am getting error in the code.

const char *dbpath = [databasePath UTF8String];
if (sqlite3_open(dbpath, &database) == SQLITE_OK)
{
    NSString *locationNo =NULL;
    NSString *querySQL = [NSString stringWithFormat:@"select count(*) from code"];
    const char *query_stmt = [querySQL UTF8String];

    if (sqlite3_prepare_v2(database,query_stmt, -1, &statement, NULL) == SQLITE_OK)
    {
        if (sqlite3_step(statement) == SQLITE_ROW)
        {
            locationNo = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 0)];
            int count= [locationNo intValue];
            sqlite3_close(database);
            NSLog(@"%@",locationNo);
            if(0==count)
            {
                NSString *insertSQL = [NSString stringWithFormat:@"insert into favourite_code (code_id,code_1,code_2,code_3,code_4,code_5,code_6, status, record_status) VALUES (\"%d\",\"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\")",1 ,code1,code2,code3,code4,code5,code6,@"Y", @"Y"];

                const char *insert_stmt = [insertSQL UTF8String];
                sqlite3_prepare_v2(database, insert_stmt,-1, &statement, NULL);
                if (sqlite3_step(statement) == SQLITE_DONE)
                {
                    return YES;
                }
                else {
                    return NO;
                }
                sqlite3_reset(statement);
                sqlite3_close(database);
            }
            else{


                =========================== Getting Error in the below lines =========================

                const char *sq1l = "update code SET code_1=?, code_2=?, code_3=?, code_4=?, code_5=?,code_6=? WHERE code_id=1";

                if (sqlite3_prepare_v2(database, sq1l, -1, &statement, NULL) != SQLITE_OK)
                {
                    NSLog(@"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
                }
                else
                {
                    sqlite3_bind_text(statement, 1, [code1 UTF8String], -1, SQLITE_TRANSIENT);
                    sqlite3_bind_text(statement, 2, [code1 UTF8String], -1, SQLITE_TRANSIENT);
                    sqlite3_bind_text(statement, 3, [code1 UTF8String], -1, SQLITE_TRANSIENT);
                    sqlite3_bind_text(statement, 4, [code1 UTF8String], -1, SQLITE_TRANSIENT);
                    sqlite3_bind_text(statement, 5, [code1 UTF8String], -1, SQLITE_TRANSIENT);
                    sqlite3_bind_text(statement, 6, [code1 UTF8String], -1, SQLITE_TRANSIENT);

                }

                int success = sqlite3_step(statement);
                if (success != SQLITE_DONE)
                {
                    NSLog(@"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
                    //result = FALSE;
                }
                else
                {
                    NSLog(@"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
                    //result = TRUE;
                }

                =================================END=========================================   


            }

            sqlite3_reset(statement);
        }
        else
        {
            NSLog(@"Not found");
            locationNo=@"1";
        }
        sqlite3_reset(statement);
    }
}

推荐答案

通常，如果您同时进行多个查询(您没有完成一些早期的SQL语句，或者您有多个线程)，就会得到此提示打开，或者您已经多次打开数据库.

Generally you will get this if you have multiple queries going on at the same time (either you didn't finalize some early SQL statement, or you have multiple threads open, or you've opened the database multiple times).

此代码对 sqlite3_close 和 sqlite3_reset (并且缺少 sqlite3_finalize )的使用略有混淆，这可能是问题.

This code has a slightly confusing use of sqlite3_close and sqlite3_reset (and a lack of sqlite3_finalize), which might be the source of the problem.

在 SQLite C/C ++接口简介中，他们指出了语句的正确顺序:

In An Introduction To The SQLite C/C++ Interface, they point out the correct order of the statements:

• sqlite3_open()，以打开数据库
• sqlite3_prepare()，以准备sql语句
• sqlite3_bind()，用于将值绑定到?占位符
• sqlite3_step()，以执行sql和/或逐步浏览结果
• sqlite3_column()，以根据需要检索数据列
• sqlite3_finalize()，以完成/关闭准备好的sql语句
• sqlite3_close()，以关闭数据库

• sqlite3_open(), to open a database
• sqlite3_prepare(), to prepare a sql statement
• sqlite3_bind(), to bind values to ? placeholders as necessary
• sqlite3_step(), to execute the sql and/or step through the results
• sqlite3_column(), to retrieve the columns of data, as necessary
• sqlite3_finalize(), to complete/close the prepared sql statement
• sqlite3_close(), to close the database

底线，您的 sqlite3_open 调用与单个 sqlite3_prepare_v2 必须具有自己的 sqlite3_reset ，如果您想重置已准备好的语句，以便可以将其与新值绑定并再次遍历；但是在完成所有准备好的语句后，仍然需要 sqlite3_finalize 声明).

Bottom line, your sqlite3_open call is not matched with a single sqlite3_close statement at the end (but you have an extraneous sqlite3_close in the middle of your code). Also, each sqlite3_prepare_v2 must have its own sqlite3_finalize (you only use sqlite3_reset if you want to reset a prepared statement so you can bind it with new values and step through it again; but you still need sqlite3_finalize when you're all done with the prepared statement).

这篇关于“数据库锁定"更新查询时iOS中出现错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！