在Swift4 for iOS11中，我想附加两个SQLite数据库文件（file1.db和file2.db），以便对所有数据（即两个数据库）进行查询。
我已经使用以下代码成功地在一个SQLite数据库中实现了一个SQL查询（…这里有一个名称查询的示例…）：
func readOneDB() -> String? {
// create SQL-DB FilePath
do {
// create a documents-URL from filename
self.file_1_URL = try createSQLFilePath(fileName: "file1", fileExtension: "db")
}
catch {
return nil
}
let query =
"""
SELECT DISTINCT n.locations
FROM names n
WHERE n.name = "\(self.myName)"
"""
// Open SQLite database
var db: OpaquePointer? = nil
if sqlite3_open(self.file_1_URL?.absoluteString, &db) == SQLITE_OK {
var statement: OpaquePointer? = nil
// Run SELECT query from db
if sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK {
// Loop through all results from query
while sqlite3_step(statement) == SQLITE_ROW {
let name = sqlite3_column_text(statement, 0)
if name != nil {
let versionString = String(cString:name!)
return nameString
} else {
print("name not found", terminator: "")
return nil
}
}
} else {
let errmsg = String(cString: sqlite3_errmsg(db))
print("error running query: \(errmsg)")
return nil
}
} else {
print("error opening database")
return nil
}
return nil
}
现在，我读到了使用下面的sql cmd可以将两个sql数据库附加在一起（以便对它们进行查询）：attach
事实上，我有以下代码没有抛出异常-但仍然不能100%工作
let attachStr = "ATTACH DATABASE \'\(self.file_2_URL!)\' AS SECOND"
if (sqlite3_exec(db, attachStr, nil, &statement, nil) == SQLITE_OK) {
print("Success!")
}
上面印着“成功！”运行代码时。
当然，使用附加代码获得成功消息时，我认为就是这样（并且db对象将两个数据库联合起来）
但事实上没有！：不幸的是，查询仍然只带来来自第一个数据库（即file1）的结果，而没有附加的第二个file2的查询结果。
怎么了？
这是完整的代码，包括附件代码和查询（见下文）。
为什么查询仍然只传递来自第一个数据库（file1）的结果，而不是来自两个数据库（即file1和file2）？???
我需要在代码中进行哪些更改才能使其生效？???
func readTwoDBs() -> String? {
// create SQL-DB FilePath
do {
// create documents-URLs for two filenames
self.file_1_URL = try createSQLFilePath(fileName: "file1", fileExtension: "db")
self.file_2_URL = try createSQLFilePath(fileName: "file2", fileExtension: "db")
}
catch {
return nil
}
let query =
"""
SELECT DISTINCT n.locations
FROM names n
WHERE n.name = "\(self.myName)"
"""
// Open SQLite database
var db: OpaquePointer? = nil
if sqlite3_open(self.file_1_URL?.absoluteString, &db) == SQLITE_OK {
var statement: OpaquePointer? = nil
let attachStr = "ATTACH DATABASE \'\(self.file_2_URL!)\' AS SECOND"
if (sqlite3_exec(db, attachStr, nil, &statement, nil) == SQLITE_OK) {
print("Success!")
}
//// !!!!!!! Success is printed but the query still only works with file1 WHY ????????????????????
// Run SELECT query from db
statement = nil
if sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK {
// Loop through all results from query
while sqlite3_step(statement) == SQLITE_ROW {
let name = sqlite3_column_text(statement, 0)
if name != nil {
let versionString = String(cString:name!)
return nameString
} else {
print("name not found", terminator: "")
return nil
}
}
} else {
let errmsg = String(cString: sqlite3_errmsg(db))
print("error running query: \(errmsg)")
return nil
}
} else {
print("error opening database")
return nil
}
return nil
}
最佳答案
在乔金的帮助下，我想我找到了一个解决办法：
ATTACH cmd实际上意味着多个数据库共享同一个数据库连接。正如Joakim所指出的，如果一个人想从两个数据库文件中都得到结果，他仍然需要做两个查询！
代码如下：
（为了便于说明，该方法的返回值是一个元组，包含两个查询的两个结果（一个来自第一个数据库，另一个来自第二个数据库）
func readTwoDBs() -> (String, String)? {
var returnValue_1: String = ""
var returnValue_2: String = ""
// create SQL-DB FilePath
do {
// create documents-URLs for two filenames
self.file_1_URL = try createSQLFilePath(fileName: "file1", fileExtension: "db")
self.file_2_URL = try createSQLFilePath(fileName: "file2", fileExtension: "db")
}
catch {
return nil
}
let query_1 =
"""
SELECT DISTINCT n.locations
FROM names n
WHERE n.name = "\(self.myName)"
"""
// !! FOR QUERY_2 YOU NEED THE "MY_DB_TWO" NAME AND ITS TABLE IN THE FROM STATEMENT !!!!!!!
// !! THIS "MY_DB_TWO" IS GIVEN BY THE ATTACH-cmd FURTHER DOWN... !!!!!!!!
let query_2 =
"""
SELECT DISTINCT n.locations
FROM MY_DB_TWO.names n
WHERE n.name = "\(self.myName)"
"""
// Open SQLite database
var db: OpaquePointer? = nil
if sqlite3_open(self.file_1_URL?.absoluteString, &db) == SQLITE_OK {
var statement: OpaquePointer? = nil
let attachStr = "ATTACH DATABASE \'\(self.file_2_URL!)\' AS MY_DB_TWO"
if (sqlite3_exec(db, attachStr, nil, &statement, nil) == SQLITE_OK) {
print("Success!")
}
// Run SELECT query Nr1 from main.db
statement = nil
if sqlite3_prepare_v2(db, query_1, -1, &statement, nil) == SQLITE_OK {
// Loop through all results from query
while sqlite3_step(statement) == SQLITE_ROW {
let name = sqlite3_column_text(statement, 0)
if name != nil {
let nameString = String(cString:name!)
returnValue_1 = nameString
} else {
print("name not found", terminator: "")
returnValue_1 = ""
}
}
} else {
let errmsg = String(cString: sqlite3_errmsg(db))
print("error running query: \(errmsg)")
returnValue_1 = ""
}
// Run SELECT query Nr2 from MY_DB_TWO
statement = nil
if sqlite3_prepare_v2(db, query_2, -1, &statement, nil) == SQLITE_OK {
// Loop through all results from query
while sqlite3_step(statement) == SQLITE_ROW {
let name = sqlite3_column_text(statement, 0)
if name != nil {
let nameString = String(cString:name!)
returnValue_2 = nameString
} else {
print("name not found", terminator: "")
returnValue_2 = ""
}
}
} else {
let errmsg = String(cString: sqlite3_errmsg(db))
print("error running query: \(errmsg)")
returnValue_2 = ""
}
} else {
print("error opening database")
}
return (returnValue_1, returnValue_2)
}