CODEWITHSUNDEEP
swiftmacossqlite
user5616998
user5616998

Reputation: 49

SQLite3 swift in macOs not getting consistent query result

func sure(_ c: UnsafePointer<UInt8>) -> String{
            return String(cString:c)
        }

func loadUsageOf(_ startDateId: String, _ endDateId: String) -> [Usage]{
            let sql = "SELECT host, date_id, loaded, switched from Usage where date_id >= ? and date_id <= ?"
            var selectStatement: OpaquePointer?
            var result: [Usage] = []
            if sqlite3_prepare_v2(db, sql, -1, &selectStatement, nil) ==  SQLITE_OK {
                sqlite3_bind_text(selectStatement, 1, startDateId, -1, nil)
                sqlite3_bind_text(selectStatement, 2, endDateId, -1, nil)
                var steps = sqlite3_step(selectStatement)
                print("Loading usage step \(steps)")
                 
                while(steps == SQLITE_ROW ){
                    let host = sure(sqlite3_column_text(selectStatement, 0))
                    let date_id = sure(sqlite3_column_text(selectStatement, 1))
                    let loaded = sqlite3_column_int64(selectStatement, 2)
                    let switched = sqlite3_column_int64(selectStatement, 3)
                    let usage = Usage(h: host, loaded: loaded, switched: switched == 0 ? nil: switched)
                    print("Loaded \(usage) on \(date_id)")
                    result.append(usage)
                    steps = sqlite3_step(selectStatement)
                    print("Loading more usages step \(steps)")
                
                }
            }
            sqlite3_finalize(selectStatement)
            return result
        }

sometime I can get all records, ( < 1% )with logs like the following

Loaded Usage: www.google.com 1604001715393 26335 on 2020-10-29
Loading more usages step 100
Loaded Usage: msnbc.com 1604001875392 5 on 2020-10-29
Loading more usages step 100
Loaded Usage: www.msnbc.com 1604001880601 8 on 2020-10-29
Loading more usages step 100
Loaded Usage: msnbc.com 1604001888772 1 on 2020-10-29
Loading more usages step 100
Loaded Usage: www.msnbc.com 1604001889921 26160 on 2020-10-29
Loading more usages step 100
Loaded Usage: www.google.com 1604002174049 3 on 2020-10-29
Loading more usages step 100
Loaded Usage: sqlite.org 1604002177776 25873 on 2020-10-29
Loading more usages step 100
Loaded Usage: www.google.com 1604002510167 27 on 2020-10-29
Loading more usages step 100
Loaded Usage: stackoverflow.com 1604002537420 15 on 2020-10-29
Loading more usages step 100
Loaded Usage: www.google.com 1604002553312 25497 on 2020-10-29
Loading more usages step 101
Views: [{"host":"cnn.com","time":6},{"host":"msnbc.com","time":6},{"host":"stackoverflow.com","time":15},{"host":"www.cnn.com","time":421},{"host":"sqlite.org","time":25873},{"host":"www.msnbc.com","time":26168},{"host":"fakenews.com","time":26776},{"host":"www.google.com","time":51862}]

but most of time, I only get one row, with following logs

Loading usage step 100
Loaded Usage: fakenews.com 1604001274360 26786 on 2020-10-29
Loading more usages step **101**
Views: [{"host":"fakenews.com","time":26786}]

seems getting SQLITE_DONE(101) instead of SQLITE_ROW(100) after the first sqlite3_step, so will not loop.

any clue ?

Upvotes: 0

Views: 81

Answers (1)

user5616998
user5616998

Reputation: 49

seems it will work fine if add an order by, root cause for why it is not working without order by is not clear.

Upvotes: 1

Related Questions