CODEWITHSUNDEEP
sqlswiftsqlitefilter
Dharma
Dharma

Reputation: 81

Swift 4 SQLITE query output fails with where clause

I am new to iOS and SWIFT but making progress.

I use SQLITE database to store employee details. While querying data, I need to filter output with a WHERE clause restricting it to just one row. The query doesn't give any output, but if I remove the WHERE clause, it works. I need to be able to filter query output to a specific i-phone of user.

This is a static function within a class I use in many places across the App. My Code:

// ***********************************
static func queryPhoneOwner() {
     let queryPhoneOwnerString = "SELECT EMPLOYEE_ID, FULL_NAME, LOCATION, UUID FROM MT_PHONE_OWNER WHERE UUID = ?"
    var queryPhoneOwnerStatement: OpaquePointer? = nil
    let db = DatabaseClass.openDatabase()
    var vUUID: String = " "
    if sqlite3_prepare_v2(db, queryPhoneOwnerString, -1, &queryPhoneOwnerStatement, nil) == SQLITE_OK {

        if let uuid = UIDevice.current.identifierForVendor?.uuidString {

            vUUID = uuid
        }

         sqlite3_bind_text(queryPhoneOwnerStatement, 1, vUUID, -1, nil)

        while (sqlite3_step(queryPhoneOwnerStatement) == SQLITE_ROW) {

            let queryResultCol1 = sqlite3_column_text(queryPhoneOwnerStatement, 0)
            let vOwnerEmployeeId = String(cString: queryResultCol1!)

            let queryResultCol2 = sqlite3_column_text(queryPhoneOwnerStatement, 1)
            let vOwnerFullName = String(cString: queryResultCol2!)

            let queryResultCol3 = sqlite3_column_text(queryPhoneOwnerStatement, 2)
            let vOwnerLocation = String(cString: queryResultCol3!)

            let queryResultCol4 = sqlite3_column_text(queryPhoneOwnerStatement, 3)
            let vOwnerUuid = String(cString: queryResultCol4!)

// and the code continues..
// FYI, Table columns pasted here from table creation process: "CREATE TABLE IF NOT EXISTS MT_PHONE_OWNER (EMPLOYEE_ID VARCHAR(10) PRIMARY KEY, FULL_NAME VARCHAR(50), LOCATION VARCHAR(15), UUID VARCHAR2(45) NOT NULL)"
//********************************************

Upvotes: 0

Views: 1703

Answers (1)

rmaddy
rmaddy

Reputation: 318824

  1. Avoid using SELECT * FROM in your code. Explicitly list the fields you wish to query. This ensures you get specific values in a specific order which is important in your sqlite3_column_text usage.
  2. Your query has one parameter. This means that your sqlite3_bind_text index must match. You are passing 4 but it needs to be 1.
  3. Not really a problem but the ? doesn't need to be in parentheses in your query.
  4. Be careful force unwrapping the results of sqlite3_column_text. There could be NULL values in the database which is why the result is optional. If the column is setup as NOT NULL then this is probably OK since the value will never be nil.

Upvotes: 1

Related Questions