Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How do I access SQLite database instance on iPhone
Storing data is one of the most important thing when we design any application. There are numerous way to store data one such way is SQLite databse.
There are multiple ways to access SQLite database on iPhone, We will be seeing the most easiest way to do so in Swift.
SQLite is a relational database management system contained in C programming library embedded to an application.
In this tutorial we will be creating one sample application which will have a text field to enter the name, we will store the name in our SQLite database and will print the same when user taps show button.
So let’s begin
Step 1 − Open Xcode -→ Single View Application -→ Let’s name is DBSqlite.
Step 2 − Let’s develop our UI, Open Main.storyboard and add one text field and two buttons as shown below.
Step 3 − Create @IBAction for both the buttons and @IBOutlet for text field and name them btnInsert, btnShowData and name respectively.
So we’ve added two buttons one for inserting the data and other for displaying it. We’ve also created one text field where we can enter the name which has to be inserted in db.
Step 4 − Let’s create our SQLite file and open the database connection.
In ViewController.swift write
Import SQLite3
Under ViewDidLoad write the following code, it will create a .sqlite file and we can print the location where it got created. We’re naming our sqlite file as “user_name.sqlite” as we are going to insert only name.
let file_URL = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false).appendingPathComponent("user_name.sqlite")Now we will be using sqlite3_open() function to open the database.
Create an object of OpaquePointer. We will be using this for operations. Create this variable above ViewDidLoad, globally.
var db: OpaquePointer?
Step 5 − Let’s create the table now, for creating the table write the below code in your viewDidLoad method.
//creating table
if sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)", nil, nil, nil) != SQLITE_OK {
let errorMsg = String(cString: sqlite3_errmsg(db)!)
print("There's error creating the table: \(errorMsg)")
}After step 5, your final code should like below.
import UIKit
import SQLite3
class ViewController: UIViewController {
var db: OpaquePointer?
@IBOutlet var name: UITextField!
override func viewDidLoad() {
super.viewDidLoad()
let file_URL = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
.appendingPathComponent("user_name.sqlite")
print (file_url) //to print the path of sqlite.
//opening the database
if sqlite3_open(file_URL.path, &db) != SQLITE_OK {
print("There's error in opening the database")
}
//create table
if sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)", nil, nil, nil) != SQLITE_OK {
let errorMsg = String(cString: sqlite3_errmsg(db)!)
print("There's error creating the table: \(errorMsg)")
}
}
@IBAction func btnInsert(_ sender: Any) {
}
@IBAction func btnShowData(_ sender: Any) {
}
}Run the above code and you should not get any error, check out the sqlite file by navigating to the location.
Step 6 − Now we will write the code to insert the data, so in the btnInsert write below code
var statement: OpaquePointer?
guard let user_name = name.text, !user_name.isEmpty else {
return
}
let query = "INSERT INTO users (name) VALUES (?)"
if sqlite3_prepare(db, query, -1, &statement, nil) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db)!)
print("error preparing insert: \(errmsg)")
return
}
if sqlite3_bind_text(statement, 1, user_name, -1, nil) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db)!)
print("failure binding name: \(errmsg)")
return
}
if sqlite3_step(statement) != SQLITE_DONE {
let errmsg = String(cString: sqlite3_errmsg(db)!)
print("failure inserting users: \(errmsg)")
returnHere we are simply writing our values from text field to our table.
Step 7 − Now we should show the data write the below code in btnShowData
let query = "SELECT * FROM users"
var statement:OpaquePointer?
if sqlite3_prepare(db, query, -1, &statement, nil) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db)!)
print("error preparing insert: \(errmsg)")
return
}
while(sqlite3_step(statement) == SQLITE_ROW) {
let name = String(cString: sqlite3_column_text(statement, 1))
print(name)
}Your final code should like below
import UIKit
import SQLite3
class ViewController: UIViewController {
var db: OpaquePointer?
@IBOutlet var name: UITextField!
override func viewDidLoad() {
super.viewDidLoad()
let file_URL = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
.appendingPathComponent("user_name.sqlite")
//opening the database
if sqlite3_open(file_URL.path, &db) != SQLITE_OK {
print("There's error in opening the database")
}
//creating table
if sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)", nil, nil, nil) != SQLITE_OK {
let errorMsg = String(cString: sqlite3_errmsg(db)!)
print("There's error creating the table: \(errorMsg)")
}
}
@IBAction func btnInsert(_ sender: Any) {
var statement: OpaquePointer?
guard let user_name = name.text, !user_name.isEmpty else {
return
}
let query = "INSERT INTO users (name) VALUES (?)"
if sqlite3_prepare(db, query, -1, &statement, nil) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db)!)
print("error preparing insert: \(errmsg)")
return
}
if sqlite3_bind_text(statement, 1, user_name, -1, nil) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db)!)
print("failure binding name: \(errmsg)")
return
}
if sqlite3_step(statement) != SQLITE_DONE {
let errmsg = String(cString: sqlite3_errmsg(db)!)
print("failure inserting users: \(errmsg)")
return
}
}
@IBAction func btnShowData(_ sender: Any) {
let query = "SELECT * FROM users"
var statement:OpaquePointer?
if sqlite3_prepare(db, query, -1, &statement, nil) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db)!)
print("error preparing insert: \(errmsg)")
return
}
while(sqlite3_step(statement) == SQLITE_ROW) {
let name = String(cString: sqlite3_column_text(statement, 1))
print(name)
}
}
}Now we will run the code and see enter the name HELLO and tap on insert. Then tap on show you will see HELLO being printed in the output in debugger area in Xcode.
803 Views
To Continue Learning Please Login