SQLite在Xcode中访问数据库时不会准备查询 [英] SQLite will not prepare query when accessing database in Xcode

问题描述

我是一个初学者iPhone开发者试图从Xcode 4.3中的sqlite数据库中获取信息。我有我的数据库（名为DB_Info.sqlite）在与我的.h和.m文件相同的目录，我还拖动数据库到Xcode中左侧栏中的文件夹部分。

I'm a beginner iPhone developer trying to take information out of a sqlite database in Xcode 4.3. I have my database (which is named DB_Info.sqlite) in the same directory as my .h and .m files, and I also dragged the database into the folders section on the left bar in Xcode.

请你快速看看我的代码，让我知道我的错误在哪里？我使用NSLogs来识别问题发生的地方，在最后一个if语句，它写在评论。非常感谢您！

Could you please take a quick look at my code and let me know where my mistake is? I have used NSLogs to identify where the problem occurs, at the very last if statement, and it's written in comments. Thank you so much in advance!

#import <sqlite3.h>
@implementation Player
{
    sqlite3 *DB_Info;
    NSString *databasePath;
    NSString *docsDir;
    NSArray *dirPaths;
}

-(Player*)createPlayer:(NSString*)playerName
{        

    dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    docsDir = [dirPaths objectAtIndex:0];
    databasePath = [[NSString alloc] initWithString: [docsDir stringByAppendingPathComponent: @"DB_Info.sqlite"]];
    const char *dbpath = [databasePath UTF8String];
    sqlite3_stmt *statement;

    if (sqlite3_open(dbpath, &DB_Info) == SQLITE_OK) { //works fine

        NSString *querySQL = [NSString stringWithFormat: @"SELECT * FROM playerlist WHERE fullName=\"%@\"", playerName];

        const char *query_stmt = [querySQL UTF8String];

        if (sqlite3_prepare_v2(DB_Info, query_stmt, -1, &statement, NULL) == SQLITE_OK) { //PROBLEM: This is where the problem is, and the if statement never goes through
        //....rest of code here
        } else {
        NSLog(@"Error");
        }
}

推荐答案

，而不是只是说错误，记录SQL错误消息

First, rather than just saying "Error", log the SQL error message

NSLog(@"%s SQL error '%s' (%1d)", __FUNCTION__, sqlite3_errmsg(database), sqlite3_errcode(database));` 

会准确告诉你发生了什么问题。人们第一次SQL项目的一个常见错误是找不到该表。如果是这样，请继续阅读。 （如果没有，请随意忽略其余部分。）

It will tell you precisely what's going wrong. A common error on people's first time SQL projects is that the table is not found. If so, read on. (If not, feel free to ignore the rest of this.)

其次，您正在您的文档文件夹中查找您的数据库。您是否明确地将其从您的捆绑包复制到您的文档文件夹？还是以编程方式创建？但如果您事先准备好了，那么在您将其复制到文档文件夹之前，它将不会出现在文档文件夹中。

Second, you're looking for your database in your Documents folder. Did you explicitly copy it from your bundle to your Documents folder at some point? Or did you create it programmatically? But if you prepared it in advance, it won't be in the Documents folder until you copy it there.

第三，我还建议您考虑使用 sqlite3_open_v2 而不是 sqlite3_open 。你正在检查是否是SQLITE_OK，但这可能是一种错误的安全感。 sqlite3_open 将创建一个数据库，如果它不在那里，这显然不是你的意图。你的应用程序应该推测是从包中复制数据库或创建数据库和表为你在你得到这个方法之前（而你没有显示，所以我不知道你是否这样做）。无论如何， sqlite3_open_v2 函数不会创建数据库，如果它不存在，除非你显式请求它通过包括 SQLITE_OPEN_CREATE 。所以，如果没有找到，类似下面的东西将不会创建数据库：

Third, I'd also suggest that you consider using sqlite3_open_v2 instead of sqlite3_open. You are checking to see if that's SQLITE_OK, but that may be giving a false sense of security. The sqlite3_open will create a database if it's not there, which is clearly not your intent. Your app should presumably being copying the db from the bundle or creating the database and the tables for you before you get to this method (and you're not showing that, so I'm not sure if you're doing that). Anyway, the sqlite3_open_v2 function will not create the database if it's not there unless you explicitly request it does so by including SQLITE_OPEN_CREATE. So, something like the following will not create the database if it's not found:

if (sqlite3_open_v2(dbpath, &DB_Info, SQLITE_OPEN_READWRITE, NULL) == SQLITE_OK) {

您建议您通过模拟器菜单上的重置内容和设置重置模拟器，或明确删除应用程序，以便删除可能已创建的任何空白数据库。如果您在设备上运行此应用，请删除该应用并重新安装。

On the off chance that a blank database has been created for you, I'd suggest you reset your simulator via "Reset Content and Settings" on the simulator's menu, or explicitly delete the app, so any blank databases that might have been created can be removed. If you're running this on a device, delete the app and reinstall it.

第四，如果数据库已提前创建，已包括在复制捆绑资源设置中？例如：

Fourth, if the database has been created in advance, have you confirmed that the database has been included in the "Copy Bundle Resources" setting? E.g.:

第五，如果你在模拟器上运行应用程序，你总是可以浏览模拟器的文件结构，并确保你的文件是你认为你的文件。您还可以运行Mac OS sqlite 程序来检查模拟器正在使用的数据库，以确保一切正常。您甚至可以在模拟器使用的数据库中测试您的SQL，这对诊断目的很有用。 （或者，如果你不喜欢Mac文本的 sqlite 程序，你也可以购买/下载图形工具，如基本。）在你可以做到这一点之前，你可能首先要配置你的Mac，所以你可以很容易地浏览模拟器的文件需要你发出Terminal程序并发出命令：

Fifth, if you're running the app on the simulator, you can always browse the simulator's file structure and make sure your files are where you think they are. You can also run the Mac OS sqlite program to inspect the database that the simulator is using to make sure everything is ok. You can even test your SQL right in the db that the simulator uses, which can be useful for diagnostic purposes. (Or, if you don't like the Mac text based sqlite program, you can also buy/download graphical tools, such as Base.) Before you can do this, you might first want to configure your Mac so you can easily browse the Simulator's files requires that you fire up the Terminal program, and issue the command:

chflags nohidden ~/Library

然后，您可以浏览到〜/ Library / Application\ Support / iPhone \ Simulator / 5.1 / Applications /各种应用程序，并确保您的数据库文件是。

Then you can browse to "~/Library/Application\ Support/iPhone\ Simulator/5.1/Applications/" and then browse the various apps that you have and make sure you db file is there.

这篇关于SQLite在Xcode中访问数据库时不会准备查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！