iosswiftxcodesqlite

Can't get data from SQLite with Swift on Xcode for iOS


I have different problems implementing SQLite in Xcode for iOS app.

I use: Xcode 16 Swift 6.0 (SwiftUI, Foundation, SQLite3) iOS 18.0

I have SQLite.sqlite file with data, I need to reed only data from database. It can happen later that I will need to implement most famous element.

Is there any useful example how to connect, get all data, and pass to view. with search option.

What is solution here?

I have next code:

DataModel.swift

struct DataModel: Identifiable {
    let value1: Int
    let value2: Int
    let value3: String
    let value4: String
    let value5: Int
}

DBManager.swift

import Foundation
import SQLite3

class DBManager {
    var db: OpaquePointer?
    let dbName: String = "MyDB.sqlite"
    
    init() {
        openDatabase()
        closeDatabase()
        fetchAllData()
    }
    
    func openDatabase() {
       // let fileURL = try! FileManager.default
       //     .url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
       //     .appendingPathComponent(dbName)
        
       // let fileURL = URL(fileURLWithPath: NSTemporaryDirectory()).appendingPathComponent(dbName)

        let dbFile = Bundle.main.url(forResource: dbName, withExtension: nil)!
        
        if sqlite3_open(dbFile.path, &db) != SQLITE_OK {
            print("Error opening database")
        } else {
            print("Database opened successfully at \(dbFile.path)")
        }
        
    }
    
    func fetchAllData() -> [DataModel] {
        var result: [DataModel] = []
        let queryStatementString = "SELECT * FROM tableOfMyDB;"
        var queryStatement: OpaquePointer?
        
        if sqlite3_prepare_v2(db, queryStatementString, -1, &queryStatement, nil) == SQLITE_OK {
            while sqlite3_step(queryStatement) == SQLITE_ROW {
                let value1 = sqlite3_column_int(queryStatement, 0)
                let value2 = sqlite3_column_int(queryStatement, 1)
                let value3 = String(cString: sqlite3_column_text(queryStatement, 2))
                let value4 = String(cString: sqlite3_column_text(queryStatement, 3))
                let value5 = sqlite3_column_int(queryStatement, 4)
                
                result.append(DataModel(value1: Int(value1), value2: Int(value2), value3: value3, value4: value4, value5: Int(value5)))
            }
        }else {
            print("Error preparing query statement")
        }
        sqlite3_finalize(queryStatement)
        return result
    }
    
    func closeDatabase() {
        sqlite3_close(db)
    }
    
}

ViewModel.swift

import Foundation
import Combine

class DataViewModel: ObservableObject {
    @Published var data: [DataModel] = []
    @Published var searchText = ""

    private var dbManager = DBManager()

    init() {
        loadData()
    }

    func loadData() {
        data = dbManager.fetchAllData()
    }

    var filteredData: [DataModel] {
        if searchText.isEmpty {
            return data
        } else {
            return data.filter { $0.value4.contains(searchText) || $0.value3.contains(searchText) }
        }
    }
}

ContentView.swift

import SwiftUI

struct ContentView: View {
    @ObservedObject var viewModel = DataViewModel()

    var body: some View {
        VStack {
            TextField("Search...", text: $viewModel.searchText)
                .textFieldStyle(RoundedBorderTextFieldStyle())
                .padding()

            List(viewModel.filteredData) { item in
                VStack(alignment: .leading) {
                    Text("Name: \(item.value4)")
                    Text("Guide: \(item.value3)")
                }
            }
        }
        .navigationTitle("Data List")
    }
}

#Preview {
    ContentView()
}

Is there any solution for above code.

After I get answers I have next working solution:

DataModel.swift

struct DataModel: Identifiable {
    let value1: Int
    let value2: Int
    let value3: String
    let value4: String
    let value5: Int
}

DBManager.swift

import Foundation
import SQLite3

class DBManager {
    var db: OpaquePointer?
    let dbName: String = "MyDB.sqlite"
    
