CODEWITHSUNDEEP
swiftsqlite
El Tomato
El Tomato

Reputation: 6707

Inserting more than 250 records to SQLite at a time

I need to insert more than a 1,000 records to an existing SQLite database. For this purpose, I have a class with a few static functions as follows.

class Application {
    static func openDatabase(path: String) -> OpaquePointer? {
        var db: OpaquePointer? = nil
        if sqlite3_open(path, &db) == SQLITE_OK {
            return db
        } else {
            return nil
        }
    }

    static func createTable(path: String) {
        let open = openDatabase(path: path)
        var createTableStatement: OpaquePointer? = nil
        let sqlCreate = "CREATE TABLE IF NOT EXISTS data (ID INTEGER PRIMARY KEY AUTOINCREMENT, year text, month text, day text, x1 text, x2 text, x3 text)"
        if sqlite3_prepare_v2(open, sqlCreate, -1, &createTableStatement, nil) == SQLITE_OK {
            if sqlite3_step(createTableStatement) == SQLITE_DONE {
                print("Table created.")
            } else {
                print("Table could not be created.")
            }
        } else {
            print("CREATE TABLE statement could not be prepared.")
        }
        sqlite3_finalize(createTableStatement)
    }

    static func insertRecord(path: String, year: String, month: String, day: String) {
        let open = openDatabase(path: path)
        var insertStatement: OpaquePointer? = nil
        let sqlInsert = "INSERT INTO data (year, month, day) VALUES (?, ?, ?)"
        if sqlite3_prepare_v2(open, sqlInsert, -1, &insertStatement, nil) == SQLITE_OK {
            let yearStr = year as NSString
            let monthStr = month as NSString
            let dayStr = day as NSString
            sqlite3_bind_text(insertStatement, 1, yearStr.utf8String, -1, nil)
            sqlite3_bind_text(insertStatement, 2, monthStr.utf8String, -1, nil)
            sqlite3_bind_text(insertStatement, 3, dayStr.utf8String, -1, nil)
            if sqlite3_step(insertStatement) == SQLITE_DONE {
                print("Successfully inserted row.")
            } else {
                print("Could not insert row.")
            }
        } else {
            print("INSERT statement could not be prepared.")
        }
        // 5
        sqlite3_finalize(insertStatement)
    }
}

And I insert records from AppDelegate. If I run it for the second time in order to insert records to the database as follows,

class AppDelegate: UIResponder, UIApplicationDelegate {
    var appFile = String()

    func application(_ application: UIApplication, didFinishLaunchingWithOptions launchOptions: [UIApplicationLaunchOptionsKey: Any]?) -> Bool {
        appFile = folderPath(s: 0, name: "Application")
        if !pathExists(path: appFile) {
            Application.createTable(path: appFile)
        } else {
            for i in 0..<10 {
                for j in 0..<12 {
                    for k in 0..<31 {
                        let yearStr = String(i + 2018)
                        let monthStr = String(j + 1)
                        let dayStr = String(k + 1)
                        Application.insertRecord(path: appFile, year: yearStr, month: monthStr, day: dayStr)
                    }
                }
            }
        }

        return true
    }
}

the app will crash after inserting 250 records to the database. The debugger says "NSInternalInconsistencyException" It crashes, I believe, because the app opens 250 files at a time. Years ago, it wasn't a problem because we could close the database like

sqlite3_close(statement)

every time the app opens a statement. Now, we can't. So what can we do to insert a number of records at a time? Thanks.

Upvotes: 0

Views: 516

Answers (1)

rmaddy
rmaddy

Reputation: 318824

When working with SQLite it is very important to properly cleanup all resources. Your primary issue is that you open the database in each call to insertRecord but you never close the database. That's bad.

For every successful call to sqlite_open there must be a corresponding call to sqlite_close.

For every successful call to sqlite3_prepare_v2 there must be a corresponding call to sqlite3_finalize and zero or more calls to sqlite3_reset.

Your method should be:

static func insertRecord(path: String, year: String, month: String, day: String) {
    if let open = openDatabase(path: path) {
        var insertStatement: OpaquePointer? = nil
        let sqlInsert = "INSERT INTO data (year, month, day) VALUES (?, ?, ?)"
        if sqlite3_prepare_v2(open, sqlInsert, -1, &insertStatement, nil) == SQLITE_OK {
            let yearStr = year as NSString
            let monthStr = month as NSString
            let dayStr = day as NSString
            sqlite3_bind_text(insertStatement, 1, yearStr.utf8String, -1, nil)
            sqlite3_bind_text(insertStatement, 2, monthStr.utf8String, -1, nil)
            sqlite3_bind_text(insertStatement, 3, dayStr.utf8String, -1, nil)
            if sqlite3_step(insertStatement) == SQLITE_DONE {
                print("Successfully inserted row.")
            } else {
                print("Could not insert row.")
            }
            sqlite3_finalize(insertStatement)
        } else {
            print("INSERT statement could not be prepared.")
        }

        sqlite3_close(open)
    } else {
        print("Database couldn't be open.")
    }
}

Upvotes: 1

Related Questions