iOS- SQLITE_MISUSE 错误(代码 21)

Question

我尝试将一组对象插入到我的数据库中，除了最后一个之外，所有对象都成功插入，它给了我这个错误

这是我用来插入数据库的代码

  - (int)insertWithQuery:(NSString *)query {
        sqlite3_stmt *statement;
        const char *dbpath = [databasePath UTF8String];

        if (sqlite3_open(dbpath, &database) == SQLITE_OK) {
            const char *insert_stmt = [query UTF8String];
            if(sqlite3_prepare_v2(database, insert_stmt, -1, &statement, NULL)!= SQLITE_OK){

                 NSLog(@"the error occurred here is %s ",sqlite3_errmsg(database));
                return DATABASE_FAILED;

                 }
            int res = sqlite3_step(statement);


            if (res == SQLITE_DONE) {
                return DATABASE_SUCCESS;
            } else if (res == SQLITE_CONSTRAINT) {
                return DATABASE_ALREADY_EXISTS;
            } else {
                return DATABASE_FAILED;
            }
            sqlite3_finalize(statement);
            sqlite3_close(database);
        } else {
            return DATABASE_FAILED;
        }
    }

打印给我的错误是

near "3": syntax error

我传递的查询看起来像

INSERT INTO report (report_id, student_id, report_title, report_body, from_date, to_date, timestamp) VALUES ("217", "1", "", "<h3>Hazem Taha Ghareeb <small>report</small></h3>From: <p>2014-06-17 </p> To: <p>2014-06-24 </p><table><thead><th>Exam</th><th>Date</th><th>Result</th></thead><tbody><tr><td>Java </td><td>2014-06-18 </td><td>138 </td></tr></tbody></table><h3>Absence</h3><table><thead><th>Date</th></thead><tbody><tr><td>2014-06-17 </td></tr><tr><td>2014-06-22 </td></tr><tr><td>2014-06-24 </td></tr></tbody></table></body></html>", "2014-06-17", "2014-06-24", "2014-06-24 12:23:58") 

有问题的是

INSERT INTO report (report_id, student_id, report_title, report_body, from_date, to_date, timestamp) VALUES ("631", "1", "C class report4", "<h3>Hazem Taha <small>report</small></h3>From: <p>30-01-2015 </p> To: <p>30-01-2015 </p><table><thead><th>Exam</th><th>Date</th><th>Result</th></thead><tbody><tr><td colspan="3"> No exams records. </td></tr></tbody></table><h3>Absence</h3><table><thead><th>Date</th></thead><tbody><tr><td> No absence records. </td></tr></tbody></table></body></html>", "2015-01-30", "2015-01-30", "2015-01-29 08:43:21”)

有知道的可以帮帮我

Answer 1

主要问题是您的查询参数中有 "。您需要使用 \" 对它们进行转义。

但我想建议使用 sqlite3_bind_xxx 方法将参数绑定(bind)到您的查询，而不是在查询语句中指定它们。您可以在这里阅读更多相关信息:SQLite3 Reference

如果你打开了一个数据库连接，你应该关闭它。在您的代码中，您从许多地方返回，因此数据库连接将无法正确关闭，这将导致数据库锁定和其他问题。

- (int)insertWithQuery:(NSString *)query
{
    int status = DATABASE_FAILED;
    sqlite3_stmt *statement;
    const char *dbpath = [databasePath UTF8String];

    if (sqlite3_open(dbpath, &database) == SQLITE_OK)
    {
         const char *insert_stmt = [query UTF8String];
         if(sqlite3_prepare_v2(database, insert_stmt, -1, &statement, NULL)!= SQLITE_OK)
         {
             NSLog(@"the error occurred here is %s ",sqlite3_errmsg(database));
             status = DATABASE_FAILED;
         }
         else
         {
            int res = sqlite3_step(statement);
            if (res == SQLITE_DONE)
            {
                status = DATABASE_SUCCESS;
            }
            else if (res == SQLITE_CONSTRAINT)
            {
                status = DATABASE_ALREADY_EXISTS;
            }
            else
            {
                status = DATABASE_FAILED;
            }
        }
        sqlite3_finalize(statement);
        sqlite3_close(database);
    }
 }