    init() {
        openDatabase()
        fetchAllData()
    }
    
    
    func openDatabase() {
        let dbFile = Bundle.main.url(forResource: dbName, withExtension: nil)!
        
        if sqlite3_open(dbFile.path, &db) != SQLITE_OK {
            print("Error opening database")
        } else {
            print("Database opened successfully at \(dbFile.path)")
        }
        
    }
    
    @discardableResult
    func fetchAllData() -> [DataModel] {
        var result: [DataModel] = []
        let queryStatementString = "SELECT * FROM TableOfMyDB;"
        var queryStatement: OpaquePointer?
        
        if sqlite3_prepare_v2(db, queryStatementString, -1, &queryStatement, nil) == SQLITE_OK {
            while sqlite3_step(queryStatement) == SQLITE_ROW {
                let value1 = sqlite3_column_int(queryStatement, 0)
                let value2 = sqlite3_column_int(queryStatement, 1)
                let value3 = String(cString: sqlite3_column_text(queryStatement, 2))
                let value4 = String(cString: sqlite3_column_text(queryStatement, 3))
                let value5 = sqlite3_column_int(queryStatement, 4)
                
                result.append(DataModel(value1: Int(value1), value2: Int(value2), value3: value3, value4: value4, value5: Int(value5)))
            }
        }else {
            print("Error preparing query statement")
        }
        sqlite3_finalize(queryStatement)
        return result
    }
    
    func closeDatabase() {
        sqlite3_close(db)
    }
    
    deinit {
        closeDatabase()
    }
}

ViewModel.swift

import Foundation
import Combine

class DataViewModel: ObservableObject {
    @Published var data: [DataModel] = []
    @Published var searchText = ""
    
    private var dbManager = DBManager()
    
    init() {
        loadData()
    }
    
    func loadData() {
        data = dbManager.fetchAllData()
    }
    
    var filteredData: [DataModel] {
        if searchText.isEmpty {
            return data
        } else {
            return data.filter { $0.value4(searchText) || String($0.value3).contains(searchText) }
        }
    }
}

ContentView.swift

import Foundation
import SwiftUI

struct ContentView: View {
    @ObservedObject var viewModel = DataViewModel()
    
    var body: some View {
        NavigationStack {
            
            List(viewModel.filteredData) { item in
                
                let vValue2 = String(item.value2)
                let vValue3 = item.item.value3
                let vValue4 = item.item.value4
                let vValue5 = item.item.value5
                
                
                HStack {
                    VStack (alignment: .leading) {
                        
                        if item.id_no > 10 {
                            
                            Text(vValue2)
                                .font(.headline)
                            
                        }
                        
                        Text(vValue3)
                            .font(.subheadline)

                    }
                    
                    Spacer()
                    
                    HStack {
                        
                        if vValue5 == 1 {
                            Text(vValue4)
                                .fontWeight(.heavy)
                                .fontDesign(.monospaced)
                                .foregroundStyle(Color.someColor) // change someColor to color of your choice
                                .frame(width: 50, height: 40, alignment: .leading)
                                .padding(.leading, 10)
                        } else {
                            Text(vValue4)
                                .fontWeight(.heavy)
                                .fontDesign(.monospaced)
                                .foregroundStyle(.primary)
                                .frame(width: 50, height: 40, alignment: .leading)
                                .padding(.leading, 20)
                        }
                    }
                }
                .listRowBackground(vValue5 == 1 ? Color.someColor.opacity(0.50) : Color(UIColor.systemBackground))
            }
            .navigationBarTitle("Data List")
            .searchable(text: $viewModel.searchText, placement: .automatic, prompt: "Search...")
        }
    }
}

#Preview {
    ContentView()
}

Solution

  • In DBManager.swift instead of this:

    init() {
        openDatabase()
        closeDatabase()
        fetchAllData()
    }
    

    Use this:

    init() {
        openDatabase()
        fetchAllData()
    }
    
    deinit {
        closeDatabase()
    }
    

    It leaves the database connection open until the object (DBManager) goes out of memory. I also recommend some protection against race conditions via actor isolation, NSLock, etc. Also, mark the class with final if you aren't going to inherit from it. Also (ok, I'm going to stop this time), you can mark your class ~Copyable and make the close database function consuming, but with your implementation, it will require quite a bit of work, so you don't need to do it.