Question 212205 in IOS Swift Question posted in
The official documentation can be found here.

Ios swift – Multiple SQLite CREATE TABLE statements: only first one executes

I’m running some SQLite commands at the launch of an iOS app to prepare database tables. The whole statement runs without returning an error.
When I try to insert data into second or third tables, I get this SQLite error: "No such table: Images".
After inspecting the local database, only the first table was created. My SQL statement is as below. Entire statement runs perfectly in sqliteonline.com and all tables are created. What could be the problem running on iOS?

static func CreateDatabase() -> Bool {
    let cmd = """
CREATE TABLE IF NOT EXISTS Conversations(Id TEXT PRIMARY KEY UNIQUE NOT NULL, InsertDate REAL NOT NULL, LastMsgDate REAL NOT NULL, Name TEXT NULL, AvatarId INTEGER NOT NULL, Unread INTEGER NOT NULL DEFAULT '0', LastMsg TEXT NULL, Flags INTEGER NULL, ReservedText TEXT NULL, ReservedInt INTEGER NULL);
CREATE TABLE IF NOT EXISTS Communities(Id INTEGER NOT NULL, UbietyId INTEGER NOT NULL, Name TEXT NOT NULL, Distance REAL NOT NULL, InsertDate REAL NOT NULL, Photo BLOB NULL, ReservedText TEXT NULL, ReservedInt INTEGER NULL, PRIMARY KEY (Id, UbietyId) ON CONFLICT REPLACE);
CREATE TABLE IF NOT EXISTS Images(Id TEXT PRIMARY KEY UNIQUE NOT NULL, InsertDate REAL NOT NULL, ExpiryDate REAL NULL, Data BLOB NOT NULL, ReservedText TEXT NULL, ReservedInt INTEGER NULL);
"""
    var pointer: OpaquePointer?
    guard sqlite3_prepare_v2(db, cmd, -1, &pointer, nil) == SQLITE_OK else {
        print(String(cString: sqlite3_errmsg(db)))
        return false }
    
    guard sqlite3_step(pointer) == SQLITE_DONE else {
        print(String(cString: sqlite3_errmsg(db)))
        return false }
    sqlite3_finalize(pointer)
    return true
}
Tags:

2

Answers


  1. 0

    Most likely, your particular iOS SQLite driver does not permit multiple statements executing in a single prepared statement API call. The fix here would be to just use separate statements for each CREATE TABLE call.

    Actually, your driver is doing the right thing, because allowing multiple statements in a single call possibly opens the door to security problems such as SQL injection.

    Login or Signup to reply.
  2. 0

    You only use sqlite3_prepare_v2 and sqlite3_step with single SELECT, INSERT, UPDATE, and DELETE statements.

    For something like your CREATE statements, you need to use sqlite3_exec.

    static func CreateDatabase() -> Bool {
    let cmd = """
CREATE TABLE IF NOT EXISTS Conversations(Id TEXT PRIMARY KEY UNIQUE NOT NULL, InsertDate REAL NOT NULL, LastMsgDate REAL NOT NULL, Name TEXT NULL, AvatarId INTEGER NOT NULL, Unread INTEGER NOT NULL DEFAULT '0', LastMsg TEXT NULL, Flags INTEGER NULL, ReservedText TEXT NULL, ReservedInt INTEGER NULL);
CREATE TABLE IF NOT EXISTS Communities(Id INTEGER NOT NULL, UbietyId INTEGER NOT NULL, Name TEXT NOT NULL, Distance REAL NOT NULL, InsertDate REAL NOT NULL, Photo BLOB NULL, ReservedText TEXT NULL, ReservedInt INTEGER NULL, PRIMARY KEY (Id, UbietyId) ON CONFLICT REPLACE);
CREATE TABLE IF NOT EXISTS Images(Id TEXT PRIMARY KEY UNIQUE NOT NULL, InsertDate REAL NOT NULL, ExpiryDate REAL NULL, Data BLOB NOT NULL, ReservedText TEXT NULL, ReservedInt INTEGER NULL);
"""

let result = sqlite3_exec(db, cmd, nil, nil, nil)
if result == SQLITE3_OK {
    return true
} else {
    print(String(cString: sqlite3_errmsg(db)))
    return false
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search