SQLite“SQL错误或缺少数据库”Objective-C

I'm currently getting an error when trying to insert data into my database.

当我试图将数据插入我的数据库时，我正在得到一个错误。

#import "ReminderDB.h"

@implementation ReminderDB

@synthesize db = _db;

-(id)init{
    _db = [self openDB];
    [self createTable:@"Reminders" withField1:@"Title" withField2:@"Who" withField3:@"Quantity"];
    return self;
}

-(NSString *) filepath{
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    return [[paths objectAtIndex:0] stringByAppendingPathComponent:@"reminders.sqlite"];
}

- (sqlite3*)openDB {
    if (_db == NULL) {
        int rc;

        if ((rc = sqlite3_open([[self filepath] UTF8String], &(_db))) != SQLITE_OK) {
            NSLog(@"%s error (%1d)", __FUNCTION__, rc);
            _db = NULL;
        } else {
            NSLog(@"db opened");
        }
    }
    return _db;
}
-(void)createTable: (NSString *) tableName
        withField1: (NSString *) field1
        withField2: (NSString *) field2
        withField3: (NSString *) field3
{
    char *err;
    NSString *sql = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS '%@' ('%@' TEXT PRIMARY KEY, '%@' TEXT, '%@' TEXT);", tableName, field1, field2, field3];
    if(sqlite3_exec(_db, [sql UTF8String], NULL, NULL, &err) != SQLITE_OK){
        sqlite3_close(_db);
        NSAssert(0, @"Could not create table");
    }
    else{
        NSLog(@"Table Created");
    }
}
-(void)addReminder:(NSString*)title
               who:(NSString*)who
          quantity:(NSString*)quantity{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO Reminders ('Title', 'Who', 'Quantity') VALUES ('%@', '%@', '%@')", title, who, quantity];
    char *err;
    int rc;
    if((rc = sqlite3_exec(_db, [sql UTF8String], NULL, NULL, &err)) != SQLITE_OK){
        NSLog(@"%s error (%1d)", __FUNCTION__, rc);
        sqlite3_close(_db);
        NSAssert(0, @"Could not update table");
    }
    else{
        NSLog(@"Table Update Successful");
    }
}

@end

This code successfully opens the database and creates the table. However when I call addReminder:who:quantity the table will not update and the error I am getting is an SQL Error or Missing Database error. Which doesn't make sense to me because I know the table is created and exists.

此代码成功地打开数据库并创建表。但是，当我调用addReminder:who:quantity时，表将不会更新，我得到的错误是一个SQL错误或丢失的数据库错误。这对我来说没有意义，因为我知道表是创建和存在的。

EDIT I have updated my addReminder:who:quantity: to use binds instead of exec's. I have also taken out the close calls. I am now getting an error when calling prepare.

编辑我更新了我的收件人:who:quantity: to use binding instead of exec。我也把电话打了。在调用prepare时，我现在得到一个错误。

