SQLite insert into Swift causing loop Swift 3.1

Question

I am trying to insert an array into SQLite with Swift 3.1

When I use the "if" statement, a single insert is achieved without issues. However, I need this to insert an Array, so I need to use the "while" statement. However, this is causing the insert statement to run in a loop.

func insertDiveGear() {
    var statement: OpaquePointer? = nil

    let names = itemDataArray

    let update = "INSERT INTO DiveGearForDive (id, name) VALUES (?, ?);"

    while sqlite3_prepare_v2(self.dlDatabase.database, update, -1, &statement, nil) == SQLITE_OK {

        for (_ , name) in names.enumerated() {

            let diveNumber = Int32(diveNumber)

            sqlite3_bind_int(statement, 1, diveNumber)
            sqlite3_bind_text(statement, 2, name, -1, nil)

            if sqlite3_step(statement) == SQLITE_DONE {
                print("Successfully inserted row.")
            } else {
                print("Could not insert row.")
            }

        }

        sqlite3_finalize(statement)
    }
}

Any help would be great

It is unclear to me why you need that while-loop. sqlite3_prepare_v2 should be called once, and sqlite3_bind_XXX/sqlite3_step in a loop for all items to insert. — Martin R
– Martin R, Commented May 1, 2017 at 17:50

Community · Accepted Answer · 2017-05-23 12:17:57Z

Your code won't exit while loop because the sqlite3_prepare_v2 will not fail. But no while loop is needed. You can do something like:

func insertDiveGear() {
    var statement: OpaquePointer? = nil

    let names = itemDataArray

    let update = "INSERT INTO DiveGearForDive (id, name) VALUES (?, ?);"

    for (diveNumber, name) in names.enumerated() {
        if sqlite3_prepare_v2(dlDatabase.database, update, -1, &statement, nil) == SQLITE_OK {
            sqlite3_bind_int(statement, 1, Int32(diveNumber))
            sqlite3_bind_text(statement, 2, name, -1, SQLITE_TRANSIENT)

            if sqlite3_step(statement) == SQLITE_DONE {
                print("Successfully inserted row.")
            } else {
                print("Could not insert row.")
            }

            sqlite3_finalize(statement)
        }
    }
}

Or, even more efficient, prepare the statement only once, and then loop through binding, stepping, and then resetting:

func insertDiveGear() {
    var statement: OpaquePointer? = nil

    let names = itemDataArray

    let update = "INSERT INTO DiveGearForDive (id, name) VALUES (?, ?);"

    guard sqlite3_prepare_v2(dlDatabase.database, update, -1, &statement, nil) == SQLITE_OK else {
        let errmsg = String(cString: sqlite3_errmsg(dlDatabase.database))
        print("failure preparing: \(errmsg)")
        return
    }

    for (diveNumber, name) in names.enumerated() {
        sqlite3_bind_int(statement, 1, Int32(diveNumber))
        sqlite3_bind_text(statement, 2, name, -1, SQLITE_TRANSIENT)

        if sqlite3_step(statement) == SQLITE_DONE {
            print("Successfully inserted row.")
        } else {
            print("Could not insert row.")
        }

        sqlite3_reset(statement)
    }

    sqlite3_finalize(statement)
}

I didn't know what to make of that let diveNumber = Int32(diveNumber) line (where is diveNumber defined; using the same name?; etc.), so adjust that as you see fit, but the main question is why the loop didn't exit, and it's because sqlite3_prepare_v2 will always return SQLITE_OK if the SQL is well formed.

Also note, I used SQLITE_TRANSIENT constant (see Cannot invoke initializer for type 'sqlite3_destructor_type') in conjunction with sqlite3_bind_text to ensure that SQLite creates its own copy of the string that it received, which is prudent when dealing with Swift strings.

Collectives™ on Stack Overflow

SQLite insert into Swift causing loop Swift 3.1

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related