In Swift4 for iOS11 I would like to attach two SQLite database-files (file1.db and file2.db) in order to make a query over all data (i.e. over both databases).
I have successfully implemented a SQL-query in one SQLite database using the following code (..here with an example of a name-query...):
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
}
Now, I read that with the following sql-cmd it is possible to attach two SQL-databases together (in order to make a query over both of them): ATTACH
In fact, I have the following code that does not throw an exception - but still not working 100%
let attachStr = "ATTACH DATABASE \'\(self.file_2_URL!)\' AS SECOND"
if (sqlite3_exec(db, attachStr, nil, &statement, nil) == SQLITE_OK) {
print("Success!")
}
There is a print of "Success!" when running the code.
Of course, getting the success-message with the attach-code, I thought this is it (and the db-object has both databases united)
But actually NO ! : The query still only brings results from the first database (i.e. file1) and no query-results of the attached second file2, unfortunately.
What is wrong ?
Here is the complete code with the attachment-code and the query (see below).
Why is the query still only delivering results from the first database (file1) and not from both databases (i.e. file1 and file2) ????
What changes do I need to make in the code for this to work ????
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
}
