SQLite Swift 绑定和检索 [英] SQLite Swift Binding and Retrieving

问题描述

我在绑定和插入数据或检索数据时遇到问题.老实说，我不确定如何确定导致问题的原因.我正在尝试使用以下代码向数据库添加 textfield、latitude 和 longitude.我几乎肯定问题出在 sqlite3_bind_text 或 sqlite3_column_text 函数上，但我将这两个函数全部包含在上下文中.当我运行调试打印语句时，每个值都作为经度值打印出来.几乎好像经度值覆盖了其他两列?我传递 latitude 和 longitude 绑定的方式不正确吗?还是我检索数据不正确?

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

}

我能够确定插入中存在缺陷.当我通过 DB Browser 查看 SQLite 数据库时，我看到经度值被插入到所有列中.我只是不确定我的语法在 sqlite3_bind_text 中的哪里有缺陷.

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.

推荐答案

我发现 sqlite3_bind_text 存在问题.我从

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
}

到

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

其中 SQLITE_TRANSIENT 定义为

where SQLITE_TRANSIENT is defined as

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

这篇关于SQLite Swift 绑定和检索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！