SQLite "SQLITE误用“错误

Question

我对SQLite有个问题。当我尝试插入条目时，我会得到一个错误。我发现错误是错误代码21的"SQLITE误用“。

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

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

以下是我的插入方法：

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

创建方法可能是有用的：

-(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!");
    }
}

}

Answer 1

我发现了一种奇怪的情况，我在语句中使用的“定义”这个词导致了错误。有趣的是SQLite关键字列表(keywords.html)中没有列出“定义”一词。

当我把“定义”替换成"def“问题时。(createDatabase法)

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

Answer 2

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

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

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

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

Answer 3

在我的例子中我用的是

sqlite3_exec

而不是

sqlite3_prepare_v2

这导致了错误。更改SQL语句解决了这个问题。