xcode SQLite“SQLITE MISUSE”错误

SQLite "SQLITE MISUSE" Error

提问by m.etka

I have a problem with SQLite. When I try to insert entries I get an error. I found that error is "SQLITE MISUSE" with error code 21 by using

我对 SQLite 有问题。当我尝试插入条目时，出现错误。我发现错误是“SQLITE MISUSE”，错误代码为 21

NSLog(@"ERROR:  Failed to add food!  (code: %d)",sqlite3_step(statement));

insertSQL string in my code is created properly when needed. Also, I see the created table with iFunBox.

在需要时正确创建我代码中的 insertSQL 字符串。另外，我看到了用 iFunBox 创建的表格。

Here is my insert method:

这是我的插入方法：

-(void)saveDataWithCategoryNumber:(int)categoryNumber foodNumber:(int)foodNumber foodName:(NSString *)foodName definiton:(NSString *)definiton ingredients:(NSString *)ingredients calorie:(int)calorie price:(int)price image1:(NSString *)image1 image2:(NSString *)image2 image3:(NSString *)image3 image4:(NSString *)image4 {
sqlite3_stmt    *statement;
const char *dbpath = [databasePath UTF8String];

if (sqlite3_open(dbpath, &foodDB) == SQLITE_OK)
{
    NSString *insertSQL = [NSString stringWithFormat: @"INSERT INTO foodDB (categoryNumber, foodNumber, foodName, definiton, ingredients, calorie, price, image1, image2, image3, image4) VALUES (%i, %i, \"%@\",  \"%@\",  \"%@\",  %i,  %i,  \"%@\",  \"%@\",  \"%@\",  \"%@\")", categoryNumber, foodNumber, foodName, definiton, ingredients, calorie, price, image1, image2, image3, image4];

    const char *insert_stmt = [insertSQL UTF8String];
    sqlite3_prepare_v2(foodDB, insert_stmt, -1, &statement, NULL);
    //char *error;
    //sqlite3_exec(foodDB, insert_stmt, NULL, NULL, &error);

    NSLog(@"insertSQL: %@",insertSQL);

    if (sqlite3_step(statement) == SQLITE_DONE)
    {
        NSLog(@"Food added.");
    } else {
        NSLog(@"ERROR:  Failed to add food!  (code: %d)",sqlite3_step(statement));

    }

    sqlite3_finalize(statement);
    sqlite3_close(foodDB);
}}

---

---

May be creation method will be useful:

可能创建方法会很有用：

-(void)createDatabase{
NSString *docsDir;
NSArray *dirPaths;
// Get the documents directory
dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

docsDir = [dirPaths objectAtIndex:0];

// Build the path to the database file
databasePath = [[NSString alloc] initWithString: [docsDir stringByAppendingPathComponent: @"foodDB.db"]];

NSFileManager *filemgr = [NSFileManager defaultManager];

if ([filemgr fileExistsAtPath: databasePath ] == NO)
{
    const char *dbpath = [databasePath UTF8String];

    if (sqlite3_open(dbpath, &foodDB) == SQLITE_OK)
    {
        char *errMsg;
        const char *sql_stmt = "CREATE TABLE IF NOT EXISTS foodDB (ID INTEGER PRIMARY KEY AUTOINCREMENT, categoryNumber INT, foodNumber INT, foodName TEXT, definition TEXT, ingredients TEXT, calorie INT, price INT, image1 TEXT, image2 TEXT, image3 TEXT, image4 TEXT)";

        if (sqlite3_exec(foodDB, sql_stmt, NULL, NULL, &errMsg) != SQLITE_OK)
        {
            NSLog(@"ERROR:  Failed to create database!");
        }

        sqlite3_close(foodDB);

    } else {
        NSLog(@"ERROR:  Failed to open/create database!");
    }
}

}

}

采纳答案by m.etka

I found a weird situation, "definition" word that I used in my statement causes the error. The funny thing is "definition" word is not listed in SQLite keywords list (http://www.sqlite.org/lang_keywords.html)

我发现了一个奇怪的情况，我在语句中使用的“定义”一词导致了错误。有趣的是 SQLite 关键字列表中没有列出“定义”一词（http://www.sqlite.org/lang_keywords.html）

When I replaced "definiton" with "def" problem solved. (createDatabase method)

当我用“def”替换“definiton”时，问题解决了。（创建数据库方法）

const char *sql_stmt = "CREATE TABLE IF NOT EXISTS foodDB (ID INTEGER PRIMARY KEY AUTOINCREMENT, categoryNumber INT, foodNumber INT, foodName TEXT, definition TEXT, ingredients TEXT, calorie INT, price INT, image1 TEXT, image2 TEXT, image3 TEXT, image4 TEXT)";

const char *sql_stmt = "CREATE TABLE IF NOT EXISTS foodDB (ID INTEGER PRIMARY KEY AUTOINCREMENT, categoryNumber INT, foodNumber INT, foodName TEXT, def TEXT, ingredients TEXT, calorie INT, price INT, image1 TEXT, image2 TEXT, image3 TEXT, image4 TEXT)";

回答by trojanfoe

Your logging logic is performing two sqlite_step()operations, which is misleading at best.

您的日志记录逻辑正在执行两个sqlite_step()操作，这充其量是误导性的。

Instead, capture the return code of the first sqlite_step()call and report thatvalue:

相反，捕获第一次sqlite_step()调用的返回码并报告该值：

int rc = sqlite3_step(statement);
if (rc == SQLITE_OK)
{
    NSLog(@"Food added.");
} else {
    NSLog(@"ERROR:  Failed to add food!: %d", rc);
}

You need to extend this logic to all sqlite_xxx()calls in your code.

您需要将此逻辑扩展到sqlite_xxx()代码中的所有调用。