Import Existing SQLite Database

Question

Created Jan ’21

Replies 7

Boosts 0

Views 4.2k

Participants 3

Hi everyone,

I'm Updating my kind of quiz app to work with a SQLite Database.

I already implemented the SQLite Library and tried a Test Project. Know I copied my existing database to the project, and changed the path variable. I get the information, that the connection to the database worked, but I can't read data out of it.

Code Block import SQLite3
class DBHelper
{
    init()
    {
        db = openDatabase()
        //createTable()
    }
    let dbPath: String = "DataiOS.sqlite"
    var db:OpaquePointer?
    func openDatabase() -> OpaquePointer?
    {
        let fileURL = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
            .appendingPathComponent(dbPath)
        var db: OpaquePointer? = nil
        if sqlite3_open(fileURL.path, &db) != SQLITE_OK
        {
            print("error opening database")
            return nil
        }
        else
        {
            print("Successfully opened connection to database at \(dbPath)")
            return db
        }
    }

My Read Function looks like this:

Code Block    func read() -> [Person] {
        let queryStatementString = "select QuestionID, Question from Questions;"
        var queryStatement: OpaquePointer? = nil
        var psns : [Person] = []
        if sqlite3_prepare_v2(db, queryStatementString, -1, &queryStatement, nil) == SQLITE_OK {
            while sqlite3_step(queryStatement) == SQLITE_ROW {
                let id = sqlite3_column_int(queryStatement, 0)
                let name = String(describing: String(cString: sqlite3_column_text(queryStatement, 1)))
                psns.append(Person(id: Int(id), name: name))
                print("Query Result:")
                print("\(id) | \(name)")
            }
        } else {
            print("SELECT statement could not be prepared")
        }
        sqlite3_finalize(queryStatement)
        return psns
    }

Thats my first Project with SQLite and Swift, and I feel like a total noob. Hope anybody could help me.

Thanks for Reading and best Regards.

Angelo

Boost

Answer 1

OOPer OP

Jan ’21

I can't read data out of it.

Please explain what happens. Does your code show SELECT statement could not be prepared, or something else?

0

Answer 2

AngeloH OP

Jan ’21

Yeah exactly

0

Answer 3

OOPer OP

Jan ’21

Yeah exactly

I guess you failed to copy the original SQLite file into the document directory.

Please show your code to copy it including where you call it.

0

Answer 4

AngeloH OP

Jan ’21

I just drag and dropped the Database into the project folder.

Here is the call of the function Read from the Class DBHelper

Code Block class ViewController: UIViewController, UITableViewDelegate, UITableViewDataSource
{
    @IBOutlet weak var personTable: UITableView!
    
    let cellReuseIdentifier = "cell"
    
    var db:DBHelper = DBHelper()
    
    var persons:[Person] = []
    
    override func viewDidLoad() {
        super.viewDidLoad()
        
        personTable.register(UITableViewCell.self, forCellReuseIdentifier: cellReuseIdentifier)
        personTable.delegate = self
        personTable.dataSource = self
        
        
        persons = db.read()
    }

0

Answer 5

OOPer OP

Jan ’21

I just drag and dropped the Database into the project folder.

That is placed in the app bundle, not in the document directory.

You need to copy the original database in the app bundle to the document directory by code, if the document directory is empty.

0

Answer 6

AngeloH OP

Jan ’21

Ah ok Thanks a lot!!
I will try it and reply if it worked.

0

Answer 7

midgeteachday OP

Jun ’22

Did this end up working for you, AngeloH? I'm a bit new to SQLite and Swift interactions myself. I have a database that has been built out to contain the data that I need (in SQLite), and I want to import it into my Xcode project to basically be able to populate data on startup that the user doesn't provide themselves, but also be able to edit it using the SQLite wrapper. Thanks!

0