let dataPath: String = "MyDB"
//var db_uninitialized: OpaquePointer? // 👈 Reference #0 -> Never used. Will fail if called.
func openDatabase() -> OpaquePointer? {
let filePath = try! FileManager.default.url ( for: .documentDirectory , in: .userDomainMask , appropriateFor: nil , create: false ).appendingPathComponent ( dataPath )
var db: OpaquePointer? = nil
if sqlite3_open ( filePath.path , &db ) != SQLITE_OK {
debugPrint ( "Cannot open DB." )
return nil
}
else {
print ( "DB successfully created." )
return db
}
}
// 👇 Reference #1 -> PRIMARY KEY column must be `unique.` `Unique` means no other rows in the column contain an equal value.
func createStockTable() {
let createTableString = """
CREATE TABLE IF NOT EXISTS Stocks (
id INTEGER PRIMARY KEY,
stockName STRING,
status INT,
imgName STRING,
prevClose DOUBLE,
curPrice DOUBLE,
yield DOUBLE,
noShares INT,
capitalization DOUBLE,
lastUpdated String
);
"""
var createTableStatement: OpaquePointer? = nil
if sqlite3_prepare_v2 ( initialized_db , createTableString , -1 , &createTableStatement , nil ) == SQLITE_OK {
if sqlite3_step ( createTableStatement ) == SQLITE_DONE {
print ( "Stock table is created successfully" )
} else {
print ( "Stock table creation failed." )
}
sqlite3_finalize ( createTableStatement )
}
sqlite3_close ( initialized_db ) // 👈 Reference #2 -> Connection lost and will need to be recreated for insertion function.
}
// 👇 Reference #3 -> extension on `OpaquePointer?` declared.
extension OpaquePointer? {
func insertStocks ( id: Int, stockName: String, status: Int, imgName: String, prevClose: Double, curPrice: Double, yield: Double, noShares: Int, capitalization: Double, lastUpdated: String) -> Bool {
let insertStatementString = "INSERT INTO Stocks (id, stockName, status, imgName, prevClose, curPrice, yield, noShares, capitalization, lastUpdated) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"
var insertStatement: OpaquePointer? = nil
if sqlite3_prepare_v2 ( self , insertStatementString , -1, &insertStatement , nil ) == SQLITE_OK {
sqlite3_bind_int ( insertStatement , 1 , Int32 ( id ) )
sqlite3_bind_text ( insertStatement , 2 , ( stockName as NSString ).utf8String , -1 , nil )
sqlite3_bind_int ( insertStatement , 3 , Int32(status))
sqlite3_bind_text ( insertStatement , 4 , ( imgName as NSString ).utf8String, -1 , nil )
sqlite3_bind_double ( insertStatement , 5 , Double ( prevClose ) )
sqlite3_bind_double ( insertStatement , 6 , Double ( curPrice ) )
sqlite3_bind_double ( insertStatement , 7 , Double ( yield ) )
sqlite3_bind_int64 ( insertStatement , 8 , Int64 ( noShares ) )
sqlite3_bind_double ( insertStatement , 9 , Double ( capitalization ) )
sqlite3_bind_text ( insertStatement , 10 , ( lastUpdated as NSString ).utf8String, -1, nil)
if sqlite3_step ( insertStatement) == SQLITE_DONE {
print("Stock Entry was created successfully")
sqlite3_finalize(insertStatement)
return true
} else {
print("Stock Entry Insert failed")
return false
}
} else {
print("INSERT Statement has failed")
return false
}
}
}
/// 👇 Reference #5 -> Change `id` input from `1` to `Int.random(in: 0...10000)` to satisfy `unique` constraint. Note this could still fail if the generated integer already exist in the `id` column.
func addStocks() {
let result = initialized_db.insertStocks ( id: Int.random(in: 0...10000), stockName: "Tulsa Motors", status: 1, imgName: "Tulsa_logo", prevClose: 125.18, curPrice: 125.18, yield: 0.025, noShares: 14357698, capitalization: .pi , lastUpdated: "2025-05-01 17:00:00")
print ( "Database insertion result: \( result )" )
}
var initialized_db = openDatabase() // 👈 Reference #6 -> Captured instance of Database connection.
createStockTable() // 👈 Reference #7 -> Connection closed at the end of function.
initialized_db = openDatabase() // 👈 Reference #8 -> Connection reestablished.
addStocks() // 👈 Reference #9 -> Dont forget to close your connection, finalize, and clean up.
If you wanted to make the id column autoincrement, like Douglas W. Palme said, you can omit it from you bind function, adjust your column indices... I would also recommend you declare it in you `creationStatement` for completeness sake.
let createTableString = """
CREATE TABLE IF NOT EXISTS Stocks (
id INTEGER PRIMARY KEY AUTOINCREMENT,
stockName STRING,
status INT,
imgName STRING,
prevClose DOUBLE,
curPrice DOUBLE,
yield DOUBLE,
noShares INT,
capitalization DOUBLE,
lastUpdated STRING
);
"""
Best regards.
