Swift 4 SQLITE query output fails with where clause

Question

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)"
//********************************************

You are not applying a condition to 'queryPhoneOwnerString.' — El Tomato
– El Tomato, Commented Mar 17, 2018 at 2:35

rmaddy · Accepted Answer · 2018-03-17 01:07:05Z

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.
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.
Not really a problem but the ? doesn't need to be in parentheses in your query.
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.

answered Mar 17, 2018 at 1:07

rmaddy

319k44 gold badges548 silver badges591 bronze badges

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

5 Comments

Dharma Over a year ago

Thank you very much for your prompt response and comments. I changed the query to ---let queryPhoneOwnerString = "SELECT EMPLOYEE_ID, FULL_NAME, LOCATION, UUID FROM MT_PHONE_OWNER WHERE UUID = ?" --- and bind text as -- sqlite3_bind_text(queryPhoneOwnerStatement, 1, vUUID, -1, nil) --, but I still have the same issue. !!??

Dharma Over a year ago

I also edited my original posting with corrected code

Dharma Over a year ago

I also tried changing sql query to have first column as UUID (SELECT UUID, EMPLOYEE_ID,....) etc instead of SELECT EMPLOYEE_ID, ....

rmaddy Over a year ago

Verify the value of vUUID that you bind to the query. Are you sure there really is a matching record in the database?

Dharma Over a year ago

I checked again. There was a problem in data in table, but your suggestion helped getting past the issue. Thank you..

Collectives™ on Stack Overflow

Swift 4 SQLITE query output fails with where clause

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related