CODEWITHSUNDEEP
iosswiftsqlite
Eric Walters
Eric Walters

Reputation: 309

SQLite Swift Binding and Retrieving

I am having an issue with either the binding and inserting of data or the retrieval of my data. I'm honestly not sure how to determine which is causing the issue. I am trying to add a textfield, latitude, and longitude to the database using the code below. I am almost positive the problem lies with the sqlite3_bind_text or the sqlite3_column_text functions but I am including both the functions in their entirety for context. When I run my debug print statements every value is printed out as the longitude value. Almost as if the longitude value is overwriting the other two columns? Is the way I am passing latitude and longitude to bind incorrectly? Or am I retrieving the data incorrectly?

@IBAction func submitPressed(_sender: UIButton) {

    var lat = ""
    var long = ""

    if(isLocationEnabled.isOn == true){
        enableLocationManager()
        let location:CLLocationCoordinate2D = locationManager.location!.coordinate
        lat = String(location.latitude)
        long = String(location.longitude)
        print(lat)
        print(long)

    } else {
        disableLocationManager()
        lat = "44.5"
        long = "-123.2"
    }

    let text = textEntered.text?.trimmingCharacters(in: .whitespacesAndNewlines)


    if(text?.isEmpty)!{
        textEntered.layer.borderColor = UIColor.red.cgColor
    }

    var statement: OpaquePointer?

    let newQuery = "INSERT INTO Location (textEntered, latitude, longitude) VALUES (?,?,?)"

    if sqlite3_prepare(database, newQuery, -1, &statement, nil) != SQLITE_OK {
        let error = String(cString: sqlite3_errmsg(database)!)
        print("Error Preparing: \(error)")
        return
    }

    if sqlite3_bind_text(statement, 1, text, -1, nil) != SQLITE_OK {
        let error = String(cString: sqlite3_errmsg(database)!)
        print("Error Binding: \(error)")
        return
    }

    if sqlite3_bind_text(statement, 2, lat, -1, nil) != SQLITE_OK {
        let error = String(cString: sqlite3_errmsg(database)!)
        print("Error Binding: \(error)")
        return
    }

    if sqlite3_bind_text(statement, 3, long, -1, nil) != SQLITE_OK {
        let error = String(cString: sqlite3_errmsg(database)!)
        print("Error Binding: \(error)")
        return
    }

    if sqlite3_step(statement) != SQLITE_DONE {
        let error = String(cString: sqlite3_errmsg(database)!)
        print("Error Inserting: \(error)")
        return
    }

    textEntered.text = ""

Then, I try to retrieve it using the code below:

func readValues() {
    posts.removeAll()

    let newQuery = "SELECT id, textEntered, latitude, longitude FROM Location"

    var statement: OpaquePointer?

    if sqlite3_prepare(database, newQuery, -1, &statement, nil) != SQLITE_OK {
        let error = String(cString: sqlite3_errmsg(database)!)
        print(error)
    }

    while(sqlite3_step(statement) == SQLITE_ROW) {
        let id = sqlite3_column_int(statement,0)
        let textEntered = String(cString: sqlite3_column_text(statement,1))
        let latitude = String(cString: sqlite3_column_text(statement,2))
        let longitude = String(cString: sqlite3_column_text(statement,3))


        print("Text Entered: \(textEntered)")
        print("Latitude: \(latitude)")
        print("Longitude: \(longitude)")

        posts.append(Post(id: Int(id),textEntered: String(describing: textEntered), latitude: String(describing: latitude), longitude: String(describing: longitude)))
    }

    self.tableViewPosts.reloadData()

}

EDIT: I was able to determine that there is a flaw in the insert. When I view the SQLite database through DB Browser I see that the longitude value is being inserted into all of the columns. I'm just not sure where my syntax is flawed in the sqlite3_bind_text. enter image description here

Upvotes: 2

Views: 1334

Answers (1)

Eric Walters
Eric Walters

Reputation: 309

I figured out there was an issue with the sqlite3_bind_text. I changed the three columns from 

if sqlite3_bind_text(statement, 1, text, -1, nil) != SQLITE_OK {
        let error = String(cString: sqlite3_errmsg(database)!)
        print("Error Binding: \(error)")
        return
}

to

 if sqlite3_bind_text(statement, 1, text, -1, SQLITE_TRANSIENT) != SQLITE_OK {
            let error = String(cString: sqlite3_errmsg(database)!)
            print("Error Binding: \(error)")
            return
}

where SQLITE_TRANSIENT is defined as 

let SQLITE_TRANSIENT = unsafeBitCast(OpaquePointer(bitPattern: -1), to: sqlite3_destructor_type.self)

Upvotes: 2

Related Questions