1

I'm new in swift and SQLLite , I had a problem and I didn't know how to fix it

I create table contain [id name Grade] and this is my insert function

let insertStatementString = "INSERT INTO Contact (Id, Name, Grade ) VALUES (?, ?, ?);"

func insert() {
    var insertStatement: COpaquePointer = nil

    // 1
    if sqlite3_prepare_v2(db, insertStatementString, -1, &insertStatement, nil) == SQLITE_OK {

        let dic: [NSString] = ["Ray", "Chris", "Martha", "Danielle"]
        let grade = [11 , 13 ,11 ,12]
        var id = Int32()
        for (index, name) in dic.enumerate() {
            sqlite3_bind_int(insertStatement, 1, index + 1)
            sqlite3_bind_text(insertStatement, 2, name.UTF8String, -1, nil)
            sqlite3_bind_int(insertStatement, 3,Int32(grade[index]))
            id = id + 1

        }
        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)
}

   insert()

when i call my query function , the while loop work for one time only , but as you can see i had 4 elements in my table

this is my query function

let queryStatementString = "SELECT * FROM Contact;"
func query() {
    var queryStatement: COpaquePointer = nil
    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 queryResultCol1 = sqlite3_column_text(queryStatement, 1)
            let grade =  sqlite3_column_int(queryStatement, 2)
            let name = String.fromCString(UnsafePointer<CChar>(queryResultCol1))!
            print("Query Result:")
            print("\(id) | \(name) | \(grade)")
        }

    } else {
        print("SELECT statement could not be prepared")
    }

    sqlite3_finalize(queryStatement)
} 
query()

last thing , this my create Table function

let db = openDatabase()
let createTableString = "CREATE TABLE Contact(" + "Id INT PRIMARY KEY NOT NULL," + "Name CHAR(255)," + "Grade INTEGER)"

func createTable() {

    var createTableStatement: COpaquePointer = nil

    if sqlite3_prepare_v2(db, createTableString, -1, &createTableStatement, nil) == SQLITE_OK {

        if sqlite3_step(createTableStatement) == SQLITE_DONE {
            print("Contact table created.")
        } else {
            print("Contact table could not be created.")
        }
    } else {
        print("CREATE TABLE statement could not be prepared.")
    }

    sqlite3_finalize(createTableStatement)
}


createTable()
1
  • BTW, remember that the last parameter to sqlite3_bind_text should be SQLITE_TRANSIENT. Commented Feb 12, 2017 at 17:58

1 Answer 1

2

In your insert() function, you call sqlite3_step() once. This is why you insert a single row. Move the call to sqlite3_step() inside your loop on names and grades. Also call sqlite3_reset() before setting bindings.

Sign up to request clarification or add additional context in comments.

3 Comments

And maybe have a look at github.com/groue/GRDB.swift - it's a nice SQLite wrapper for Swift.
still have the same problem
Hmm. You may have to reset the statement between two executions, with sqlite3_reset(). And of course don't forget to check the return value of sqlite3_step(). And to make sure you call it four times.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.