0

I am trying to save data into an sqlite database with the below function.

func insertSpecieDetail(_ specieId: Int)
{
    //
    //  Create a temporary SpecieDetailModel for storing our values from the query.
    //
    let specieDetail: SpecieDetailModel = SpecieDetailModel()

    //
    //  Create an instance of our statement for SQLite to use.
    //
    var statement: OpaquePointer? = nil


    let update:String = String(format:SQL.InsertUserUpdates, specieId)
    if sqlite3_prepare_v2(self.database, update, -1, &statement, nil) == SQLITE_OK {

        sqlite3_bind_text(statement, 1, specieDetail.userCommonName, -1, nil);
        sqlite3_bind_text(statement, 2, specieDetail.userCommonNameFR, -1, nil);
        sqlite3_bind_text(statement, 3, specieDetail.userCommonNameES, -1, nil);
        sqlite3_bind_text(statement, 4, specieDetail.userCommonNameDE, -1, nil);
        sqlite3_bind_text(statement, 5, specieDetail.userNotes, -1, nil);

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

        sqlite3_finalize(statement)
}

SQL.InsertUserUpdates = 

"INSERT INTO RL_Species (user_common_name, user_common_name_fr, user_common_name_es, user_common_name_de, user_notes) VALUE (?, ?, ?, ?, ?)" + "WHERE RL_Species.id = %d"

Not getting any errors, but the system is skipping over after the command:

if sqlite3_prepare_v2(self.database, update, -1, &statement, nil) == SQLITE_OK

On this same line, I checked what "update" equals and is below

"INSERT INTO RL_Species (user_common_name, user_common_name_fr, user_common_name_es, user_common_name_de, user_notes) VALUE (?, ?, ?, ?, ?)WHERE RL_Species.id = 8"

so it seems that the string is finding the specieId OK.

What am I missing?

UPDATE: Found the VALUE vs. VALUES error, but still am getting an error "near" WHERE.

func insertSpecieDetail(_ specieId: Int)
{
    //
    //  Create a temporary SpecieDetailModel for storing our values from the query.
    //
    let specieDetail: SpecieDetailModel = SpecieDetailModel()

    //
    //  Create an instance of our statement for SQLite to use.
    //
    var statement: OpaquePointer? = nil

    let update = "INSERT INTO RL_Species (user_common_name, user_common_name_fr, user_common_name_es, user_common_name_de, user_notes) VALUES ('\(specieDetail.commonName)', '\(specieDetail.commonNameFR)', '\(specieDetail.commonNameES)', '\(specieDetail.commonNameDE)', '\(specieDetail.userNotes)') WHERE RL_Species.id = '\(specieId)';"

    if sqlite3_prepare_v2(database, update, -1, &statement, nil) == SQLITE_OK {


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

        // forces SQLite to send error message
        let errorMessage = String.init(validatingUTF8: sqlite3_errmsg(database))!
        print("update failed! \(errorMessage)")        }

        sqlite3_finalize(statement)
}
David Sanford
  • 735
  • 12
  • 26
  • Where did you get that code from? The last parameter of `sqlite3_bind_text()` must not be `nil`. And to get a useful error message, call [sqlite3_errmsg()](http://www.sqlite.org/c3ref/errcode.html). – CL. Dec 19 '16 at 09:46
  • Thank you, the error message helped a lot. I found that I was using VALUE and not VALUES. But, it seems that was the first of the errors. I am getting an error "near" WHERE, but cannot find it. I have updated the original post to reflect a change made to eliminate the nil. – David Sanford Dec 19 '16 at 11:11
  • 1
    Just use the [correct parameter value](http://www.sqlite.org/c3ref/bind_blob.html) (when in doubt, SQLITE_TRANSIENT). And [INSERT](http://www.sqlite.org/lang_insert.html) has no WHERE. – CL. Dec 19 '16 at 13:12
  • Thanks, CL. It took me a while but i figured it out. It seems there were a few errors and had to restructure it in order for it to work. The sqlite3_errmsg() suggestion is what helped a ton. Thank you – David Sanford Dec 19 '16 at 14:48
  • When I use the SQLITE_TRANSIENT, I get an error of an unresolved identifier. – David Sanford Dec 19 '16 at 19:57

0 Answers0