-(void)addReminder:(NSString*)title
           who:(NSString*)who
      quantity:(NSString*)quantity{

    const char *sql = "INSERT INTO Reminders ('Title', 'Who', 'Quantity') VALUES (?, ?, ?)";
    sqlite3_stmt *statement;
    if (sqlite3_prepare_v2(_db, sql, -1, &statement, NULL) == SQLITE_OK) {
        // Bind the parameters (note that these use a 1-based index, not 0).
        sqlite3_bind_text(statement, 1, [title UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(statement, 2, [who UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(statement, 3, [quantity UTF8String], -1, SQLITE_TRANSIENT);
        NSLog(@"Binding successful");
    }
    int returnCode = sqlite3_step(statement);
    if (returnCode != SQLITE_DONE) {
        // error handling...
        NSLog(@"An error occoured (%d)", returnCode);
    }
    else{
        NSLog(@"Table Updated");
    }
}

I know the problem is in the prepare call because I am not getting "Binding successful" in my log.

我知道问题在准备调用中，因为我的日志中没有“绑定成功”。

1 个解决方案

#1

A couple of thoughts:

几个想法:

I don't see anything in the provided code snippet that would produce a warning that says "SQL Error or Missing Database". Have you identified where that's being produced?

在提供的代码片段中，我没有看到任何会产生“SQL错误或丢失数据库”的警告。你确定了生产的地方吗?
If you close the database anywhere, don't forget to set _db to NULL. You might have a close method that looks like:

如果在任何地方关闭数据库，不要忘记将_db设置为NULL。你可能有一个接近的方法，看起来像:
```
- (int)closeDB {
    if (_db) {
        int rc = sqlite3_close(_db);
        _db = NULL;
        return rc;
    }
    return SQLITE_OK;
}
```
Having said that, you generally don't open and close databases while an app is running, so this might not be critical. But if you are closing it anywhere, make sure to NULL the pointer, too. Otherwise your openDB method won't work properly if you try to reopen the database.

话虽如此，你通常不会在应用程序运行时打开和关闭数据库，所以这可能并不重要。但如果你在任何地方关闭它，也要确保指针为空。否则，如果尝试重新打开数据库，您的openDB方法将无法正常工作。

In your addReminder method, make sure to log sqlite3_errmsg:

在您的寻址方法中，请确保记录sqlite3_errmsg:

-(void)addReminder:(NSString*)title
               who:(NSString*)who
          quantity:(NSString*)quantity {

    const char *sql = "INSERT INTO Reminders ('Title', 'Who', 'Quantity') VALUES (?, ?, ?)";
    sqlite3_stmt *statement;
    int returnCode;

    if ((returnCode = sqlite3_prepare_v2(_db, sql, -1, &statement, NULL)) == SQLITE_OK) {
        // Bind the parameters (note that these use a 1-based index, not 0).
        sqlite3_bind_text(statement, 1, [title UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(statement, 2, [who UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(statement, 3, [quantity UTF8String], -1, SQLITE_TRANSIENT);
        NSLog(@"Binding successful");
    } else {
        NSLog(@"Prepare failed: %s (%ld)", sqlite3_errmsg(_db), (long) returnCode);
        return;
    }

    returnCode = sqlite3_step(statement);
    if (returnCode != SQLITE_DONE) {
        // error handling...
        NSLog(@"An error occurred: %s (%ld)", sqlite3_errmsg(_db), (long)returnCode);
    }
    else{
        NSLog(@"Table Updated");
    }

    sqlite3_finalize(statement);
}

Note, don't forget to call sqlite3_finalize whenever you call sqlite3_prepare_v2 or else you'll leak memory.

注意，在调用sqlite3_prepare_v2时不要忘记调用sqlite3_finalize，否则会泄漏内存。

As an aside, your CREATE TABLE call is creating Quantity as a TEXT column. You might want to create that as INTEGER if it's an integer value, or REAL if it's a floating point value.

顺便提一下，您的CREATE TABLE调用将创建数量作为文本列。如果它是一个整数值，你可能想把它创建为整数值;如果它是一个浮点值，你可能想把它创建为实数。

When you bind values for the Quantity value, you might want to use the type-appropriate bind call, e.g.

当您绑定数量值的值时，您可能需要使用类型合适的bind调用，例如。
```
sqlite3_bind_int(statement, 3, [quantity intValue]);
```
Clearly, though, your createTable method seems to be hard coded to create a table with three TEXT columns, so you might want to refactor that, too (though SQLite ignores the column definition when inserting values, so this isn't absolutely critical).

显然，您的createTable方法似乎很难编码来创建一个包含三个文本列的表，所以您可能也想重构它(尽管SQLite在插入值时忽略了列定义，所以这不是绝对关键的)。

But, by storing numeric data types, it means that when you sort by Quantity column or do arithmetic calculations, SQLite will handle this properly. Actually, SQLite is pretty flexible in terms of converting string values to numeric values in many cases, so this isn't critical, but I think it's still appropriate to store numeric values as numbers, rather than strings.

但是，通过存储数字数据类型，这意味着当您按数量列排序或进行算术计算时，SQLite将正确地处理这个问题。实际上，在很多情况下，SQLite在将字符串值转换为数字值方面非常灵活，所以这并不重要，但我认为将数字值存储为数字而不是字符串仍然是合适的。

Note, the above point is completely unrelated to your problem at hand, but it does reflect best practice.

注意，上面这一点与你手头的问题完全无关，但它确实反映了最佳实践。
You say that your sqlite3_prepare_v2 statement is producing an error that says:

你说你的sqlite3_prepare_v2语句产生的错误是:

table Reminders has no column named Who

表提醒没有命名为Who的列

Your create statement only creates the table if it doesn't exist. So if you accidentally created it earlier using different columns, then "Who" might not be found, because it won't recreate a table that already exists.

创建语句只在表不存在的情况下创建该表。因此，如果您不小心使用不同的列创建了它，那么“Who”可能不会被找到，因为它不会重新创建一个已经存在的表。

Open the database from the simulator/device (not from the project folder, if you have one there) using your Mac OS X database tool of choice (or the sqlite3 command line program, if you don't have a nice database tool) and take a look at the table and make sure what the column names are. When dealing with the simulator, the precise location of this folder depends upon what version of Xcode you're using. In Xcode 5, it's in ~/Library/Application Support/iPhone Simulator and then navigate to the appropriate iOS version and application, and look in the appropriate Documents subfolder. In Xcode 6 it's located in ~/Library/Developer/CoreSimulator/Devices (and you have to figure out which cryptic folder the app is in, often simplified if you sort this folder by "date"). And, if you're not seeing ~/Library, make sure to unhide the Library folder, easily shown by using the command chflags nohidden ~/Library from the Terminal command line.

从模拟器/设备打开数据库(而不是从项目文件夹,如果你有一个)使用Mac OS X数据库工具的选择(或sqlite3命令行程序,如果你没有一个好的数据库工具),看看表,确保列名称是什么。在处理模拟器时，这个文件夹的精确位置取决于您使用的是什么版本的Xcode。在Xcode 5中，它位于~/Library/Application Support/iPhone模拟器中，然后导航到相应的iOS版本和应用程序，并查看相应的Documents子文件夹。在Xcode 6中，它位于~/Library/Developer/CoreSimulator/设备中(你必须弄清楚应用程序在哪个神秘文件夹中，如果你按“日期”对这个文件夹排序，通常会简化。)而且，如果您没有看到~/Library，请确保打开Library文件夹，使用命令chflags nohidden ~/Library从终端命令行轻松显示。

Or just delete the app from the simulator/device and start over, letting it recreate the database again, so you know you're not dealing with an old, out-of-date database.

或者直接从模拟器/设备中删除应用程序，重新创建数据库，这样你就知道你不是在处理过时的数据库。
While I encourage you to finish this up using the SQLite C API (it's a good learning experience), I must say that you might want to investigate FMDB, which is an Objective-C wrapper around the SQLite C API. I wouldn't do that right now (you have too many moving parts and I don't want to confuse the situation further), but after you address your immediate challenges, it's worth considering.

虽然我鼓励您使用SQLite C API(这是一个很好的学习经验)完成这一工作，但是我必须说您可能想要研究FMDB，它是一个围绕SQLite C API的Objective-C包装器。我现在不会这么做(你有太多的移动部件，我不想把情况弄得更混乱)，但是在你解决了眼前的挑战之后，这是值得考虑的。

#1