iPhone的SQLite应用程序可以附加到其他数据库吗？

Question

ATTACH DATABASE命令对于在SQLite数据库文件之间传输行非常有用，它允许您从跨多个数据库的表中连接行。 例如：

$ sqlite3 BookLoansDB.sqlite 
sqlite> ATTACH DATABASE '/Users/.../Documents/BooksDB.sqlite' AS books_db;
sqlite> select B.BookName, B.Pages, BL.LentTo from main.tblBookLoan BL inner join books_db.tblBook B on B.BookID = BL.BookID;
The Client|512|Jenny
The Pelican Brief|432|Mike

我该如何在iPhone上使用Objective-C完成相同的操作。我尝试了以下代码，但没有成功：

NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentDirectory = [paths objectAtIndex:0];
const char *booksDBPath = [[documentDirectory stringByAppendingPathComponent:@"BooksDB.sqlite"] UTF8String];
const char *bookLoansDBPath = [[documentDirectory stringByAppendingPathComponent:@"BookLoansDB.sqlite"] UTF8String];
sqlite3 *bookLoansDB;
int result = sqlite3_open(bookLoansDBPath, &bookLoansDB);
sqlite3_stmt *attachStmt;
NSString *attachSQL = [NSString stringWithFormat: @"ATTACH DATABASE \'%s\' AS books_db", bookLoansDBPath];
result = sqlite3_prepare_v2(bookLoansDB, [attachSQL UTF8String] , -1, &attachStmt, nil);
char *errorMessage;
result = sqlite3_exec(bookLoansDB, [attachSQL UTF8String], NULL, NULL, &errorMessage);
sqlite3_stmt *selectStmt;
NSString *selectSQL = @"select * from main.tblBookLoan BL inner join books_db.tblBook B on B.BookID = BL.BookID";
result = sqlite3_prepare_v2(bookLoansDB, [selectSQL UTF8String] , -1, &selectStmt, nil);
// result == 1
result = sqlite3_step(selectStmt) ; 
// result == 21
if (result == SQLITE_ROW)
{
    //do something
}

这能做到吗？

- Nick

---

在查看了您的评论后，似乎您得到的 sqlite3_prepare_v2 结果为 1，这是 SQLITE_ERROR，您应该使用 sqlite3_errmsg(bookLoansDB) 检查错误。 - Joe

Answer 1

我已经成功运行了这个示例（在“附加数据库”SQL中混淆了我的数据库名称）。所以，是的，它可以做到。感谢Deepmist指引我正确的方向。

由于这样的示例相当难找，我已经粘贴下面的工作版本。

NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentDirectory = [paths objectAtIndex:0];
const char *booksDBPath = [[documentDirectory stringByAppendingPathComponent:@"BooksDB.sqlite"] UTF8String];
const char *bookLoansDBPath = [[documentDirectory stringByAppendingPathComponent:@"BookLoansDB.sqlite"] UTF8String];
sqlite3 *bookLoansDB;
if (sqlite3_open(bookLoansDBPath, &bookLoansDB) == SQLITE_OK) {
    NSString *attachSQL = [NSString stringWithFormat: @"ATTACH DATABASE \'%s\' AS books_db", booksDBPath];
    char *errorMessage;
    if (sqlite3_exec(bookLoansDB, [attachSQL UTF8String], NULL, NULL, &errorMessage) == SQLITE_OK) {
        sqlite3_stmt *selectStmt;
        NSString *selectSQL = @"select * from main.tblBookLoan BL inner join books_db.tblBook B on B.BookID = BL.BookID";
        if (sqlite3_prepare_v2(bookLoansDB, [selectSQL UTF8String] , -1, &selectStmt, nil) == SQLITE_OK) {
            int n=0;
            while (sqlite3_step(selectStmt) == SQLITE_ROW) {
                //do something
            }
        }
        else {
            NSLog(@"Error while creating select statement: '%s'", sqlite3_errmsg(bookLoansDB));
        }
    }
    else {
        NSLog(@"Error while attaching databases: '%s'", errorMessage);
    }
}
else {
    NSLog(@"Failed to open database at %@ with error %s", booksDBPath, sqlite3_errmsg(bookLoansDB));
    sqlite3_close(bookLoansDB);
}

- Nick

Answer 2

你可以在 iPhone 上附加 SQLite 数据库。很难说你的代码出了什么问题，但是如果它看起来更像这样的话，应该会有所帮助：

NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentDirectory = [paths objectAtIndex:0];
const char *booksDBPath = [[documentDirectory stringByAppendingPathComponent:@"BooksDB.sqlite"] UTF8String];
const char *bookLoansDBPath = [[documentDirectory stringByAppendingPathComponent:@"BookLoansDB.sqlite"] UTF8String];
sqlite3 *bookLoansDB;
if (sqlite3_open(bookLoansDBPath, &bookLoansDB) == SQLITE_OK) {
    NSString *attachSQL = [NSString stringWithFormat: @"ATTACH DATABASE \'%s\' AS books_db", bookLoansDBPath];
    char *errorMessage;
    if (sqlite3_exec(bookLoansDB, [attachSQL UTF8String], NULL, NULL, &errorMessage) == SQLITE_OK && errorMessage == nil) {
        sqlite3_stmt *selectStmt;
        NSString *selectSQL = @"select * from main.tblBookLoan BL inner join books_db.tblBook B on B.BookID = BL.BookID";
        if (sqlite3_prepare_v2(bookLoansDB, [selectSQL UTF8String] , -1, &selectStmt, nil) == SQLITE_OK) {
            while (sqlite3_step(selectStmt) == SQLITE_ROW) {
                //process row
            }
        }
        else {
            NSLog(@"Error while creating select statement: '%s'", sqlite3_errmsg(bookLoansDB));
        }
    }
    else {
        NSLog(@"Error while attaching databases: '%s'", errorMessage);
    }
}
else {
    NSLog(@"Failed to open database at %@ with error %s", booksDBPath, sqlite3_errmsg(bookLoansDB));
    sqlite3_close(bookLoansDB);
}

我没有测试过这段代码，只是修改了你的代码，所以可能需要修复。

- Joe

---

使用此代码时出现了错误：创建选择语句时出错：'no such table: books_db.tblBook'。令人沮丧的是，相同的 SQL 在命令行 sqlite 示例中可以正常工作。谢谢。 - Nick